IRR Function

Calculates the internal rate of return for a series of cash flows.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A rate of return

Taught hands-on in Financial Modeling · Financial Functions Toolkit module

Overview

IRR finds the discount rate at which a project's NPV is exactly zero — its own rate of return. If IRR beats your cost of capital, the project creates value.

Cash flows must be evenly spaced (usually years) and include at least one negative and one positive value. Unlike NPV, the time-0 investment goes inside the range.

Syntax

=IRR(values, [guess])

Arguments

ArgumentRequired?What it does
valuesYesThe cash flows in order, starting with today's investment.
guessNoA starting guess. Omitted = 10%.

Examples

Example 1 — Return on the new store

The $150,000 fit-out and four years of returns.

B7fx=IRR(B2:B6)
AB
1YearCash flow
20 (today)-$150,000
31$40,000
42$50,000
53$60,000
64$55,000
7IRR12.96%

12.96% is above Northstar's 8% cost of capital, agreeing with the positive NPV.

Common mistakes

  • #NUM! error. All flows have the same sign, or Excel couldn't converge in 20 tries. Check the signs or supply a guess.
  • Flows that flip sign several times. There can be more than one IRR. Use MIRR or NPV instead.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.