What is the Excel MIRR Function in Excel?

Calculate Modified Internal Rate of Return for investments. Learn MIRR syntax, examples, and how to evaluate investment projects effectively. =MIRR(values, finance_rate, reinvest_rate) Use AskFormulas to generate validated formulas instantly.

How do I use Excel MIRR Function to calculate mirr for a simple 5-year investment project?

To calculate mirr for a simple 5-year investment project, use the formula: =MIRR(B2:B7, 0.10, 0.12). Basic Investment Analysis This formula returns: 13.5%.

How can I optimize Excel MIRR Function performance?

To optimize Excel MIRR Function: 1) Use specific ranges instead of entire columns, 2) Avoid volatile functions within Excel MIRR Function, 3) Consider array formulas for bulk operations, 4) Cache results when possible. AskFormulas automatically applies optimization best practices.

What is the syntax for Excel MIRR Function?

The Excel MIRR Function syntax in Excel is: =MIRR(values, finance_rate, reinvest_rate). It takes 3 parameter(s): values (required), finance_rate (required), reinvest_rate (required).

How do I use Excel MIRR Function to analyze a business expansion project with multiple negative cash flows?

To analyze a business expansion project with multiple negative cash flows, use the formula: =MIRR(C2:C8, 0.08, 0.10). Business Project Evaluation This formula returns: 11.2%.

Can Excel MIRR Function handle setup in Excel?

Yes, Excel MIRR Function can handle setup. For example: =MIRR(B2:B7, 0.10, 0.12) will calculate mirr for a simple 5-year investment project. This returns: 13.5%

Excel MIRR Function

Calculate Modified Internal Rate of Return for investments. Learn MIRR syntax, examples, and how to evaluate investment projects effectively.

Excel

Google Sheets

financial

intermediate

Syntax Preview

Excel

Google Sheets

=MIRR(values, finance_rate, reinvest_rate)

Quick Answer

TL;DR

MIRR function MIRR function is a financial function in Excel and Google Sheets that calculates the Modified Internal Rate of Return for a series of cash flows, accounting for both the cost of investment and the interest received on reinvestment of cash. Unlike IRR, MIRR assumes that positive cash flows are reinvested at a specified reinvestment rate and negative cash flows are financed at a specified finance rate, providing a more realistic and stable measure of investment profitability.

=MIRR(values, finance_rate, reinvest_rate)

Syntax: `=MIRR(values, finance_rate, reinvest_rate)` Example: `=MIRR(B2:B7, 0.10, 0.12)` returns 13.5%, calculating the modified internal rate of return for a $100,000 investment with positive cash flows over 5 years, using 10% financing cost and 12% reinvestment rate

Understanding the MIRR Function

Syntax and Parameters

How to Use MIRR - Step by Step

## What is the MIRR Function?

The MIRR (Modified Internal Rate of Return) function is one of Excel's most sophisticated financial analysis tools, designed to overcome key limitations of the traditional IRR function. First introduced in early versions of Excel, it has become essential for financial analysts, CFOs, and investment professionals who need accurate return calculations that reflect real-world financing and reinvestment scenarios.

## When to Use MIRR

Use MIRR when analyzing capital projects where the cost of capital differs from expected reinvestment returns, comparing multiple projects with different sizes, durations, or cash flow patterns, evaluating real estate investments where financing costs are well-defined, performing private equity and venture capital return calculations, making project portfolio selection and capital budgeting decisions, or measuring performance for investment funds and portfolios. MIRR provides a more accurate picture of profitability than IRR by using realistic reinvestment assumptions.

## How MIRR Works

At its core, MIRR operates by separating the financing and reinvestment aspects of cash flows. The function processes data by first calculating the present value of all negative cash flows (investments) using the finance rate, then calculating the future value of all positive cash flows (returns) using the reinvestment rate, and finally computing the rate of return that equates these two values. This methodology ensures realistic assumptions about reinvestment and produces a single, unambiguous rate of return.

## Key Differences: MIRR vs IRR

The critical distinction is that MIRR always produces a single answer rather than multiple possible rates, handles unconventional cash flows (multiple sign changes) more reliably than IRR, uses realistic reinvestment rate assumptions rather than assuming reinvestment at IRR, performs better with projects that have different financing and reinvestment characteristics, and is more accurate for comparing projects of different sizes or durations. For most investment analysis, MIRR provides a more realistic measure of returns.

