Excel RATE Function

[gavs73]

Hi

I'm trying to use the Excel RATE function to replace an existing SQL equivilent, but I am getting different results, is anyone able to please point me towards the actual Math behind the Excel RATE function ? obviously i can see in the help section the component parts of the RATE function, but it doesn't include the actual math used by Excel.

Thanks in advance for any help.

Kind Regards
Gavin

 

[joeu2004]

is anyone able to please point me towards the actual Math behind the Excel RATE function ?

Frankly, no one can. Excel RATE is implemented with an iterative algorithm that is similar to Excel IRR. In very high-level terms (and imprecisely), it iteratively tries various rates until the "NPV" is close to zero. Actually, the formula that must become close to zero is:

(See PV function . There is a typo in the image: "pmt(..." should be "pmt*(...".)

I could provide a VBA function that even works better than the Excel implementation. But I don't know if that would be useful for your purposes.

However, you neglect to say how you would use the RATE function equivalent.

If you would use RATE with pmt=0, the formula above becomes simply pv*(1+r)^n + fv = 0, and the formula for r is simply:

r = (fv/pv)^(1/n) - 1

Note: In the math formula above, both pv and fv are positive values, not signed cash flows as Excel RATE requires.

 

[gavs73]

thank you so much for your reply, much appreciated.