MIRR Function

Returns a Double specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts).

Syntax

MIRR(values(), finance_rate, reinvest_rate)

Parameters

values() (Required) - Array of Double specifying cash flow values. The array must contain at least one negative value (payment) and one positive value (receipt).
finance_rate (Required) - Double specifying the interest rate paid as the cost of financing.
reinvest_rate (Required) - Double specifying the interest rate received on gains from cash reinvestment.

Return Value

Returns a Variant (Double) representing the modified internal rate of return, expressed as a decimal per period. For example, 0.1 represents 10%.

Remarks

The Modified Internal Rate of Return (MIRR) is a variation of the internal rate of return (IRR) that addresses some of IRR's limitations. Unlike IRR, MIRR assumes: - Negative cash flows (investments) are financed at the finance_rate - Positive cash flows (returns) are reinvested at the reinvest_rate

This makes MIRR more realistic than IRR for most real-world investment scenarios where the cost of capital and reinvestment rate differ.

Key Characteristics:

Returns a rate per period (e.g., if values are monthly cash flows, result is monthly rate)
To convert to annual percentage: multiply by periods per year and by 100
Values must include at least one positive and one negative value
Values are assumed to occur at regular intervals (end of each period)
First value occurs at end of first period (not at time zero like NPV)
Error 5 (Invalid procedure call) if values array contains no positive or no negative values
Finance_rate and reinvest_rate should be expressed as decimals (e.g., 0.1 for 10%)

MIRR vs IRR:

IRR assumes all cash flows are reinvested at the IRR itself (often unrealistic)
MIRR allows separate rates for financing costs and reinvestment gains (more realistic)
MIRR is generally more stable and easier to interpret than IRR
MIRR always returns a single value, while IRR can have multiple solutions

When to Use:

Evaluating capital investments with different borrowing and reinvestment rates
Comparing mutually exclusive projects with different cash flow patterns
Analysis of real estate investments, business projects, or equipment purchases
Any scenario where the cost of capital differs from the expected return on reinvestment
When you need a more conservative and realistic return measure than IRR

Typical Uses

Capital Budgeting - Evaluate investment projects with realistic rate assumptions
Real Estate Analysis - Calculate returns on property investments
Equipment Purchase Decisions - Assess whether to buy or lease equipment
Business Valuation - Determine value of business investments
Portfolio Management - Evaluate investment performance with actual reinvestment rates
Loan vs Investment Comparison - Compare cost of financing to investment returns
Project Ranking - Rank multiple projects by profitability
Sensitivity Analysis - Test how changes in rates affect investment viability

Basic Examples

' Example 1: Simple investment analysis
Dim cashFlows(4) As Double
cashFlows(0) = -100000  ' Initial investment
cashFlows(1) = 30000    ' Year 1 return
cashFlows(2) = 35000    ' Year 2 return
cashFlows(3) = 40000    ' Year 3 return
cashFlows(4) = 25000    ' Year 4 return

Dim financeRate As Double
Dim reinvestRate As Double
Dim result As Double

financeRate = 0.08   ' 8% cost of capital
reinvestRate = 0.05  ' 5% reinvestment rate
result = MIRR(cashFlows(), financeRate, reinvestRate)
' Result is approximately 0.1065 (10.65% annual return)

' Example 2: Real estate investment
Dim propertyFlows(9) As Double
Dim i As Integer

propertyFlows(0) = -500000  ' Purchase price
For i = 1 To 8
propertyFlows(i) = 50000  ' Annual rental income
Next i
propertyFlows(9) = 600000   ' Sale price in year 9

Dim mortgageRate As Double
Dim marketRate As Double

mortgageRate = 0.06   ' 6% mortgage cost
marketRate = 0.04     ' 4% market return

If MIRR(propertyFlows(), mortgageRate, marketRate) > mortgageRate Then
MsgBox "Property investment beats financing cost"
End If

' Example 3: Monthly cash flows (convert to annual)
Dim monthlyCashFlows(11) As Double
monthlyCashFlows(0) = -10000
' ... populate remaining months

