Use the IRR Function to Find Your Rate of Return
The IRR function in Excel calculates the internal rate of return — the discount rate that makes the net present value of all cash flows equal to zero. In plain terms, it tells you the percentage return on an investment over time, accounting for when money goes in and comes out.
To use it, you need a column of cash flows in chronological order, starting with your initial investment (usually a negative number) and followed by returns or withdrawals. Excel's IRR function then solves for the rate that balances those flows. The result appears as a decimal you convert to a percentage.
This is useful for comparing investments, evaluating projects, or understanding what return you actually earned on money you put into something over several years. Unlike a straightforward percentage gain, IRR accounts for the timing of each cash flow.
Key Takeaways
- The IRR function requires cash flows in a single column, listed in chronological order, with the initial investment as a negative number.
- The formula is =IRR(range) where range is the cell range containing all your cash flows.
- Excel returns the result as a decimal; multiply by 100 or format as percentage to see it as a rate.
- If Excel cannot find a rate, check that you have both negative and positive cash flows, and that they span at least two periods.
- IRR assumes cash flows occur at the end of each period; use XIRR if your cash flows happen on irregular dates.
Set Up Your Cash Flow Column
Start by listing all cash flows in a single column, from top to bottom, in the order they occur. The first entry should be your initial investment or outlay — enter it as a negative number. Each subsequent row holds the cash you receive or spend in that period.
For example, if you invest $10,000 today, receive $3,000 next year, $4,000 the year after, and $5,000 the year after that, your column would look like this:
| Year | Cash Flow |
| 0 | -10000 |
| 1 | 3000 |
| 2 | 4000 |
| 3 | 5000 |
The year column is for your reference only — Excel only needs the cash flow numbers. Make sure negative numbers are truly negative (not text), and that there are no blank cells in the middle of your range. If you have a year with zero cash flow, enter 0, not a blank.
Enter the IRR Formula
Click on an empty cell where you want the result to appear. Type =IRR( followed by the range of cells containing your cash flows, then close the parenthesis. If your cash flows are in cells B2 through B5, the formula is =IRR(B2:B5).
Press Enter. Excel calculates and displays the result as a decimal — for example, 0.1234. To see this as a percentage, multiply by 100 in your head (12.34%) or format the cell as a percentage. Right-click the cell, select Format Cells, choose Percentage, and set decimal places to 2.
If you see an error like #NUM!, it usually means your cash flows do not include both negative and positive values, or Excel cannot find a solution. Check that your initial investment is negative and that you have at least one positive return.
Understand What the Result Means
The IRR is the annual rate of return that your cash flows generate. If IRR returns 0.15, that means your investment grows at 15% per year on average, accounting for when each dollar enters or leaves.
Use IRR to compare two investments: the one with the higher IRR is theoretically the better return, assuming the same risk and time horizon. You can also compare IRR to your cost of capital or required return — if IRR is higher, the investment may be worth pursuing.
Keep in mind that IRR assumes you reinvest any cash you receive at the same rate, which may not reflect reality. It also does not account for risk, taxes, or inflation. Use it as one tool among several when making investment decisions, not as the only measure.
Use XIRR for Irregular Cash Flow Dates
The standard IRR function assumes cash flows happen at the end of each period — year 1, year 2, year 3, and so on. If your cash flows occur on specific calendar dates that are not evenly spaced, use XIRR instead.
XIRR requires two ranges: one for dates and one for cash flows. Set it up like this: =XIRR(cash_flow_range, date_range). For example, if cash flows are in B2:B5 and dates are in A2:A5, type =XIRR(B2:B5, A2:A5). The dates must be in a recognized date format, and the first date should correspond to your initial investment.
XIRR is more accurate when investments or returns happen on specific dates rather than at neat yearly intervals. The result is still an annual rate, even if your cash flows span months or irregular periods.
Common Mistakes and How to Fix Them
The most frequent error is forgetting to make the initial investment negative. IRR needs at least one outflow and one inflow to calculate a rate. If all your numbers are positive, Excel cannot solve the equation and returns #NUM!.
Another mistake is including blank cells in your range. If you skip a row or leave a cell empty, Excel may misinterpret the sequence. Always use a continuous range with no gaps, and enter 0 for any year with no cash flow.
Some users enter cash flows out of order or mix up the sign — for example, entering a return as negative instead of positive. Double-check that your column runs from earliest to latest, with the initial investment negative and subsequent returns positive (or vice versa if you are modeling a loan or debt).
If Excel still cannot find a solution, try providing a guess. Add a second argument to IRR: =IRR(range, guess). The guess is a starting point — try 0.1 for 10% or 0.2 for 20%. This helps Excel converge on an answer if the default method stalls.
Frequently Asked Questions
What is the difference between IRR and NPV?
NPV (net present value) discounts all cash flows to today's dollars using a rate you choose. IRR is the rate that makes NPV equal zero. Think of NPV as answering "how much is this worth?" and IRR as answering "what return rate does this represent?" You use both together: if IRR exceeds your required return, the investment may be worth pursuing.
Can IRR be negative?
Yes. A negative IRR means your cash flows result in a net loss over time. For example, if you invest $10,000 and receive only $8,000 back over several years, IRR will be negative, showing that your money shrank rather than grew.
Why does Excel sometimes show multiple IRR values?
Unusual cash flow patterns — such as large outflows in the middle of the investment — can create multiple rates that satisfy the IRR equation. Excel returns the first one it finds. If you suspect multiple solutions, graph your cash flows or consult a financial advisor to understand which rate is most relevant to your situation.
What if my cash flows are monthly instead of yearly?
IRR assumes the periods are equal. If you have monthly cash flows, the result is a monthly rate, not annual. To convert to annual, use the formula =(1 + monthly_IRR)^12 - 1. Alternatively, use XIRR with actual calendar dates, which always returns an annual rate.
Does IRR account for taxes or inflation?
No. IRR is calculated on the raw cash flows you enter. To account for taxes, reduce your cash inflows by the tax amount before calculating IRR. To account for inflation, adjust your cash flows to constant dollars. IRR itself does not make these adjustments automatically.