XNPV vs. NPV(quarterly) different results

[magis]

I am dicounting a series of future cash flows at two rates back to 1/25/06 .
The cashflows are quarterly and even once we get to 2018 but the first and
the last payments are different than all the other payments . Because I have
exact dates, to simplify the calculation I used the XNPV fucntion. I checked
my work by recalclulating, discounting quarterly, using the NPV and PV
function in Excel and on my HP. The quarterly discounting gives a very
different answer from the XNPV result. Because the XNPV function discounts
daily, I would have expected the XNPV result to be a lower present value than
under quarterly discounting. My results are below. Can anyone help me get a
comfort level on what the PV is for this series of cash flows?

Many thanks in advance,
Anne

Discount HP 12C
Rate XNPV (below) NPV(Excel) PV (Excel) NPV then PV
16.0% $15,325,146 $13,530,060 $13,514,077 $13,530,060
17.5% $12,662,811 $10,931,861 $10,917,838 $10,931,861
Difference $2,662,335 $2,598,198 $2,596,238 $2,598,198
(NPV is end of period
started at 4/25/06)

16.0% 17.5%
Cash Payment PV at PV at
Flow Date Quarters 1/25/2006 1/25/2006
0 1/25/2006 - - -
2,593,876 1/25/2018 48 394,266 331,670
5,276,303 4/25/2018 49 771,560 646,763
5,276,303 7/25/2018 50 741,964 619,726
5,276,303 10/25/2018 51 713,197 593,540
5,276,303 1/25/2019 52 685,545 568,461
5,276,303 4/25/2019 53 659,532 544,953
5,276,303 7/25/2019 54 634,234 522,172
5,276,303 10/25/2019 55 609,644 500,109
5,276,303 1/25/2020 56 586,007 478,977
5,276,303 4/25/2020 57 563,529 458,954
5,276,303 7/25/2020 58 541,913 439,768
5,276,303 10/25/2020 59 520,902 421,187
5,276,303 1/25/2021 60 500,706 403,390
5,276,303 4/25/2021 61 481,707 386,708
5,276,303 7/25/2021 62 463,229 370,542
5,276,303 10/25/2021 63 445,269 354,886
5,276,303 1/25/2022 64 428,005 339,891
5,276,303 4/25/2022 65 411,765 325,835
5,276,303 7/25/2022 66 395,970 312,214
5,276,303 10/25/2022 67 380,618 299,022
5,276,303 1/25/2023 68 365,861 286,387
5,276,303 4/25/2023 69 351,978 274,544
5,276,303 7/25/2023 70 338,477 263,067
5,276,303 10/25/2023 71 325,354 251,951
5,276,303 1/25/2024 72 312,739 241,306
5,276,303 4/25/2024 73 300,743 231,218
5,276,303 7/25/2024 74 289,207 221,552
5,276,303 10/25/2024 75 277,994 212,191
437,573 1/25/2025 76 22,161 16,854

13,514,077 10,917,838

 

[joeu2004]

Anne,

Can anyone help me get a comfort level on what
the PV is for this series of cash flows?

The short answer is: if you want to compare XNPV()
and NPV() results, use RATE(p,,-1,1+r) for the
NPV() rate, where "r" is the annual rate and "p"
is the number of periods per year.

I think you are asking two questions:

1. Shouldn't XNPV() be less than NPV(), given that
XNPV() uses a daily compounding rate, whereas NPV()
uses a monthly compounding rate in this case?

2. Which compounding rate or period frequency -- daily
or monthly -- is correct for computing PV in general?

The answer to #1 is a qualified "yes": XNPV is less
than or equal to NPV __if__ you use the same assumptions
for determining the period rates. It is your varying
assumptions that cause the contradiction that you
observe. I will elaborate below.

I think a contributing factor to your discomfort is
the fact that your PV computations seem to be incorrect.
My PV computations differ from yours, but they do match
your XNPV and NPV results under varying assumptions
about the periodic rates.

I cannot infer what you did differently. My formulas
are as follows. I will explain them below.