## Real-World Applications

Financial professionals use MIRR for evaluating capital investment projects with different financing and reinvestment assumptions, comparing investment opportunities on an apples-to-apples basis, real estate investment analysis with tax considerations, private equity and venture capital return calculations, project portfolio selection and capital budgeting decisions, and performance measurement for investment funds and portfolios. It's particularly valuable in situations where you need to account for realistic reinvestment opportunities.

## Platform Compatibility

The function is available in Excel 2007 and later versions (including Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013) and works identically in Google Sheets with the same syntax and parameters. Models using MIRR transfer seamlessly between platforms without modification, though very small differences (beyond 10 decimal places) may occur due to calculation precision differences.

## Syntax and Parameters

The MIRR function requires three parameters, all mandatory:

**=MIRR(values, finance_rate, reinvest_rate)**

### Parameter 1: values (required)

An array or range containing cash flows that occur at regular intervals. Must contain at least one positive and one negative value. Example: B2:B7 containing initial investment (negative) and subsequent returns (positive). The values must be in chronological order, with the first value typically representing the initial investment. Empty cells are treated as zero.

### Parameter 2: finance_rate (required)

The interest rate paid on money used in the cash flows, also known as the borrowing rate or cost of capital. Expressed as a decimal (0.10 for 10%) or percentage (10%). This represents your cost of financing negative cash flows. Example: If your weighted average cost of capital (WACC) is 10%, use 0.10. This rate should reflect the actual cost of obtaining the funds for the investment.

### Parameter 3: reinvest_rate (required)

The interest rate received on reinvested cash flows, representing the expected return on reinvestment of positive cash flows. Expressed as a decimal (0.12 for 12%) or percentage (12%). This should reflect realistic returns available from alternative investments. Example: If you can reinvest proceeds at 12%, use 0.12. Conservative assumptions lead to more reliable investment decisions.

### Return Type

The function returns a number representing the modified internal rate of return as a decimal. Format as percentage to display as a rate of return (e.g., 0.135 displays as 13.5% when formatted). The result represents the annualized rate of return for the investment, accounting for both financing costs and reinvestment opportunities.

## How to Use MIRR - Step by Step

Follow these steps to calculate modified internal rate of return:

### Step 1: Organize Your Cash Flow Data

Arrange cash flows in chronological order in a single column or row. Negative values represent cash outflows (investments), positive values represent inflows (returns). Ensure the series includes at least one negative and one positive value. Example: Year 0: -$100,000, Years 1-5: positive cash flows.

### Step 2: Determine Your Finance Rate

Identify the appropriate cost of capital or borrowing rate. This could be your company's weighted average cost of capital (WACC), loan interest rate, or opportunity cost. Express as a decimal: 10% = 0.10. Document the source and justification for this rate in your model.

### Step 3: Determine Your Reinvestment Rate

Establish a realistic rate at which positive cash flows can be reinvested. This might be your expected return from alternative investments, market rates, or a conservative hurdle rate. Express as a decimal: 12% = 0.12. Be conservative - overestimating this rate can lead to poor investment decisions.

### Step 4: Set Up Your Spreadsheet

Create a clear structure with labeled sections. Place cash flows in a continuous range (e.g., B2:B7). Create named cells for finance_rate (e.g., D2) and reinvest_rate (e.g., D3). Label all inputs clearly for transparency and audit trail purposes.

### Step 5: Enter the MIRR Formula

Click the cell where you want the result. Type: =MIRR(B2:B7, D2, D3) where B2:B7 contains your cash flows, D2 is the finance rate, and D3 is the reinvestment rate. Alternatively use direct values: =MIRR(B2:B7, 0.10, 0.12). Press Enter to calculate.

### Step 6: Format the Result as Percentage

Select the result cell and apply percentage formatting. Right-click, choose Format Cells, select Percentage with 1 decimal place. The MIRR will display as a percentage (e.g., 13.5%), making it easier to interpret and compare with other investment opportunities.

### Step 7: Validate Your Results

Verify the calculation makes sense: MIRR should typically fall between your finance rate and reinvestment rate if the investment is profitable. If MIRR is negative, the investment destroys value. Compare with NPV to ensure consistency - positive NPV should correspond with MIRR greater than your hurdle rate.

### Step 8: Perform Sensitivity Analysis

