Finance,EXCEL PROBLEMS

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s