Now that we’ve established the inputs, let’s quickly recall the MIRR formula:
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:
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).
If you skipped downloading the workbook, you can just copy the code here:
Function XMIRR(cash_flow, dates, borrow_rate, refinance_rate)
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)
years = days_count / 365
XMIRR = (FV / -PV) ^ (1 / years) – 1
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!