NPV vs. XNPV

[Guest]

I am calculating the net present value of annual payments over a 20 year
term. The payment is received in the middle of each year. Because the
payments are periodic does it matter which function I use (NPV or XNPV)?

I have run both NPV and XNPV functions and get the same result. I am trying
to confirm. My date value range is (A2013:A2033) for the XNPV function is as
follows:

cell formula
A2013 =06/15/2005
A2014 =A2013 + 365
A2015 =A2014 + 365
A2016 =A2015 + 365
A2017 =A2016 + 365
A2018 =A2017 + 365
A2019 =A2018 + 365
A2020 =A2019 + 365
A2021 =A2020 + 365
A2022 =A2021 + 365
A2023 =A2022 + 365
A2024 =A2023 + 365
A2025 =A2024 + 365
A2026 =A2025 + 365
A2027 =A2026 + 365
A2028 =A2027 + 365
A2029 =A2028 + 365
A2030 =A2029 + 365
A2031 =A2030 + 365
A2032 =A2031 + 365
A2033 =A2032 + 365

Any help will be appreciated.

 

[Daniel CHEN]

Both NPV and XNPV return the same results, because your payment are
periodic - both function do the same thing.
If your payments are not periodic, then only XNPV works.
--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
=================================

 

[Guest]

I am calculating the net present value of annual payments
over a 20 year term. The payment is received in the middle
of each year. Because the payments are periodic does it
matter which function I use (NPV or XNPV)?

There can be a small, probably imperceptible difference
because "middle of each year" is not the same number of days
apart in all cases.

I have run both NPV and XNPV functions and get the same
result. I am trying to confirm.

As I would expect, especially if you round to a penny or dollar.

My date value range is (A2013:A2033) for the XNPV function
is as follows:
cell formula
A2013 =06/15/2005
A2014 =A2013 + 365
[... etc ...]

Do you really have cash flows exactly 365 days apart? Or,
as I suspect, do you have cash flows on June 15 of every
year?

In fact, if June 15 is a weekend, I wonder if the date of the
cash flow is the preceding Friday or following Monday,
further varying the number of days between "annual" cash
flows.

Returning to your original question ....

Because the payments are periodic does it
matter which function I use (NPV or XNPV)?

Does it matter whether your use a small hammer or a
sledgehammer?

My answer is: use the right tool that fits the job, and no
more. Or as Einstein put it: a solution should be as simple
as possible, and no simpler.

For even cash flows, I would use NPV. The added "accuracy"
gained by using XNPV for even cash flows is cancelled by the
fact that the computation is an estimate in the first place.

(Of course, XNPV is the right tool to use for uneven cash flows.)

Finally, you might consider the fact that NPV is a built-in
function, whereas XNPV is part of the Analysis ToolPak.
Personally, that never bothers me. But some people think
it's worthwhile trying to avoid ATP functions. And I can
certainly circumstances where I might agree.