 Despite the arguable superiority of the Modified Investment Rate of Return (MIRR) in analyzing investments or capital budgeting decisions (a topic for a different time), the MIRR formula is the black sheep of the time-value-of-money family… and it would appear that Excel’s development team would agree!

While Excel provides timing-sensitive formulas for the IRR and NPV functions through the XNPV and XIRR respectively, there is no such functionality for the MIRR. An accurate, easy to use =XMIRR formula has not existed… so we made one!

You can download it below, copy it and go, or you can go through this post as we provide you a step-by-step tutorial of how to go about manually creating the function, and then creating your own User-Defined Function!

[emaillocker id=”3743″]

[/emaillocker]

In order to use function in another workbook, make sure that the Developer tab is enabled, head into the XMIRR.xlsm file, open the XMIRR module, and then just copy and paste into a worksheet module of your choosing!

Calculating XMIRR Manually

The underlying logic we used in the VBA formula is the same as our manual calculation – we wanted to do something manual in order to walk you right through the calculation. VBA isn’t quite as easy to understand.

If we want to calculate the XMIRR from “scratch”, we’ll have to take a few steps.

First, let’s take a look at the MIRR formula. $\large MIRR = (\frac{FV_{Pos. CFs}*(Reinvest Rate)}{PV_{Neg. CFs}*(Borrowing Rate)})^{1/n}-1$

The MIRR isn’t entirely difficult to compute. The key problem is in the accurate treatment of “n”, or the “period” for each series of cash flows. In Excel’s MIRR formula, each additional cell in a set of cash flows represents one year (“n”). Our task, is to make sure that our formula accurately reflects the changes in “n”.

Step 1: Setting Up the Problem The inputs in the earlier section provide everything we need for an XMIRR setup: we have a series of cash flows, their respective dates, a borrowing rate (also called the financing rate), and a re-investment rate. The rates are shown further below.

Take note! Period 5 is only 3 months rather than 12. We’ve done this to make sure that our formula is date-flexible. The checks at the top help confirm this.

Before we can calculate the present and future values of the cash flows, we need to figure out two things:

1. The # of days in each period from the respective beginning and end of the analysis period. This is shown in two lines: one line counts the number of days from the beginning of the analysis, and the second counts the number of days left in the analysis starting from the total and subtracting down. These are used for PV and FV calculations respectively.
2. We need a means to isolate cash outflows and cash inflows as we are doing a present value calculation for the outflows, and a future value equation for the inflows. We do this by using logical statements that result in binary TRUE/FALSE switches. This sets us up perfectly for a SUMPRODUCT formula when we perform the time value calculations.

Step 2: Time Value of Money Calculations Now that we’re all set up, we need to create the respective borrowing and re-investment factors.

In order to do so, the borrowing rate and re-investment rate need to be converted from annual rates to daily rates. Be careful! Your first thought might be to just divide by 365. This is incorrect because we are dealing with time value equations, and those are exponential. The in order to convert annual rates to daily rates, we the formula (1+r)^(1/365)-1. If you intended to convert the rates to monthly, 365 would be replaced with 12.

Now, let’s recall our time value of money equations: $\large FV=PV*(1+r)^{n}$

With a little bit of algebra, we get $\large PV=\frac{FV}{(1+r)^{n}}$.

When we create factors for the Borrowing rate and the Re-investment rate, we use these exact same formulas, except that the respective PV/FV input being multiplied is set to 1. From here, we can see the exponential impact time has on an investment at our chosen rates.

Once the periodic factors have been computed, the SUMPRODUCT formula makes time value calculations a breeze. We “sum the product” of the factors with their respective TRUE/FALSE switches, and the series of cash flows for the whole investment. The TRUE/FALSE statements act as 1’s and 0’s so that when we calculation the PV of outflows (negative cash flows), it only acts on the cash flow items which are actually negative. The same is true for the FV, and inflows.

Last of all, we’ll just find the total number of days by subtracting the end of the analysis period from the beginning of the analysis period, and then divide that by 365 days to make it a yearly figure.

Step 3: The Outputs Now that we’ve established the inputs, let’s quickly recall the MIRR formula: $\large MIRR = (\frac{FV_{Pos. CFs}*(Reinvest Rate)}{PV_{Neg. CFs}*(Borrowing Rate)})^{1/n}-1$

We have everything we need to compute the MIRR. We’ll need to multiply our PV of negative cash flows by -1 to give us a positive number, but the calculation is quite straight forward: (FV/PV)^(1/365)-1.

