Return Rates With IRR

NPV asks: at a given rate, is this project worth it? IRR (Internal Rate of Return) flips the question: what rate would make this project exactly break even?

IRR is the discount rate at which the NPV equals zero. It is the project's own built-in return, expressed as a single percentage.

People love it because it is easy to compare against a target return like 10%.

The function is =IRR(values, [guess]).

• values — a range of cash flows, in order, one per period.
• guess — optional starting estimate for the calculation, default 10%.

Unlike NPV, IRR has no separate rate argument — finding the rate is the whole point.

This is a key difference from NPV. IRR's value range must include the time-zero cash flow — usually the negative initial investment.

So the range needs at least one negative value (money out) and at least one positive value (money in). Without a sign change, IRR has nothing to solve and returns an error.

Put the -investment first, then the inflows.

You invest -1,000 today and receive 300, 400, 500, and 600 over the next four years. List all five values in a column, B1:B5.

IRR finds the rate that makes those flows net to zero in present-value terms.

The result is about 24.9% — this project's internal return.

Comparing IRR to your required return (the hurdle rate) gives a quick verdict.

• IRR > hurdle rate — accept the project; it earns more than you demand.
• IRR < hurdle rate — reject it.

Our 24.9% comfortably beats a 10% hurdle, so the project is attractive.

IRR and NPV are two views of the same math. At the IRR, the NPV is zero by definition.

You can prove it: feed the IRR result back into NPV as the rate, and the result lands at (essentially) zero.

This is a great sanity check and shows the two functions are perfectly consistent.

IRR solves by iteration — it tries rates until it converges. The optional guess tells it where to start.

Most of the time the default 10% works fine. But unusual cash flows can make IRR fail to converge, returning a #NUM! error.

If that happens, supply a guess closer to the expected answer.

If cash flows change sign more than once (out, in, out, in...), IRR can have several mathematically valid answers, and Excel only reports one.

This makes IRR unreliable for projects with mid-life outflows, like a big maintenance cost.

In those cases, lean on NPV, which always gives a single clear number.

Plain IRR assumes evenly spaced periods. For irregular dates use XIRR: =XIRR(values, dates, [guess]).

IRR also assumes interim cash is reinvested at the IRR itself, which can flatter results. MIRR fixes this by letting you set separate finance and reinvestment rates.

Reach for XIRR with real dates and MIRR for more realistic reinvestment assumptions.

IRR, XIRR, and MIRR all exist in Google Sheets with the same syntax as Excel.

The same rules carry over: include the initial outlay, ensure at least one sign change, and beware multiple IRRs.

Together with PMT, PV, FV, and NPV, you now have the core financial toolkit for loans, savings, and investment decisions in either app.

IRR returns a decimal, like 0.249, not a formatted percent. Apply percentage formatting to the cell, or multiply by 100 if you need a raw number.

For a clean text label you can wrap it in TEXT, for example to show "24.9%" inside a sentence.

Remember it is still the same value — only the display changes.

Test what IRR actually computes.

You learned to find a project's own return with =IRR(values).

• IRR is the rate where NPV equals zero; no separate rate argument is needed.
• The value range must include the initial outlay and have at least one sign change.
• Accept when IRR beats your hurdle rate.
• Watch for the multiple-IRR trap; use XIRR for dates and MIRR for realistic reinvestment.

All work the same in Excel and Google Sheets.