In: Accounting
You borrowed $20,000 to purchase a new car. The loan was for 4 years at a nominal rate of 6% per year compunded monthly. You have been making equal monthly payments on the loan. You just made your 18th payment.
A) What is your monthly payment
B) How much of your first payment was interest? How much of your current (18th) payment is interest?
C) How much of the loan has been repaid immediately after the 18th payment?
D) Based on your answer to part c, determine the total interest paid during the first 18 payments.
Amount borrowed | $20,000 | Loan amortization table | |||||||
Rate | 0.50% | (6%/12) | Time | EMI | Interest | Principal | Beg Value | ||
NPER | 48 | (4 x 12) | 0 | 20000 | |||||
EMI(Mothly payment) | $469.70 | Ans A | 1 | $ 469.70 | $ 100.00 | $ 369.70 | $ 19,630.30 | ||
=PMT(0.5%,48,-20000) | 2 | $ 469.70 | $ 98.15 | $ 371.55 | $ 19,258.75 | ||||
3 | $ 469.70 | $ 96.29 | $ 373.41 | $ 18,885.35 | |||||
Interest in first payment | $100.00 | Ans B | 4 | $ 469.70 | $ 94.43 | $ 375.27 | $ 18,510.07 | ||
=IPMT(0.5%,1,48,-20000) | 5 | $ 469.70 | $ 92.55 | $ 377.15 | $ 18,132.92 | ||||
6 | $ 469.70 | $ 90.66 | $ 379.04 | $ 17,753.89 | |||||
Interest in 18th payment | $67.29 | 7 | $ 469.70 | $ 88.77 | $ 380.93 | $ 17,372.96 | |||
=IPMT(0.5%,18,48,-20000) | 8 | $ 469.70 | $ 86.86 | $ 382.84 | $ 16,990.12 | ||||
9 | $ 469.70 | $ 84.95 | $ 384.75 | $ 16,605.37 | |||||
loan paid till 18th payment | $ (6,945.12) | 10 | $ 469.70 | $ 83.03 | $ 386.67 | $ 16,218.70 | |||
=CUMPRINC(0.5%,48,20000,1,18,0) | 11 | $ 469.70 | $ 81.09 | $ 388.61 | $ 15,830.09 | ||||
12 | $ 469.70 | $ 79.15 | $ 390.55 | $ 15,439.54 | |||||
$6,945.12 | Ans C | 13 | $ 469.70 | $ 77.20 | $ 392.50 | $ 15,047.04 | |||
14 | $ 469.70 | $ 75.24 | $ 394.46 | $ 14,652.58 | |||||
Total interest paid till 18th payment | $ (1,509.49) | 15 | $ 469.70 | $ 73.26 | $ 396.44 | $ 14,256.14 | |||
=CUMIPMT(0.5%,48,20000,1,18,0) | 16 | $ 469.70 | $ 71.28 | $ 398.42 | $ 13,857.72 | ||||
17 | $ 469.70 | $ 69.29 | $ 400.41 | $ 13,457.31 | |||||
$1,509.49 | Ans D | 18 | $ 469.70 | $ 67.29 | $ 402.41 | $ 13,054.89 | |||
Total | $ 1,509.49 | $ 6,945.11 |