NPV in Excel: Discount Period-End Cash Flows Without Moving Time Zero

=NPV(rate, value1, [value2], ...)

Rate and the first value argument are required. Microsoft permits one through 254 value arguments. The rate is for one interval between cash flows, and the numeric flows are ordered, equally spaced period-end amounts.

The first numeric flow passed to NPV is discounted one period. A flow occurring at time zero belongs outside the NPV call. That timing convention is essential even if the worksheet labels the first row “Initial cost.”

Positive numbers represent receipts and negative numbers represent payments from the chosen perspective. All amounts must use compatible currency units. The function does not select a discount rate or determine whether an illustrative project should be undertaken.

Put periods 0, 1, 2 and 3 in A2:A5 and cash flows -1000, 400, 500 and 600 in B2:B5. Put periodic rate 10% in E1.

Use =NPV($E$1,B3:B5)+B2 in E2. Independent present value of the future receipts is 1227.6483846732, and adding the time-zero cost gives 227.6483846732.

In C2 enter =B2/(1+$E$1)^A2 and fill down. Summing C2:C5 gives the same independent result. The exponent zero leaves the initial cost undiscounted.

=NPV($E$1,B2:B5) instead discounts every listed flow one period later, including the initial −1000. Its independent result is 206.9530769756, equal to the correctly timed result divided by 1.1.

This is a different timeline, not a rounding discrepancy. Including the initial flow inside NPV and also adding it outside would count it twice and create another error.

The source’s first example deliberately describes its initial cost as occurring one year from today, so including that cost inside NPV is consistent there. Do not copy the formula without also retaining its timing assumption.

For a separate schedule with initial −1000 and future flows 400, 0, 600, use =NPV(10%,400,0,600)-1000. Independent value is −185.5747558227.

If the zero period is omitted and the numeric sequence becomes 400,600, the second receipt moves forward one interval. =NPV(10%,400,600)-1000 instead gives −140.4958677686.

A numeric zero preserves its period. Blank-looking cells and text are not reliable timing placeholders because Microsoft’s array/reference rules count numeric entries. Keep explicit period labels and zero amounts where the calendar genuinely contains a zero-flow interval.

Microsoft’s future-start example uses flows −10000, 3000, 4200, 6800 inside NPV at 10%. Independent result is 1188.4434123352, consistent with 1188.44.

Its second example places −40000 at time zero and future annual flows 8000, 9200, 10000, 12000, 14500 at an 8% rate. Independent value is 1922.0615549324.

Adding a sixth-year payment of −9000 inside the future-flow list changes the independent value to −3749.4650870156. It must be appended as period six, not subtracted undiscounted as though paid today.

Use a monthly rate for monthly intervals and an annual rate for annual intervals. A quoted annual effective rate converts to a monthly equivalent through (1+annual_rate)^(1/12)-1; dividing by twelve instead describes a different nominal-rate convention.

For irregular dates, XNPV supplies an explicit date-based calculation. For equal recurring annuity payments with beginning/end timing, PV has a different contract. IRR finds a rate making a consistent periodic discounted sum zero; NPV evaluates the sum at a supplied rate.

The dedicated reference says that only numbers in arrays/references are counted and lists text, logical values, empties and error values as ignored. This is the source’s wording, not a newly executed guarantee for every native error/coercion path. The reproducible schedules here contain numeric amounts only.

Validate a numeric rate above −1 for ordinary real-valued discounting, and investigate missing cash flows instead of replacing every problematic cell automatically. The dedicated page does not enumerate a complete error-code table. Check timing, signs, rate units and included periods before interpreting the final number.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft NPV reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.