MIRR Function in Google Sheets: Formula, Examples & Troubleshooting
Calculate MIRR in Google Sheets using separate financing and reinvestment rates. Audit signed cash flows and compare scenarios with a later project outlay.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =MIRR(cashflow_amounts,financing_rate,reinvestment_return_rate) calculates a modified return for a periodic cash-flow stream using separate funding and reinvestment assumptions. It returns a rate, not a monetary profit.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Google’s documentation was reviewed October 17, 2026. The examples are independently calculated financial-model expectations, not native Sheets test results.
Syntax and arguments
=MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate)
| Argument | Required meaning |
|---|---|
cashflow_amounts |
Range or array with at least one negative and one positive flow |
financing_rate |
Interest rate paid on invested funds |
reinvestment_return_rate |
Rate earned by reinvesting received income |
All three arguments are required. There is no documented omitted-rate default. Enter a percentage such as 8% or its decimal equivalent 0.08, not the number 8 for an intended eight percent. Google defines income as positive and payments as negative from the investment owner’s perspective. Google MIRR reference.
The cash flows are periodic. Keep both rate assumptions on the same interval as the cash-flow schedule. An annual-rate example should not silently become a monthly calculation because rows were copied into a monthly report.
Example: project receipts reinvested at 6%
Enter A1:B5 as follows:
| Period, in years | Cash flow |
|---|---|
| 0 | -1000 |
| 1 | 400 |
| 2 | 450 |
| 3 | 500 |
Enter 8% in E2, labeled Financing rate, and 6% in E3, labeled Reinvestment rate.
- Put
=MIRR(B2:B5,E2,E3)in E5. - Format the result as a percentage with four or more decimal places.
- The expected result is approximately 12.568748% per year under these assumptions.
- Retain all four cash-flow rows. There are three intervals between period 0 and period 3.
Here is an independent audit of the modified-return model. Bring positive receipts forward to the final period at the reinvestment rate:
400*1.06^2 + 450*1.06 + 500 = 1426.44
The only negative flow occurs at time zero, so its present magnitude is 1,000. The equivalent three-period growth rate is:
=(1426.44/1000)^(1/3)-1
This produces approximately 0.125687481, consistent with the expected percentage. The calculation explains the model mathematically; it is not a description of Google’s source code or numerical implementation.
Notice that changing only the financing assumption from 8% to 12% does not change this particular model’s expected result. Its sole outlay is already at time zero. That observation is specific to this dataset, not a claim that MIRR generally ignores financing costs.
Advanced example: a later refurbishment outlay
Create a second series in H1:I5 with the same periods 0 through 3 and flows -1000, 400, -200, 1000. The negative 200 occurs at period 2.
Use =MIRR(I2:I5,$E$2,$E$3) with the original 8% financing and 6% reinvestment assumptions. The independently calculated expected rate is approximately 7.355253%.
The audit now has two separate parts:
Present magnitude of payments = 1000 + 200/1.08^2 = 1171.467764
Final value of receipts = 400*1.06^2 + 1000 = 1449.44
Modified rate = (1449.44/1171.467764)^(1/3)-1
Use full precision for the intermediate values, rather than the rounded audit numbers above. At financing 12% and reinvestment 6%, the expected model result becomes approximately 7.725241%. At financing 8% and reinvestment 10%, it becomes approximately 8.201808%.
These sensitivities show why the two inputs need distinct labels and defensible assumptions. They are not interchangeable knobs, and a larger reported rate is not by itself evidence of a better economic outcome.
Interpretation and troubleshooting
MIRR versus an undiscounted total: the first dataset’s simple sum is 350. That amount and the 12.568748% modified rate answer different questions. One totals cash amounts; the other incorporates timing and the stated reinvestment model.
Wrong sign convention: review the owner’s viewpoint consistently. A project expense belongs among negative flows; a receipt belongs among positive flows. Google requires both signs.
Missing zero periods: if no cash moves in an otherwise real period, represent it with a numeric zero. Removing the position shortens the model’s timeline. This guide does not establish how Sheets treats blank or textual cells inside the input range.
All assumptions are annual but rows are quarterly: resolve the unit mismatch before calculation. Do not divide an effective annual rate by four without first deciding what rate convention your model requires.
Unexpected error: inspect the numeric range, presence of opposite signs, and rate inputs. The reviewed help does not provide a comprehensive error-code table, negative-rate domain, or coercion specification; no exact behavior for those cases is claimed.
Irregular dates: MIRR has no date-array parameter. A date-sensitive analysis needs a separately specified model rather than assuming MIRR reads dates from adjacent cells.
Support and references
Google currently documents MIRR. Its introduction, retirement, and last working version are unknown from the reviewed evidence. Identically named functions in other software are not used here as proof of Google behavior. The synthetic examples explain calculation mechanics and are not investment advice.
Reviewed October 17, 2026: Google MIRR documentation.