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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">cash flows</td><td style=";">rate</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">31/01/2016</td><td style="text-align: right;;">-10,000</td><td style="text-align: right;;">9.00%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">29/02/2016</td><td style="text-align: right;;">574</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">31/03/2016</td><td style="text-align: right;;">864</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">30/04/2016</td><td style="text-align: right;;">1,246</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">31/05/2016</td><td style="text-align: right;;">2,748</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">30/06/2016</td><td style="text-align: right;;">3,367</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">31/07/2016</td><td style="text-align: right;;">2,437</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">31/08/2016</td><td style="text-align: right;;">2,276</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">30/09/2016</td><td style="text-align: right;;">1,839</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">31/10/2016</td><td style="text-align: right;;">1,264</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">30/11/2016</td><td style="text-align: right;;">623</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">XNPV</td><td style="text-align: right;;">6561</td><td style="text-align: right;;">9.00%</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">XNPV</td><td style="text-align: right;;">7178</td><td style="text-align: right;;">0.75%</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B14</th><td style="text-align:left">=XNPV(<font color="Blue">C14,B2:B12,A2:A12</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C14</th><td style="text-align:left">=C2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B15</th><td style="text-align:left">=XNPV(<font color="Blue">C15,B2:B12,A2:A12</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C15</th><td style="text-align:left">=C2/12</td></tr></tbody></table></td></tr></table><br />
 

pdvsa

Board Regular
Joined
Apr 22, 2010
Messages
50
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

pdvsa

Board Regular
Joined
Apr 22, 2010
Messages
50
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

pdvsa

Board Regular
Joined
Apr 22, 2010
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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/1603/31/1604/30/1605/31/1606/30/1607/31/1608/31/1609/30/1610/31/1611/30/16
12345678910
$574
8641,2462,7483,3672,4372,2761,8391,264623

<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....
 

Forum statistics

Threads
1,141,062
Messages
5,704,061
Members
421,325
Latest member
tapete86

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