How to calculate NPV and IRR in Excel

What NPV tells you

A project costs $120,000 today and pays back $28,000, $35,000, $38,000, $36,000 and $30,000 over the next five years. The inflows add up to $167,000, but money in five years is worth less than money now, because you could have invested it elsewhere in the meantime. NPV (net present value) converts every future amount into today's money at a discount rate you choose, then adds them up, including the investment.

The discount rate is your input, in F1: usually your cost of borrowing or the return you could get elsewhere, often called the hurdle rate. A positive NPV means the project earns more than that rate. A negative NPV means you would do better at the hurdle rate.

NPV in Excel, and why period 0 goes outside

Excel's NPV assumes the first value you give it arrives at the end of period 1. Your investment happens now, at period 0, so it must not be discounted. The correct pattern is =NPV(F1,C3:C7)+C2: discount years 1 to 5, then add the negative year-0 amount as it is.

The formula below calculates exactly the same thing written out, which shows what NPV does: each flow is divided by (1 + rate) to the power of its year. You can use it as a check on your NPV formula. The result is about $9,491 at 9%.

The most common mistake: putting year 0 inside NPV

=NPV(F1,C2:C7) looks tidier, but it treats the $120,000 as if it were paid a year from now and shifts every flow one year later. The answer comes out as the correct NPV divided by 1.09, about $8,707 instead of $9,491. No error appears, so this mistake survives into many business cases.

The formula below is what =NPV(F1,C2:C7) actually calculates. The gap grows with the rate and the size of the investment.

A second trap: NPV skips empty cells in a range. If a year with no cash flow is left blank, every later flow moves one year earlier. Type 0 in those years.

IRR: the rate where NPV is zero

IRR (internal rate of return) answers the question the other way round: what discount rate would make the NPV exactly 0? Unlike NPV, IRR takes the whole range including the year-0 investment, =IRR(C2:C7). For this project it returns about 11.95%. Since that is above the 9% hurdle rate, IRR and NPV agree: the project is worth doing at 9%.

You can check any IRR by putting it back into the NPV formula as the rate. The result should be close to 0; it is only off by a couple of dollars here because 11.95% is rounded.

IRR needs at least one negative and one positive value, or it returns #NUM!. If the flows change sign more than once (for example a big repair cost in year 3), there can be more than one IRR; give IRR a second argument, a guess near the rate you expect, or rely on NPV instead. IRR assumes evenly spaced periods. With monthly flows it returns a monthly rate; convert it to yearly with =(1+monthly rate)^12-1.

XNPV and XIRR: when cash flows have real dates

NPV and IRR assume every flow is exactly one period apart. Real payments are not: the first inflow here arrives 440 days after the investment and the last more than five years later. XNPV and XIRR take a column of dates and discount each flow by the exact days elapsed, divided by 365. Unlike NPV, XNPV includes the first flow in the range and does not discount it, so =XNPV(F1,C2:C7,B2:B7) is right as it is.

The formula below is XNPV's calculation written out. With the real dates, the NPV falls from $9,491 to about $8,653, and =XIRR(C2:C7,B2:B7) gives about 11.6% instead of 11.95%, because most of the money arrives later than the evenly spaced model assumed.

Store the dates as real Excel dates, and make the first date the earliest one; an invalid or earlier date makes XNPV and XIRR return an error.

Which to use

To decide whether one project is worth it, use NPV at your hurdle rate: positive means yes. To compare projects of different sizes, NPV tells you which adds the most money, while IRR tells you which earns the highest rate; they can rank projects differently, and when they disagree, NPV is usually the safer guide because a high rate on a small amount can be worth less than a lower rate on a large one.

Use XNPV and XIRR whenever you know the actual dates, such as invoices, rent or investment statements. Use NPV and IRR for plans with yearly or monthly periods. If you need IRR with a separate financing and reinvestment rate, Excel also has MIRR(values,finance_rate,reinvest_rate).