Dim monthlyFinanceRate As Double
Dim monthlyReinvestRate As Double

monthlyFinanceRate = 0.08 / 12    ' Annual rate / 12
monthlyReinvestRate = 0.05 / 12   ' Annual rate / 12

Dim monthlyReturn As Double
Dim annualReturn As Double

monthlyReturn = MIRR(monthlyCashFlows(), monthlyFinanceRate, monthlyReinvestRate)
annualReturn = (1 + monthlyReturn) ^ 12 - 1  ' Convert to annual

' Example 4: Comparing two projects
Dim projectA(3) As Double
Dim projectB(3) As Double

projectA(0) = -50000: projectA(1) = 20000
projectA(2) = 25000: projectA(3) = 30000

projectB(0) = -50000: projectB(1) = 15000
projectB(2) = 20000: projectB(3) = 40000

Dim costOfCapital As Double
Dim reinvestmentRate As Double

costOfCapital = 0.1
reinvestmentRate = 0.06

Dim mirrA As Double, mirrB As Double
mirrA = MIRR(projectA(), costOfCapital, reinvestmentRate)
mirrB = MIRR(projectB(), costOfCapital, reinvestmentRate)

If mirrA > mirrB Then
Debug.Print "Project A has better MIRR"
Else
Debug.Print "Project B has better MIRR"
End If

Common Patterns

' Pattern 1: Safe MIRR calculation with validation
Function SafeMIRR(cashFlows() As Double, finRate As Double, _
reinvRate As Double) As Variant
Dim hasPositive As Boolean
Dim hasNegative As Boolean
Dim i As Integer

' Validate array has both positive and negative values
For i = LBound(cashFlows) To UBound(cashFlows)
If cashFlows(i) > 0 Then hasPositive = True
If cashFlows(i) < 0 Then hasNegative = True
Next i

If Not hasPositive Or Not hasNegative Then
SafeMIRR = Null
Exit Function
End If

On Error Resume Next
SafeMIRR = MIRR(cashFlows(), finRate, reinvRate)
If Err.Number <> 0 Then
SafeMIRR = Null
End If
On Error GoTo 0
End Function

' Pattern 2: Convert MIRR to annualized percentage
Function AnnualizedMIRR(cashFlows() As Double, finRate As Double, _
reinvRate As Double, periodsPerYear As Integer) As Double
Dim periodMIRR As Double
periodMIRR = MIRR(cashFlows(), finRate, reinvRate)

' Convert to effective annual rate
AnnualizedMIRR = ((1 + periodMIRR) ^ periodsPerYear - 1) * 100
End Function

' Pattern 3: MIRR-based investment decision
Function ShouldInvest(cashFlows() As Double, finRate As Double, _
reinvRate As Double, hurdle As Double) As Boolean
Dim projectMIRR As Double
projectMIRR = MIRR(cashFlows(), finRate, reinvRate)
ShouldInvest = (projectMIRR >= hurdle)
End Function

' Pattern 4: Sensitivity analysis on rates
Sub AnalyzeMIRRSensitivity(cashFlows() As Double)
Dim finRate As Double
Dim reinvRate As Double
Dim result As Double

Debug.Print "Finance Rate", "Reinvest Rate", "MIRR"

For finRate = 0.05 To 0.15 Step 0.01
For reinvRate = 0.03 To 0.1 Step 0.01
result = MIRR(cashFlows(), finRate, reinvRate)
Debug.Print Format(finRate, "0.00%"), _
Format(reinvRate, "0.00%"), _
Format(result, "0.00%")
Next reinvRate
Next finRate
End Sub

' Pattern 5: Break-even analysis
Function BreakEvenFinanceRate(cashFlows() As Double, _
targetMIRR As Double, _
reinvRate As Double) As Double
Dim lowRate As Double, highRate As Double
Dim midRate As Double, testMIRR As Double
Dim tolerance As Double

lowRate = 0
highRate = 1
tolerance = 0.0001

' Binary search for break-even rate
Do While (highRate - lowRate) > tolerance
midRate = (lowRate + highRate) / 2
testMIRR = MIRR(cashFlows(), midRate, reinvRate)

