Formuladrill

Financial Updated November 2026

IRR

IRR(values, [guess]) returns the rate at which the net present value of a series of equally spaced cash flows equals zero. The range must start with the negative investment and contain at least one positive value. Format the result as a percentage; for cash flows on irregular dates use XIRR.

Syntax

IRR(values, [guess])
PartWhat it is
valuesA range or array of cash flows in order, starting with the initial outlay as a negative number.
guessOptional starting estimate (10% if omitted). Give one when IRR returns #NUM!.

Examples

=IRR({-1000, 400, 400, 400})

Result: 9.70%

Invest 1,000, receive 400 a year for three years.

=IRR(B1:B4)

Result: 16.34% when B1:B4 holds -10000, 3000, 4200, 6800

=IF(IRR(B1:B4) > 12%, "Accept", "Reject")

Result: Accept, because 16.34% beats a 12% hurdle rate

=XIRR(B1:B4, A1:A4)

Result: The annualized return using the real dates in A1:A4

Use XIRR for cash flows that are not evenly spaced.

Common mistakes

  • #NUM!: all cash flows have the same sign, or Excel did not converge. Make the first value negative and try a guess such as 0.2.
  • The result shows 0.097 instead of 9.7%: format the cell as a percentage.
  • Monthly cash flows give a monthly IRR; annualize with =(1 + IRR(range))^12 - 1.
  • Text or blank cells in the range are ignored, which shifts the periods; put 0 where there is no cash flow.

Practice IRR

These drills check your answer on live data, free, no account needed.

  • Internal rate of returnThe project pays out 10,000 now and receives the cash in years 1 to 4. Calculate the internal rate of return in B7.
  • Compound annual growthRevenue grew from the value in B1 to the value in B2 over the years in B3. Calculate the compound annual growth rate in B4.

Questions

What is the difference between IRR and XIRR?

IRR assumes equal spacing between cash flows and returns a rate per period. XIRR uses actual dates and always returns an annual rate.

IRR or NPV, which should I use?

They answer related questions: NPV gives the value added at a chosen rate, IRR gives the rate the project earns. Projects with changing signs in the cash flows can have several IRRs, so NPV is the safer decision rule.

Why does IRR give #NUM!?

Usually the range has no sign change, or the iteration did not converge. Check that the investment is negative and add a guess argument.

Related

  • NPVHow the Excel NPV function discounts future cash flows at a rate, why the initial investment is added outside the function, with a worked example and XNPV.
  • PMTHow the Excel PMT function calculates the payment on a loan or the deposit for a savings goal, with mortgage and car loan examples and the monthly rate rule.
  • ROUNDHow to round in Excel with ROUND, ROUNDUP, ROUNDDOWN and MROUND: to 2 decimals, to the nearest hundred and nearest 5, and why formatting is not rounding.
  • IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.

Previous: NPVNext: UNIQUE