Step 1 Setup the problem as follows:
Interest rate per year: 5.0000%
Analysis period (months): 60
option A option B
down payment $2,000 $45,000
monthly payment $800
closing cost $500
resale value at the end $15,000 $15,000
Net present worth $33,094 $33,312
Difference in NPV = -$217,832,246
To increase the accuracy, you can multiply the difference with a large number which still should be zero (note that 0*100000 = 0).
Step 2
Run goal seek for the difference to be zero by changing the interest rate in cell "C16".
Interest rate per year: 4.7930%
Analysis period (months): 60
option A option B
down payment $2,000 $45,000
monthly payment $800
closing cost $500
resale value $15,000 $15,000
Net present worth $33,191 $33,191
Difference in NPV = $0
Note that you need to create the table with the two columns only once. It is shown here as new table just to explain the steps.
FAQ
Where is goal seek in excel:
How does the goal seek screen looks like for this example?:
