How to make rate calculation

[emerywang]

Hi. I'm trying to duplicate an Excel calculation that looks like this:

=RATE(23/12,0,-C12,Z12)

It returns the interest rate per period of annuity. Couldn't find it in FMP, all the financial functions I saw calculated things based on a given interest rate.

Anybody know if FMP can do this, or how I could create the function?

Thanks.

-Emery

[zincb]

I checked the Excel documentation and this is an iterated calculation that does not depend on the number of iterations you set in the Calculation tab of the options panel. Attached is a file that does the job, but it is slower than the Excel formula, most likely because they use a more sophisticated divideby2-and-check algorithm, than the plodding iteration I used. The final answer does vary a small amount based on your initial guess, and whether it is high or low, when compared to the answer. Excel stops after 20 iterations or when successive iterations agree to within 0.0000001. The attached file "heads" for the answer and stops the moment it overshoots by one iteration.

rate.zip

[emerywang]

Wow, that's fantastic, Thanks. BTW, my finance guy says this is also known as a compound interest calculation, which he can also do on his financial calculator by pushing a button that does that. However, he says that his calculator totals aren't as accurate as the Excel ones sometimes, because Excel links up with the computer to keep track of the actual date, leap year, etc.

Does your filemaker calculation do that too? Wish Filemaker included this whole formula, seems they're a little weak on financial calculations.

Thanks again, have no idea how you figured that out.

-Emery

[zincb]

No, this quick solution assumes all periods are of equal length. If you want a leap year-enabled, date-range solution, you'll have to send me a half dozen Vente Americanos, with room for cream. Oh, heck, it could be fun...