Excel IRR Function

last modified October 5, 2026

The IRR function returns the internal rate of return for a series of cash flows that occur at regular intervals. The internal rate of return is the discount rate at which the net present value of the cash flows equals zero, so it expresses an investment's break-even return as a single percentage. This tutorial covers the syntax, the NPV = 0 condition, the sign-change requirement, and how Excel searches for a solution.

IRR Function Basics

The IRR function determines the discount rate that makes the net present value (NPV) of an investment's cash flows equal to zero. A higher IRR means a more profitable investment, provided the rate exceeds the cost of capital. The function takes the cash flow values and an optional starting guess.

Component	Description
Function Name	IRR
Syntax	=IRR(values, [guess])
Arguments	values (required), guess (optional)
Return Value	Internal rate of return as a decimal

This table breaks down the essential components of the IRR function. It shows the function name, syntax format, arguments, and the return value.

Argument	Meaning
values	A range or array of numbers for which you want the internal rate of return. The range must contain at least one positive and one negative value, so that the cash flow changes sign at least once.
guess	A number you expect the result to be close to. When omitted, Excel starts from 0.1 (10%). Change it when the result is unexpected or when IRR returns #NUM!.

The order of the values matters. Excel treats the first value as period 0, the second as period 1, and so on, assuming that every period has the same length. The initial investment is therefore entered first, usually as a negative number.

The NPV = 0 Definition

The internal rate of return is the solution of the equation NPV = 0. Written out, IRR finds the rate r such that

0 = CF0 + CF1/(1+r) + CF2/(1+r)^2 + ... + CFn/(1+r)^n

Here CF0 is the period 0 cash flow and CFn is the cash flow in the last period. This equation cannot be rearranged to isolate r, so Excel solves it by iteration. It starts from the guess (0.1 by default), refines the estimate repeatedly, and stops when the result is accurate to within 0.00001%. If no solution is found within 20 iterations, IRR returns the #NUM! error.

You can confirm the definition with the NPV function. If A1:A4 holds a set of cash flows and B1 contains =IRR(A1:A4), then =NPV(B1, A2:A4)+A1 returns a value very close to zero. The period 0 value is added outside NPV because Excel's NPV discounts its first argument by one period.

Basic IRR Example

This example demonstrates the simplest use of IRR with a series of cash flows. The initial investment is negative, representing an outflow.

A	B
-10000
3000
4200
6800
	=IRR(A1:A4)

=IRR(A1:A4)

The formula returns approximately 0.163, or 16.3%. That is the annual rate at which the four cash flows have a net present value of zero. The smaller stream {-1000, 300, 420, 680} gives exactly the same result, because IRR depends on the proportions of the cash flows rather than on their absolute size.

IRR for an Equal Cash Flow Stream

When an investment returns the same amount each period, the cash flow pattern is called an annuity. This pattern has a single sign change, so it has exactly one IRR.

A	B
-1000
500
500
500
	=IRR(A1:A4)

=IRR(A1:A4)                          ' 0.234 or 23.4%
=IRR({-5000,1500,1500,1500,1500})    ' 0.077 or 7.7%

An initial outlay of 1,000 followed by three payments of 500 earns approximately 23.4%. The second formula uses an array constant and shows that a longer, smaller annuity, 5,000 repaid by four payments of 1,500, earns only about 7.7%.

IRR with Mixed Inflows and Outflows

Cash flows do not have to be positive after the initial investment. This example includes a negative value in the middle of the stream, representing an additional cost.

A	B
-5000
1500
-1000
3000
4000
	=IRR(A1:A5)

=IRR(A1:A5)

The formula returns approximately 0.136, or 13.6%. The extra outflow of 1,000 in period 2 lowers the return compared with a stream of uninterrupted inflows. Because the values change sign more than once, a second mathematical root exists below -100% and is not a meaningful rate.

IRR with the Guess Parameter

When a series of cash flows changes sign more than once, the NPV equation can have more than one real solution and therefore more than one IRR. The guess argument tells Excel where to begin searching, which decides which solution it returns.

