NPV and XNPV

[pdvsa]

Date cash flows interest rate
1/31/2016 -10,000 9.00%
2/29/2016 574
3/31/2016 864
4/30/2016 1,246
5/31/2016 2,748
6/30/2016 3,367
7/31/2016 2,437
8/31/2016 2,276
9/30/2016 1,839
10/31/2016 1,264
11/30/2016 623

Using Excel's NPV function = 817(and subtracting out the initial investment 10k, manually)
but using XNPV i get = 6,561

Obviously, there is a huge discrepancy. I understand that you can include the initial payment of time =0 when using XNPV and this is what I did. Not sure why I am getting such a high number for XNPV. I believe that XNPV converts the 9% annual rate to a daily rate automatically but I dont think that explains the difference. XNPV is designed to use dates and include the initial payment.

thank you

 

[Andrew Poulsom]

You haven't discounted the initial outlay.

 

[pdvsa]

it is not suppose to be discounted because it is at time =0, which is usually the point of initial investment.

 

[Andrew Poulsom]

How did you calculate the 10,817?

 

[StephenCrump]

You're discounting at 9% per month:

C2: =1.09^-A2
B13: =SUMPRODUCT(B2:B11,C2:C11)

Sheet1

*	A	B	C
1	Time	Cashflow	Discount
2	1	574	0.92
3	2	864	0.84
4	3	1,246	0.77
5	4	2,748	0.71
6	5	3,367	0.65
7	6	2,437	0.60
8	7	2,276	0.55
9	8	1,839	0.50
10	9	1,264	0.46
11	10	623	0.42
12	*	*	*
13	PV	$10,817	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Using the Mr Excel add in: ￿￿

This thread may help: http://www.mrexcel.com/forum/excel-questions/914722-mrexcelhtml-maker-pasting-only-r-r.html

 

[pdvsa]

Stephen,

The question is how to use XNPV and including the initial pmt of 10k at t=0 and return the NPV of 817.

 

[pdvsa]

Stephan, on second read it appears your answer was directed towards andrew in regards to how to calculate the PV.

I cAn add that the NET PV is simply the "net" of the PV (10,817) and the initial outlay (10,000). Hence the term "net" pv.

 

[StephenCrump]

No, Andrew and I both think your 10,817 / 817 calculation is incorrect.

My post demonstrates how it's been calculated: using a 9% per month discount rate.

C14: =XNPV(9%,C2:C12,B2:B12)
C15: =C2+NPV(9%/12,C3:C12)
C16: =C2+NPV(9%,C3:C12)

Sheet1

*	A	B	C
1	Time	Date	Cashflow
2	0	31/01/2016	-10,000
3	1	29/02/2016	574
4	2	31/03/2016	864
5	3	30/04/2016	1,246
6	4	31/05/2016	2,748
7	5	30/06/2016	3,367
8	6	31/07/2016	2,437
9	7	31/08/2016	2,276
10	8	30/09/2016	1,839
11	9	31/10/2016	1,264
12	10	30/11/2016	623
13	*	*	*
14	XNPV	*	$6,561
15	NPV	*	$6,532
16	Your NPV	*	$817

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:71px;"><col style="width:65px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[darrylworth]

Please double check... he has 10 periods and you are discounting for the full year....12 periods.

 

[StephenCrump]

Please double check... he has 10 periods and you are discounting for the full year....12 periods.

Thanks, but I'm not sure what you mean?

XNPV uses discount factors based on (1+AnnualRate)^-(DifferenceInDays/365)

My NPV calculation assumes a monthly discount rate of 9%/12 = 0.75%.

The metholodology for both is the same regardless of whether there are 10 months or 100 months of cash flow.

 

[darrylworth]

My mistake... I had NPV where XNPV was... XNPV is using the dates, not periods... what I get for typing instead of copying.