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

 

[StephenCrump]

What formulae are you using?

My guess is that you've specified an NPV interest rate of 9% per period (= month).

That's a discount rate of 1.09^12-1 = 181% p.a.

 

[pdvsa]

Stephen, the rate is yearly but i did convert it to monthly to match the periods present in the table. Any other suggestions please let me know.

Tony

 

[Andrew Poulsom]

Stephen, the rate is yearly but i did convert it to monthly to match the periods present in the table. Any other suggestions please let me know.

Tony

Are you sure?

Excel 2010
	A	B	C
1	Date	cash flows	rate
2	31/01/2016	-10,000	9.00%
3	29/02/2016	574
4	31/03/2016	864
5	30/04/2016	1,246
6	31/05/2016	2,748
7	30/06/2016	3,367
8	31/07/2016	2,437
9	31/08/2016	2,276
10	30/09/2016	1,839
11	31/10/2016	1,264
12	30/11/2016	623
13
14	XNPV	6561	9.00%
15	XNPV	7178	0.75%
Sheet5

Cell Formulas
Range	Formula
B14	=XNPV(C14,B2:B12,A2:A12)
B15	=XNPV(C15,B2:B12,A2:A12)
C14	=C2
C15	=C2/12

 

[pdvsa]

That looks like something there. I am not at my computer right now but possibly my dates being referenced in XNPV are not formatted as true dates.

Also, i have not posted much to this forum and not sure how you formatted your data as a table. I imagine this functionality is buried somewhere and need to find.

 

[Andrew Poulsom]

I'm in the UK so I use the dd/mm/yyyy date format.

To include a snapshot of your worksheet you can use the HTML Maker here:

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

 

[pdvsa]

Andrew,

I did not notice but the net present value should be about 817. XNPV is calculating way too high at over 6500 even if converting the 9% to a monthly rate. the NPV should be about 817 and XNPV should be close to this calculation. See pic below.

Using the Mr Excel add in:
￿￿

 

[pdvsa]

well that didnt work very well. I pasted the HTML into the body as instructed but not sure why it looks like an icon and not the table.

 

[Andrew Poulsom]

Please explain why the NPV should be 817.

 

[pdvsa]

if you take the PV of each individual cash flow this sums to 10,817 and subtract the initial investment of 10,000 = 817
XNPV should be close to this.

I will copy and paste this here but not sure how it will format:

02/29/16	03/31/16	04/30/16	05/31/16	06/30/16	07/31/16	08/31/16	09/30/16	10/31/16	11/30/16
1	2	3	4	5	6	7	8	9	10
$574	864	1,246	2,748	3,367	2,437	2,276	1,839	1,264	623

<colgroup><col span="2"><col><col span="7"></colgroup><tbody>
</tbody>

the sum of the discounted cash flows above = 10,817

let me know what you think....