# NPV and XNPV

#### pdvsa

##### Board Regular
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

##### Board Regular
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?

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### StephenCrump

##### MrExcel MVP
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

##### MrExcel MVP
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
AB
1DateCash
231 Jan 15 12:00 AM-10,000
32 Mar 15 10:00 AM574
41 Apr 15 08:00 PM864
52 May 15 06:00 AM1,246
61 Jun 15 04:00 PM2,748
72 Jul 15 02:00 AM3,367
81 Aug 15 12:00 PM2,437
931 Aug 15 10:00 PM2,276
101 Oct 15 08:00 AM1,839
1131 Oct 15 06:00 PM1,264
121 Dec 15 04:00 AM623
1331 Dec 15 02:00 PM
1431 Jan 16 12:00 AM
15
16XNPV\$6,561.01
17NPV\$6,559.12

</tbody>
Sheet1

#### pdvsa

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

Replies
3
Views
190
Replies
1
Views
241
Replies
7
Views
15K

1,141,063
Messages
5,704,064
Members
421,326
Latest member
pfaustino

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back