what if

[lezawang]

Hi
I want to use what-if to find how many months i need to pay for a load. I had no problem to use what-if to find the interest rate if i pay certain amount/month. but what-if did not work to find out how many months i need to pay if the monthly payment was for example 400. here is my table

load	20000
term	60
rate	0.04
payment	=pmt(b3/12,b2,b1)

<tbody>
</tbody>

What-if (goal seek):

Set cell = $B$4
To value: -400
By changing cell =$B$2

but i got strange number -2E+07

Thank you.

 

[mole999]

The strange number may be down to the width of the column, or you could consider setting a maximum of decimals to display

 

[joeu2004]

Hi
I want to use what-if to find how many months i need to pay for a load.
[....]

load	20000
term	60
rate	0.04
payment	=pmt(b3/12,b2,b1)

<tbody>
</tbody>

What-if (goal seek):
Set cell = $B$4
To value: -400
By changing cell =$B$2
but i got strange number -2E+07

First, you do not need to use what-if operations (Goal Seek or Solver). You can calculate it directly with the following formula:

=NPER(B3/12, -400, B1)

It returns about 54.7875772559356.

Second, when I do use Goal Seek, I get about 54.7875770521179 if B2 is 60 initially.

(I get about 54.7875656153471 if B2 is 1 initially. Note that B2 cannot be zero or empty initially because that causes PMT to return an error.)

Small differences arise because of limitations in the Goal Seek algorithm.