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

 

[pdvsa]

I think this is a text book example of how one can be tricked by period interest rates.

I agree with Stephen's calculation. In my calculation, i incorrectly assumed i was not using XNPV correctly but in fact i was. XNPV will convert the rates automatically whereas NPV's rate must be converted from the annual 9% to a monthly.

I would like to add one point though. I am not certain if dividing by 12 is the most accurate. I believe, not entirely certain though, if converting from annual to monthly it is done with this formula: (1+r)^(1/12)-1. This returns a rate of .72, which is a just a tad different than .75 arrived at by dividing 9%/12. The difference is compounding.

I believe XNPV accounts for compunding automatically.

Does this sound accurate?

 

[StephenCrump]

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

If we could somehow force the XNPV monthly payment dates to be equidistant, i.e. 365/12 days apart, then a monthly payment due in N months time, discounted at 9% p.a., would have PV = 1.09^-(N/12), i.e. consistent with an interest rate of 1.09^(1/12)-1 = 0.72% per month.

I'd be using 0.72% for NPV myself.

 

[StephenCrump]

As close as we can get, given that XNPV truncates dates to integer ...

B16: =XNPV(9%,B2:B12,A2:A12)
B17: =NPV(1.09^(1/12)-1,B3:B12)+B2

Excel 2010

	A	B
1	Date	Cash
2	31 Jan 15 12:00 AM	-10,000
3	2 Mar 15 10:00 AM	574
4	1 Apr 15 08:00 PM	864
5	2 May 15 06:00 AM	1,246
6	1 Jun 15 04:00 PM	2,748
7	2 Jul 15 02:00 AM	3,367
8	1 Aug 15 12:00 PM	2,437
9	31 Aug 15 10:00 PM	2,276
10	1 Oct 15 08:00 AM	1,839
11	31 Oct 15 06:00 PM	1,264
12	1 Dec 15 04:00 AM	623
13	31 Dec 15 02:00 PM
14	31 Jan 16 12:00 AM
15
16	XNPV	$6,561.01
17	NPV	$6,559.12

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[pdvsa]

Andrew/Stephen: Thank you for making sense out of this. Appreciate the responses.