Homework answers / question archive / Shelly Cashman Excel 2016 | Module 4: SAM Project 1b PT Associates FINANCIAL FUNCTIONS, DATA TABLES, AND AMORTIZATION SCHEDULES   GETTING STARTED ? Open the file SC_EX16_4b_ FirstLastName _1

Shelly Cashman Excel 2016 | Module 4: SAM Project 1b PT Associates FINANCIAL FUNCTIONS, DATA TABLES, AND AMORTIZATION SCHEDULES   GETTING STARTED ? Open the file SC_EX16_4b_ FirstLastName _1

MS Excel

Share With

---

Shelly Cashman Excel 2016 | Module 4: SAM Project 1b

PT Associates

FINANCIAL FUNCTIONS, DATA TABLES, AND AMORTIZATION SCHEDULES

 

GETTING STARTED

?

Open the file

SC_EX16_4b_

FirstLastName

_1.xlsx

, available for download

from the SAM website.

?

Save the file as

SC_EX16_4b_

FirstLastName

_2.xlsx

by changing the “1” to a

“2”.

o

If you do not see the

.xlsx

file extension

in the Save As dialog box, do not

type it. The program will add the file extension for you automatically.

?

With the file

SC_EX16_4b_

FirstLastName

_2.xlsx

still open, ensure that your

first and last name is displayed in cell B6 of the Documentation sheet.

o

If cell B6 does not display your name, delete the file and download a new

copy from the SAM website.

PROJECT STEP

1.

Hannah Lee would like to purchase a clinic suite in an medical office

condominum where she has been working as a physical therapist for the past

12 years. She has been speaking to loan officers at credit unions and banks and

will analyze the monthly cost of mortgage rates as well as review the overall

impact this purchase would have on her budget.

Switch to the

Clinic Mortgage

worksheet.

In cell D5, create a formula using the

PMT

function to determine the monthly

payments for the anticipated Clinic mortgage, using the defined names

Rate

,

Term_Years

, and

Loan_Amount

as the rate, nper, and pv arguments in the

formula.

a.

Put a

negative sign

before the PMT function to make the formula return a

positive value.

b.

In the function,

Rate

should be

divided

by

12

to calculate the monthly

interest rate, and

Term_Years

should be

multiplied

by

12

to calculate

the total number of monthly payments.

2.

Hannah calculated the anticipated total cost of the clinic using the mortgage

interest rate she expects to qualify for. She now wants to determine how

different interest rates could impact the total cost of the clinic.

Select the range A12:A26 and fill it with a percent series based on the values in

range A12:A13. The values are the interest rates that Hannah will analyze in the

Varying Interest Rate Schedule.

3.

Create a single variable data table to determine the impact that the variable

interest rates (in the range A12:A22) will have on the total cost of the clinic.

 

Shelly Cashman

Excel 2016 | Module 4: SAM Project 1b

a.

In cell B11, create a formula without using a function that references cell

D5

(the monthly payments).

b.

In cell C11, create a formula without using a function that references cell

D6

(the total interest paid on the loan).

c.

In cell D11, create a formula without using a function that references cell

D7

(the total cost of the mortgage).

d.

Select the range A11:D26 and create a single-variable data table, using an

absolute

reference

to cell

D3

(the mortgage interest rate) as the

Column input cell.

4.

To help Hannah identify how each rate in her Variable Interest Rate Schedule

compares to the interest rate she anticipates on her mortgage, she decides to

highlight the matching interest rate in the schedule with a conditional

formatting rule.

Apply a Highlight Cells conditional formatting rule to the range A12:A26 that

formats any cell in the range that is equal to the value in cell

D3

(using an

absolute

reference to cell D3) with

Yellow Fill with Dark Yellow Text

.

5.

Hannah now wishes to finalize the Amortization schedule.

In cell J4, create a formula without using a function that

subtracts

the value in

cell

I4

from the value in cell

H4

to determine how much of the mortgage

principal is being paid off each year.

Copy the formula in cell J4 to the range J5:J18.

6.

In cell K4, create a formula using the

IF

function to calculate the interest paid

on the mortgage (or the difference between the total payments made each year

and the total amount of mortgage principal paid each year).

