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.

<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>
[TD="class: xl100, width: 282, colspan: 2"]Income[/TD]

[TD="class: xl87"]Annual Income[/TD]
[TD="class: xl89"] $ 50,000.00
[/TD]

[TD="class: xl87"]Monthly Income
[/TD]
[TD="class: xl90"] $ 4,166.67
[/TD]

[TD="class: xl87"]Income tax rate[/TD]
[TD="class: xl95, align: right"]6.25%[/TD]

[TD="class: xl92"]Monthly take home[/TD]
[TD="class: xl93"] $ 3,906.25 [/TD]

[TD="class: xl92"] [/TD]
[TD="class: xl93"] [/TD]

[TD="class: xl101, colspan: 2"]Fixed Expenses[/TD]

[TD="class: xl87"]Rent[/TD]
[TD="class: xl91"] $ 1,200.00 [/TD]

[TD="class: xl87"]Utilities[/TD]
[TD="class: xl94"] $ 125.00 [/TD]

[TD="class: xl92"]Total[/TD]
[TD="class: xl93"] $ 1,325.00 [/TD]

[TD="class: xl92"] [/TD]
[TD="class: xl93"] [/TD]

[TD="class: xl101, colspan: 2"]Variable Expenses[/TD]

[TD="class: xl87"]Dinning out[/TD]
[TD="class: xl89"] $ 250.00 [/TD]

[TD="class: xl87"]Entertainment[/TD]
[TD="class: xl89"] $ 150.00 [/TD]

[TD="class: xl87"]Misc[/TD]
[TD="class: xl94"] $ 150.00 [/TD]

[TD="class: xl88"]Total[/TD]
[TD="class: xl96"] $ 550.00 [/TD]

[TD="class: xl97"] [/TD]

[TD="class: xl98"]Available capital[/TD]
[TD="class: xl99"] $ 2,031.25
[/TD]

</tbody>

<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>
[TD="class: xl89, width: 247"]Purchase price[/TD]
[TD="class: xl90, width: 81"] $ 30,000.00 [/TD]

[TD="class: xl89"]Down payment[/TD]
[TD="class: xl90"] $ 5,000.00 [/TD]

[TD="class: xl89"]Amount of loan[/TD]
[TD="class: xl90"] $ 25,000.00 [/TD]

[TD="class: xl89"]Interest rate[/TD]
[TD="class: xl91, align: right"]4.00%[/TD]

[TD="class: xl89"]Periodic rate[/TD]
[TD="class: xl91, align: right"]0.33%[/TD]

[TD="class: xl89"]Total number of payments[/TD]
[TD="class: xl89, align: right"]60[/TD]

[TD="class: xl96, colspan: 2"]Loan Summary[/TD]

[TD="class: xl89"]Monthly payment[/TD]
[TD="class: xl92, align: right"]$460.41 [/TD]

[TD="class: xl89"]Total value repaid[/TD]
[TD="class: xl92, align: right"]$27,624.78 [/TD]

[TD="class: xl89"]Total interest paid[/TD]
[TD="class: xl92, align: right"]$2,624.78 [/TD]

[TD="class: xl93"]Percentage of available capital[/TD]
[TD="class: xl94, align: right"]23%[/TD]

[TD="class: xl93"]Insurance rate based on total car price[/TD]
[TD="class: xl95, align: right"]6%[/TD]

[TD="class: xl93"]Annual insurance premium[/TD]
[TD="class: xl92, align: right"]$1,500.00 [/TD]

</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