Internal rate of return, or IRR, is the yearly return that makes the value of an investment equal to its cost. Excel calculates it with one function, as long as you list the cash flows in order, with money you pay out as negative numbers and money you receive as positive numbers. This guide explains what IRR means, shows the IRR, XIRR and NPV formulas with worked examples that you can check by hand, explains how to compare IRR with a required return, and covers the common mistakes: wrong signs, missing negative flows, uneven dates and multiple answers. It is written for small business owners, students and freelancers who build investment models for clients.
Updated November 2026
In this guide
- What IRR means
- The IRR function in Excel
- Worked example 1: a simple two-year investment
- Worked example 2: two equal payments
- Check IRR with NPV
- IRR for monthly cash flows
- XIRR for dates that are not evenly spaced
- How to interpret IRR
- Why IRR returns #NUM! or a strange answer
- Practice problems
- Building a reusable IRR template
- Common mistakes
What IRR means
IRR is the discount rate at which the net present value of all cash flows is zero. In plain words, it is the annual growth rate of the money you put in. If you invest 1,000 today and receive 1,210 after two years, your money grew by 21 percent in total, which is 10 percent per year, because 1,000 times 1.1 times 1.1 equals 1,210. So the IRR of that investment is exactly 10 percent. A project is usually worth doing when its IRR is higher than the return you require, called the hurdle rate, or the cost of borrowing the money.
The IRR function in Excel
The syntax is =IRR(values, [guess]). Values is a range of cash flows, and the first one is normally the negative initial investment. Excel assumes the flows are equally spaced, such as yearly or monthly. Guess is optional and is a starting point for Excel to search from, with 10 percent as the default. Microsoft describes the IRR function in its support documentation. The result is a rate per period, so for monthly flows you must multiply or compound the monthly rate to get a yearly rate.
Worked example 1: a simple two-year investment
Put −1000 in A1, 0 in A2 and 1210 in A3. Enter =IRR(A1:A3). Excel returns 10 percent, which matches the hand calculation. Try a three-year version: −1000, 0, 0 and 1331 in A1:A4. The answer is again 10 percent, because 1.1 to the power of 3 equals 1.331. These cases are good tests when you build a model, because you know the answer before Excel gives it.
Worked example 2: two equal payments
You invest 1,000 and receive 600 at the end of year 1 and 600 at the end of year 2. Enter −1000, 600 and 600 in A1:A3 and use =IRR(A1:A3). The result is about 13.07 percent. You can check this by solving the equation: 600 divided by (1 plus r), plus 600 divided by (1 plus r) squared, equals 1,000. Let x equal 1 divided by (1 plus r). The equation becomes 600x² plus 600x minus 1000 equals 0, so x is about 0.8844, and r is about 0.1307. This is why we say that the IRR is higher than the simple average return of 10 percent per year: the money comes back early, so it earns more per year.
Check IRR with NPV
Net present value, or NPV, is the sum of the discounted cash flows. In Excel, the NPV function discounts values starting from period 1, so add the initial investment outside the function: =NPV(rate,B2:B3)+B1. Using the same cash flows with a required return of 10 percent, the present value of the two payments is 600 divided by 1.1, which is 545.45, plus 600 divided by 1.21, which is 495.87, for a total of 1,041.32. Subtract the investment of 1,000, and the NPV is about 41.32. A positive NPV at 10 percent agrees with an IRR of 13.07 percent, which is above 10 percent. If you set the discount rate equal to the IRR, the NPV becomes zero, which is the definition of IRR.
| Cash flow | Year 0 | Year 1 | Year 2 | Result |
|---|---|---|---|---|
| Investment A | -1000 | 600 | 600 | IRR 13.07%, NPV at 10% = 41.32 |
| Investment B | -1000 | 0 | 1210 | IRR 10.00%, NPV at 10% = 0 |
IRR for monthly cash flows
When the cash flows are monthly, IRR returns a monthly rate. To convert it to an effective annual rate, use =(1+IRR(A1:A13))^12-1. For example, a monthly IRR of 1 percent is an annual rate of about 12.68 percent, because 1.01 to the power of 12 equals 1.1268. Do not simply multiply by 12 when you present results to a client unless they ask for a nominal rate, since the compounding effect matters on longer projects. Label the result clearly as monthly, nominal annual or effective annual to avoid confusion.
XIRR for dates that are not evenly spaced
IRR assumes equal gaps between flows. If your cash flows happen on irregular dates, such as invoices paid on different days, use XIRR: =XIRR(values, dates, [guess]). Put the cash flows in one column and the matching dates in another, with the first date being the investment date. XIRR returns an annual rate based on a 365-day year. For example, an investment of −1000 on 1 January 2026 and a receipt of 1,100 on 1 January 2027 gives an XIRR of 10 percent. XIRR is more accurate for real-world deals, and it is the better choice for loans, invoices and any project with uneven timing. The dates must be real Excel dates, not text, so convert text first as shown in our guide to converting text to numbers in Excel.
How to interpret IRR
Compare IRR with the return you require. If a project has an IRR of 13 percent and the loan that funds it costs 8 percent, the project adds value. If the IRR is below the hurdle rate, the project destroys value, and you should reject it or look for ways to improve the cash flows. When you compare projects, do not rely on IRR alone. A small project can have a very high IRR but a small total profit, while a large project with a lower IRR can add more money. Use IRR together with NPV, the payback period and a risk review. For a loan perspective, see our guide to calculating a loan payment in Excel.
Why IRR returns #NUM! or a strange answer
Excel returns #NUM! when it cannot find a rate after 20 attempts. The most common cause is that all cash flows have the same sign. IRR needs at least one negative and one positive flow, otherwise no rate can make the NPV zero. A second cause is a bad guess, so try =IRR(A1:A6,0.05) or a negative guess for losing projects. A third cause is text in the range. A fourth cause is that the cash flows change sign more than once, such as a project with a large cleanup cost at the end. In that case there can be more than one IRR, and the result depends on the guess. When this happens, use NPV at several discount rates or use MIRR, which assumes a stated reinvestment rate and always returns one answer.
Practice problems
Try these. Problem 1: invest 500 and get back 605 after two years. The total growth is 21 percent, so IRR is 10 percent. Problem 2: invest 2,000 and get back 2,420 after two years. The IRR is again 10 percent. Problem 3: invest 1,000 and receive 400, 400 and 400 at the end of years 1 to 3. Use =IRR and you will get about 9.7 percent. Problem 4: invest 1,000 and receive only 900 after one year. The IRR is minus 10 percent, a loss. Problem 5: enter −1000, 300, 300, 300 and 300. Excel returns about 7.7 percent. After each one, use NPV at the IRR to confirm that the result is close to zero.
Building a reusable IRR template
A reusable template saves time on client work. On an Inputs sheet, list the project name, the start date, the hurdle rate and the cash flows by year, with a clear label that outflows are negative. On a Results sheet, show the IRR with =IRR(Inputs!B5:B15), the NPV at the hurdle rate with =NPV(Inputs!B2,Inputs!B6:B15)+Inputs!B5, and a decision cell such as =IF(B2>Inputs!B2,"Accept","Reject") using the approach from our IF statements guide. Add a line chart of cumulative cash flow so the client can see the payback point, and a small table that shows NPV at 5, 8, 10 and 12 percent. Use data validation to stop users typing text into the cash flow cells, and format outflows in red so that sign mistakes are obvious. Finally, add a notes box listing assumptions, because a model without assumptions is hard to trust and hard to audit.
Common mistakes
The most common mistake is to leave out the initial investment or to enter it as a positive number. Another is to include the initial investment inside the NPV function, which discounts it by one period and gives a wrong NPV. A third is to read a monthly IRR as if it were yearly. A fourth is to compare IRRs of projects with very different sizes or lengths. A fifth is to forget taxes, fees and inflation, which can change the real return. Finally, always state your assumptions, including the timing of flows and the reinvestment rate, because IRR assumes that interim cash flows are reinvested at the IRR itself, which may not be realistic.
Frequently asked questions
What is IRR in Excel?
IRR is the annual rate of return that makes the net present value of a series of cash flows equal to zero.
What is the difference between IRR and XIRR?
IRR assumes equal time gaps between cash flows. XIRR uses actual dates.
Why does IRR show #NUM!?
Usually because all cash flows have the same sign, or because Excel cannot find a rate from the guess.
What is a good IRR?
One that is higher than your hurdle rate or cost of funds, after allowing for risk.
Should the first cash flow be negative?
Yes. The initial investment is a payment, so it is entered as a negative number.
Related guides and free tools
- Loan payment in Excel
- How to round numbers in Excel
- Percentage increase in Excel
- Annual income calculator
- All free calculators
- All free calculators and templates
Sources: IRR function, Microsoft Support


