Jump to content

XIRR Formula in FMP?


gurs

This topic is 6451 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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!

Link to comment
Share on other sites

  • Newbies

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6451 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
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.