what if behaviour

[lezawang]

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.

 

[DRSteele]

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:

	A	B	C
1	loan	40,000.00	40,000.00
2	term	60	60
3	rate	4%	17.27%
4	payment	-736.66	-1,000.00

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet55​

Worksheet Formulas Cell Formula C3 =RATE(C2,C4,C1)*12 B4 =PMT(B3/12,B2,B1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[joeu2004]

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.