If testMIRR > targetMIRR Then
lowRate = midRate
Else
highRate = midRate
End If
Loop

BreakEvenFinanceRate = midRate
End Function

' Pattern 6: Compare MIRR to IRR
Sub CompareMIRRtoIRR(cashFlows() As Double, finRate As Double, reinvRate As Double)
Dim irrValue As Double
Dim mirrValue As Double

irrValue = IRR(cashFlows())
mirrValue = MIRR(cashFlows(), finRate, reinvRate)

Debug.Print "IRR: " & Format(irrValue * 100, "0.00") & "%"
Debug.Print "MIRR: " & Format(mirrValue * 100, "0.00") & "%"
Debug.Print "Difference: " & Format((irrValue - mirrValue) * 100, "0.00") & "%"
End Sub

' Pattern 7: Multi-year project evaluation
Function EvaluateMultiYearProject(initialInvestment As Double, _
annualReturns() As Double, _
salvageValue As Double, _
finRate As Double, _
reinvRate As Double) As String
Dim cashFlows() As Double
Dim i As Integer
Dim years As Integer

years = UBound(annualReturns) - LBound(annualReturns) + 1
ReDim cashFlows(0 To years)

cashFlows(0) = -initialInvestment
For i = LBound(annualReturns) To UBound(annualReturns)
cashFlows(i - LBound(annualReturns) + 1) = annualReturns(i)
Next i
cashFlows(years) = cashFlows(years) + salvageValue

Dim projectMIRR As Double
projectMIRR = MIRR(cashFlows(), finRate, reinvRate)

EvaluateMultiYearProject = "Project MIRR: " & _
Format(projectMIRR * 100, "0.00") & "%"
End Function

' Pattern 8: Ranking multiple investments
Type Investment
Name As String
CashFlows() As Double
MIRR As Double
End Type

Function RankInvestments(investments() As Investment, _
finRate As Double, _
reinvRate As Double) As Investment()
Dim i As Integer, j As Integer
Dim temp As Investment

' Calculate MIRR for each investment
For i = LBound(investments) To UBound(investments)
investments(i).MIRR = MIRR(investments(i).CashFlows(), finRate, reinvRate)
Next i

' Bubble sort by MIRR (descending)
For i = LBound(investments) To UBound(investments) - 1
For j = i + 1 To UBound(investments)
If investments(j).MIRR > investments(i).MIRR Then
temp = investments(i)
investments(i) = investments(j)
investments(j) = temp
End If
Next j
Next i

RankInvestments = investments
End Function

' Pattern 9: Net Present Value equivalent using MIRR
Function MIRRtoNPV(initialInvestment As Double, mirrRate As Double, _
periods As Integer) As Double
' Convert MIRR back to equivalent NPV
' Useful for comparing MIRR-based and NPV-based analyses
MIRRtoNPV = initialInvestment * ((1 + mirrRate) ^ periods)
End Function

' Pattern 10: Quarterly to annual MIRR conversion
Function QuarterlyToAnnualMIRR(quarterlyCashFlows() As Double, _
quarterlyFinRate As Double, _
quarterlyReinvRate As Double) As Double
Dim quarterlyMIRR As Double

quarterlyMIRR = MIRR(quarterlyCashFlows(), quarterlyFinRate, quarterlyReinvRate)

' Convert quarterly rate to effective annual rate
QuarterlyToAnnualMIRR = (1 + quarterlyMIRR) ^ 4 - 1
End Function

Advanced Usage

Example 1: Investment Analysis Class

' Class: InvestmentAnalyzer
' Provides comprehensive investment analysis using MIRR

Option Explicit

Private m_cashFlows() As Double
Private m_financeRate As Double
Private m_reinvestRate As Double
Private m_periods As Integer

Public Sub Initialize(cashFlows() As Double, finRate As Double, reinvRate As Double)
Dim i As Integer
m_periods = UBound(cashFlows) - LBound(cashFlows) + 1
ReDim m_cashFlows(0 To m_periods - 1)

