# what if behaviour

#### lezawang

##### Well-known Member
Hello
I am trying to use what-if to calculate the rate if the payment is known. In this example if the interest rate is 4% then the payment is -736.66. Now I want to know what is the interest rate if the payment is -1000, What-If gave me the answer which is 17%. I thought if the payment is higher then the interest rate should be low. Am I right?

 loan 40000 term 60 rate 4% payment =pmt(A3/12,A2,A1)

<tbody>
</tbody>

Any help would be very much appreciated.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### DRSteele

##### Well-known Member
The periodic payments in an annuity include both principal and interest; the higher the payments, the higher the implied interest rate, and vice versa.

It's not necessary to use GoalSeek. You can simply use the other financial functions to calculate the inherent implied Rate given the Payment, Loan and Term. Like this:

ABC
1loan40,000.0040,000.00
2term6060
3rate4%17.27%
4payment-736.66-1,000.00

</tbody>
Sheet55

Worksheet Formulas
CellFormula
C3=RATE(C2,C4,C1)*12
B4=PMT(B3/12,B2,B1)

</tbody>

<tbody>
</tbody>

#### joeu2004

##### Banned user
In this example if the interest rate is 4% then the payment is -736.66. Now I want to know what is the interest rate if the payment is -1000, What-If gave me the answer which is 17%. I thought if the payment is higher then the interest rate should be low. Am I right?

Obviously not. Perhaps this will you help you understand conceptually. In your example, you will pay back the principal of 40,000 over 60 months in either case. With 60 payments of 736.66, the total payment is 44,199.60, which is 40,000 principal plus 4199.60 total interest. With 60 payments of 1000, the total payment is 60,000, which is 40,000 plus 20,000 total interest. More total interest means a higher interest rate.

Last edited:

Replies
0
Views
836
Replies
1
Views
1K
Replies
0
Views
1K
Replies
3
Views
2K
Replies
0
Views
358

1,195,924
Messages
6,012,331
Members
441,690
Latest member
CyberWrek

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

### Which adblocker are you using?

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

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