Net present value, or NPV, tells you whether an investment is worth making by converting all future cash flows into today’s money and subtracting the cost. A positive NPV means the project earns more than your required return, and a negative NPV means it earns less. Excel has the NPV function for regular yearly or monthly cash flows and XNPV for cash flows on specific dates. The most common error is to include the initial investment inside the function, which gives a wrong answer. This guide explains the formula, shows worked examples that you can check by hand, covers how to choose a discount rate, compares NPV with IRR and payback, and lists the mistakes to avoid when you build an investment model for a client.
Updated November 2026
In this guide
- What NPV means
- The NPV function in Excel
- Worked example 1: a three-year project
- Worked example 2: a project with a negative NPV
- The classic mistake: putting the investment inside NPV
- Use a cell range for the cash flows
- XNPV for irregular dates
- How to choose a discount rate
- NPV, IRR and payback compared
- Monthly cash flows and the discount rate
- Practice problems
- Build an NPV template for a client
- Common mistakes
What NPV means
Money today is worth more than the same money later, because you can invest it, and because of inflation and risk. NPV applies this idea by discounting each future cash flow at a rate that reflects your required return. The formula is the sum of each cash flow divided by one plus the rate, raised to the power of the period number. The initial investment happens today, so it is not discounted. If the total after subtracting the investment is above zero, the project adds value. For example, 1,100 received in one year, discounted at 10 percent, is worth exactly 1,000 today, so an investment of 1,000 that returns 1,100 after one year has an NPV of zero at 10 percent.
The NPV function in Excel
The syntax is =NPV(rate, value1, [value2], ...). Rate is the discount rate per period, and the values are the cash flows for periods 1, 2, 3 and so on. Here is the point that causes most errors: Excel assumes the first value in the list happens at the end of period 1, not today. So you do not include the initial investment inside NPV. You add it outside: =NPV(rate, future_cash_flows) + initial_investment, where the investment is entered as a negative number. Keep the rate and the periods in the same unit, so a monthly cash flow needs a monthly rate.
Worked example 1: a three-year project
You invest 5,000 today and expect cash flows of 2,000, 2,500 and 2,500 at the end of years 1, 2 and 3. Your required return is 8 percent. Enter −5000 in A1, and 2000, 2500 and 2500 in A2:A4. Use =NPV(8%,A2:A4)+A1. Check it by hand: 2,000 divided by 1.08 is 1,851.85. 2,500 divided by 1.08 squared, which is 1.1664, is 2,143.35. 2,500 divided by 1.08 cubed, which is 1.259712, is 1,984.58. The three present values add up to 5,979.78. Subtract the investment of 5,000 and the NPV is about 979.78. The project is worth doing at 8 percent, since the NPV is positive.
Worked example 2: a project with a negative NPV
Now take an investment of 1,000 that returns 300 at the end of each of the next three years, with a required return of 10 percent. The present value of the three payments is 300 times 2.48685, which is about 746.06, because 2.48685 is the sum of 1 divided by 1.1, 1.21 and 1.331. The NPV is 746.06 minus 1,000, which is about −253.94. Excel gives the same with =NPV(10%,300,300,300)-1000. The negative result means the project earns less than 10 percent, so on these numbers you should reject it, or look for a way to raise the cash flows or reduce the cost.
The classic mistake: putting the investment inside NPV
If you include the first cash flow, which is the investment, inside the function, Excel treats it as happening one period later and discounts it. Using the same cash flows as the project in the first example of our IRR guide, which are −1000, 600 and 600 with a 10 percent rate, the wrong formula =NPV(10%,-1000,600,600) returns about 37.57. The correct formula is =NPV(10%,600,600)-1000, which returns about 41.32. The difference is small in this case, but it grows with larger amounts and longer projects, and it can flip a decision. Always keep the time zero cash flow outside the function.
| Formula | Result | Correct? |
|---|---|---|
| =NPV(10%,-1000,600,600) | 37.57 | No, discounts the investment |
| =NPV(10%,600,600)-1000 | 41.32 | Yes |
Use a cell range for the cash flows
In a real model, put the cash flows in a row or column with the period numbers above them. For example, A1 can hold the investment as a negative number, and B1:F1 can hold the cash flows for years 1 to 5. The formula becomes =NPV($H$1,B1:F1)+A1, where H1 holds the discount rate. Using a single cell for the rate means you can change it and see the NPV update at once. You can also build a small table that shows the NPV at 6, 8, 10 and 12 percent, called a sensitivity table, so that your client can see how the decision changes with the rate.
XNPV for irregular dates
NPV assumes equal gaps between payments. If the cash flows happen on specific dates, use =XNPV(rate, values, dates). Unlike NPV, XNPV treats the first value as happening on the first date, so you include the initial investment in the values range, and its date is the start date. For example, if you pay −1,000 on 1 January 2026 and receive 1,100 on 1 January 2027, then =XNPV(10%,A1:A2,B1:B2) returns 0, because the period is 365 days and 1,100 divided by 1.1 equals 1,000. XNPV uses a 365-day year, and the dates must be real Excel dates. If they are text, convert them first, as shown in our guide to converting text to numbers in Excel.
How to choose a discount rate
The discount rate is the return you require. For a business it is often the cost of capital, which blends the cost of loans and the return expected by owners. For a small project it may be the interest rate on a loan used to fund it, plus an allowance for risk. A safer project can use a lower rate, and a riskier one a higher rate. Always test several rates, because a project that looks good at 6 percent may fail at 12 percent. If you do not know the rate, calculate the IRR with the IRR function, and compare it with your target return. The rate at which NPV equals zero is the IRR.
NPV, IRR and payback compared
Each measure answers a different question. NPV gives the value added in money, IRR gives the return as a percentage, and the payback period gives the time to recover the cost. NPV is the most reliable for choosing between projects, because it measures the total value created. IRR is easier to communicate, but it can mislead when projects differ in size or when cash flows change sign more than once. Payback ignores cash flows after the payback date and ignores the time value of money. Show all three in a report to a client, and explain which one drives the recommendation.
| Measure | Answers | Weakness |
|---|---|---|
| NPV | How much value is created | Needs a discount rate |
| IRR | What yearly return is earned | Can mislead for different sizes |
| Payback | How fast the cost is recovered | Ignores later cash flows |
Monthly cash flows and the discount rate
When cash flows are monthly, convert the annual rate to a monthly rate. The simple approach is to divide by 12, so 12 percent a year becomes 1 percent a month. A more exact approach is =(1+12%)^(1/12)-1, which gives about 0.9489 percent. Use the same method every time and state it in your assumptions. Then use =NPV(monthly_rate, cash_flows)+initial. If you mix annual rates with monthly cash flows, the result will be badly wrong, since the discounting is applied 12 times too strongly.
Practice problems
Try these. Problem 1: invest 1,000 and receive 1,100 after one year at 10 percent. The NPV is 0. Problem 2: invest 1,000 and receive 1,210 after two years at 10 percent. The NPV is also 0, because 1,210 divided by 1.21 is 1,000. Problem 3: invest 2,000 and receive 2,400 after one year at 5 percent. The present value is 2,285.71, so the NPV is 285.71. Problem 4: invest 500 and receive 100 each year for 5 years at 0 percent. The NPV is 0, since the cash flows add up to exactly 500. Problem 5: change the rate in problem 3 to 20 percent. The present value is 2,000, so the NPV is 0, and the IRR is 20 percent. Check each with the Excel formula.
Build an NPV template for a client
Create an Inputs area with the project name, the discount rate, the initial investment and the yearly cash flows. Add a Results area with the NPV, the IRR using =IRR(range), the payback year, and a decision cell with =IF(NPV>0,"Accept","Reject"), as shown in our IF statements guide. Add a sensitivity table of NPV at several rates, and a line chart of cumulative cash flow. Protect formula cells and use data validation for inputs, as covered in our drop-down list guide. Add a notes section that states the assumptions about timing, taxes and inflation. A clear template with notes is far easier for a client to trust and reuse.
Common mistakes
The most common mistake is including the initial investment inside NPV. Another is a rate and period mismatch, such as an annual rate with monthly flows. A third is to enter outflows as positive numbers, so the signs are wrong. A fourth is to ignore taxes, working capital and inflation, which can change the cash flows. A fifth is to treat NPV as exact, when it depends on estimates that may be wrong. Always add a sensitivity check, state the assumptions, and review the cash flow forecast with the client before you present the result.
Frequently asked questions
What is NPV in Excel?
NPV is the present value of future cash flows minus the initial investment, calculated with the NPV or XNPV function.
Why is my NPV wrong?
Most often because the initial investment was included inside the NPV function. Add it outside.
What is the difference between NPV and XNPV?
NPV assumes equal periods. XNPV uses actual dates.
What does a positive NPV mean?
The project earns more than your required return, so it adds value.
How is NPV related to IRR?
IRR is the discount rate that makes NPV equal to zero.
Related guides and free tools
- How to calculate IRR in Excel
- Loan payment in Excel
- IF statements in Excel
- How to round numbers in Excel
- All free calculators
- All free calculators and templates
Sources: IRR function, Microsoft Support


