How to Use the MIRR Function in Google Sheets to Calculate Modified Internal Rate of Return with Reinvestment Rates

When evaluating capital investment proposals, corporate venture financing, or commercial real estate acquisitions, the standard Internal Rate of Return (IRR) is frequently utilized as a baseline metric. However, financial economists and analysts recognise a fundamental mathematical flaw in traditional IRR: it assumes that intermediate positive cash flows generated by the project are continuously reinvested at the project’s internal rate of return—an assumption that severely inflates projected profitability when IRR is high.

To overcome this reinvestment rate bias and handle projects with unconventional negative cash flows during later stages, Google Sheets provides the MIRR (Modified Internal Rate of Return) function. MIRR explicitly separates the cost of borrowing capital from the realistic rate earned when reinvesting surplus cash balances. This guide details its mathematical formula, syntax rules, parameter definitions, and financial modeling applications.

Understanding the Financial Mathematics of MIRR

The MIRR calculation adjusts project cash flows through a three-step financial mechanism:

1. Discounting Outflows: All negative cash flows (capital outlays and operational losses) are discounted back to Period 0 using the specified finance rate (the company’s cost of capital).
2. Compounding Inflows: All positive cash flows are compounded forward to the final period using the specified reinvestment rate (e.g. money market or treasury yields).
3. Solving for Geometric Return: MIRR solves for the single compound interest rate that equates the present value of the financed outflows with the terminal value of the reinvested inflows.

Syntax and Parameter Breakdown

=MIRR(cash_flows, finance_rate, reinvestment_rate)

Parameter Reference

• cash_flows: A contiguous range or array containing the sequence of project cash flows (e.g. B2:B7). Must contain at least one negative cash flow (initial investment) and at least one positive cash flow.
• finance_rate: The annual interest rate paid on borrowed funds financing the project (expressed as a percentage or decimal, e.g. 8% or 0.08).
• reinvestment_rate: The realistic annual return earned on positive cash flows reinvested back into corporate treasury operations (e.g. 4.5% or 0.045).

Step-by-Step Practical Implementation

Consider an enterprise machinery overhaul requiring an initial outlay of £250,000 with a financing cost of 7.5%, while surplus cash is swept into money market accounts earning 4.0%:

Structure your Google Sheet rows as follows:

A1: Year 0 (Outlay)        | B1: -250000
A2: Year 1 Cash Inflow     | B2: 60000
A3: Year 2 Cash Inflow     | B3: 85000
A4: Year 3 Cash Inflow     | B4: 95000
A5: Year 4 Cash Inflow     | B5: 110000
A6: Finance Cost Rate      | B6: 7.5%
A7: Reinvestment Rate      | B7: 4.0%
A8: Standard IRR           | B8: =IRR(B1:B5)
A9: Modified IRR (MIRR)    | B9: =MIRR(B1:B5, B6, B7)

In this scenario:

• Standard IRR returns 12.84% (unrealistically assuming all intermediate cash is reinvested at 12.84%).
• MIRR returns a conservative, institutionally defensible 9.62%, reflecting realistic 4.0% reinvestment dynamics.

Comparison: IRR vs XIRR vs MIRR

Financial Function	Cash Flow Timing	Reinvestment Rate Assumption	Risk of Multiple Roots
IRR	Strictly periodic (equal intervals)	Equal to IRR itself (Unrealistic)	High (with alternating cash flows)
XIRR	Irregular (specific calendar dates)	Equal to XIRR itself	High
MIRR	Strictly periodic (equal intervals)	Custom user-defined reinvestment rate	None (Mathematically unique single solution)

Troubleshooting Common Errors

• #DIV/0! Error: Occurs when the cash flow range does not contain both positive and negative values. Verify that your initial outlay is entered with a minus sign.
• #VALUE! Error: Occurs if text characters or formatted dates are included in the finance or reinvestment rate arguments.