NPV Calculator in Excel: Formula, Example, and Tips

Formulas & functions · Updated November 18, 2026

To calculate net present value (NPV) in Excel, enter the discount rate and future cash flows, then use =NPV(rate, future_cash_flows)+initial_cash_flow. Excel’s NPV function treats the first value in its range as a cash flow at the end of period one, so a project’s time-zero investment normally belongs outside the function and is added separately.

What NPV means

Net present value expresses a series of expected cash flows in today’s money. It discounts each future receipt or payment to account for the time value of money, then adds those present values together. In a project evaluation, the initial outlay is usually negative, while later receipts are positive. NPV therefore measures the value left after accounting for both the initial cost and the selected discount rate.

A positive NPV means the projected cash flows have a present value greater than the initial investment at the chosen rate. A negative NPV means they do not. A result of zero means the cash flows exactly meet that rate’s required return in the model. These are conclusions about the assumptions entered, not guarantees about what will happen: actual cash flows, timing, costs, and risks can differ from estimates.

Excel does not have a separate NPV calculator window. The NPV function is the calculator: enter inputs in cells, write a formula, and format the result as currency if appropriate. The function is available in current Excel versions, including Microsoft 365 for Windows.

Set up the cash-flow table

A simple layout makes the timing easy to inspect and reduces the chance of putting the initial investment in the wrong place. For example, use one cell for the discount rate, one for the initial cash flow, and one row for each later period:

  • Cell B1: discount rate, such as 10%.
  • Cell A2: Initial investment; cell B2: -10000.
  • Cells A3:A5: Year 1, Year 2, and Year 3.
  • Cells B3:B5: the cash flow expected at the end of each year.

Enter the initial investment as a negative number because it is money paid out. Enter later receipts as positive numbers and later costs as negative numbers. If you expect both receipts and costs in a period, enter their net amount for that period or list the flows separately in a way that makes the timing clear. Keep periods in chronological order and label them so another reader can tell what each value represents.

Use a rate that matches the time interval. If the cash flows are annual, an annual discount rate is usually the relevant input; if they are monthly, use a rate expressed per month. Do not mix annual cash flows with a monthly rate, or monthly cash flows with an annual rate, without converting the rate and aligning the periods. Whether the rate should be nominal or effective depends on the assumptions behind the analysis.

Calculate NPV with Excel’s function

With the example layout above, cell B1 contains 10%, B2 contains -10000, and B3:B5 contain 3000, 4000, and 5000. Enter the following formula in another cell:

With the discount rate in B1, the initial investment in B2, and Year 1 through Year 3 cash flows in B3:B5, enter =NPV(B1,B3:B5)+B2.

The formula returns approximately -$210.37. The calculation discounts the future cash flows as follows: 3000 divided by 1.10 is about 2727.27; 4000 divided by 1.10 squared is about 3305.79; and 5000 divided by 1.10 cubed is about 3756.57. Those present values total about 9789.63. Adding the initial outlay of -10000 gives an NPV of -210.37, subject to rounding.

Excel’s function syntax is NPV(rate,value1,[value2],...). The rate is the discount rate for each period, and the values are the cash flows beginning at the end of the first period. A range is convenient when the values are stored in neighboring cells. The initial outlay is added separately in the example because it occurs at the start, not at the end of Year 1.

You can format the result cell as currency or accounting format using Home > Number. Formatting changes how the value appears, not the underlying calculation. Keep enough decimal places while checking the result, especially when comparing projects whose NPVs are close.

Why the initial investment stays outside NPV

The most common NPV mistake is including the initial investment as the first value in the function. Excel interprets the first value supplied to NPV as arriving at the end of the first period. If the first value is the time-zero investment, Excel discounts it by one period even though it should not be discounted. That shifts the result and produces an incorrect answer for the usual project cash-flow timeline.

For the example, if the initial -10000 is in B2 and later cash flows are in B3:B5, avoid including B2 in the NPV range. Use the formula shown above, which discounts only B3:B5 and then adds B2 unchanged. If the initial cash flow occurs at the end of the first period rather than at time zero, the timing assumption is different; describe the actual dates or periods before deciding how to model it.

A useful check is to calculate the present value of the future flows independently, then add the initial amount. If the project has no initial investment and all flows genuinely start at the end of period one, the NPV function can be used without an extra initial amount. The important point is not to add or exclude a cash flow mechanically: place it according to when it occurs.

Choose a suitable discount rate

The rate is not just a number that makes a formula work; it represents the return or cost threshold used to evaluate the cash flows. A company may base it on a required return, financing costs, or a project-specific hurdle rate. The appropriate choice depends on the purpose of the analysis and the risks and assumptions involved. A spreadsheet cannot decide which rate is suitable for you.

Because future cash flows are discounted, a higher rate generally reduces the present value of positive future receipts. A lower rate generally increases it. This makes a sensitivity check useful: calculate the project at several plausible rates and observe whether the conclusion changes. You can put alternative rates in separate cells and copy the NPV formula for each, taking care that every formula refers to the intended rate.

