MIRR Function (Modified Internal Rate of Return)
Calculate the modified internal rate of return with different financing and reinvestment rates
MIRR Calculator
MIRR Calculation
Calculates the modified internal rate of return (MIRR) for a series of cash flows with different financing and reinvestment rates.
Explanation
MIRR vs. IRR
Difference:
IRR: Assumes constant reinvestment rate
MIRR: Considers different financing and reinvestment rates
Advantage:
MIRR is more realistic and better for comparing projects
What is MIRR?
- MIRR = Modified Internal Rate of Return
- Considers realistic financing costs
- Considers realistic reinvestment return
- More accurate assessment than IRR
- Better suited for project comparison
|
|
Mathematical Foundations of MIRR Calculation
The MIRR (Modified Internal Rate of Return) considers different rates for financing and reinvestment:
Positive Cash Flows Compounded
Compounded at reinvestment rate
Negative Cash Flows Discounted
Discounted at finance rate
Description of Parameters
Cash Flow List
The cash flow list contains a series of payment streams occurring at regular intervals (e.g., annually). The list must contain at least one negative value (cash outflow, e.g., investment) and at least one positive value (cash inflow, e.g., returns).
Important: The order is critical! The first value is typically the initial investment (negative), followed by returns (positive).
Example:
-10000 (Investment)
4000 (Return Year 1)
4000 (Return Year 2)
5000 (Return Year 3)
Finance Rate
The finance rate is the interest rate at which capital is borrowed (financed). This is typically the loan interest rate or the cost of debt.
Example: If you take a loan at 5%, the finance rate is 5%.
The value is entered as a percentage (e.g., 5 for 5%).
Reinvest Rate
The reinvestment rate is the interest rate at which cash inflows (positive cash flows) are reinvested. This is typically the return you can achieve with excess funds.
Example: If you can invest returns at 6%, the reinvestment rate is 6%.
The value is entered as a percentage (e.g., 6 for 6%).
Result (MIRR)
The result is the modified internal rate of return (MIRR) expressed as a percentage. This value indicates the average annual return considering realistic financing and reinvestment rates.
Interpretation: An MIRR of 5.73% means that the investment grows at an average rate of 5.73% per year when financing costs (5%) and reinvestment return (6%) are considered.
Quick Reference
MIRR vs. IRR
IRR: Constant rate for both directions
MIRR: Different rates more realistic
Typical Values
• Finance: 3-8%
• Reinvestment: 4-10%
• MIRR usually between both
• MIRR < IRR (more realistic)
Use Cases
• Investment evaluation
• Project comparison
• Capital budgeting
• Private equity
• Real estate investment
MIRR Modified Rate of Return - Detailed Explanation
Fundamentals
The MIRR (Modified Internal Rate of Return) is an improved version of IRR that considers realistic assumptions about financing and reinvestment costs.
MIRR considers two different interest rates:
• Finance Rate: Cost of debt
• Reinvestment Rate: Return on surplus funds
Why MIRR is Better
IRR has limitations that MIRR overcomes:
Benefits of MIRR
1. Realistic: Treats inflows and outflows differently
2. Unique: One solution even with complex cash flows
3. Comparable: Better basis for project comparison
4. Practical: Matches real financing conditions
Calculation & Methodology
MIRR is calculated in three steps:
1. Discount negative cash flows at finance rate
2. Compound positive cash flows at reinvestment rate
3. Find MIRR where both values are equal
Practical Application
MIRR is used in many financial decisions:
Areas of Use
- Capital budgeting (project selection)
- Comparing different investments
- Evaluating private equity deals
- Real estate investment analysis
- Corporate financing
Calculation Tips
- Realistic Rates: Use actual market rates
- Consistent Periods: All cash flows should have equal intervals
- Minimum Requirement: Min. 1 negative + 1 positive value
- Rate Comparison: MIRR usually between both rates
- Compare with IRR: MIRR < IRR shows rate effect
- Sensitivity: Test different scenarios
Key Insights
MIRR is More Practical Than IRR
MIRR better reflects reality by considering that debt and surplus are traded at different rates. This leads to more reliable decisions.
MIRR Between the Rates
MIRR typically falls between the finance and reinvestment rates. If finance is 5% and reinvestment is 8%, MIRR often falls between 6-7%.
Unique Solution
Unlike IRR, MIRR always has a unique solution, even with unconventional cash flow patterns (multiple sign changes).
Decision Rule
Investments with MIRR > cost of capital should be pursued. When comparing: Higher MIRR = better investment (under equal conditions).
|
|