Step Instructions Points Possible 1 You are ready to start completing the loan amortization table.
Display the Loan worksheet. In cell B8, type a reference formula
to cell B1. The balance before the first payment is identical to the
loan amount. Do not type the value; use the cell reference instead.
In cell B9, subtract the principal from the beginning balance on the
previous row. Copy the formula to the range B10:B19. 5
2 Now, you will calculate the interest for the first payment. In cell C8,
calculate the interest for the first payment using the IPMT function.
Copy the function to the range C9:C19. 5
3 Now, you will calculate the principal paid.
In cell D8, calculate the principal paid for the first payment using the
PPMT function. Copy the function to the range D9:019 5
4 Rows 21-23 contain a summary section for cumulative totals after the
first year.
In cell B22, insert the CUMIPMT function that calculates the cumulative
interest after the first year. 5
5 The next summary statistic will calculate the principal paid after the first
year.
In cell B23, insert the CUMPRINC function that calculates the cumulative
principal paid after the first year. 5
6 Rows 25-28 contain a section for what-if analysis.
In cell B27, use the RATE financial function to calculate the periodic rate
using $1,400 as the monthly payment (cell B26), the NPER, and loan
amount in the original input section. 5
A B C D E F
1 Loan (PV) $300,000.00
2 APR 4.50%
3 Monthly Rate 0.38%
4 NPER 360
5 Monthly Payment $1,520.06
6
7 Payment Number Beg Balance Interest Principal
8 1
9 2
10 3
11 4
12 5
13 6
14 7
15 8
16 9
17 10
18 11
19 12
20
21 Cumulative Totals after Year 1
22 Interest
23 Principal Paid
24
25 What if the goal is $1,400 monthly payment?
26 Monthly Payment $1,400.00
27 Monthly Rate Needed