Test how MIRR changes with different assumptions. Create a data table showing MIRR across various finance and reinvestment rates. This helps understand the range of potential outcomes and makes your analysis more robust to assumption changes. Document key scenarios (base case, best case, worst case).

Practical Examples

Common Errors and Solutions

#DIV/0! Error

MIRR returns #DIV/0! error

Cause:

All cash flows are positive or all are negative, making the calculation mathematically impossible

Solution:

Ensure your cash flow series includes at least one positive and one negative value. Verify the initial investment (typically negative) and subsequent returns (typically positive) are correctly entered. Check for formatting issues that might hide negative signs.

Prevention:

Always validate cash flow data includes both inflows and outflows before running MIRR calculations. Use conditional formatting to highlight negative values in red for easy visual verification.

Example:

#NUM! Error

MIRR returns #NUM! error

Cause:

Finance rate or reinvestment rate is equal to -1 (i.e., -100%), or the values array contains invalid numeric data

Solution:

Check that both rate parameters are greater than -1. Verify all cells in the values range contain valid numbers. Common issue: rates entered as percentages (10) instead of decimals (0.10). Ensure no blank cells interrupt the cash flow series.

Prevention:

Use data validation to ensure rates are entered correctly (between -99% and reasonable positive values) and cash flows are numeric values. Create helper cells to convert percentage inputs to decimal format.

Example:

#VALUE! Error

MIRR returns #VALUE! error

Cause:

Non-numeric data in the values range, or rate parameters are not numbers

Solution:

Clean your data by removing text, ensuring all cash flows are numbers. Use VALUE() or NUMBERVALUE() to convert text that looks like numbers. Verify rate cells contain decimal numbers, not text. Check for hidden characters or extra spaces.

Prevention:

Apply proper formatting to cash flow cells and use data validation rules to prevent text entry. Use ISNUMBER() to validate all inputs before calculation.

Example:

Misleading Results

MIRR seems unrealistic or doesn't match expectations

Cause:

Using rates that don't match the cash flow period (e.g., annual rates with monthly cash flows), or incorrect assumptions for finance/reinvestment rates

Solution:

Ensure rate periods match cash flow frequency. For monthly flows, use monthly rates (annual rate / 12). For quarterly, divide by 4. Document your rate assumptions and verify they're realistic for your scenario. Cross-check with NPV to ensure consistency.

Prevention:

Always document the period frequency and convert rates appropriately. Include notes about rate assumptions in your model. Create a separate assumptions section showing annual vs periodic rates.

Example:

Best Practices and Pro Tips

MIRR vs IRR: When to Use Which

MIRR is generally more realistic than IRR because it assumes cash flows are reinvested at the reinvestment rate rather than the IRR itself. Use MIRR when reinvestment rates differ from the project's return rate, and when comparing projects with different cash flow patterns. IRR is still useful for quick screening, but MIRR provides a more accurate picture for final investment decisions.

Choosing Appropriate Rates

The finance rate typically represents your cost of capital or weighted average cost of capital (WACC). The reinvestment rate should reflect realistic returns available from alternative investments. Conservative assumptions lead to more reliable investment decisions. As a rule of thumb, reinvestment rate should be lower than your optimistic IRR.

Document Your Assumptions

Always create a dedicated assumptions section in your model showing finance rate, reinvestment rate, and their justification. This transparency is crucial for investment committee reviews and audit trails. Include sources for your rates (e.g., 'WACC calculated from company financials' or 'Based on 10-year Treasury yield plus risk premium').

Period Consistency is Critical

One of the most common mistakes is mismatching rate periods with cash flow periods. If cash flows are quarterly, rates must be quarterly. If annual, rates must be annual. Mismatched periods will produce meaningless results that appear correct but are mathematically wrong. Always verify period alignment before finalizing your analysis.

Sensitivity Analysis

Create a data table showing how MIRR changes with different finance and reinvestment rates. This helps understand the range of potential outcomes and makes your analysis more robust to assumption changes. Use Excel's What-If Analysis tools to automate this process.

Combine with Other Metrics

Never rely on MIRR alone for investment decisions. Use it alongside NPV, payback period, and qualitative factors. MIRR shows returns, but NPV shows absolute value creation, and both perspectives are important. A project with high MIRR but low NPV might not be as attractive as one with moderate MIRR but high NPV.

Excel vs Google Sheets Compatibility

MIRR works identically in Excel and Google Sheets with the same syntax and parameters. However, very small differences (beyond 10 decimal places) may occur due to calculation precision differences between platforms. For practical business decisions, these differences are negligible.

