I am trying to replicate the XIRR function from Excel in FMP. Basically, I have a database that has different cash flow entries for a given security, and the cash flows do not occur at regular intervals. I need to know what the internal rate of return is for this series of cash flows.

So, for example, one record might indicate that I paid $20 for 2 shares of IBM on Jan 1, the next that I got a $1 dividend on May 30, the next that I sold 1 share for $12 on October 14 and the last that I sold the other share for $8 on December 12. What was the internal rate of return for these cash flows? In Excel, it's derived with the XIRR function.

Is there a way to do this in FMP? If not, is there a way to have FMP automatically outsource the calculation (either in real-time or periodically) to Excel?

Thanks for the help!

Come on experts, don't leave me hanging! Pretty please! I know there's a FMP guru out there whose brain is far bigger than mine with an answer to this one. Where fore art thou, big-brained FMP Guru?

It may help if you provide the logic for your IRR function. It may require a recursive solution, or maybe it'll be easy to translate into a FileMaker calc.

It may help if you provide the logic for your IRR function. It may require a recursive solution, or maybe it'll be easy to translate into a FileMaker calc.

XIRR returns a number in the form of a percentage. That number is the interest rate by which a series of non-periodic cash flows must discounted in order to have the net present value (NPV) of that stream equal zero. It is similar to the IRR function, except that with IRR the cash flows must occur at periodic intervals (e.g., monthly, quarterly, etc.). Both IRR and XIRR are recursive calculations.

Here is the explanation of the XIRR function from the Excel help file: Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.

So for the example I gave in my first post regarding the shares of IBM, here is what the Excel setup would look like

Cash_Flow_______Date

______-20_______1-Jan

________1_______30-May

_______12_______14-Oct

________8_______12-Dec

XIRR = 6.1%

XIRR Formula is XIRR(A2:A5,B2:B5), assuming “Cash Flow” is in cell A1

I have earned a 6.1% return on invested capital. Or, put another way, if my required rate of return on this investment is 6.1%, the net present value of this investment is zero, since I did not earn anything in excess of my required rate of return.

I would actually settle for an IRR function, but would really like XIRR. Does this give you enough information? What other details can I provide?

Thanks for your help!

Maybe this thread from last month will be of some use:

http://www.fmforums.com/forum/showtopic.php?tid/179258/

I give a scripted algorithm for determining the IRR. It apparently has some flaws, but I don't have the inclination to debug it.