For i = 0 To m_periods - 1
m_cashFlows(i) = cashFlows(LBound(cashFlows) + i)
Next i

m_financeRate = finRate
m_reinvestRate = reinvRate
End Sub

Public Function GetMIRR() As Double
GetMIRR = MIRR(m_cashFlows(), m_financeRate, m_reinvestRate)
End Function

Public Function GetIRR() As Double
GetIRR = IRR(m_cashFlows())
End Function

Public Function GetNPV(discountRate As Double) As Double
GetNPV = NPV(discountRate, m_cashFlows())
End Function

Public Function GetPaybackPeriod() As Integer
Dim cumulative As Double
Dim i As Integer

cumulative = 0
For i = 0 To m_periods - 1
cumulative = cumulative + m_cashFlows(i)
If cumulative >= 0 Then
GetPaybackPeriod = i + 1
Exit Function
End If
Next i

GetPaybackPeriod = -1 ' Never pays back
End Function

Public Function GenerateReport() As String
Dim report As String
report = "Investment Analysis Report" & vbCrLf
report = report & String(40, "-") & vbCrLf
report = report & "Periods: " & m_periods & vbCrLf
report = report & "Finance Rate: " & Format(m_financeRate * 100, "0.00") & "%" & vbCrLf
report = report & "Reinvest Rate: " & Format(m_reinvestRate * 100, "0.00") & "%" & vbCrLf
report = report & vbCrLf
report = report & "MIRR: " & Format(GetMIRR() * 100, "0.00") & "%" & vbCrLf
report = report & "IRR: " & Format(GetIRR() * 100, "0.00") & "%" & vbCrLf
report = report & "NPV @ Finance Rate: " & _
Format(GetNPV(m_financeRate), "$#,##0.00") & vbCrLf
report = report & "Payback Period: " & GetPaybackPeriod() & " periods" & vbCrLf

GenerateReport = report
End Function

Public Function IsViable(hurdleRate As Double) As Boolean
IsViable = (GetMIRR() >= hurdleRate)
End Function

Example 2: Real Estate Investment Calculator

' Class: RealEstateInvestment
' Specialized calculator for real estate MIRR analysis

Option Explicit

Private m_purchasePrice As Double
Private m_downPayment As Double
Private m_annualRent As Double
Private m_holdingPeriod As Integer
Private m_appreciationRate As Double
Private m_mortgageRate As Double
Private m_reinvestmentRate As Double

Public Sub ConfigureInvestment(purchasePrice As Double, downPayment As Double, _
annualRent As Double, holdingYears As Integer, _
appreciation As Double)
m_purchasePrice = purchasePrice
m_downPayment = downPayment
m_annualRent = annualRent
m_holdingPeriod = holdingYears
m_appreciationRate = appreciation
End Sub

Public Sub ConfigureRates(mortgageRate As Double, reinvestmentRate As Double)
m_mortgageRate = mortgageRate
m_reinvestmentRate = reinvestmentRate
End Sub

Public Function CalculateMIRR() As Double
Dim cashFlows() As Double
Dim i As Integer
Dim salePrice As Double

ReDim cashFlows(0 To m_holdingPeriod)

' Initial investment (down payment)
cashFlows(0) = -m_downPayment

' Annual rental income
For i = 1 To m_holdingPeriod - 1
cashFlows(i) = m_annualRent
Next i

' Final year: rent + sale proceeds
salePrice = m_purchasePrice * ((1 + m_appreciationRate) ^ m_holdingPeriod)
cashFlows(m_holdingPeriod) = m_annualRent + (salePrice - (m_purchasePrice - m_downPayment))

CalculateMIRR = MIRR(cashFlows(), m_mortgageRate, m_reinvestmentRate)
End Function

Public Function GetAnnualizedReturn() As String
Dim mirrValue As Double
mirrValue = CalculateMIRR()
GetAnnualizedReturn = Format(mirrValue * 100, "0.00") & "% per year"
End Function

