IRR

Overview

Description

Internal rate of return (IRR) is a metric of profitability for potential investments. Internal rate of return is a discount rate that sets the net present value (NPV) of all future cash flows of the investment equal to zero. An approximate solution is found using numerical iteration.

Use this when you need to find the discount rate at which an investment's cash flows break even (NPV = 0).

Syntax

IRR('Node' [, Guess])

Parameters

Node: Yearly cash flows. At least one of the cash flows must be negative and at least one positive.
Guess (optional): A first guess at the rate. Defaults to 10%.

Example

IRR of a project with mixed cash flows

This example calculates the internal rate of return for a project with an initial investment and subsequent positive cash flows.

Input node: CF

Year	Value
2025	-10000
2026	1155
2027	5000
2028	7000

Our goal is to find a discount rate with which the discounted cash flows sum up to zero:

Formula: IRR('CF')

Year	→ IRR Result
2027	-23%
2028	12%

The IRR function tells us the rate is 12%. The result for cash flows up to year 2027 is not interesting for us and can be filtered out with the FILTER function.

Related Functions

Function	When to use instead
NPV	When you need the net present value at a known discount rate rather than solving for the rate.
CAGR	When you need compound growth over N periods rather than the break-even discount rate.