The formula almost looks familiar doesn’t it? Well, it should! The MIRR formula is also the same as the CAGR formula: $\large CAGR=(\frac{FV}{PV})^{\frac{1}{n}}-1$

Coding the VBA-based “User-Defined Function”

As you’ll soon see, programming a function in VBA isn’t a whole lot different than running through a simple calculative model. You start with a set of inputs, apply them through a series of equations, and obtain an output (or, for some crazy array formulas which we use for debt sizing, multiple outputs).

[emaillocker id=”3744″]

Function XMIRR(cash_flow, dates, borrow_rate, refinance_rate)

‘declare variables

Dim num, i As Integer
Dim days_count, days_count1 As Long
Dim daily_borrow, daily_refinance As Double
Dim borrow_factor, refinance_factor, years, PV, FV As Single

num = cash_flow.Count

daily_borrow = (1 + borrow_rate) ^ (1 / 365) – 1
daily_refinance = (1 + refinance_rate) ^ (1 / 365) – 1

‘ MsgBox daily_borrow, daily_finance

PV = 0
FV = 0

For i = 1 To num

days_count = dates(i) – dates(1)
days_count1 = dates(num) – dates(i)

borrow_factor = 1 / (1 + daily_borrow) ^ days_count
refinance_factor = (1 + daily_refinance) ^ days_count1

If cash_flow(i) <= 0 Then PV = PV + borrow_factor * cash_flow(i)
If cash_flow(i) > 0 Then FV = FV + refinance_factor * cash_flow(i)

Next i

years = days_count / 365

XMIRR = (FV / -PV) ^ (1 / years) – 1
End Function

[/emaillocker]

Step 1 – Setting Up & Efficient Coding

We’re going to start off with Option Explicit – this forces us to define our variables. This is best coding practice for a couple of reasons: it saves memory by telling Excel exactly what sort of variable it should be looking for, but more importantly, it helps with debugging code. Without declaring all of your variables up front, debugging can be a nightmare; its also nice to give a good thought about exactly what you need in order to keep your math efficient.

Step 2 – Find the # of Cash Flow Periods

Then we use the .count function to compute the number of periods (“num”) in which cash flows occur.

Step 3 – Converting from Annual to Monthly

Just as we did in the manual calculation earlier, we’ll take our borrowing rate and re-investment rate, and convert them from annual figures into daily figures by using (1+r)^(1/365)-1 formula. Remember, the inputs for this function are annual figures.

Step 4 – Completing the Setup

We’ll complete the setup by setting we’ll also go ahead and set PV and FV equal to 0. This is more of a safety measure – we want Excel to reset this each time it calculates the formula, or else the numbers will go all wonky.

Step 5 – The For… Next Loop

If you’ve never done a for… next loop in Excel, this is probably the trickiest part to understand of the whole set of code. Once you wrap your head around it though, you’ll see that its fairly simple. It works by taking a variable (in our case, “i”), and performing a set of calculations for given number “i” represents; this loop repeats until the number you tell it to stop.

In the loop, we are going to use “i” as the cash flow period being calculated. We begin by setting it to 1, and end it with the number of periods defined earlier using the .count function (the “num” variable). If you walked through the earlier part of the tutorial where we calculated MIRR manually, the rest of this section should look familiar!

The days_count expressions create the values that each period use for either PV or FV calculations. From there, we create the borrowing and re-investment factors. These formulas should look familiar – they are exactly the same as those in the workbook.

As the last part of the loop, we’ll calculate cash flows. The only difference between the worksheet equations and the ones we use here are that rather than using TRUE/FALSE switches, we’re just going to use an IF formula to determine whether cash flows are positive or negative, and apply FV and PV formulas to them respectively.

Now that we’ve completed our calculations, we end the loop by saying “Next i” – the loop will continually calculate the PV and FV for the periods in the range from 1 to “num”, and add them together as it goes along.

Step 6 – Completing the Function

Well, we’ve done all of the hard work. Now we’ll just take our days_count, and divide it by 365 to annualize everything, and fill in the MIRR formula (being careful to multiple PV of the negative cash flows by -1 to adjust appropriately).

That about wraps it up! In this tutorial, we’ve tried to be as clear as we could about how to derive the XMIRR in Excel itself, and then how to apply the same logic in the Visual Basic Editor. Hopefully you enjoyed our first post, and will stay tuned for more great content to come!