NPV and XNPV

pdvsa

Board Regular
Joined
Apr 22, 2010
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 22, 2010
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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

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

pdvsa

Board Regular
Joined
Apr 22, 2010
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Andrew/Stephen: Thank you for making sense out of this. Appreciate the responses.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top