Public Function CompareToStocks(stockMarketReturn As Double) As String
Dim propertyReturn As Double
propertyReturn = CalculateMIRR()

If propertyReturn > stockMarketReturn Then
CompareToStocks = "Property investment outperforms stocks by " & _
Format((propertyReturn - stockMarketReturn) * 100, "0.00") & "%"
Else
CompareToStocks = "Stocks outperform property by " & _
Format((stockMarketReturn - propertyReturn) * 100, "0.00") & "%"
End If
End Function

Example 3: Project Portfolio Optimizer

' Module: PortfolioOptimizer
' Selects optimal mix of projects given budget constraint

Option Explicit

Type Project
ID As String
Name As String
InitialCost As Double
CashFlows() As Double
MIRR As Double
Selected As Boolean
End Type

Function OptimizePortfolio(projects() As Project, budget As Double, _
finRate As Double, reinvRate As Double) As Project()
Dim i As Integer, j As Integer
Dim totalCost As Double
Dim temp As Project

' Calculate MIRR for each project
For i = LBound(projects) To UBound(projects)
projects(i).MIRR = MIRR(projects(i).CashFlows(), finRate, reinvRate)
projects(i).Selected = False
Next i

' Sort by MIRR descending (greedy approach)
For i = LBound(projects) To UBound(projects) - 1
For j = i + 1 To UBound(projects)
If projects(j).MIRR > projects(i).MIRR Then
temp = projects(i)
projects(i) = projects(j)
projects(j) = temp
End If
Next j
Next i

' Select projects until budget exhausted
totalCost = 0
For i = LBound(projects) To UBound(projects)
If totalCost + projects(i).InitialCost <= budget Then
projects(i).Selected = True
totalCost = totalCost + projects(i).InitialCost
End If
Next i

OptimizePortfolio = projects
End Function

Function GetPortfolioMIRR(projects() As Project) As Double
Dim combinedFlows() As Double
Dim maxPeriods As Integer
Dim i As Integer, p As Integer

' Find maximum periods across all selected projects
For i = LBound(projects) To UBound(projects)
If projects(i).Selected Then
If UBound(projects(i).CashFlows) > maxPeriods Then
maxPeriods = UBound(projects(i).CashFlows)
End If
End If
Next i

ReDim combinedFlows(0 To maxPeriods)

' Combine cash flows from all selected projects
For i = LBound(projects) To UBound(projects)
If projects(i).Selected Then
For p = 0 To UBound(projects(i).CashFlows)
combinedFlows(p) = combinedFlows(p) + projects(i).CashFlows(p)
Next p
End If
Next i

GetPortfolioMIRR = MIRR(combinedFlows(), 0.08, 0.05) ' Example rates
End Function

Example 4: Monte Carlo Simulation with MIRR

' Module: MIRRSimulation
' Performs Monte Carlo simulation on MIRR with uncertain cash flows

Option Explicit

Function SimulateMIRR(baseCashFlows() As Double, volatility As Double, _
finRate As Double, reinvRate As Double, _
simulations As Long) As Double()
Dim results() As Double
Dim sim As Long, i As Integer
Dim simulatedFlows() As Double
Dim periods As Integer

periods = UBound(baseCashFlows) - LBound(baseCashFlows) + 1
ReDim results(1 To simulations)
ReDim simulatedFlows(0 To periods - 1)

Randomize Timer

For sim = 1 To simulations
' Generate random cash flows based on volatility
For i = 0 To periods - 1
Dim randomFactor As Double
randomFactor = 1 + (Rnd() - 0.5) * 2 * volatility
simulatedFlows(i) = baseCashFlows(i) * randomFactor
Next i

On Error Resume Next
results(sim) = MIRR(simulatedFlows(), finRate, reinvRate)
If Err.Number <> 0 Then
results(sim) = 0 ' Invalid scenario
End If
On Error GoTo 0
Next sim

SimulateMIRR = results
End Function

Function AnalyzeSimulationResults(results() As Double) As String
Dim i As Long
Dim sum As Double, sumSq As Double
Dim mean As Double, stdDev As Double
Dim count As Long

