# 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

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

#### pdvsa

##### Board Regular

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

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

##### MrExcel MVP
Please explain why the NPV should be 817.

#### pdvsa

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

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

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.

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