In Excel, use =NPV(rate, year_1_cash_flow:year_n_cash_flow)+year_0_cash_flow to calculate net present value when cash flows occur at the end of each year. Excel’s NPV function does not treat the first value in its range as a time-zero cash flow, so keep an upfront investment outside the range; to see the result year by year, add each year’s discounted cash flow to a running total.
What Excel NPV means
Net present value, or NPV, expresses future cash flows as a value at a chosen starting date. It discounts later amounts because money available now could be used or invested before those future payments arrive. The discount rate represents the required return or other rate selected for the analysis; it is an input, not something Excel chooses for you.
Excel’s NPV function assumes that the values supplied are equally spaced in time and that the first value arrives one period after the valuation date. With annual data, that means the first cash flow in the NPV range is normally the end-of-year-one amount. This timing rule is the main reason an otherwise plausible NPV formula can produce a misleading result.
Build a simple annual cash-flow example
Suppose a project costs $10,000 now and is expected to generate $3,000, $3,200, $3,500, $3,600, and $4,000 at the ends of the next five years. For illustration, use a 10% annual discount rate. These amounts are examples for explaining the spreadsheet, not a forecast or investment recommendation.
- Enter the initial outflow in B2 as -10000.
- Enter the end-of-year cash flows in B3:B7, in order: 3000, 3200, 3500, 3600, and 4000.
- Enter the annual discount rate, 10%, in F1.
With the initial investment in B2, the five annual cash flows in B3:B7, and the annual rate in F1, enter this formula to calculate the project’s NPV at time zero:
=NPV(F1,B3:B7)+B2
The present values of the five future inflows are approximately $2,727.27, $2,644.63, $2,629.60, $2,458.85, and $2,483.68. Their total is about $12,944.03; adding the -$10,000 initial outflow gives an NPV of approximately $2,944.03. Small differences in displayed totals can result from rounding the individual amounts for presentation; keep full precision in the cells.
Show NPV one year at a time
The standard NPV function returns one value for the entire selected range. For a year-by-year view, make a discounted-cash-flow column and a cumulative-NPV column. Put year numbers 1 through 5 in A3:A7, the cash flows in B3:B7, and the rate in F1. Leave the time-zero investment in B2. In C3, calculate the present value of the first year’s flow; copy the formula down through C7.
With year numbers in A3:A7, cash flows in B3:B7, and the discount rate in F1, enter this in C3 and fill down:
=B3/(1+$F$1)^A3
The absolute reference to F1 keeps the rate cell fixed as the formula is copied, while the relative references to A3 and B3 change by row. The exponent is the year number, so the year-two flow is discounted for two years, not one.
To show cumulative NPV including the initial outflow, set C2 equal to B2, then build the running total in another column. With the initial investment in B2, yearly present values in C3:C7, and the running totals in D2:D7, enter this in D2:
=B2
With the prior running total in D2 and the first year’s present value in C3, enter this in D3 and fill down through D7:
=D2+C3
The cumulative figures are approximately -$7,272.73 after year one, -$4,628.10 after year two, -$1,998.50 after year three, $460.35 after year four, and $2,944.03 after year five. Each is the time-zero value of the initial cost plus the discounted cash flows received up to that year. This makes the timing and contribution of each year visible instead of hiding them in a single result.
Why the initial investment sits outside NPV
Excel interprets the first value inside NPV as arriving one period from now. If you put the time-zero investment in the first position of the range, Excel discounts it by one period as if it occurred at the end of year one. That changes the result. For a conventional project with a payment today and annual flows afterward, put the initial outflow in a separate cell and add it to the function result, as in the example.
Cash flows do not all have to be positive. A later equipment replacement, cleanup cost, or additional investment can be entered as a negative amount in its appropriate year. Excel includes positive and negative values in the same calculation. Check the signs carefully: an initial cost entered as positive rather than negative will increase the calculated NPV instead of reducing it.
Check timing, periods, and the rate
The rate and the cash-flow spacing need to match. If each row represents one year, use an annual rate. If the rows represent months, use a rate per month, or convert the annual assumption consistently; applying an annual rate to monthly periods discounts the flows as if each month were a full year. Likewise, a range with a missing year should not silently be treated as though its remaining values were evenly spaced one year apart. Include a zero for a year with no cash flow when the annual timeline continues.
- Confirm that the first value passed to NPV is the end of the first period, not a payment made today.
- Check that the discount rate uses the same period as the cash-flow rows.
- Use negative signs for outflows and positive signs for inflows.
- Verify the year labels and make sure there are no accidental blank rows or omitted periods.
- Keep full precision in calculations and use cell formatting to round displayed currency.
Excel formulas in this article use commas between arguments, as in standard Microsoft 365 for Windows examples. Depending on Windows regional settings, Excel may display or require semicolons as the argument separator. If a correctly structured formula is rejected, check the list separator configured for the computer rather than changing the financial logic.
When to use XNPV instead
Annual NPV is appropriate when cash flows occur at regular, evenly spaced intervals, such as each year-end. If actual cash-flow dates are irregular, Excel’s XNPV function discounts using the dates supplied rather than assuming a fixed interval. Its first date is the valuation date for the series, so include the initial cash flow and its date when that is the intended starting point.
With cash-flow amounts in B2:B7, their corresponding Excel dates in A2:A7, and the discount rate in F1, use:
=XNPV(F1,B2:B7,A2:A7)
For example, the dates might include an initial payment on January 1 and receipts on dates that vary from year to year. Do not use NPV merely because there are six rows; choose the function based on whether the timing is regular. Both functions require a rate assumption, and the result depends on that input as well as on the cash-flow estimates.
Interpret the result without overreading it
A positive NPV means the discounted inflows exceed the discounted outflows under the cash-flow estimates and rate entered. A negative NPV means they do not under those assumptions. It is not a guarantee that a project will deliver the forecast, and it does not establish that the chosen rate is appropriate. Changing expected receipts, timing, costs, or the discount rate can materially change the result.
For a useful review, compare the year-by-year cash-flow assumptions with their source, test reasonable alternative rates or cash-flow scenarios, and make sure the valuation date is clear. Avoid rounding intermediate formulas to whole dollars if precision matters. This spreadsheet example is educational, not financial, tax, investment, or legal advice; check actual contract terms with the lender or a qualified professional when applicable.
Common mistakes and quick checks
A frequent mistake is to use =NPV(F1,B2:B7) when B2 contains the upfront investment and B3:B7 contain later annual flows. That discounts the upfront amount as a future period. Another is to calculate each year’s discounted amount correctly but add the undiscounted initial cost with the wrong sign. Comparing the final cumulative value with the direct NPV formula is a practical check: both should agree when they use the same rate, periods, and cash flows.
Also distinguish the annual discounted contribution from cumulative NPV. The discounted contribution for year three is only that year’s present value. Cumulative NPV through year three includes the initial investment and the discounted contributions from years one, two, and three. Label columns clearly so that a reader does not mistake one measure for the other.
Frequently Asked Questions
Does Excel NPV include the initial investment?
No. The function discounts every value in its range as a future periodic cash flow. For a payment today, calculate NPV on the later flows and add the time-zero amount separately.
How do I calculate NPV for each year in Excel?
Discount each year’s cash flow by its year number, then add that present value to a running total that starts with the initial outflow. The running total gives cumulative NPV through each year.
Should year zero be included in the NPV range?
Not when using Excel’s ordinary NPV function with standard end-of-period cash flows. Keep year zero outside the range. A date-based XNPV setup is different: include cash flows with their actual dates and use the intended starting date.
Why does my NPV change when I change the rate?
The rate determines how strongly future cash flows are discounted. A higher positive rate generally reduces the present value of future inflows, while a lower rate generally raises it, assuming the cash flows and their timing remain unchanged.
Can I use NPV for monthly cash flows?
Yes, if the cash flows are equally spaced monthly and the rate is a monthly rate. If the dates are irregular, use XNPV with actual dates instead of treating uneven intervals as equal periods.
Related
- Large Excel Cheat Sheet Mouse Pad: What to Know
- Excel Mail Merge: Create Letters, Labels, and Emails
- Excel Daylight Saving Time: How Dates and Hours Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUBSTITUTE Multiple Values: Formulas and TipsTo substitute multiple values in Excel, nest the SUBSTITUTE function for each old-to-new text pair, or use a…
- How to Add Page Numbers in Word Bottom Right CornerTo add page numbers at the bottom right in Word for Microsoft 365 on Windows, select Insert >…
- Lowercase Names in Excel: Formula, Fill Down, and ReplaceTo lowercase names in Excel, use the LOWER function in a helper column, then fill the formula down.…
- Excel Power Query Filter Rows: Practical Steps and ExamplesTo filter rows in Excel Power Query, open a query in the Power Query Editor, select the filter…
- CHAR Excel Examples: Formulas, Line Breaks, and MoreIn Excel for Microsoft 365, use CHAR(number) to return the character assigned to a numeric code, such as…
- Excel LET: Can It Use Multiple Variables?Yes. Excel’s LET function allows multiple named values in one formula: write each name and value as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.