MIRR discrepency monthly vs annual

**Dewdman42** · 10-01-2008, 12:49 AM

I have come up with a discrepency between two versions of a cash flow analysis that I am trying to do with the MIRR function. In both cases, the total in flow and outflows add up the same amount. The only difference is that one is done using 4 total flows, the first being the entry, and then 3 annual cash flows. The other is done using 37 total flows, the first being entry and the other 36 being monthly flows with the mirr rate being multiplied by 12 to get an annualized rate. The only negative flow is the entry.

They add up to the same sum of inflows and outflows, but the output from MIRR is off by 2% between the two versions, and strangely enough, its the annual version that has a higher percentage than the monthly version. I would have expected the annual to be lower since the inflow for the year would have been pushed further forward in time, thus discounting the cash flow more then when cashed out earlier.

I also ran the same cash flows with the NPV function and also get two different present values using annual and monthly versions, again the annual value being HIGHER than the monthly value by a significant amount.

I would just like to understand what is causing this discrepency and why the monthly version is producing a lower MIRR rate and lower NPV than the annual version, all other things being equal. Anyone have any ideas?

**Dewdman42** · 10-01-2008, 11:09 PM

I found the answer to my own question. I will put it here for anyone in the future searching for it.

Converting the results of IRR from a monthly periodic rate to an annualized rate is not as simple as using x12. TVM effects it as well. To accurately convert from a monthly cash flow IRR rate to annualized rate, use the following formula:

annual = ((monthly+1)^12)-1

or it could also be done with the help of an excel function:

annual = EFFECT(monthly*12,12)

To convert in the opposite direction, as might be neccessary for using NPV function use the following formula to obtain a monthly periodic discount rate from the desired annual discount rate:

  monthly = ((annual+1)^(1/12))-1

or

 monthly =NOMINAL(annual,12)/12

I have verified that these produce consistent results.

Now that I know this, I am thinking I should always use one of the above formulas when converting to/from an annual interest rate for PV and other formulas that need a monthly rate provided.

**shg** · 10-01-2008, 11:58 PM

It was thoughtful of you to share your results.

Thanks, and welcome to the forum.

**Dewdman42** · 10-02-2008, 12:21 AM

Thanks a lot. I still have one tiny discrepancy. Its not a big deal for me, but it would be nice to understand the discrepancy, which very well may be misunderstanding on my part about finance theory.

When I use the above functions to convert the interest rates between annual and monthly, I get the same MIRR rate for either the annual based cash flows or monthly based cash flows. Yay?

However, when I use NPV for the two cash flows, I get different results. I have tried to use the NOMINAL function to adjust the discount rate used for the monthly cash flows, and the results are closer, but still not equal, including if I change the discount rate to be the same rate produced by MIRR analysis of the flows.

My feeling is that this is probably correct, the monthly cash flows should produce a higher NPV value(which it does), since the cash is being pulled out of the investment earlier while it has more time-value.

However, it seems to me that if that is the case, then the MIRR value between the annual and monthly flows should also be different, with the monthly IRR being higher, for the same reason.

If anyone has any insights about this, I would appreciate it. Thanks.