Goal Seek Car Loan

Using Goal Seek for a Real Example: a car loan to buy a car from Company T

I have been demonstrating the Goal Seek utility in Excel for quite a while now and I always start with the same example: a loan to buy a house. The information put into the model is:

• cost of the property
• deposit to be paid
• the length of the loan/mortgage
• the interest rate to be paid

the model then finds

• the total amount to be borrowed
• the monthly repayments
• the total amount to be repaid over the length of the loan
• the total interest to be paid over the length of the loan

I then use Goal Seek to ask it to, say, change the monthly repayments by way of changing the duration of the loan, the deposit, the interest rate and the total cost of the property … one at a time since Goal Seek can only cope with one change at a time.

I came across a car price list the other day for a major manufacturer and it shows

• all models of all of their vehicles for country T
• all current selling prices
• the value of a 25% deposit
• monthly repayments if the buyer wants finance, over

• 48 months
• 60 months
• 72 months

I changed my Goal Seek worksheet a little to show the amount one would borrow, depending on the deposit made and then I used this slightly revised Goal Seek model to explore this new, real, situation.

Here is a very simple photo of part of the price list: I have hidden the manufacturer and the model of vehicle … no free advertising today! The column headings are

model, list price, 25% deposit, 48 monthly payments, 60 monthly payments and 72 monthly payments

Firstly, I wanted to know what rate of interest they were charging as they don’t say. So, I asked Goal Seek to change the monthly payments cell to equal the monthly payment for 48 months as shown on their leaflet by changing the interest rate cell. click OK and within a second it told me they are charging 5.1%

I tested the calculations at 60 and 72 months just to make sure their answers were consistent with each other and they were. So then I wanted to know what the monthly payments would be at the rate of interest of 5.1% if I paid a deposit of 45% of the value of the car, rather than the 25% example they provided.

The following screenshot sets out the original Goal seek template I use and the information needed for this exercise:

Duncan Williamson

If you would like to try this Goal Seek technique, click this link to download the file goal_seek_car_loan