a.

The formula should first check if the value in cell

H4

(the balance

remaining on the loan each year) is

greater than 0

.

b.

If the value in cell H4 is greater than 0, the formula should return the

value in

J4

subtracted from

the value in cell

D5

multiplied by 12

. Use

a

relative

cell reference to cell J4 and an

absolute

cell reference to cell

D5. (

Hint:

Use 12*$D$5–J4 as the

is_true

argument value in the formula.)

c.

If the value in cell H4 is not greater than 0, the formula should return a

value of

0

.

Copy the formula from cell K4 into the range K5:K18.

7.

Apply the

Accounting

number format with

two

decimal places and

$

as the

symbol to the range K4:K18.

8.

In cell K20, create a formula without using a function that references the

defined name

Down_Payment

.

9.

Hannah decides to add custom cell borders to the amortization schedule to

make it easier to read.

Apply custom cell borders with a

Blue, Accent 5, Darker 50%

(9

th

column, 6

th

row in the Theme Colors palette) line color as described on the next page:

a.

Add an

Outline

border with a

Medium

border style (2

nd

column, 5

th

row)

to the range G3:K21.

 

Shelly Cashman

Excel 2016 | Module 4: SAM Project 1b

b.

Add a

Vertical Line

border with a

Light

border style (1

st

column, 7

th

row)

to the range G3:K21.

c.

Add a

Top

border with a

Light

border style (1

st

column, 7

th

row) to the

range G4:K4.

d.

Add a

Bottom

border with a

Light

border style (1

st

column, 7

th

row) to the

range G18:K18.

10.

To make the various elements of the

Clinic Mortgage

worksheet easier to select

and print, Hannah wants to add custom names to ranges in the worksheet.

a.

Apply the custom name

Mortgage_Payment

to the range A2:D7.

b.

Apply the custom name

Interest_Rate_Schedule

to the range A9:D26.

c.

Apply the custom name

Amortization_Schedule

to the range G2:K21.

11.

Assign names to the cells in the range D5:D7 by selecting the range C5:D7 and

creating names from the selection using the values in the Left column as the

defined names.

12.

Hannah wishes to protect the worksheet, so that she doesn’t make any

accidental changes to the values. However, since she made assumptions about

the price of the clinic, the down payment, and the mortgage interest rate, she

wants to be able to update these values in the protected worksheet.

a.

Select and unlock the range B5:B6.

b.

Select and unlock cell D3.

c.

Protect the

Clinic Mortgage

worksheet without a password.

13.

Hannah had previously hidden a worksheet containing data on her other

financial obligations. Now she wants to review the data in that worksheet along

with the data she just calculated.

Unhide

the

Outstanding Loans

worksheet.

14.

Switch to the

Outstanding Loans

worksheet.

In cell B9, create a formula without using a function that determines the total

interest associated with the loan. First

multiply

the value in cell

B7

(the

number of years) by the value in cell

B8

(the monthly payment) and by

12

(to

convert the yearly terms to monthly terms), and then

subtract

the value in cell

B5

(the loan amount).

Copy the formula without the formatting in cell B9 into the range C9:E9.

15.

Hannah would like to be able to see the remaining balance of her other loans at

the end of the current year.

In cell B12, create a formula using the

PV

function to determine the outstanding

balance of the loans at the end of the current year using the parameters below:

a.

For the rate parameter, use the value in cell

B6

(the yearly interest rate of

the mortgage)

divided by 12

.

b.

For the nper parameter,

subtract

the value in cell

B11

(the current year

of the loan) from the value in cell

B7

(the total number of years of the

loan), and

multiply

that by

12

.

 

Shelly Cashman

Excel 2016 | Module 4: SAM Project 1b

c.

For the pmt parameter, use the value in cell

B8

(the monthly payment),

putting a

negative sign

before this value to make the outcome of the PV

function positive.

Copy the formula without the formatting from cell B12 to the range C12:E12.

Your workbook should look like the Final Figures below. Save your changes, close the

workbook, and then exit Excel. Follow the directions on the SAM website to submit your

completed project.

Final Figure 1: Clinic Mortgage Worksheet

Final Figure 2: Outstanding Loans Worksheet