A2:A31: date (1/25/2006, 1/25/2018, 4/25/2018,..., 1/25/2025)
B3:B31: calendar quarters (48, 49,..., 76)
C3:C31: calendar days: =A3-$A$2
G2:G31: cash flows (0, 2,593,876, 5,276,303,..., 437,573)
H3:H31: PV at 16%/4 months: =PV(16%/4,B3,,-G3)
I3:I31: PV at 16%^(1/4) months: =PV(RATE(4,,-1,1.16),B3,,-G3)
J3:J31: PV at 16%^(1/365) calendar days:
=PV(RATE(365,,-1,1.16),C3,,-G3)
L3:L31: PV at 16%/365 calendar days:
=PV(16%/365,C3,,-G3)
H32:L32: NPV, sum of the PVs: =SUM(H2:H31)

My results are:
H32: 13,530,060
I32: 15,342,347
J32: 15,325,146
L32: 12,909,932

Note that H32 matches your NPV computations using
both Excel and HP12C. This is the NPV of the monthly
cash flows using 16%/4 for the periodic rate.

Also note that J32 matches your XNPV computation.
This is the NPV of the monthly cash flow using
"16%^(1/365)" for the daily rate -- that is, the
daily rate that results in a 16% annual rate.
"16%^(1/365)" is my stylistic shorthand for the actual
formula, which is (1+16%)^1/365 - 1 or equivalently
RATE(365,,-1,1.16).

Note the important difference in assumptions. Your
use of 16%/4 monthly rate results in an annual rate
of nearly 17%, not 16%. To be consistent with Excel's
XNPV -- that is, to have the same effective annual
rate -- you would want a monthly NPV rate of "16%^(1/4)"
(stylistically; see above). I32 is the NPV using
"16%^(1/4)", and it does indeed exceed J32, as you
expected.

Conversely, L32 is the NPV using 16%/365, the daily
rate computed in the same way that you determined
the monthly rate for H32. Note that L32 is indeed
less than H32, as you expected.

Conclusion: If you want to compare XNPV() and NPV()
results, use RATE(p,,-1,1+r) for the NPV() rate,
where "r" is the annual rate and "p" is the number
of periods per year.

I hope that restores your confidence in the NPV and
XNPV computations, whether you do it with a function
or by summing the PV of the cash flows.

The answer to #2 is less clear: should the NPV be
computed based on daily or periodic compounding?
As a corollary: should the daily or periodic rate
be computed as r/p or as "r^(1/p)" (stylistically;
see above)?

I would argue that there is no single correct answer.

If you are computing the PV of a real investment, I
would use the compounding frequency and method of
computing the rate that fits the investment. For
example, money market instruments compound daily,
and the daily rate is r/365. Thus, for example, if
you are comparing two investments that both yield
10% annually, but one compounds daily and that other
compounds monthly, the first will correctly have the
lower PV.

On the other hand, if the discount rate is arbritrary
(e.g, cost of capital) and especially if it is an
inflation rate, I would use a geometric rate that
preserves the annual rate, i.e. "r^(1/p)" (stylistically;
see above). Thus, for example, if the cost of an
opportunity grows at an inflation rate of 3% per year
(example: PV of the cost of materials), it should
not matter whether we consider daily or monthly cash
flows; the annual result must still be 3% per year.

But when using NPV to compare opportunities with
arbitrary discount rates and the same periodicity,
where the relative PV is important, but not the actual
number, I would use the simple rate of r/p just because
it is easier to remember and compute.

I hope that helps.


-----

 

[joeu2004]

[Reposting abridged version.]

Anne,

Can anyone help me get a comfort level on what
the PV is for this series of cash flows?

The short answer is: if you want to compare XNPV()
and NPV() results, use RATE(p,,-1,1+r) for the
NPV() rate, where "r" is the annual rate and "p"
is the number of periods per year.

I think you are asking two questions:

1. Shouldn't XNPV() be less than NPV(), given that
XNPV() uses a daily compounding rate, whereas NPV()
uses a monthly compounding rate in this case?

2. Which compounding rate or period frequency -- daily
or monthly -- is correct for computing PV in general?

The answer to #1 is a qualified "yes": XNPV is less
than or equal to NPV __if__ you use the same assumptions
for determining the period rates. It is your varying
assumptions that cause the contradiction that you
observe. I will elaborate below.

I think a contributing factor to your discomfort is
the fact that your PV computations seem to be incorrect.
My PV computations differ from yours, but they do match
your XNPV and NPV results under varying assumptions
about the periodic rates.

I cannot infer what you did differently. My formulas
are as follows. I will explain them below.

