Calculating IRR

[jlfried]

Looking for help on a formula to calculate Internal Rate of Return, given the following information:

Initial Investment - $100
Cash at end of Year 5 - $175
IRR = ??


I've tried using the XIRR formula since years 1-4 are not specified by date range or dollar amounts, but no luck. Any suggestions?

[MrShorty]

I am not sure what the correct answer, since I am not sure exactly how one should calculate IRR here. It is also not clear to me exactly what is not working for you.

If I add a pair of dates 5 years apart and use those in the =XIRR({-100;175},{40179;42005}) It returns 11.836%. [Note that 40179 and 42005 are the serial numbers for 1 Jan 2010 and 1 Jan 2015]
If I use the RATE() function =RATE(5,0,-100,175), it returns 11.843%
If I use the IRR() function =IRR({-100;175}), I get 75% [same calculation as (175-100)/100 maybe].

They all make sense, but I am not sure which is correct. What variations have you attempted? What result are you expecting?

[jlfried]

This is close to what I was expecting for results. The rate function almost seems cleaner to use, and validates the XIRR. Very useful, thank you much!

[joeu2004]

If I add a pair of dates 5 years apart and use those in the =XIRR({-100;175},{40179;42005}) It returns 11.836%. [Note that 40179 and 42005 are the serial numbers for 1 Jan 2010 and 1 Jan 2015]
If I use the RATE() function =RATE(5,0,-100,175), it returns 11.843%
If I use the IRR() function =IRR({-100;175}), I get 75% [same calculation as (175-100)/100 maybe]. They all make sense, but I am not sure which is correct.

The following shows the correct use of IRR, as well as a more-natural way to use XIRR (adjust for regional differences) -- not to say that MrShorty's method is "wrong" or "not as good".

Please Login or Register  to view this content.

They are all correct, albeit different, based on their respective assumptions and internal limitations. Note that they are all about 11.84% when rounded.

For Excel IRR, we must show the cash flows in each year, including the zero cash flows in the interim 4 years.

The difference between XIRR and Excel IRR is, in part. due to the fact that XIRR compounds daily, whereas our use of IRR compounds annually. Also, XIRR uses the exact number of days between the two dates, which is 1826 due to the leap year (2012), whereas IRR uses the number of years (5).

There are also differences in the accuracy and termination conditions of the internal algorithms. But note that =XIRR({-100,175},{"1/1/2010","12/31/2014"}) returns about 11.8426913023%.

The difference between RATE and Excel IRR appears to be due to differences in their internal iterative calculations -- perhaps even binary floating-point differences.

The last line above shows the "exact" non-iterative calculation. RATE could calculate the exact rate when pmt is zero. But apparently, it does not.

FYI, when there is just an initial and final balance, the rate is also called the CAGR. Some say "compound annual growth rate". I prefer "compound average growth rate", since the period (annual or otherwise) depends on what 5 represents. For example, it is the same calculation for the same compound monthly growth rate over 5 months -- that is, about 11.84% per month.