Assignment#1 Loan Amortization

Bill has $25,000 in an investment account earning 6 percent per year. Bill decides to

purchase a new car with a sticker price of $25,000. The car dealer offers Bill either

$2,000 cash back or 2% financing for 5 years. If Bill takes the financing, he will make

60 equal monthly payments. Otherwise, he will pay $23,000 today for the car.

(a) Calculate the monthly payment required if Bill agrees to the sticker price of $25,000 and finances the car at 2% per year.

(b) Prepare the amortization schedule for the loan using an Excel spreadsheet.

(c) Prepare a graph in Excel that shows the portion of each payment that goes to interest and the portion that goes to principal.

(d) Based on the time value of money, should Bill take the $2,000 cash back or the 2%financing? Clearly explain and defend your choice