NPV vs XNPV

[Energy]

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?

 

[joeu2004]

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.

 

[joeu2004]

Errata ....

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.

 

[joeu2004]

Errata ....

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)

 

[Energy]

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]

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!!

Errata ....

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)

 

[joeu2004]

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.