Excel Exercise 3

CS 102

Feb. 19, 2001

Have you ever seen those new car advertisements on television? You know, the ones where you have to decide between a big rebate and a low interest rate. I bet you've wondered which decision would give the lowest monthly payment. Now's your chance to find out, by making use of the ``what if'' capacity of a spreadsheet.

Create the spreadsheet shown on the next page. Note that all three design components of a spreadsheet are present: Introduction, Initial Data, and Model.

Substitute your name for mine in the introduction.

The initial data assumes that the cost of the car is $15,000 (This is several thousand dollars below the average for a new car these days, as shocking as that might be!) and the dealer is offering a $2,000 rebate or a 5.0% interest rate on a five year loan. Your friendly Credit Union is offering an 11.0% loan with a five year term.

The model section uses the PMT financial function to compute the monthly payments. Here is the formula for the monthly payment for the loan from the Credit Union: 

   =PMT(B10/12, B13*12, B6-B7)
Your cell references may be slightly different from mine; adjust your formula as necessary. The PMT function takes three inputs: interest rate, number of payments, and present (initial) value of the loan. An explanation is in order here. A payment on an auto loan is made once a month. Therefore, a monthly interest rate is necessary; this is just the annual interest rate divided by 12. The total number of payments is, for the same reason, the number of years times 12. For the credit union case, the amount of the loan is the price of the car minus the rebate.

Derive a similar formula for the monthly payment for the loan from the dealer.

There are parentheses around the monthly payment numbers because that's how accountant-types like to show dollar amounts which they have to pay to someone else. The default currency format automatically generates these parentheses.

Create the spreadsheet and save it with the name CARLOAN. Type the answers to these ``what if'' questions on your spreadsheet. You will probably find it useful to develop formulas to assist you in answering each of the questions.

  1. What is the total of the monthly payments for each of the loans? (Hint: write a formula involving the monthly payment, the term of the loan, and a constant number.) Compare this to the amount borrowed. Why are the monthly payment totals so much more than the original amounts borrowed?

  2. Right now, the monthly payments are about equal. What happens if the term of the loan is increased or decreased?

  3. If the dealer offers a 4.0% loan, what must the rebate be so that the monthly payments are again roughly equal (within a dollar of each other)?

E-mail a copy of your spreadsheet to Send mail to kelliher AT DOMAIN goucher.edu.


Thomas P. Kelliher
Sun Feb 18 17:24:39 EST 2001
Tom Kelliher