Shelly Cashman Excel 2016 | Module 4: SAM Project 1a Camp Millowski FINANCIAL FUNCTIONS, DATA TABLES, AND AMORTIZATION SCHEDULES GETTING STARTED ? Open the file SC_EX16_4a_ FirstLastName _1

Subject:MS ExcelPrice:19.99 Bought10

Share With

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

Camp Millowski

FINANCIAL FUNCTIONS, DATA TABLES, AND AMORTIZATION SCHEDULES

GETTING STARTED

?

Open the file

SC_EX16_4a_

FirstLastName

_1.xlsx

, available for download

from the SAM website.

?

Save the file as

SC_EX16_4a_

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_4a_

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.

Justin and Kaleen Millowski have always dreamed of purchasing and running a

campground. Kaleen wants to be ready when a campground becomes available,

so she decides to start calculating how a mortgage will impact her family’s

budget on a monthly basis and over the life of the loan. She also wants to

consider how different mortgage interest rates will impact the total cost of the

campground.

Switch to the

Campground Mortgage

worksheet.

In cell D5, create a formula using the

PMT

function to determine the monthly

payments for the anticipated Campground 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.

Kaleen calculated the anticipated total cost of the campground 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 campground.

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

range A12:A13. These values are the interest rates that Kaleen 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

campground.

 

 

Shelly Cashman

Excel 2016 | Module 4: SAM Project 1a

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 Kaleen 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

Green Fill with Dark Green Text

.

5.

Kaleen 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.

Kaleen decides to add custom cell borders to the amortization schedule to make

it easier to read.

Apply custom cell borders with a

Green, Accent 6, Darker 50%

(10

th

column,

6

th

row in the Theme Colors palette) line color as described below:

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 1a

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

Campground Mortgage

worksheet easier to

select and print, Kaleen 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.

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

accidental changes to the values. However, since her assumptions about the

price of the campground, the down payment, and the mortgage interest rate

may be incorrect, 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

Campground Mortgage

worksheet without a password.

13.

Kaleen had previously hidden a worksheet containing data on other recently

purchased campgrounds in New Hampshire. Now she wants to compare the

data in that worksheet with the data she just calculated.

Unhide

the

Campground Research

worksheet.

14.

Switch to the

Campground Research

worksheet.

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

interest associated with the mortgage. First

multiply

the value in cell

B6

(the

number of terms) by the value in cell

B7

(the number of monthly payments)

and by

12

(to convert the yearly terms to monthly terms), and then

subtract

the value in cell

B4

(the total loan amount).

Copy the formula in cell B8 into the range C8:E8.

15.

Kaleen would like to be able to see the remaining balance of the campground

mortgage at the end of the current year.

In cell B11, create a formula using the

PV

function to determine the outstanding

balance of the campground mortgage at the end of the current year using the

parameters below:

a.

For the rate parameter, use the value in cell

B5

(the yearly interest rate of

the mortgage)

divided by 12

.

b.

For the nper parameter,

subtract

the value in cell

B10

(the current year

of the mortgage) from the value in cell

B6

(the total number of years of

the mortgage), and

multiply

that by

12

.

 

 

Shelly Cashman

Excel 2016 | Module 4: SAM Project 1a

c.

For the pmt parameter, use the value in cell

B7

(the monthly payments),

putting a

negative sign

before this value to make the outcome of the PV

function positive.

Copy the formula from cell B11 to the range C11:E11.

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: Campground Mortgage Worksheet

Final Figure 2: Campground Research Worksheet