Help in reconciling result of XNPV and NPV

[xiedwoo]

Hello everyone,

Please I need your help in resolving my present value analysis results. I have used both the XNPV and NPV formulas but getting different results. For the XNPV, I have used the discount rate of 25% and for the NPV, I have converted the discount rate to a monthly equivalent rate of 1.877% to reflect the frequency of cash flows used in the analysis. However, I am still left with a big difference and would need your assistance to resolve. Please see below for details of the work I am doing. Thank you.

Dates Cash Flows (in USD)

28-Sep-16 -
30-Sep-16 1,389,252
31-Oct-16 1,381,127
30-Nov-16 1,343,525
31-Dec-16 1,328,294
31-Jan-17 1,283,984
28-Feb-17 1,258,643
31-Mar-17 1,210,211
30-Apr-17 1,179,076
31-May-17 1,165,973
30-Jun-17 1,124,419
31-Jul-17 1,090,262
31-Aug-17 1,059,340
30-Sep-17 1,049,304
31-Oct-17 1,013,981
30-Nov-17 960,746
31-Dec-17 924,587
31-Jan-18 890,138
28-Feb-18 871,682
31-Mar-18 814,918
30-Apr-18 785,584
31-May-18 775,228
30-Jun-18 754,633
31-Jul-18 718,702
31-Aug-18 687,091
30-Sep-18 684,751
31-Oct-18 667,292
30-Nov-18 631,528
31-Dec-18 610,374
31-Jan-19 601,353
28-Feb-19 591,303
31-Mar-19 556,327
30-Apr-19 545,625
31-May-19 536,891
30-Jun-19 526,879
31-Jul-19 503,506
31-Aug-19 489,154
30-Sep-19 478,709
31-Oct-19 463,374
30-Nov-19 439,867
31-Dec-19 418,668
31-Jan-20 413,490
29-Feb-20 403,829
31-Mar-20 385,642
30-Apr-20 375,231
31-May-20 362,079
30-Jun-20 345,373
31-Jul-20 333,699
31-Aug-20 5,356,918

XNPV @ 25% 29,277,981

NPV @ 1.877% 28,776,208

[joeu2004]

I have used both the XNPV and NPV formulas but getting different results.
[....]
Dates Cash Flows (in USD)
28-Sep-16 -
30-Sep-16 1,389,252
31-Oct-16 1,381,127
[....]
31-Jul-20 333,699
31-Aug-20 5,356,918

XNPV @ 25% 29,277,981
NPV @ 1.877% 28,776,208

First, for NPV, it appears that you used (1+25%)^(1/12)-1, which is correct to use instead of the rounded value.

Second, it appears that you used NPV(...,B3:B50), but XNPV(...,B2:B50,A2:A50).

But note that B2 is only 2 days before B3, whereas that use of NPV effectively assumes that B2 is a month before (i.e. one full time period).

If we use 31-Aug-16 instead of 28-Sep-16, XNPV returns about 28,781,069.08. That is a lot closer to 28,776,207.73, which NPV returns. Specifically, XNPV is about 0.02% more than NPV.

That difference is probably due to the fact that the XNPV periods are not 365/12 or 366/12 days (about 30.4167 or 30.5). Instead, they are 28, 29, 30 or 31 days.

Alternatively, use B3+NPV(...,B4:B50) and XNPV(...,B3:B50,A3:A50). If you wish, you can use PV to roll both back 2 days to 28-Sep-16.

[newdoverman]

I used your example values excluding the first value because I believe that it skews the results for the reasons given by joeu2004 above.

My results

	C	D
1	XNPV	$29,313,800.53
2
3	NPV	$28,760,562.83

If you use Aug 31 as suggested and fill out the rate for NPV to 6 decimal places, this is the result

	F	G	H	I	J
1	XNPV	$28,781,069.08	Rate	0.25
2	NPV	$28,776,342.74	Rate	0.018769	(1+25%)^(1/12)-1
3	Difference	$4,726.33
4	% Diff	0.016421667

I hope that this is of some help.

[xiedwoo]

Thank you very much, joeu2004 and newdoverman. I think the solution is to use August 31, 2016 as the start date as you suggested. I am really grateful.