Menu
EXCEL XNPV FUNCTION
Excel XNPV Function Introduction
In Excel, the XNPV function utilises particular dates that match to each cash flow discounted in the series, whereas the normal NPV function assumes all time periods are equal. As a result, the XNPV function is considerably more exact than the standard NPV calculation and should be used instead.
Description of Excel XNPV Function
Syntax :
=XNPV(Rate, Cash Flows, Dates of Cash Flow)
Parameters :
1. Rate – The discount rate that will be applied for the duration of the term (see hurdle rate and WACC articles to learn about what rate to use).
2. Values (Cash Flows) – This is an array of numeric values that indicate payments and revenue, where:
• Negative values are considered outgoing payments (negative cash flow).
• Positive numbers are regarded as earnings (positive cash flow).
3. Dates (of Cash Flows) – This is a collection of dates that correlate to a collection of payments. The length of the date array should be the same as the length of the values array.
Where:
di = the i‘th payment date
d1 = the 0’th payment date
Pi = the i‘th payment3.
Step By Step Guide of XNPV Function
Example : The assumptions were made in the XNPV illustration: • The discount rate is 10% • The start date is June 30, 2018 (date we are discounting the cash flows back to)
• Cash flows are received on the exact date they correspond to • There is just a 6-month wait between the start date and the first cash flow. According the following, the XNPV formula yields a value of $772,830.7, but the regular NPV formula yields a value of $670,316.4. The reason for this discrepancy is because XNPV understands that the time period between the starting date and the first cash flow is just 6 months, but the NPV function considers it a full-time period.
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
