RATE function not returning accurate result. Or is it?

[juntjoo]

On top I have my input section for my different loan/mortgage formulas below. The way its set up each formula result should correspond to the above inputs but as you can see everything matches up except the the RATE formula.

Inside it I have it as:"=RATE(AF2,AC2*-1,AE2)"

Any ideas? Maybe it is accurate and the others or not?? But that wouldn't make sense.


Edit: sorry pic is so big. Didn't realize til afterwards and this edit mode doesn't appear to allow me to go back to the attachments.

[quekbc]

Multiply that by 12 and you get your 4%

[joeu2004]

On top I have my input section for my different loan/mortgage formulas below. The way its set up each formula result should correspond to the above inputs but as you can see everything matches up except the the RATE formula. Inside it I have it as:"=RATE(AF2,AC2*-1,AE2)"

Next time, attach an example Excel file instead of an image. Then we can see the formulas that you use.

Apparently, you calculate the number of payments in AF2 with a formula similar to:

=NPER(AD2%/12,-AC2,AE2)
or
=NPER(AD2/100/12,-AC2,AE2)

The point is: AD2 contains the number 4, which you interpret as 4%, an annual interest rate. And you divide by 12 to determine a monthly interest. Note that 4%/12 is about 0.33%, which is the same as the decimal number 0.0033.

Likewise, when you write =RATE(AF2,-AC2,AE2), the result is the monthly interest rate 0.33% or 0.0033 (approximately).

If you want the annual interest rate, you should multiply by 12, to wit:

=RATE(AF2,-AC2,AE2)*12

But that returns 4% when formatted as Percentage, or 0.04 when formatted as General or Number.

If you want the percentage in the same form as AD2, you also need to multiply by 100, to wit:

=RATE(AF2,-AC2,AE2)*12*100

formatted as General or Number. That returns 4.

PS: I recommend that you put 4% into AD2 instead of 4. That might be less confusing in the long run.

[juntjoo]

Next time, attach an example Excel file instead of an image. Then we can see the formulas that you use.

Apparently, you calculate the number of payments in AF2 with a formula similar to:

=NPER(AD2%/12,-AC2,AE2)
or
=NPER(AD2/100/12,-AC2,AE2)

The point is: AD2 contains the number 4, which you interpret as 4%, an annual interest rate. And you divide by 12 to determine a monthly interest. Note that 4%/12 is about 0.33%, which is the same as the decimal number 0.0033.

Likewise, when you write =RATE(AF2,-AC2,AE2), the result is the monthly interest rate 0.33% or 0.0033 (approximately).

If you want the annual interest rate, you should multiply by 12, to wit:

=RATE(AF2,-AC2,AE2)*12

But that returns 4% when formatted as Percentage, or 0.04 when formatted as General or Number.

If you want the percentage in the same form as AD2, you also need to multiply by 100, to wit:

=RATE(AF2,-AC2,AE2)*12*100

formatted as General or Number. That returns 4.

PS: I recommend that you put 4% into AD2 instead of 4. That might be less confusing in the long run.

Before i study your responses as this stuff is still a little complicated for me, let me just thank you all first. I'll be back if i need more help. Thanks a lot!!