Joanne Krol wants to purchase a newer model automobile to replace her rusty 1989 car. The bank where Joanne has a checking account, US Bank, is advertising an annual interest rate of 6.75 percent for a three-year loan on used cars. By selling her old car and using some cash she has accumulated, Joanne has $3,000 available as a down payment. Under her current budget, Joanne figures that the maximum monthly loan payment she can afford is $300. She wants to find out the maximum car price she can afford and keep the monthly payment no higher than $300. She cannot alter the interest rate or the three-year term. Use the Excel Goal Seek command to figure out the highest purchase price Joanne can afford.
| Annual interest rate | 6.75% |
| Term (years) | 3 |
| Purchase price | $12,752.00 |
| Down payment | $3,000.00 |
| Loan amount | $9,752.00 |
| Monthly payment | $300.00 |
Note for cell
Payment function – what is the payment on a load
=PMT( rate, nper, pv, fv, type)
rate: interest rate 6.75% pa = 6.65/12 a month
nper: number of payments: in 3 years there are 3*12 payments
pv: the amount borrowed
fv: future vlaue; 0 since we want to pay off all the laod
type: payemtn made at start or end of month (if omitted, then end-of-month
Yo can use the PMT function and start off with a dummy loan amount.
In the B1 cell please enter 6.75% (the annual interest rate)
In the B2 cell, enter 300 (the max payment)
In the B3 cell you should enter 36 (the number of payments)
In the B4 enter a starting amount of 10000
In the B5 enter =PMT(B1/12,36,0,B4)
This results in a payment of $274.84 per month.)
Click on B5 and click on the Data tab then click on Solver. Solver will open and have a Target Cell suggested which should be B5 that you selected. In the Equal to select Value of and enter -300 (payments are minus as they are money going out). In the By Changing Cells field enter B4 and click on Solve. The solution offered should be $9752 in B4.
To the amount of $9752 add the $3000 Joanne already had, and the total that she can payout for a new car including all fees is $12752
