Newbies rsfoley Posted August 5, 2006 Newbies Share Posted August 5, 2006 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 Link to comment Share on other sites More sharing options...

Ender Posted August 5, 2006 Share Posted August 5, 2006 (edited) 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 Link to comment Share on other sites More sharing options...

Newbies rsfoley Posted August 5, 2006 Author Newbies Share Posted August 5, 2006 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. Link to comment Share on other sites More sharing options...

Ender Posted August 6, 2006 Share Posted August 6, 2006 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. : Link to comment Share on other sites More sharing options...

Ender Posted August 6, 2006 Share Posted August 6, 2006 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. Link to comment Share on other sites More sharing options...

Newbies rsfoley Posted August 7, 2006 Author Newbies Share Posted August 7, 2006 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. Link to comment Share on other sites More sharing options...

Newbies rsfoley Posted August 7, 2006 Author Newbies Share Posted August 7, 2006 (edited) I got it to work. Thanks for all the help. Edited August 7, 2006 by Guest Link to comment Share on other sites More sharing options...

Ender Posted August 7, 2006 Share Posted August 7, 2006 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. Link to comment Share on other sites More sharing options...

Newbies rsfoley Posted August 15, 2006 Author Newbies Share Posted August 15, 2006 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. Link to comment Share on other sites More sharing options...

## Recommended Posts

This topic is

5591 days old. Please don't post here. Open a new topic instead.## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account## Sign in

Already have an account? Sign in here.

Sign In Now