Consider the cash flows -10,000, 15,000, and -2,000. They change sign twice, and the NPV equation has two roots: about 35.2% and about -85.2%. With the default guess of 0.1 Excel converges to 35.2%.

A	B
-10000
15000
-2000
	=IRR(A1:A3)

=IRR(A1:A3)         ' 0.352 or 35.2%
=IRR(A1:A3, -0.9)   ' -0.852 or -85.2%

The first formula uses the default guess and returns the positive root. The second starts near the other root and returns it instead. Always treat an IRR computed from alternating-sign cash flows with care, and confirm the figure with NPV.

IRR for Monthly Cash Flows

When the periods are months, IRR returns a monthly rate. This example converts that rate into an effective annual rate with the standard compounding formula.

A	B
-10000
1750
1750
1750
1750
1750
1750
	=(1+IRR(A1:A7))^12-1

=(1+IRR(A1:A7))^12-1

The inner IRR(A1:A7) returns about 0.0141, or 1.41% per month. Raising 1 + r to the twelfth power and subtracting one annualizes it, giving approximately 0.183, or 18.3% per year. This conversion is valid only when the twelve monthly rates can be compounded, which is why the periods must be evenly spaced.

IRR for a Business Investment

This example calculates the IRR for a five-year business investment with an initial outlay followed by increasing annual returns.

A	B
-250000
50000
75000
100000
125000
150000
	=IRR(A1:A6)

=IRR(A1:A6)

The formula returns approximately 0.233, or 23.3%. If the company's cost of capital is below that figure, the project adds value. Comparing this IRR with the required return is equivalent to checking whether the NPV at that required return is positive.

IRR vs XIRR

IRR assumes that every cash flow occurs exactly one period after the previous one. XIRR accepts a matching list of dates, so it handles cash flows that fall on irregular dates and always returns an annualized rate. Use IRR for evenly spaced periods and XIRR when the actual dates matter.

Feature	IRR	XIRR
Timing	Equal periods	Specific dates
Syntax	=IRR(values, [guess])	=XIRR(values, dates, [guess])
Result	Rate per period	Annualized rate
Typical use	Regular project cash flows	Portfolios and dated payments

A monthly IRR is a monthly rate, while XIRR always reports a yearly rate. If a project's cash flows arrive on known calendar dates, XIRR is the more accurate choice.

IRR Limitations and Considerations

IRR is widely used but rests on assumptions that can mislead. First, it assumes that the interim cash flows are reinvested at the IRR itself, which may not be realistic. Second, when cash flows alternate in sign, several IRRs can exist and Excel returns only the one near the guess. Third, because IRR is a percentage, it ignores the absolute size of an investment and can rank projects differently from NPV. The modified internal rate of return, MIRR, addresses the reinvestment assumption by taking separate finance and reinvestment rates.

IRR Errors and Troubleshooting

Cause	Symptom	Fix
All cash flows have the same sign	#NUM! error	Include at least one positive and one negative value
Values are not numeric	#VALUE! error	Make sure every referenced cell contains a number
No convergence within 20 iterations	#NUM! error	Supply a guess close to the expected rate
Alternating-sign cash flows	Unexpected rate	Multiple IRRs may exist; try another guess or use NPV
Cash flows out of chronological order	Wrong rate	Enter the values in the order the periods occur
Cash flows fall on irregular dates	Misleading rate	Use XIRR with a list of dates instead

A result that looks wrong is often a sign-change problem. When the cash flows change sign only once, the IRR is unique and reliable. When they change sign several times, verify the answer by testing the NPV at the reported rate; a value far from zero means the guess led Excel to a different root.

The IRR function is essential for financial analysis in Excel. It turns a stream of cash flows into a single break-even rate by solving the NPV = 0 equation, making projects easy to compare with a required return. Use it for evenly spaced cash flows, remember the sign-change requirement, and choose the guess deliberately when the pattern is unconventional. For dated cash flows, switch to XIRR, and for a more conservative reinvestment assumption, consider MIRR. Mastering these details turns IRR from a black box into a dependable decision tool.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.