Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What gets XNPV to calculate correctly if initial values are zero?
I calculated a XNPV on a series of cash flows. The cash flow in the first 27
years is zero and then there are positive cash flows for another 25 years. The calculation read the first positive cash flow as if it were at time zero instead of discounting it back 27 years. I ended up just using a PV, but would like to figure out how to use the XNPV function for this series of cash flows. Thanks for any ideas. Jim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What gets XNPV to calculate correctly if initial values are zero?
Jim McCartney wrote:
I calculated a XNPV on a series of cash flows. The cash flow in the first 27 years is zero and then there are positive cash flows for another 25 years. The calculation read the first positive cash flow as if it were at time zero instead of discounting it back 27 years. Not in my experiments. Am I misinterpreting your example? In any case, I would be interested in knowing what version of Excel you are using. And I would be interested in seeing the details of a simple experiment, complete with formulas and your results. I am using Office Excel 2003. For my example, here is A1:A5 and B1:B5. 1/1/1999 0 1/1/2003 1000 1/1/2004 1000 1/1/2005 1000 1/1/2006 1000 (4 years of zero cash flows followed by 4 years of positive cash flows.) XNPV(5%, B1:B5, A1:A5) results in $3062.52. If C1:C5 has the following formula to compute the number of years (C5, for example): =YEAR(C5) - YEAR($C$1) and D1:D5 has the following formula to compute (-)PV (D5 for example): =PV(5%, D5, 0, -B5) then the =SUM(D1:D5) results in $3,063.13. Close enough: a 59-cent difference. Note: In practical terms, the formulation of my example probably has an off-by-one error in terms of number of discount periods. I am using this only to demonstrate that XNPV does indeed seem to take the periods of zero cash flows into account. In fact, if you look at the formula in the XNPV help page, it should be obvious that XNPV would. But I wonder if it is the off-by-one error of the formulation that is tripping you up. That would be a usage error, not an XNPV error. PS: If your cash flows are indeed evenly spaced (e.g. every year), you might be able to use NPV instead of XNPV. NPV has a debatable off-one-error by discounting the first cash flow. Some people complain that NPV is wrongly defined. But ironically, that might be exactly what you want. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Excel won't calculate my formulas correctly. | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
How do you calculate the difference between two values within a p. | Excel Discussion (Misc queries) | |||
calculate the sum of cells where values are between 6 and 10 in e. | Excel Worksheet Functions |