Calculation Problem

[rsfoley]

I have just started using FM. I am currently developing a database which requires a Internal Rate of Return (IRR)calculation. I have searched high and low and cannot seem to find a plug-in or solution that may provide this calculation.

Can anyone help point me in the right direction?

Thanks

[Ender]

There's no built-in IRR function in FileMaker. Reading up on IRR formulas, it looks like you'd need a recursive custom function (only available in FM7 Developer or FM8 Advanced) or perhaps a recursive script. Here's why:

...the IRR is the discount rate which sets the NPV of the given cash flows made at the given times to zero.

In general there is no closed-form solution for IRR. One must find it iteratively. In other words, pick a value for IRR. Plug it into the NPV calculation. See how close to zero the NPV is. Based on that, pick a different IRR value and repeat until the NPV is as close to zero as you care.

From: http://invest-faq.com/articles/analy-int-rate-return.html

If you need help with a script for this, it would help to know what the structure is of your payments (are they in a repeating field, a portal, or what).

Edited August 5, 2006 by Guest

[rsfoley]

Thanks for the response.

Here, is some detail on what I am trying to calculate. This database is being developed for real estate evaluation purposes and this calculation is trying to return the IRR on the initial cash investment on the property, inflows from rental cash inflows and net proceeds on sale.

Essentially, the sample cash flows go something like this:

Year 1 Beginning $-80,000

Year 1 End $ 5,000

Year 2 End $ 5,250

Year 3 End $ 5,513

Year 4 End $ 5,788

Year 5 End $ 96,078 (90,000 + 6,078)

Each cash flow is calculated in seperate fields within the database.

The last year includes the $80,000 initial investment + capital appreciation of $10,000 and the additional Cash Flow of $6,078.

The cash flows after year 1 are increasing by 5% each year for Growth. I have calculated IRR per Excel at 8.94%.

Thanks again.

[Ender]

Well, here's a start on this problem. The NPV is calculated by:

Payment1 / (1 + IRR) +

Payment2 / (1 + IRR)^2 +

Payment3 / (1 + IRR)^3 +

Payment4 / (1 + IRR)^4 +

Payment5 / (1 + IRR)^5 -

Initial Payment

Where the IRR is some number in the range 0 to 1.

The general algorithm for figuring out the IRR is then a looping script that progressively narrows down the search range until the NPV is sufficiently close to zero. Unfortunately, I don't have the brain power tonight to work out the details on that range-narrowing algorithm. :

[Ender]

Here you go. It's kind of a binary search algorithm, where at each iteration, the search range is cut in half. It stops when NVP <= gIRRPrecision or if the number of iterations exceeds 100 (keeps it from going into an infinite loop when the IRR can't be found).

Set Field [ gIRRLow, 0 ] 

Set Field [ gIRRHigh, 1 ] 

Set Field [ gIteration, 1 ] 

Set Field [ IRR, gIRRHigh ] 

Loop 

 Set Field [ IRR, (gIRRLow + gIRRHigh)/2 ] 

 Exit Loop If [ Abs(NPV) <= gIRRPrecision or gIteration > 100 ] 

 If [ Abs((Payment1 / (1 + gIRRLow) + 

  Payment2 / (1 + gIRRLow)^2 + 

  Payment3 / (1 + gIRRLow)^3 + 

  Payment4 / (1 + gIRRLow)^4 + 

  Payment5 / (1 + gIRRLow)^5 - 

  InitialPayment) + NPV) 

  < 

  Abs((Payment1 / (1 + gIRRHigh) + 

  Payment2 / (1 + gIRRHigh)^2 + 

  Payment3 / (1 + gIRRHigh)^3 + 

  Payment4 / (1 + gIRRHigh)^4 + 

  Payment5 / (1 + gIRRHigh)^5 - 

  InitialPayment) + NPV) ] 

   Set Field [ gIRRHigh, IRR ] 

  Else 

   Set Field [ gIRRLow, IRR ] 

 End If 

 Set Field [ gIteration, gIteration + 1 ] 

End Loop 

In this case, a few globals are used for holding temp values as the algorithm progresses (gIRRHigh, gIRRLow, gIteration). The NPV field is that calc that I gave earlier. The IRR field is a regular number field that gets set by this script. You can set the gIRRPrecision field to 1 to start, and adjust it from there if you need more precision.

[rsfoley]

That's great. Thanks for the help.

I just need to figure out how to use some of the items you used in the script.

For some reason my Set Field script does not want to allow me to setup for the first line. I'll figure it out though.

Thanks again.

[rsfoley]

I got it to work.

Thanks for all the help.

Edited August 7, 2006 by Guest

[Ender]

You might need to check the application help on how to use Set Field[]. In this case, gIRRLow is the target field, and 0 is the calc.

[rsfoley]

Looking for some additional help on this IRR calculation.

I have an example where I have values for a 5, 10, 15 & 20 year IRR's. The 5 & 10 work, but the 15 and 20 year IRR's do not compute. The calculation seems to get stuck and exits the loop before NPV gets to 0.

The final cash flow at each 5 year increment is current year cash flow + 175,000. In the 15 Year example below it is 12,268 + 175,000.

Here are the numbers:

-145000

9960

10109

12261

10415

10571

10730

10891

11054

11220

11388

11559

11732

11908

12087

187268

In Excel, IRR is 8.22% but in the database using the formula above IRR rounds to 12.5% and NPV is -43,894 rather than 0.

Thanks for anyone who can provide a suggestion. This is the last calculation I need to complete the database.