Question

you have reached an agreement with an auto dealer regarding a new car. She has offered you a trade-in allowance of $7 000 on your old car for a new one that she has “reluctantly” reduced to only $32 000 (before trade-in). She has further agreed upon a contract that requires you to pay $543.56 each of the next 60 months, beginning one month from today. What is your interest rate, expressed as an annual percentage? Give your answer to the nearest 0.01%. With your instructor’s approval, write a computer program or use a spreadsheet to solve the problem.

Answer 1

The Interest Rate will be 11%

the amortisation schedule is as per below table

we wil find out the interest Rate -Through the Goal Seek function on EXCEL

lets assume the interest rate is 12% p.a. - so the monthly rate will be 1%

From the monthly payment of 543.56 interest Portion @ 1 % per month on the opening Principal outstanding amount is computed and reduced to Arrive at the principal Portion. This principal is reduced from the opening Principal outstanding to arrive at the closing Principal outstanding for every month

This computation when we extrapolate to 60 months- we will get the amortisation schedule. However- the closing Balance of principal outstanding would not be zero in our amortisation table- since we had only assumed the interest rate as 12%.

This Assumed interest rate of 12 % is entered in cell A1 which is marked yellow . and this interest rate is cross referenced for the entire amortisation table as shown below

to determine the exact interest rate at which the final balance at the 60th month becomes 0, we use the Goal seek Function.

as seen from the above screen shot,   The Cell F63 which is the final loan balance at 60th month is set to a target of 0, by changing the Cell A1.

By clicking ok-- the value in cell A1 changes to 11%( which is our answer) and the loan balance at 60th month becomes zero.