count = UBound(results) - LBound(results) + 1

For i = LBound(results) To UBound(results)
sum = sum + results(i)
sumSq = sumSq + results(i) ^ 2
Next i

mean = sum / count
stdDev = Sqr((sumSq / count) - (mean ^ 2))

Dim report As String
report = "Monte Carlo MIRR Analysis" & vbCrLf
report = report & "Simulations: " & count & vbCrLf
report = report & "Mean MIRR: " & Format(mean * 100, "0.00") & "%" & vbCrLf
report = report & "Std Dev: " & Format(stdDev * 100, "0.00") & "%" & vbCrLf
report = report & "95% Confidence Interval: " & _
Format((mean - 1.96 * stdDev) * 100, "0.00") & "% to " & _
Format((mean + 1.96 * stdDev) * 100, "0.00") & "%" & vbCrLf

AnalyzeSimulationResults = report
End Function

Error Handling

On Error Resume Next
result = MIRR(cashFlows(), finRate, reinvRate)
If Err.Number = 5 Then
MsgBox "Invalid procedure call - check that cash flows " & _
"contain both positive and negative values"
ElseIf Err.Number <> 0 Then
MsgBox "Error calculating MIRR: " & Err.Description
End If
On Error GoTo 0

Performance Considerations

MIRR calculation is iterative and can be computationally intensive for large arrays
Cache MIRR results if using the same cash flows repeatedly
For sensitivity analysis with many rate variations, consider pre-calculating once
Array size affects performance - MIRR on 1000 periods is slower than 10 periods
Consider using simplified models for real-time calculations

Best Practices

Validate inputs - Ensure array contains both positive and negative values before calling MIRR
Use realistic rates - Finance and reinvestment rates should reflect actual market conditions
Match rate periods - If cash flows are monthly, use monthly rates (annual rate / 12)
Compare to hurdle rate - Set minimum acceptable MIRR based on cost of capital
Consider risk - Higher risk projects should have higher required MIRR
Document assumptions - Clearly state finance and reinvestment rate assumptions
Use with other metrics - Combine MIRR with NPV, IRR, and payback period for complete analysis
Test sensitivity - Vary rates to see how robust the investment is to assumption changes
Account for inflation - Use real rates (adjusted for inflation) for long-term projects
Round appropriately - Display MIRR as percentage with 2 decimal places for clarity

Comparison with Other Financial Functions

Function	Purpose	Key Difference from MIRR
IRR	Internal Rate of Return	Assumes reinvestment at IRR (often unrealistic); MIRR uses separate reinvestment rate
NPV	Net Present Value	Returns dollar amount, not percentage; uses single discount rate
PV	Present Value	Works with annuities/single payments, not irregular cash flows
FV	Future Value	Forward-looking value calculation; MIRR calculates rate of return
Rate	Interest rate for annuity	For regular payments only; MIRR handles irregular cash flows

Platform Notes

Available in VBA (Excel, Access, etc.)
Not available in VBScript
Part of VBA Financial functions library
Requires at least one positive and one negative value in the array
Arrays can be 0-based or 1-based (function handles both)

Limitations

Requires at least one positive and one negative cash flow value
All cash flows must occur at regular intervals
Does not account for irregular timing between cash flows (see XIRR for that)
Result is sensitive to both finance_rate and reinvest_rate assumptions
Cannot handle multiple sign changes as robustly as some other methods
First cash flow is assumed to occur at end of first period (not time zero)

IRR - Calculate internal rate of return (assumes reinvestment at IRR)
NPV - Calculate net present value using discount rate
PV - Calculate present value of an investment
FV - Calculate future value of an investment
Rate - Calculate interest rate for an annuity
Pmt - Calculate payment for a loan/annuity
XIRR - IRR for irregular cash flow timing (Excel only)
XNPV - NPV for irregular cash flow timing (Excel only)

VB6 Parser Notes

MIRR is parsed as a regular function call (CallExpression). This module exists primarily for documentation purposes to provide comprehensive reference material for VB6 developers working with financial calculations involving modified internal rate of return analysis.