I’ve been looking into the VBA MIRR function lately, and it’s pretty cool for financial analysis tasks. It’s tucked under the financial functions in VBA and is a nifty tool when you’re coding in Excel. Basically, this function is all about giving you the Modified Internal Rate of Return for a set of cash flows that occur at regular intervals. It’s a step up from the basic IRR, taking into account both the financing cost and the reinvestment returns. So, if you’re into the whole investment analysis gig, understanding the MIRR is a must.

But hey, don’t freak out if finance isn’t your second language. Take it from me, once you get the hang of playing with these functions in VBA, it starts to make sense. For starters, you can find it in the Microsoft 365 version of Excel, which a lot of us are already using. And trust me, even if you’re just starting with VBA, the MIRR function can be your go-to for comparing different investment options or just seeing how a potential investment could pan out over time.

Syntax

**MIRR**(ValueArray, FinanceRate, ReinvestRate)
  • ValueArray: My cash flows.
  • FinanceRate: My investment cost rate.
  • ReinvestRate: My cash reinvestment rate.

Arguments

When I’m working with calculations on cash flows, I need to consider a few elements in the mix:


  • ValueArray: This is the meat of the operation, the array where I track all the cash going in and out. I’ve got to input the costs (those are my negative values) and the returns (the positive values). It’s important to have at least one of each to make sense of it all.



  • FinanceRate: Pretty much the interest rate I’m paying on the dough I’ve used in these cash flows. This is the cost of my investment, expressed as a decimal.



  • ReinvestRate: This is the good part—how much interest I’m raking in on the cash that I’ve reinvested. It’s the return on reinvestment, again, nodded off as a decimal value.


Example

I recently put together a nifty little VBA snippet to calculate the Modified Internal Rate of Return (MIRR) for a series of cash flows. Here’s a quick rundown:

Sub example_MIRR()
    Dim cF(0 To 9) As Double
    cF(0) = -1000 ' Initial investment
    ' Next 9 years of cash returns
    cF(1) = 213.6
    cF(2) = 259.22
    cF(3) = 314.6
    cF(4) = 381.79
    cF(5) = 463.34
    cF(6) = 562.31
    cF(7) = 682.42
    cF(8) = 828.19
    cF(9) = 1005.09
    ' Finance rate at 9% and reinvest rate at 8%
    Range("A1").Value = MIRR(cF, 0.09, 0.08)
End Sub

I kicked off with an initial outlay of $1,000 (cF(0) = -1000) and plugged in the returns for the subsequent nine years. Setting the finance rate at 9% and the reinvest rate at 8%, I used the MIRR function to work its magic. The output? A satisfying 22% internal rate of return, which I displayed in cell A1. I gotta say, it’s pretty cool how a few lines of code can offer up such valuable insights into my investments.

Notes

  • IRR Function Pitfalls:
    • #DIV/0! error occurs if there’s no negative or positive value in the series.
    • VBA throws a run-time error 5 in such cases after 20 attempts.
  • IRR in Practice:
    • Essential for assessing returns over five years or other periods.
    • Helps estimate if income outweighs business start-up costs.
  • Excel’s MIRR Function:
    • Avoids pitfalls by considering financing interest and cash reinvestment.
  • Net Present Value:
    • Related to IRR, NPV calculates today’s value of forecasted income.
  • Continuous Learning:
    • Excel forums offer training courses and insights from experts.
    • A subscription benefits access to exclusive communities.

Remember, always check your worksheet carefully to prevent errors!

Similar Posts