XNPV in Excel: Discount Cash Flows by Their Actual Dates

=XNPV(rate, values, dates)

All arguments are required. Rate is the annual discount rate used with the documented 365-day year. Values and dates are matching sequences, with each amount attached to its corresponding date.

The first date establishes time zero. Microsoft says later entries may be in any order but must not precede that starting date. Keep the first date earliest and keep every amount/date pair together when sorting.

The dedicated reference requires at least one positive and one negative cash flow. That is the documented product contract, even though a mathematical discounted sum can be formed for other sign patterns. This guide’s examples follow the mixed-sign requirement.

Enter −1000, 400 and 700 in A2:A4. In B2:B4 enter =DATE(2026,1,1), =DATE(2026,7,2) and =DATE(2027,1,1). Put 10% in E1.

=XNPV(E1,A2:A4,B2:B4) independently gives 17.7984696638. The day offsets from B2 are 0, 182 and 365.

The first flow has exponent zero and is therefore included undiscounted. Unlike NPV, XNPV does not place its first listed amount one generic period into the future. Do not add the initial payment a second time outside the function.

In C2 enter =A2/(1+$E$1)^((B2-$B$2)/365) and fill down through C4. Summing these terms has the same independent target as XNPV.

The middle receipt is discounted for 182/365 of a year, not one half by assumption and not a full year because it is the second row. Actual date differences determine the exponent.

Use numeric DATE results instead of locale-dependent text such as 1/7/26. The displayed date format does not change a valid underlying date serial, but ambiguous text can be interpreted differently before a date is formed.

Keep the first −1000/January 1 pair in place. Swapping the two later amount/date pairs leaves the mathematical discounted sum unchanged because each amount retains its own date.

Sorting the amount column alone instead changes the schedule and can change the result. A spreadsheet’s rows are convenient storage, but the essential unit is the amount/date pair.

For audit work, keep a day-offset column and a discounted-value column beside the original inputs. They expose an unintended earlier date or a misaligned pair more clearly than a single aggregate output.

A flow one calendar year after January 1, 2028 occurs 366 days later because 2028 is a leap year. XNPV’s documented exponent for that gap is 366/365, not exactly one.

That differs from an annual periodic NPV model that simply advances one period. The distinction is intentional in the date-based formula and should not be removed by substituting a 366-day denominator in a leap year.

Date numbers are truncated to integers according to Microsoft. Intraday times are therefore not a separately modeled fraction of a day under the documented date treatment. These examples use date-only values.

Microsoft’s five amount/date pairs are −10000 on January 1, 2008; 2750 on March 1, 2008; 4250 on October 30, 2008; 3250 on February 15, 2009; and 2750 on April 1, 2009.

At 9%, independent XNPV is 2086.6476020315, matching its displayed 2086.65. Enter the dates with DATE rather than copying ambiguous slash-formatted strings from a different locale.

XIRR seeks a rate that makes this dated discounted sum zero. That is a related calculation, not a substitute for evaluating XNPV at a specified model rate.

Microsoft documents #VALUE! for nonnumeric arguments or invalid dates, #NUM! for a date preceding the starting date, and #NUM! when values and dates have different numbers of entries.

Its prose says later dates must be later, while its listed date error specifically concerns earlier dates. Equal-date native behavior is not certified by this guide; all worked schedules use strictly later subsequent dates.

Validate aligned numeric flows, date-only values and an ordinary rate above −1 for the real-valued model. The rate is a modeling assumption, not a recommendation. Native date coercions, workbook date-base edge cases and complete error behavior remain untested.

• 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 XNPV 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.