IRR Function in Google Sheets: Formula, Examples & Troubleshooting
Calculate periodic IRR in Google Sheets with signed cash flows and an explicit guess. Verify the result with discounted flows and recognize multiple-root risks.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =IRR(B2:B5,0.1) estimates the rate that makes a periodic cash-flow stream’s net present value zero. Include both money paid out as negative values and money received as positive values, in time order.
Verification: NOT_TESTED_IN_GOOGLE_SHEETS. Primary Google documentation was reviewed October 17, 2026. Rates and discounted-flow checks were calculated independently; no Google Sheets solver run is claimed.
Syntax and arguments
=IRR(cashflow_amounts, [rate_guess])
cashflow_amounts: required range or array of periodic cash flows. Google requires at least one positive and one negative value.rate_guess: optional starting estimate, default 0.1, meaning 10%.
Income is positive and payments are negative from the investment owner’s perspective. Google directs irregularly dated cash flows to XIRR and documents the zero-NPV relationship. Google IRR reference.
A guess is not a target return or a guaranteed answer. Label it separately from any minimum acceptable return used in your own analysis.
Example: three equal annual receipts
Enter this synthetic project cash-flow table in A1:B5:
| Period | Cash flow |
|---|---|
| 0 | -1000 |
| 1 | 400 |
| 2 | 400 |
| 3 | 400 |
- Enter
=IRR(B2:B5,0.1)in E2. - Format E2 as a percentage with at least four decimal places.
- The independently calculated expected result is approximately 9.701026% per year for this annual schedule.
- Keep the initial -1,000 in the range. Removing it would remove the investment being evaluated and leave no negative cash flow.
The model’s equation is:
-1000 + 400/(1+r) + 400/(1+r)^2 + 400/(1+r)^3 = 0
The answer is a rate per spacing interval. The same four amounts at monthly intervals describe a monthly return, not the annual result claimed for this table. An annual equivalent of a genuinely monthly rate can be calculated with (1+r)^12-1; that mathematical conversion does not repair cash flows that were irregular to begin with.
Independently audit the answer
Put =B2/(1+$E$2)^A2 in C2 and fill through C5. Then use =SUM(C2:C5) in C6. With the unrounded root, the expected sum is approximately zero. The row for period 0 remains -1,000 because its discount exponent is zero.
A shorter check is:
=B2+NPV(E2,B3:B5)
Google’s NPV reference treats its first cash-flow argument as a future flow, which is why the time-zero B2 sits outside this NPV call. Use E2’s underlying value rather than retyping its rounded percentage. A small residual from finite precision is different from a materially nonzero result.
At a trial rate of 10%, the same independently calculated present-value sum is about -5.259204. That negative value is consistent with the zero lying slightly below 10% for this conventional one-outflow-then-inflows example.
Advanced warning: more than one valid rate
Consider a separate three-period stream in H2:H4: -100, 230, and -132, corresponding to periods 0, 1, and 2. Its discounted-value equation has two mathematical roots, 10% and 20%:
-100 + 230/1.1 - 132/1.1^2 = 0
-100 + 230/1.2 - 132/1.2^2 = 0
This independently verified example demonstrates why a single solver result need not uniquely describe a nonconventional cash-flow pattern. A formula such as =IRR(H2:H4,0.1) supplies one starting estimate. This guide does not claim which root Google’s solver will return for every guess, nor a documented iteration count or convergence tolerance.
Inspect sign changes and the underlying discounted-value relationship instead of treating a displayed percentage as an automatic investment decision. IRR also does not express project size: a large and a small project can share a rate while producing very different cash amounts.
Errors and troubleshooting
- Only positive or only negative inputs: this violates Google’s required mix of signs. Review the cash-flow perspective and missing initial entries.
- A period has no flow: put an explicit numeric zero in its position. Deleting that row changes the modeled spacing. Blank-cell handling is not established here.
- Dates are uneven: IRR accepts amounts, not a date schedule. Use Google’s documented XIRR alternative after reviewing that function’s own contract.
- The result looks too large: check whether the intervals are months, quarters, or years; percentage formatting alone cannot tell you.
- A solver error appears: first verify numeric inputs, opposite signs, time order, and spacing. Try an informed explicit guess only after checking the model. Exact error codes and solver internals were not established by the reviewed help page.
- The verification sum is not close to zero: confirm the audit uses the same amounts, period positions, and full-precision result.
Support and references
Google currently documents IRR. Its first introduction, retirement, and last working version are not established by these sources. No solver-parity claim is made for other spreadsheets.
Reviewed October 17, 2026: Google IRR and Google NPV.