Question

wity. Amortization schedule Excel Online Structured Activity: Amortization schedule The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. X Open spreadshee a. Complete an amortization schedule for a $30,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 12% compounded annually. Round all answers to the nearest cent Ending Beginning Balance Repayment of Prindpal Year Payment Interest Balance b. What percentage of the payment represents interest and what percentage represents principal for each of the three years? Round all answers to two decimal places. Interest % Principal Year 1: Year 2: c. Why do these percentages change over time? 1. These percentages change over time because even though the total payment is constant the amount of interest paid each year is declining as the remaining or outstanding balance declines.

Answer 1

a]

Yearly loan payment is calculated using PMT function in Excel :

rate = 12% (annual rate of interest)

nper = 3 (3 year loan with 1 annual payment each year)

pv = 30000 (loan amount)

PMT is calculated to be $12,490.47

А1 : х v ft =PMT(12%,3,30000) B C D E A 1 | ($12,490.47)

Interest in any year = principal outstanding at beginning of year * 12%

Principal portion of yearly payment = yearly payment minus interest portion of payment

principal outstanding at end of year = principal outstanding at beginning of year minus principal portion of yearly payment

D E G H 1 Year AB Beginning balance 1 $ 30,000.00 2 $ 21,109.53 3 $ 11,152.20 Payment Interest $12,490.47 $3,600.00 $12,490.47 $2,533.14 $12,490.47 $1,338.26 $12,490.47 F Ending Balance $21,109.53 $11,152.20 $ Principal $ 8,890.47 $ 9,957.33 $11,152.20

с D E ДА В Beginning 1 Year balance 2 1 30000 32 =F2 43 =F3 Payment Interest =$H$2 =B2*12% =$H$2 =B3*12% =$H$2 =B4*12% Principal =C2-D2 =C3-D3 =C4-D4 F Ending Balance =B2-E2 =B3-E3 =B4-E4 =PMT(12%,3,30000)*-1

b]

A B 7 Year Interest 8 1 $ 3,600.00 9 2 $ 2,533.14 3 $ 1,338.26 C D Principal Payment % interest $ 8,890.47 $12,490.47 28.82% $ 9,957.33 $12,490.47 20.28% $11,152.20 $12,490.47 10.71% F % principal 71.18% 79.72% 89.29%

7 Year Jinterest 8 1 3600 9 2 2533.14 10 3 1338.26 Principal 8890.47 9957.33 11152.2 Payment 12490.47 1 2490.47 12490.47 % interest =B8/D8 =B9/09 =B10/D10 % principal =C8/D8 =C9/09 =C10/D10

c]

These percentages change over time because even though the yearly payment is constant, the amount of interest paid each year is declining as the outstanding balance declines