Question

Name: SID: nment 5 Barbara borrowed $12 000.00 from the bank at 9% compounded monthly. The loan is amortized with end-of-month payments over five years. a) Calculate the interest included in the 20th payment. b) Calculate the principal repaid in the 36th payment. c) Construct a partial amortization schedule showing the details of the first two payments, the 20th payment, the 36th payment, and the last two payments. d) Calculate the totals of amount paid, interest paid, and the principal repaid. Payment Number Amount Paid Interest Paid Principal Repaid Outstanding Principal Balance $12 000.00 19 20 35 36

Answer 1

2]

a]

The interest amount in the 20th month payment is calculated using IPMT function in Excel :

rate = 9% / 12   (converting annual rate into monthly rate)

per = 20 (we are calculating the interest amount in the 20th month payment)

nper = 5*12 (5 year loan with 12 monthly payments each year)

pv = 12000 (loan amount)

IPMT is calculated to be $65.73

A1 =IPMT(9%/12,20,5*12,12000) B C D E F A ($65.73)! 11

b]

The principal amount in the 36th month payment is calculated using PPMT function in Excel :

rate = 9% / 12   (converting annual rate into monthly rate)

per = 36 (we are calculating the principal amount in the 36th month payment)

nper = 5*12 (5 year loan with 12 monthly payments each year)

pv = 12000 (loan amount)

PPMT is calculated to be $206.66

=PPMT(9%/12,36,5*12,12000) B C D F A ($206.66)

c]

Monthly loan payment is calculated using PMT function in Excel :

rate = 9% / 12   (converting annual rate into monthly rate)

nper = 5*12 (5 year loan with 12 monthly payments each year)

pv = 12000 (loan amount)

PMT is calculated to be $249.10

Interest in any month = principal outstanding at beginning of month * 9% / 12

Principal portion of monthly payment = monthly payment minus interest portion of payment

principal outstanding at end of month = principal outstanding at beginning of month minus principal portion of monthly payment

A B C D E F 1 Month Principal Principal outstanding outstanding Jat beginning Payment Interest Principal at end 1 $ 12,000.00 $ 249.10 $ 90.00 $ 159.10 $11,840.90 2 $ 11,840.90 $ 249.10 $ 88.81 $ 160.29 $11,680.61

A B C D E Principal Principal outstanding outstanding 1 Month Jat beginning Payment Interest Principal at end 20 $ 8,764.11 $ 249.10 $ 65.73 $ 183.37 $ 8,580.74 21 $ 8,580.74 $ 249.10 $ 64.36 $ 184.74 $ 8,396.00 22 $ 8,396.00 $ 249.10 $ 62.97 $186.13 $ 8,209.87 23 $ 8,209.87 $249.10 $ 61.57 $ 187.53 $ 8,022.34 24 $ 8,022.34 $ 249.10 $ 60.17 $ 188.93 $ 7,833.41 25 $ 7,833.41 $ 249.10 $ 58.75 $ 190.35 $ 7,643.06 26 $ 7,643.06 $ 249.10 $ 57.32 $ 191.78 $ 7,451.28 27 $ 7,451.28 $ 249.10 $ 55.88 $ 193.22 $ 7,258.06 28 $ 7,258.06 $ 249.10 $ 54.44 $ 194.66 $ 7,063.40 29 $ 7,063.40 $249.10 $ 52.98 $ 196.12 $ 6,867.28 30 $ 6,867.28 $ 249.10 $ 51.50 $ 197.60 $ 6,669.68 31 $ 6,669.68 $ 249.10 $ 50.02 $ 199.08 $ 6,470.60 32 $ 6,470.60 $ 249.10 $ 48.53 $ 200.57 $ 6,270.03 33 $ 6,270.03 $ 249.10 $ 47.03 $ 202.08 $ 6,067.96 34 $ 6,067.96 $ 249.10 $ 45.51 $ 203.59 $ 5,864.37 35 $ 5,864.37 $ 249.10 $ 43.98 $ 205.12 $ 5,659.25 36 $ 5,659.25 $ 249.10 $ 42.44 $ 206.66 $5,452.59

B C D E Principal Principal outstanding outstanding 1 Month Jat beginning Payment Interest Principal at end 60 59 $ 492.65 $ 249.10 $ 3.69 $ 245.41 $ 247.25 61 60 $ 247.25 $ 249.10 $ 1.85 $ 247.25 $ (0.00)

C Principal outstanding at 1 Month beginning 2 1 12000 32 =F2 Payment Interest =$H$2 =B2*9%/12 =$H$2 =B3*9%/12 Principal =C2-D2 =C3-D3 Principal outstanding at end =B2-E2 =B3-E3 =PMT(9%/12,5*12,-12000)

в Principal outstanding at 1 Month beginning 21 20 =F20 22 21 =F21 23 22 F22 24 23 =F23 =F24 26 25 27 26 =F25 =F26 =F27 Principal outstanding at Payment Interest Principal end =$H$2 =B21*9%/12 =C21-D21 =B21-E21 =$H$2 =B22*9%/12 =C22-D22 =B22-E22 =$H$2 =B23*9%/12 =C23-D23 =B23-E23 =$H$2 =B24*9%/12 =C24-D24 =B24-E24 =$H$2 =B25*9%/12 =C25-D25 =B25-E25 =$H$2 =B26*9%/12 =C26-D26 =B26-E26 =$H$2 =B27*9%/12 =C27-D27 =B27-E27 =$H$2 =B28*9%/12 =C28-D28 =B28-E28 =$H$2 =B29*9%/12 =C29-D29 =B29-E29 =$H$2 =B30*9%/12 =C30-D30 =B30-E30 =$H$2 =B31*9%/12 =C31-D31 =B31-E31 =$H$2 =B32*9%/12 =C32-D32 =B32-E32 =$H$2 =B33*9%/12 =C33-D33 =B33-E33 =$H$2 =B34*9%/12 =C34-D34 =B34-E34 =$H$2 =B35*9%/12 =C35-D35 =B35-E35 =$H$2 =B36*9%/12 =C36-D36 =B36-E36 =$H$2 =B37*9%/12 =C37-D37 =B37-E37 -F28 29 28 30 29 31 30 32 31 33 32 34 33 35 34 36 35 =F29 -F30 =F31 =F32 =F33 =F34 =F35 =F36

F Principal outstanding at 1 Month beginning =F59 61 60 =F60 Principal outstanding at Payment Interest Principal end =$H$2 =B60*9%/12 =C60-D60 =B60-E60 =$H$2 =B61*9%/12 =C61-061 =B61-E61 60 59

d]

total amount paid = monthly payment * total number of payments

total amount paid = $249.10 * 5 * 12 = $14,946.02

Interest repaid = total amount paid - loan amount

Interest repaid = $14,946.02 - $12,000 = $2,946.02

Principal repaid = loan amount =  $12,000