MIRR vs Similar Functions

Use Cases and Applications

## MIRR vs Similar Functions

Understanding when to use MIRR versus similar financial functions ensures accurate investment analysis.

### MIRR Function

- Calculates Modified Internal Rate of Return with separate finance and reinvestment rates
- Syntax: =MIRR(values, finance_rate, reinvest_rate)
- Use when: Need realistic assumptions about reinvestment, comparing projects with different cash flow patterns
- Returns: Single modified rate of return as percentage
- Best for: Capital budgeting, real estate investments, project comparison

### IRR Function

- Calculates Internal Rate of Return assuming reinvestment at IRR
- Syntax: =IRR(values, [guess])
- Use when: Quick screening of investment opportunities, conventional cash flows
- Returns: Rate at which NPV equals zero
- Best for: Simple projects with conventional cash flows (one negative followed by positives)

### NPV Function

- Calculates Net Present Value of cash flows
- Syntax: =NPV(rate, value1, [value2], ...)
- Use when: Need to know absolute dollar value created by investment
- Returns: Present value of all cash flows minus initial investment
- Best for: Comparing investments of different sizes, value creation analysis

### When to Choose MIRR

Choose MIRR when your cost of capital differs from realistic reinvestment rates, analyzing projects with unconventional cash flows (multiple sign changes), comparing projects of significantly different sizes or durations, need to avoid the multiple-rate problem that can occur with IRR, or when investment committees require conservative, realistic return assumptions.

### Key Relationships

For the same project: IRR will typically be higher than MIRR when reinvestment rate   IRR. NPV and MIRR should tell consistent stories - positive NPV should correspond with MIRR   hurdle rate. MIRR always produces a single answer, while IRR can produce multiple answers for unconventional cash flows.

### Related Financial Functions

**IRR:** Calculates internal rate of return with single rate assumption.

**NPV:** Net present value of cash flows at specified discount rate.

**PMT:** Calculates periodic payment for loans or investments.

**FV:** Future value of investment with constant payments.

**RATE:** Returns interest rate per period of investment or loan.

## Common Use Cases

MIRR excels in diverse investment and financial scenarios:

### Capital Investment Project Evaluation

Evaluating major capital expenditures like equipment purchases, facility expansions, or technology implementations. CFOs use MIRR to assess whether projects exceed the company's hurdle rate when realistic financing and reinvestment assumptions are applied, providing better investment decisions than IRR alone.

### Real Estate Investment Analysis

Analyzing rental property acquisitions, commercial real estate developments, or REIT investments. Real estate professionals use MIRR to account for mortgage financing costs and realistic reinvestment rates for rental income, providing accurate returns that reflect leveraged investment economics.

### Private Equity and Venture Capital

Calculating fund performance and individual investment returns for PE and VC portfolios. Fund managers use MIRR to provide limited partners with realistic return expectations that account for fund expenses (finance rate) and reinvestment opportunities for early exits (reinvestment rate).

### Project Portfolio Selection

Comparing and ranking multiple competing projects for capital allocation decisions. Portfolio managers create ranking tables using MIRR to compare projects on an apples-to-apples basis, ensuring capital flows to the highest-return opportunities when realistic assumptions are applied.

### Business Acquisition Analysis

Evaluating acquisition targets by modeling projected cash flows from operations. Corporate development teams use MIRR to assess whether acquisition prices are justified by expected cash generation, incorporating acquisition financing costs and realistic assumptions about reinvesting operating cash flows.

### Performance Measurement for Investment Funds

Reporting time-weighted returns for mutual funds, hedge funds, or separately managed accounts. Fund administrators use MIRR to provide investors with return metrics that reflect realistic reinvestment assumptions rather than assuming all proceeds can be reinvested at the fund's IRR.

### Renewable Energy Project Evaluation

Analyzing solar, wind, or other renewable energy investments with long-term cash flows. Energy analysts use MIRR to model projects with high upfront costs, project financing, and steady cash flows over 20+ years, providing realistic returns that account for tax incentives and power purchase agreements.

### Lease vs Buy Decisions

Comparing the economics of leasing versus purchasing equipment or facilities. Treasury departments use MIRR to evaluate cash flow differences between alternatives, incorporating the company's borrowing costs and opportunity costs of deployed capital to make optimal financing decisions.