<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="VB6Parse Library Reference - mirr - Financial">
<title>mirr - Financial - VB6Parse Library Reference</title>
<link rel="stylesheet" href="../../../assets/css/style.css">
<link rel="stylesheet" href="../../../assets/css/docs-style.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/styles/github-dark.min.css">
<script src="../../../assets/js/theme-switcher.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/highlight.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/languages/vbnet.min.js"></script>
<script>hljs.highlightAll();</script>
</head>
<body>
<header class="docs-header">
<div class="container">
<h1><a href="../../../index.html">VB6Parse</a> / <a href="../../../library/index.html">Library</a> / <a href="../../../library/functions/financial/index.html">Financial</a> / mirr</h1>
<p class="tagline">VB6 Library Reference</p>
</div>
</header>
<nav class="docs-nav">
<div class="container">
<a href="../../../index.html">Home</a>
<a href="../../../library/index.html">Library Reference</a>
<a href="../../../documentation.html">Documentation</a>
<a href="https://docs.rs/vb6parse" target="_blank">API Docs</a>
<a href="https://github.com/scriptandcompile/vb6parse" target="_blank">GitHub</a>
<button id="theme-toggle" class="theme-toggle" aria-label="Toggle theme">
<span class="theme-icon">🌙</span>
</button>
</div>
</nav>
<main class="container">
<article class="library-item">
<h1 id="mirr-function">MIRR Function</h1>
<p>Returns a Double specifying the modified internal rate of return for a series of periodic
cash flows (payments and receipts).</p>
<h2 id="syntax">Syntax</h2>
<pre><code class="language-vbnet">MIRR(values(), finance_rate, reinvest_rate)</code></pre>
<h2 id="parameters">Parameters</h2>
<ul>
<li><strong><code>values()</code></strong> (Required) - Array of Double specifying cash flow values. The array must contain
at least one negative value (payment) and one positive value (receipt).</li>
<li><strong><code>finance_rate</code></strong> (Required) - Double specifying the interest rate paid as the cost of financing.</li>
<li><strong><code>reinvest_rate</code></strong> (Required) - Double specifying the interest rate received on gains from cash
reinvestment.</li>
</ul>
<h2 id="return-value">Return Value</h2>
<p>Returns a <strong>Variant (Double)</strong> representing the modified internal rate of return, expressed as
a decimal per period. For example, 0.1 represents 10%.</p>
<h2 id="remarks">Remarks</h2>
<p>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 <code>finance_rate</code>
- Positive cash flows (returns) are reinvested at the <code>reinvest_rate</code>
This makes MIRR more realistic than IRR for most real-world investment scenarios where the
cost of capital and reinvestment rate differ.</p>
<h3 id="key-characteristics">Key Characteristics:</h3>
<ul>
<li>Returns a rate per period (e.g., if values are monthly cash flows, result is monthly rate)</li>
<li>To convert to annual percentage: multiply by periods per year and by 100</li>
<li>Values must include at least one positive and one negative value</li>
<li>Values are assumed to occur at regular intervals (end of each period)</li>
<li>First value occurs at end of first period (not at time zero like NPV)</li>
<li>Error 5 (Invalid procedure call) if values array contains no positive or no negative values</li>
<li><code>Finance_rate</code> and <code>reinvest_rate</code> should be expressed as decimals (e.g., 0.1 for 10%)</li>
</ul>
<h3 id="mirr-vs-irr">MIRR vs IRR:</h3>
<ul>
<li><strong>IRR</strong> assumes all cash flows are reinvested at the IRR itself (often unrealistic)</li>
<li><strong>MIRR</strong> allows separate rates for financing costs and reinvestment gains (more realistic)</li>
<li><strong>MIRR</strong> is generally more stable and easier to interpret than IRR</li>
<li><strong>MIRR</strong> always returns a single value, while IRR can have multiple solutions</li>
</ul>
<h3 id="when-to-use">When to Use:</h3>
<ul>
<li>Evaluating capital investments with different borrowing and reinvestment rates</li>
<li>Comparing mutually exclusive projects with different cash flow patterns</li>
<li>Analysis of real estate investments, business projects, or equipment purchases</li>
<li>Any scenario where the cost of capital differs from the expected return on reinvestment</li>
<li>When you need a more conservative and realistic return measure than IRR</li>
</ul>
<h2 id="typical-uses">Typical Uses</h2>
<ol>
<li><strong>Capital Budgeting</strong> - Evaluate investment projects with realistic rate assumptions</li>
<li><strong>Real Estate Analysis</strong> - Calculate returns on property investments</li>
<li><strong>Equipment Purchase Decisions</strong> - Assess whether to buy or lease equipment</li>
<li><strong>Business Valuation</strong> - Determine value of business investments</li>
<li><strong>Portfolio Management</strong> - Evaluate investment performance with actual reinvestment rates</li>
<li><strong>Loan vs Investment Comparison</strong> - Compare cost of financing to investment returns</li>
<li><strong>Project Ranking</strong> - Rank multiple projects by profitability</li>
<li><strong>Sensitivity Analysis</strong> - Test how changes in rates affect investment viability</li>
</ol>
<h2 id="basic-examples">Basic Examples</h2>
<pre><code class="language-vbnet">' 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)</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<h2 id="common-patterns">Common Patterns</h2>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<pre><code class="language-vbnet">' 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</code></pre>
<h2 id="advanced-usage">Advanced Usage</h2>
<h3 id="example-1-investment-analysis-class">Example 1: Investment Analysis Class</h3>
<pre><code class="language-vbnet">' 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</code></pre>
<h3 id="example-2-real-estate-investment-calculator">Example 2: Real Estate Investment Calculator</h3>
<pre><code class="language-vbnet">' 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</code></pre>
<h3 id="example-3-project-portfolio-optimizer">Example 3: Project Portfolio Optimizer</h3>
<pre><code class="language-vbnet">' 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</code></pre>
<h3 id="example-4-monte-carlo-simulation-with-mirr">Example 4: Monte Carlo Simulation with MIRR</h3>
<pre><code class="language-vbnet">' 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</code></pre>
<h2 id="error-handling">Error Handling</h2>
<pre><code class="language-vbnet">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</code></pre>
<h2 id="performance-considerations">Performance Considerations</h2>
<ul>
<li>MIRR calculation is iterative and can be computationally intensive for large arrays</li>
<li>Cache MIRR results if using the same cash flows repeatedly</li>
<li>For sensitivity analysis with many rate variations, consider pre-calculating once</li>
<li>Array size affects performance - MIRR on 1000 periods is slower than 10 periods</li>
<li>Consider using simplified models for real-time calculations</li>
</ul>
<h2 id="best-practices">Best Practices</h2>
<ol>
<li><strong>Validate inputs</strong> - Ensure array contains both positive and negative values before calling MIRR</li>
<li><strong>Use realistic rates</strong> - Finance and reinvestment rates should reflect actual market conditions</li>
<li><strong>Match rate periods</strong> - If cash flows are monthly, use monthly rates (annual rate / 12)</li>
<li><strong>Compare to hurdle rate</strong> - Set minimum acceptable MIRR based on cost of capital</li>
<li><strong>Consider risk</strong> - Higher risk projects should have higher required MIRR</li>
<li><strong>Document assumptions</strong> - Clearly state finance and reinvestment rate assumptions</li>
<li><strong>Use with other metrics</strong> - Combine MIRR with NPV, IRR, and payback period for complete analysis</li>
<li><strong>Test sensitivity</strong> - Vary rates to see how robust the investment is to assumption changes</li>
<li><strong>Account for inflation</strong> - Use real rates (adjusted for inflation) for long-term projects</li>
<li><strong>Round appropriately</strong> - Display MIRR as percentage with 2 decimal places for clarity</li>
</ol>
<h2 id="comparison-with-other-financial-functions">Comparison with Other Financial Functions</h2>
<table>
<thead>
<tr>
<th>Function</th>
<th>Purpose</th>
<th>Key Difference from MIRR</th>
</tr>
</thead>
<tbody>
<tr>
<td><strong>IRR</strong></td>
<td>Internal Rate of Return</td>
<td>Assumes reinvestment at IRR (often unrealistic); MIRR uses separate reinvestment rate</td>
</tr>
<tr>
<td><strong>NPV</strong></td>
<td>Net Present Value</td>
<td>Returns dollar amount, not percentage; uses single discount rate</td>
</tr>
<tr>
<td><strong>PV</strong></td>
<td>Present Value</td>
<td>Works with annuities/single payments, not irregular cash flows</td>
</tr>
<tr>
<td><strong>FV</strong></td>
<td>Future Value</td>
<td>Forward-looking value calculation; MIRR calculates rate of return</td>
</tr>
<tr>
<td><strong>Rate</strong></td>
<td>Interest rate for annuity</td>
<td>For regular payments only; MIRR handles irregular cash flows</td>
</tr>
</tbody>
</table>
<h2 id="platform-notes">Platform Notes</h2>
<ul>
<li>Available in VBA (Excel, Access, etc.)</li>
<li>Not available in <code>VBScript</code></li>
<li>Part of VBA Financial functions library</li>
<li>Requires at least one positive and one negative value in the array</li>
<li>Arrays can be 0-based or 1-based (function handles both)</li>
</ul>
<h2 id="limitations">Limitations</h2>
<ul>
<li>Requires at least one positive and one negative cash flow value</li>
<li>All cash flows must occur at regular intervals</li>
<li>Does not account for irregular timing between cash flows (see XIRR for that)</li>
<li>Result is sensitive to both <code>finance_rate</code> and <code>reinvest_rate</code> assumptions</li>
<li>Cannot handle multiple sign changes as robustly as some other methods</li>
<li>First cash flow is assumed to occur at end of first period (not time zero)</li>
</ul>
<h2 id="related-functions">Related Functions</h2>
<ul>
<li><strong>IRR</strong> - Calculate internal rate of return (assumes reinvestment at IRR)</li>
<li><strong>NPV</strong> - Calculate net present value using discount rate</li>
<li><strong>PV</strong> - Calculate present value of an investment</li>
<li><strong>FV</strong> - Calculate future value of an investment</li>
<li><strong>Rate</strong> - Calculate interest rate for an annuity</li>
<li><strong>Pmt</strong> - Calculate payment for a loan/annuity</li>
<li><strong>XIRR</strong> - IRR for irregular cash flow timing (Excel only)</li>
<li><strong>XNPV</strong> - NPV for irregular cash flow timing (Excel only)</li>
</ul>
<h2 id="vb6-parser-notes">VB6 Parser Notes</h2>
<p>MIRR is parsed as a regular function call (<code>CallExpression</code>). 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.</p>
</article>
<div style="margin-top: 3rem; padding-top: 2rem; border-top: 1px solid var(--border-color);">
<p>
<a href="index.html">← Back to Financial</a> |
<a href="../index.html">View all functions</a>
</p>
</div>
</main>
<footer>
<div class="container">
<p>© 2024-2026 VB6Parse Contributors. Licensed under the MIT License.</p>
</div>
</footer>
</body>
</html>