Cash flows and the discount rate should also be consistent about inflation. For example, nominal cash-flow forecasts that include expected inflation should be paired with a nominal rate; forecasts expressed in purchasing-power terms should be paired with a real rate. Mixing these bases can distort the result. For a real decision, verify the assumptions and relevant terms with the lender or a qualified financial professional. This spreadsheet explanation is not financial, tax, investment, or legal advice.

Use XNPV when dates are irregular

Regular NPV is designed for cash flows separated by equal periods, such as each year-end or each month-end. It does not inspect calendar dates in your table. If payments happen at uneven intervals, such as a deposit today, a payment after seven months, and another after eighteen months, a period-based NPV can misstate the effect of timing.

Excel’s XNPV function discounts cash flows using their actual dates. Put the discount rate in B1, the dates in A2:A5, and the corresponding cash flows in B2:B5; the first date and first cash flow should align, and the dates should be valid Excel dates. Enter =XNPV(B1,B2:B5,A2:A5) in the result cell. Unlike the usual NPV setup, include the initial cash flow in the values range when it occurs on the first listed date. XNPV uses the first date as its starting date and calculates using a 365-day year.

For ordinary annual flows on evenly spaced year-end dates, NPV is generally the more direct choice. For dates that are not evenly spaced, XNPV is designed for that pattern. Confirm that every amount is paired with the correct date; a mismatched range can make the result meaningless even if Excel accepts the formula.

Interpret and compare results carefully

For a single project, a positive NPV indicates that the forecast exceeds the selected discount-rate threshold, while a negative NPV indicates it falls short under those assumptions. Comparing alternatives can be useful, but compare like with like: use consistent rates, cash-flow definitions, currencies, and timing conventions. Differences in project life or scale can complicate a simple ranking. A larger project may have a higher total NPV while requiring substantially more capital.

NPV is an absolute dollar measure, not a percentage return. It does not by itself describe how quickly an investment pays back, how uncertain the forecast is, or how much capital is needed. Readers sometimes treat a positive value as proof that an investment is safe or certain to succeed; it is not. It is a result from a model based on estimated cash flows and a chosen discount rate. Scenario analysis can reveal how sensitive that result is to changes in sales, costs, timing, or the rate.

Round only for display where possible. If you round each period’s discounted amount before summing, small rounding differences can accumulate. Keep full precision in the formula and format the result cell to show the number of decimal places appropriate for the report.

Common formula and modeling problems

  • Discounting the initial investment: Including a time-zero outlay as the first value in NPV makes Excel treat it as a period-one flow. Keep it outside the range for a standard time-zero investment.
  • Using the wrong rate interval: A yearly rate with monthly cash-flow periods does not match the function’s period assumptions. Convert the rate or use a consistent interval.
  • Mixing signs: Outflows are generally negative and inflows positive. Reversing signs can reverse the meaning of the result.
  • Assuming Excel reads dates in NPV: NPV counts equal periods; it does not use calendar dates. Use XNPV when the actual dates are irregular.
  • Leaving out future costs: Include relevant expected maintenance, fees, taxes, or other cash flows if they belong in the analysis. Omitting them can make a project appear more valuable than the modeled economics support.
  • Comparing inconsistent forecasts: Differences in inflation assumptions, period length, or rate basis can make two results incomparable.

If a formula returns an unexpected result, first verify the cell references and signs, then check whether the first value is meant to occur at the end of period one. Also inspect the rate cell: a value entered as 10 rather than 10% means a rate of 1000% in Excel’s percentage arithmetic. The formula bar can help you confirm what is stored in a cell rather than relying only on its displayed format.

Frequently Asked Questions

Should the initial investment be included in Excel NPV?
For a standard project evaluation where the investment occurs at time zero, do not include it in the NPV range. Calculate the present value of later cash flows with NPV, then add the initial investment as a negative amount.

Why is my Excel NPV result different from my calculator?
Check whether the calculator includes the time-zero amount, whether both calculations use the same rate per period, and whether the cash flows are timed at period-end. Different timing assumptions or a rate entered as 10 instead of 10% can change the result substantially.

Can Excel NPV calculate monthly cash flows?
Yes, if the cash flows occur at regular monthly intervals and the rate is expressed per month. Keep the initial time-zero amount outside the NPV range. If intervals vary by actual date, consider XNPV instead.

What does a negative NPV mean?
It means the modeled future cash flows, discounted at the selected rate, are worth less than the initial outlay. It does not prove the project will lose money in every sense; it says the forecast does not meet that rate-based threshold under the inputs used.

What is the difference between NPV and XNPV in Excel?
NPV assumes equally spaced periods and treats the first value as occurring at the end of period one. XNPV uses a list of dates, so it is intended for cash flows that occur at irregular intervals; its values and dates must be aligned.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.