NPV vs XNPV
 

Hi there,

hoping someone can clarify something....

Why do you get different NPV values using NPV and XNPV, if the timeframes
are equal distance for the XNPV calculation?

I understand XNPV is for when the cashflow is not periodic??

But surely if the dates are periodic (in this case annually over 12 years)
it should give the same result as the standard NPV calculation?

Answer: NPV vs XNPV
 

Hi there!

Great question! While both NPV and XNPV are used to calculate the present value of future cash flows, they differ in how they take into account the timing of those cash flows.

NPV assumes that the cash flows occur at equal time intervals, such as annually or quarterly. However, XNPV takes into account the actual dates on which the cash flows occur. This means that if the cash flows are not evenly spaced, XNPV will give a more accurate result than NPV.

To answer your question, if the cash flows occur at equal time intervals, then NPV and XNPV should give the same result. However, if the cash flows occur on specific dates, such as irregularly timed investments or loan repayments, then XNPV will give a more accurate result.

Here's an example to illustrate this:

1. Let's say you have an investment that pays $1,000 annually for 5 years, with a discount rate of 10%.
2. If you use the NPV function in Excel, you would enter "=NPV(10%,1000,1000,1000,1000,1000)" and get a result of $3,791.
3. However, if you use the XNPV function and enter the actual dates on which the cash flows occur, such as "=XNPV(10%,{1/1/2022,1/1/2023,1/1/2024,1/1/2025,1/1/2026},{1000,1000,1000,1000,1000})", you would get a result of $3,486.
4. This is because XNPV takes into account the time value of money for each individual cash flow, rather than assuming they occur at equal intervals.

I hope this helps clarify the difference between NPV and XNPV!

NPV vs XNPV
 

On Oct 22, 8:47*pm, Energy wrote:
Why do you get different NPV values using NPV
and XNPV, if the timeframes are equal distance
for the XNPV calculation?

It would help if you provided a specific example. What results are
you geting for NPV and for XNPV? What rate are you using for each?
What are the values and dates?

The following is a lot speculation.

1. Do you know that for XNPV, "rate" is the annual discount, but for
NPV, "rate" is the periodic discount rate?

Suppose the annual rate is 12%, and the cash flow frequency is
monthly. Ostensibly, the NPV rate would be 1% (12% / 12). But if you
want to match the XNPV methodology, the monthly discount rate is
actually RATE(12,0,-1,1+12%).

2. Do you know that XNPV discounts over the exact number of days?
This will create a small (but usually only small) difference between
XNPV and NPV.

For example, you might consider monthly cash flows on the first of
every month to be "equal distance". NPV does, too. But for XNPV,
cash flows on Jan 1, Feb 1, Mar 1, etc are not exactly equal distance.

Do either or both of these factors explain the difference you are
seeing?

If not, again, please post numerical details.

NPV vs XNPV
 

Errata ....

On Oct 22, 9:39*pm, I wrote:
1. Do you know that for XNPV, "rate" is the annual
discount, but for NPV, "rate" is the periodic
discount rate?

But you wrote: "surely if the dates are periodic
(in this case annually over 12 years) it should give
the same result as the standard NPV calculation?"

So this speculation does not apply to you.

However, my speculation about the exact daily computation of XNPV
might. Over 12 years, there might be 3 leap years. But again, the
difference should be small. And again, a numerical example would
improve your chances of getting a dispositive answer.

NPV vs XNPV
 

Errata ....

On Oct 22, 9:39*pm, I wrote:
On Oct 22, 8:47*pm, Energy wrote:
Why do you get different NPV values using NPV
and XNPV, if the timeframes are equal distance
for the XNPV calculation?
[....]
Do either or both of these factors explain the
difference you are seeing?
If not, again, please post numerical details.

Following my own advice, I tried the following:

=npv(10%,A1:A3)
=xnpv(10%,A1:A3,B1:B3)

where A1:A3 contains -10000, 3000, 4200, and B1:B3 contains 1/1/2009,
1/1/2010, 1/1/2011. Note that those 1-year periods are exactly equal
in length (365 days).

NPV results in about -3456.05. XNPV results in about -3801.65.

Neither of my previous speculations would explain such a large
difference.

The explanation is: Excel's NPV discounts the first cash flow,
whereas XNPV does not.

Note that the following matches the XNPV results:

=A1 + npv(10%,A2:A3)

NPV vs XNPV
 

Year 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020

Cashflow -380 -190 -140 -65 100 130 110 300 350 350 350 350 350 350

