XNPV
Returns the net present value for a schedule of cash flows that is not necessarily periodic. XNPV discounts each cash flow based on its exact date, unlike NPV which assumes equal intervals.
XNPV(rate, values, dates)Arguments
rateThe discount rate to apply to the cash flowsvaluesA series of cash flows corresponding to the payment schedule in dates. The first payment is optional and represents a cost or payment at the beginning of the investment.datesA schedule of payment dates that corresponds to the cash flow payments.
=XNPV(0.09, B2:B6, C2:C6)Returns the NPV at a 9% discount rate for irregular cash flows
=XNPV(0.12, {-10000,3000,4200,6800}, {"1/1/2024","6/1/2024","3/1/2025","9/1/2025"})Calculates NPV with a 12% rate for this investment
=XNPV(A1, B2:B10, C2:C10)Uses the rate stored in A1 to calculate NPV across a larger schedule
- •Unlike NPV, XNPV uses exact dates so it handles any cash flow schedule
- •The first cash flow (usually negative) represents the initial investment
- •Rate is per year — use 0.09 for 9%, not 9
- •Pair XNPV with XIRR: if XNPV at rate X is 0, XIRR equals X
- •Using XNPV when cash flows actually occur at regular intervals - plain NPV is simpler and sufficient in that case, and also has different assumptions about when the first cash flow occurs
- •Forgetting that XNPV's rate argument is an annual rate, so it needs to reflect the actual yearly discount rate even if payments happen at irregular, non-annual intervals
- •Mismatching the size of values and dates, or using non-chronological or text dates, which can produce an incorrect result without necessarily raising a visible error
XIRRFinds the discount rate that makes XNPV equal to zero, rather than calculating XNPV at a fixed rate.NPVThe regular-interval counterpart to XNPV, assuming cash flows occur at evenly spaced periods rather than specific dates.PVHandles a single amount or a series of identical regular payments, a simpler case than XNPV's arbitrary, date-based cash flow schedule.How is XNPV different from NPV?
NPV assumes cash flows occur at equal, regular intervals starting one period from now. XNPV instead discounts each cash flow based on its exact date, so it works correctly for irregular payment schedules.
Is the rate in XNPV annual or per-period?
Always annual - XNPV converts the time between each cash flow's date and the first date into a fraction of a year internally, so the rate you supply should be the yearly rate.
Why might XNPV give an unexpected result even without an error?
The dates and values arrays might not correspond correctly, or the dates might not be genuine Excel date values - always double-check that both arrays are the same size and properly aligned.
Need to translate a formula using XNPV?
Use our translator to convert your complete formula