Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
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.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
Already member? Sign In
