Getting ridiculous IRR answers

Hi all,

I'm trying to use the IRR function for monthly cashflows and I'm getting 3359.90% annualized. This seems wrong...

See cashflows and calculations in the attached workbook.

Any help would be great!

I agree, the math makes sense. The 35% is a monthly IRR however. How do I get to an annualized IRR from that? I summed up the numbers for each year and came up with an IRR of 96%. So then, my question would be how do I convert from a monthly IRR (the 36%) to an annual IRR. I had tried to do this with the following formula:

Formula:

Please Login or Register  to view this content.

Is that the correct formula? If not, what is?

Originally Posted by bgreeson

I agree, the math makes sense. The 35% is a monthly IRR however. How do I get to an annualized IRR from that? I summed up the numbers for each year and came up with an IRR of 96%. So then, my question would be how do I convert from a monthly IRR (the 36%) to an annual IRR. I had tried to do this with the following formula:

Formula:

Please Login or Register  to view this content.

Is that the correct formula? If not, what is?

Based on these cashflows, it appears that the resulting IRR is indeed correct. The main reason why I am convinced of this is because of the LARGE positive number of $3M in the 4th period of the cashflow (1/2/2016). Cashflow timing plays a very heavy role in determining IRR and NPV and as such, numbers earlier to the base date (1/11/2015) have a higher weight.

35% Monthly IRR -correct
Conversion to Annual IRR is correct as well.

Keep in mind though, that even though it does not seem to apply in this case, the IRR functions best when the cashflows either go from negative straight to positive once, or vice versa. Having cashflows cut across the $0 mark (i.e. switching between negative and positive) multiple times can result in multiple roots (i.e. multiple IRRs). In which case you will need to use the estimate.

Originally Posted by bgreeson

I agree, the math makes sense.

Although the result makes sense for the numbers given, beware of "garbage in, garbage out".

I cannot think of too many situations that generate a return of 3+ million just 3 months after an initial investment of 1+ million. That greatly influences the IRR for the 60-month cash flow series.

The effect of cash flows on the IRR drops off significantly over time. The 60-month IRR is essentially set within the first 12 months.

Originally Posted by bgreeson

The 35% is a monthly IRR however. How do I get to an annualized IRR from that? I summed up the numbers for each year and came up with an IRR of 96%. So then, my question would be how do I convert from a monthly IRR (the 36%) to an annual IRR. I had tried to do this with the following formula: =(IRR_Value+1)^12-1. Is that the correct formula?

There are two methods of annualizing the monthly IRR, both valid. Which to use depends on the purpose for calculating the IRR. Sometimes the method is dictated by law or industry standards.

If you want an annualized IRR that is comparable to the Excel XIRR result, compounding the monthly IRR, as you did, is correct.

Sometimes, we simply multiply the monthly IRR by 12. (We could nominalize the Excel XIRR result to get a comparable number.)

The huge difference between the IRR of the simple annual net cash flows (96.38%) and
the annualized IRR of the monthly cash flows (3360% compounded or 412% nominal) is the timing of the 3+ million return just 3 months after the initial 1+ million investment.