NPV vs. XNPV - same periods, different results - why?

[Mariner]

Simple cash flow stream:

-4,000 outflow at 12/31/07
5,000 inflow at 12/31/08
2,000 inflow at 12/31/09

discount rate of 10% yields the following:

NPV = $1,998.50
XNPV = $2,198.35

Is this because XNPV is continuously compounding? Why is it different when distance between periods is the same?

Thanks in advance,

 

[iliace]

Let's say I have my amounts in A1:A3, dates in B1:B3, and rate in C1. This is the array-formula equivalent of NPV:

=SUM(A1:A3/((1+C1)^ROW(A1:A3)))
or
=SUM(A1:A3/({1.1;1.21;1.331}))

This is the XNPV:

=SUM(A1:A3/((1+C1)^((B1:B3-B1)/365)))
or
=SUM(A1:A3/({1;1.10028727366367;1.21031600103004}))

In essence, NPV is like an ordinary annuity, and XNPV is like an annuity due.

 

[Mariner]

My formulas are as follows:

+NPV(C1,A1:A3) = $1,998.50
+XNPV(C1,A1:A3,B1:B3) = $2,198.35

How would I trick the dates in column B to match the NPV amount?

Thanks,

 

[Mariner]

Dates are 12/31/05, 12/31/06, and 12/31/07.

 

[iliace]

Add a dummy date with a 0 cash flow, 365 days before your first cash flow.

Normally a capital outlay at the beginning of a term is not discounted, though. That's why XNPV does it this way.

Right now I have:

A1:A4 = {0;-4000;5000;2000}
B1:B4 = {12/31/2006;12/31/2007;12/31/2008;12/31/2009}
C1 = 0.1

=NPV(C1,A2,A3,A4) returns 1998.5
=XNPV(C1,A1:A4,B1:B4) returns 1997.03

You're still going to have a slight difference due to days in a year vs straight-line periods when you have a leap year (2008). If instead I have this:

B1:B4 = {12/31/2004;12/31/2005;12/31/2006;12/31/2007}

They both returns 1998.5

 

[Mariner]

Thanks, Iliace! Much appreciated.

 

[CHBRO]

I have same problem different XNPV and NPV values for identical periods and amounts:
Year Free
Cash Flow
-----------------
2011 1,864
2012 1,887
2013 1,984
2014 2,034
2015 2,137
2016 2,148
2017 2,212

Discount factor= 8%
XNPV = 11,349
NPV = 10,511
Difference 7.4%

 

[iliace]

You have the same issue - the first value is not discounted under XNPV.

Excel Workbook
	A	B	C	D	E	F
1		1/1/2010	0
2	2011	1/1/2011	1,864		0.08
3	2012	1/1/2012	1,887
4	2013	1/1/2013	1,984		NPV	$10,512.43
5	2014	1/1/2014	2,034		XNPV	$10,510.65
6	2015	1/1/2015	2,137
7	2016	1/1/2016	2,148
8	2017	1/1/2017	2,212
Sheet1

 

[CHBRO]

Brilliant! iliace - many thanks for the help
All the best, CHBRO