Home » Uncategorized » THE MIRR EXCEL FORMULA WITH DATES (XMIRR)

Most of my 50 year career was in fixed income investments (Bonds, MBS, etc.). We did not use MIRR, but we did have concepts that had some of the same advantages. If you have been following this blog, you have seen terms such as Holding Period Return (HPR) or Horizon Analysis. My posts on bond swap analysis and MBS holding period returns are examples of these calculations. Take the bond swap analysis for example. If you are swapping one bond for another bond with a high yield to maturity, you have a problem from the start. Both yields use an IRR, so you have two reinvestment rates assumed, and you can’t assume two different reinvestment rates. You need to assume only one reinvestment rate, and that is where these alternative examples comes in.

Enough about me. As I pointed out earlier, this post is not meant to explain MIRR (you can find a lot of examples of the pros and cons of MIRR on the internet). This post is to answer a few questions about how a few adjustments can make the MIRR more useful.

You can download the Excel workbook here: MIRR

The first question I run across is “can I use MIRR for periodic cash flows that are not yearly, but are maybe monthly or quarterly? The answer is yes. Like other Excel formulas, dividing the Finance and Reinvestment rates by the number of periods in a year, and multiplying the results by the same number of periods per year, results in an equivalent APR that can be converted to an APY. See sheet “Convert MIRR to Periodic”

Next, I break down the MIRR formula to show how this formula works:

The process starts by breaking down the cash flows into two columns. In the first column are the negative cash flows and the other column, positive cash flows. Take the Present Value (PV) of the negative cash flows and the Future Value (FV) of the positive cash flows.
Then divide the FV by the PV and covert the results to APR and APY. See sheet “MIRR”

First, you have the input cells in yellow. The example assumes the periodic cash flow is monthly.

The cash flows are entered in the yellow cells (the numbers will automatically adjust to fit the cash flows.

Next is are the calculations to get the APR and APY:

I called the last sheet “XMIRR”. Excel doesn’t have an XMIRR formula, but this is a work around for cash flows with dates.

After showing that the PV and FV calculations on sheets “Convert MIRR to Periodic” and “MIRR”   would result in the same APY as the MIRR Excel function (12.310%), I needed to convert a few things to prove that my XMIRR calculations would result in the same APY and APR.
1. I used dates which were all 30 days apart.
2. I needed an option in the PV and FV formulas to use a 360 day base year (XIRR assumes actual/365)
3. Both Finance and Reinvestment rates on the “MIRR” sheet were converted from APY to APR in the PV and FV formulas by choosing monthly periods. For this sheet, I needed to convert those APRs back to Annual Equivalents.
The two cells (I2 and I3) are dropdown boxes. If you want to use this XMIRR calculator, I would change Monthly to Annual and cell I3 to 365 in order to be consistent with the XIRR Excel function.


You can download the Excel workbook here: MIRR

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*