Question

You want to buy a new sports coupe for $68,500, and the finance office at the dealership has quoted you a loan with an APR of 5.9 for 60 months to buy the car. What will your monthly payments be? What is the effective annual rate on this loan? Construct a loan amortization table in Excel to show how the loan is repaid.

Answer 1

EMI = Loan / PVAF (r%, n)

= $ 68,500 / PVAF (0.4917%, 60)

= $ 68,500 / 51.8497

= $ 1321.13

PVAF = [ 1 - (1+r)^-n ] / r

= [ 1 - (1+0.004917)^-60 ] / 0.004917

= [ 1 - (1.004917)^-60 ] / 0.004917

= [ 1 - 0.7451] / 0.004917

= 0.2549 / 0.004917

= 51.8497

Effective rate = (1+r)ⁿ - 1

= (1+0.004917)¹² - 1

=(1.004917)¹² - 1

= 1.0606 - 1

= 0.0606 i.e 6.06%

Loan Amortization Table: