Having a problem using goal seek.

[Sarabrooke]

One of the steps in my homework says to "Use Goal Seek to determine the optimal purchase price based on the objective of no more than 15% of available capital in cell B20 for the monthly payment."
I read a post earlier concerning this exact same question and I tried to follow the hints that were left but it kept saying that the cell must contain a value. Can someone explain to me what I may be doing wrong. I even tried deleting the formulas and just typing the numbers in and it was wrong.

 

[sheetspread]

The cell should contain a formula

 

[Sarabrooke]

The cell should contain a formula

Yeah I undid deleting the formulas so they are still there. I don't understand what I'm doing wrong.

 

[sheetspread]

If I recall correctly, there was an error by the publisher. Can you post your spreadsheet?

 

[Sarabrooke]

It won't let me post attachments on here. Will it help if I post the numbers and the formulas?

 

[sheetspread]

Yes

 

[Sarabrooke]

Will this help? I just decided to post some of the information from the spreadsheet and the formulas too.

Income
Annual Income	$ 50,000.00
Monthly Income	$ 4,166.67
Income tax rate	6.25%
Monthly take home	$ 3,906.25
Fixed Expenses
Rent	$ 1,200.00
Utilities	$ 125.00
Total	$ 1,325.00
Variable Expenses
Dinning out	$ 250.00
Entertainment	$ 150.00
Misc	$ 150.00
Total	$ 550.00
Available capital	$ 2,031.25

<colgroup><col style="mso-width-source:userset;mso-width-alt:7350;width:151pt" width="201"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> </colgroup><tbody>
</tbody>

Purchase price	$ 30,000.00
Down payment	$ 5,000.00
Amount of loan	$ 25,000.00
Interest rate	4.00%
Periodic rate	0.33%
Total number of payments	60
Loan Summary
Monthly payment	$460.41
Total value repaid	$27,624.78
Total interest paid	$2,624.78
Percentage of available capital	23%
Insurance rate based on total car price	6%
Annual insurance premium	$1,500.00

<colgroup><col style="mso-width-source:userset;mso-width-alt:9033;width:185pt" width="247"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> </colgroup><tbody>
</tbody>

The formula used to find the available capital is = B7(monthly take home)-B12 (Fixed expenses total)-B18 (the variable expenses total)
The formula for the purchase price is the amount of loan-the down payment

 

[sheetspread]

With goal seek I got $16,544 for the loan plus $5000 (assuming a flat down payment) so the total is $21,544.

 

[sheetspread]

As for what I said above, "set" should be a formula, "by changing" should be a value. What exactly is in the monthly payment cell?

 

[Sarabrooke]

The monthly payment cell's formula is =PMT(E8,E9,-E6). E8 is 0.33%, E9 is 60, and E6 is $25,000.00