How to Calculate NPV in Excel (Net Present Value)

Net present value answers the one question a discounted cash flow analysis exists to answer: is this deal worth more than it costs? You take every dollar the investment will produce, discount each one back to today at a rate that reflects your cost of capital and the risk, add them up, and subtract the price. A positive number means the deal creates value at that discount rate. A negative number means it destroys it. Excel has an NPV function built for exactly this, and it is one of the most consistently misused functions in the entire program.

This walks through calculating net present value in Excel the right way, the timing quirk in the NPV function that produces wrong answers even when the formula looks correct, when to switch to XNPV, and how to get the cash flows out of the PDFs they usually arrive in.

Last updated July 2026.

What is net present value?

Net present value is the value today of a series of future cash flows, discounted at a chosen rate, minus the initial investment. It rests on a simple truth: a dollar next year is worth less than a dollar today, because today's dollar can be put to work. The discount rate is how much less. Run every future cash flow through that rate, sum the present values, subtract what you pay, and the result tells you in plain dollars whether the deal beats the return baked into your discount rate.

The decision rule is direct. A positive NPV means the investment earns more than your required return and adds value. A negative NPV means it falls short. Zero means it earns exactly your discount rate, no more. Because the answer is a dollar amount rather than a percentage, NPV tells you how much value is created, which a rate of return cannot.

How do you calculate NPV in Excel?

The reliable formula is =initial_investment + NPV(rate, future_cash_flows), where the initial investment is a negative number sitting outside the NPV function. The NPV function discounts the range you hand it and sums the results. You then add the day one outflow separately. Here is a five year project discounted at 10%:

That returns roughly 30,000, so at a 10% required return the project is worth about thirty thousand dollars more than it costs. Notice the initial investment in B2 is not inside the NPV call. That single detail is where most NPV mistakes live, and it is worth its own section.

Why is my NPV function giving the wrong answer?

Because Excel's NPV function assumes the first cash flow arrives at the end of period one, not today. Microsoft's own documentation is explicit: the NPV investment begins one period before the first value in the list. So if you include your year zero outlay inside the NPV arguments, Excel discounts it by a full period as though you paid it a year from now, and every subsequent flow is off by a period too. The number looks plausible, which is what makes the error so durable.

The fix is the structure shown above. Keep the time zero investment out of the NPV function and add it to the result, because a cash flow that happens today needs no discounting. Only the future flows, the ones that genuinely arrive at the end of periods one through five, belong inside NPV. Get that boundary right and the function behaves. Get it wrong and you will discount today's money by a year, which quietly overstates or understates the deal depending on the sign.

What is the difference between NPV and XNPV in Excel?

NPV assumes cash flows are spaced exactly one period apart. XNPV lets you give each flow a real date, so it discounts by the actual number of days rather than assuming clean annual intervals. Its syntax is =XNPV(rate, values, dates), and unlike NPV, it does include the time zero flow inside the values range because the first date you list becomes the reference point for all the discounting.

That difference in how each function treats the first cash flow trips people up when they switch between them. With plain NPV you exclude the day one investment and add it back. With XNPV you include it, dated, right in the range. XNPV is the better tool whenever cash flows land on irregular calendar dates, which describes most real transactions, since closings and distributions rarely fall on tidy anniversaries.

What discount rate should I use for NPV?

Use the return you could earn on a comparable risk investment, which for a company is usually its weighted average cost of capital and for an individual investor is the return on the next best alternative of similar risk. The rate is a judgment about opportunity cost and risk, not a fact you look up, and it drives the answer more than almost any other input. A higher discount rate punishes distant cash flows harder, which is why speculative deals should be tested at higher rates.

Because NPV is so sensitive to this one number, never rely on a single rate. Build a small table that recalculates NPV across a band of discount rates, say 8% to 14%, and look at where the deal flips from positive to negative. The rate at which NPV hits exactly zero is the internal rate of return, which ties the two measures together and is worth calculating alongside. If you want the rate view, walk through how to calculate IRR in Excel on the same cash flow column.

Is a higher or lower NPV better?

Higher is better, as long as you are comparing projects with the same discount rate and roughly the same scale of investment. A higher NPV means more value created in today's dollars. But NPV is an absolute figure, so a large project will tend to show a bigger NPV than a small one simply because more money is at work, which does not automatically make it the better use of capital if it also ties up far more.

When you are ranking projects that compete for the same limited budget, look at NPV alongside how much capital each one consumes, and bring the rate of return into the picture. NPV tells you how much value a deal adds. The return measures tell you how hard the money works while it is committed. Good capital decisions weigh both rather than optimizing one and ignoring the other. Discounted cash flow is also the backbone of putting a defensible value on an entire business, not just a single project, which is the same math applied to a company's whole stream of future earnings.

How do you get the cash flows into Excel in the first place?

An NPV is only as trustworthy as the cash flows behind it, and those come out of financial statements, not out of thin air. Operating income sits on a trailing statement, rent on a rent roll, and both usually arrive as PDFs or scanned printouts. Retyping a year of line items by hand is slow and is exactly where a wrong digit enters a model and skews the discounting.

Convert the documents rather than rekeying them. Send a property's operating statement PDF to Excel so every income and expense line lands on its own row, and pull the rent roll the same way to verify in place income. Scanned pages still work because OCR reads the characters off the image. One check before you build a formula on the data: select a cash flow column and look at the status bar, and if Excel shows a count but no sum, the numbers came across as text and NPV will ignore them. The PDF to Excel for real estate page covers the rest of the deal package, and the NOI walkthrough makes sure the income line feeding your model is clean.

A short checklist before you trust the NPV

• Keep the time zero investment outside the NPV function and add it back, or switch to XNPV and include it with its date.
• Confirm the discount rate reflects the real opportunity cost and risk, not a round number picked for convenience.
• Build a sensitivity table across a band of discount rates rather than trusting one point estimate.
• Check that every cash flow cell is a real number, not text, so none are silently dropped from the sum.
• Calculate the internal rate of return alongside NPV so you see both the dollar value and the return.

Do that and the net present value you put in front of a decision maker is one that holds up to questions. The formula was never the hard part. The cash flows, and one timing assumption, were.