# what if

#### lezawang

##### Well-known Member
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.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### mole999

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

#### joeu2004

##### Banned user
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.

Replies
4
Views
822
Replies
0
Views
757
Replies
0
Views
304
Replies
0
Views
358
Replies
14
Views
696

Threads
1,191,690
Messages
5,988,105
Members
440,126
Latest member
duque00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back