A2:A31: date (1/25/2006, 1/25/2018, 4/25/2018,..., 1/25/2025)
B3:B31: calendar quarters (48, 49,..., 76)
C3:C31: calendar days: =A3-$A$2
G2:G31: cash flows (0, 2,593,876, 5,276,303,..., 437,573)
H3:H31: PV at 16%/4 months: =PV(16%/4,B3,,-G3)
I3:I31: PV at 16%^(1/4) months: =PV(RATE(4,,-1,1.16),B3,,-G3)
J3:J31: PV at 16%^(1/365) calendar days:
=PV(RATE(365,,-1,1.16),C3,,-G3)
L3:L31: PV at 16%/365 calendar days:
=PV(16%/365,C3,,-G3)
H32:L32: NPV, sum of the PVs: =SUM(H2:H31)

My results are:
H32: 13,530,060
I32: 15,342,347
J32: 15,325,146
L32: 12,909,932

Note that H32 matches your NPV computations using
both Excel and HP12C. This is the NPV of the monthly
cash flows using 16%/4 for the periodic rate.

Also note that J32 matches your XNPV computation.
This is the NPV of the monthly cash flow using
"16%^(1/365)" for the daily rate -- that is, the
daily rate that results in a 16% annual rate.
"16%^(1/365)" is my stylistic shorthand for the actual
formula, which is (1+16%)^1/365 - 1 or equivalently
RATE(365,,-1,1.16).

Note the important difference in assumptions. Your
use of 16%/4 monthly rate results in an annual rate
of nearly 17%, not 16%. To be consistent with Excel's
XNPV -- that is, to have the same effective annual
rate -- you would want a monthly NPV rate of "16%^(1/4)"
(stylistically; see above). I32 is the NPV using
"16%^(1/4)", and it does indeed exceed J32, as you
expected.

Conversely, L32 is the NPV using 16%/365, the daily
rate computed in the same way that you determined
the monthly rate for H32. Note that L32 is indeed
less than H32, as you expected.

Conclusion: If you want to compare XNPV() and NPV()
results, use RATE(p,,-1,1+r) for the NPV() rate,
where "r" is the annual rate and "p" is the number
of periods per year.

I hope that restores your confidence in the NPV and
XNPV computations, whether you do it with a function
or by summing the PV of the cash flows.

The answer to #2 is less clear: should the NPV be
computed based on daily or periodic compounding?
As a corollary: should the daily or periodic rate
be computed as r/p or as "r^(1/p)" (stylistically;
see above)?

I would argue that there is no single correct answer.

If you are computing the PV of a real investment, I
would use the compounding frequency and method of
computing the rate that fits the investment. For
example, money market instruments compound daily,
and the daily rate is r/365. Thus, for example, if
you are comparing two investments that both yield
10% annually, but one compounds daily and that other
compounds monthly, the first will correctly have the
lower PV.

On the other hand, if the discount rate is arbritrary
(e.g, cost of capital) and especially if it is an
inflation rate, I would use a geometric rate that
preserves the annual rate, i.e. "r^(1/p)" (stylistically;
see above). Thus, for example, if the cost of an
opportunity grows at an inflation rate of 3% per year
(example: PV of the cost of materials), it should
not matter whether we consider daily or monthly cash
flows; the annual result must still be 3% per year.

But when using NPV to compare opportunities with
arbitrary discount rates and the same periodicity,
where the relative PV is important, but not the actual
number, I would use the simple rate of r/p just because
it is easier to remember and compute.

I hope that helps.

 

[joeu2004]

Can anyone help me get a comfort level on what
the PV is for this series of cash flows?

The short answer is: if you want to compare XNPV()
and NPV() results, use RATE(p,,-1,1+r) for the NPV()
rate, where "r" is the annual rate and "p" is the number
of periods per year.

I think a contributing factor to your discomfort is the
fact that your PV computations seem to be incorrect.
My PV computations differ from yours, but they do match
your XNPV and NPV results under varying assumptions
about the periodic rates.

For a more complete explanation, see my previous
response(s) by using Google Groups or some other
newsreader. For some reason (length?), my other
postings to this thread do not appear in the MS
Community Newsgroups. I know there can be some
delay. But it has been more than 12 hours, and my
other more-recent postings to these newsgroups have
appeared in much less time -- typically under 5 min.

(It will be interesting to see if this one succeeds.)