Disc Rate 10%
NPV $390.93
XNPV $1,958.43




"Energy" wrote:

Hi there,

hoping someone can clarify something....

Why do you get different NPV values using NPV and XNPV, if the timeframes
are equal distance for the XNPV calculation?

I understand XNPV is for when the cashflow is not periodic??

But surely if the dates are periodic (in this case annually over 12 years)
it should give the same result as the standard NPV calculation?

NPV vs XNPV
 

Many many thanks for all your replies.
I posted on the data, dates and numbers I used as well as the results....

The NPV difference is huge .....

And it doesnt seem to hold to add the first year/numebr/data point back in....

I really am stumped!!

"joeu2004" wrote:

Errata ....

On Oct 22, 9:39 pm, I wrote:
On Oct 22, 8:47 pm, Energy wrote:
Why do you get different NPV values using NPV
and XNPV, if the timeframes are equal distance
for the XNPV calculation?
[....]
Do either or both of these factors explain the
difference you are seeing?
If not, again, please post numerical details.

Following my own advice, I tried the following:

=npv(10%,A1:A3)
=xnpv(10%,A1:A3,B1:B3)

where A1:A3 contains -10000, 3000, 4200, and B1:B3 contains 1/1/2009,
1/1/2010, 1/1/2011. Note that those 1-year periods are exactly equal
in length (365 days).

NPV results in about -3456.05. XNPV results in about -3801.65.

Neither of my previous speculations would explain such a large
difference.

The explanation is: Excel's NPV discounts the first cash flow,
whereas XNPV does not.

Note that the following matches the XNPV results:

=A1 + npv(10%,A2:A3)

NPV vs XNPV
 

You neglected to show the way you are calling NPV and XNPV. But I was
able to infer it from the results that you show.

It appears that you are making two mistakes.

The first is one that I mentioned in an earlier post: you are
including the initial cash flow (-380) in the NPV value list. You
should not, if you want to match the XNPV result.

Suppose 2007, 2008 etc are in B1:O1; and -380, -190 etc are in B2:O2.
You should to compute the NPV with: =B1+NPV(10%,C1:O1).

BTW, that is probably the correct NPV for you in any case. Is -380 at
the beginning of the period, or at the end? If it at the end, do you
want the NPV as of the end of the period, or as of the beginning?

(If -380 is at the end __and__ you want the NPV as of the beginning,
you will need to change your usage of XNPV.)

The second and more significant mistake is your data entry for years.
It appears that you entering literally 2007, 2008 etc; but XNPV
expects dates ("serial numbers"). If you format those cells as Date,
you will see that they are not the dates you had in mind.

Change those cells to 1/1/2007, 1/1/2008 etc. (Hint: After entering
those first two, select the two cells and drag to the right using the
box handle in the lower right corner.) If you just want to see the
year, use Format Custom and enter "yyyy" (without the quotes).

The you should compute the (X)NPV with: =XNPV(10%,B1:O1,B2:O2). Note
that you __do__ include column B in range for XNPV. This assumes that
-380 is at the beginning of the period, __or__ -380 is at the end of
the period and you want the NPV as of the end of the period. (Which
is really just two ways of saying the same thing.)

If you make both corrections, you should find that NPV returns about
430.02, whereas XNPV returns about 429.38.

That small difference is because of one of the other issue that I
mentioned previously, namely: from XNPV's point of view, the periods
are __not__ all equal in length. Leap years are 366 days, not 365
days.

Does that answer all of your questions? If not, post back.



On Oct 23, 1:15*pm, Energy wrote:
Year * *2007 * *2008 * *2009 * *2010 * *2011 * *2012 * *2013 * *2014 * *2015 * *2016 * *2017 * *2018 * *2019 * *2020

Cashflow * * * *-380 * *-190 * *-140 * *-65 * * 100 * * 130 * * 110 * * 300 * * 350 * * 350 * * 350 * * 350 * * 350 * * 350

Disc Rate * * * 10% * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
NPV * * $390.93 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
XNPV * *$1,958.43 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *



"Energy" wrote:
Hi there,

hoping someone can clarify something....

Why do you get different NPV values using NPV and XNPV, if the timeframes
are equal distance for the XNPV calculation?

I understand XNPV is for when the cashflow is not periodic??

But surely if the dates are periodic (in this case annually over 12 years)
it should give the same result as the standard NPV calculation?- Hide quoted text -

- Show quoted text -