Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cannot verify XNPV with PV or HP-12C
Using both Excel 2003 and Excel 2007, I calculated the net present value of
the stream of payments shown below four different ways: 1) Using the XNPV function for the full stream of payments: Result = $25.20 2) Adding the individual XNPVs: Result = $25.20 3) Adding the individual Excel PVs: Result = $17.19 4) Using the HP-12C financial calculator to compute and add the individual PVs: Result = $17.19 The purpose of calculations 3 and 4 was to manually verify the correctness of calculations 1 and 2. Since they don't match, it seems to me that either my logic is wrong or there is an error in XNPV. Could someone please help me understand the discrepancy between the first two calculations and the second two calculations? Pmt Amount Date Days Cum XNPV PV Cf0 ($400) 01/01/09 0 0 ($400.00) ($400.00) Cf1 $100 04/01/09 90 90 $94.65 $94.02 Cf2 $100 06/30/09 90 180 $89.58 $88.40 Cf3 $100 09/28/09 90 270 $84.78 $83.12 Cf4 $100 12/27/09 90 360 $80.24 $78.15 Cf5 $100 03/27/10 90 450 $75.95 $73.48 Total= $25.20 $17.19 =XNPV $25.20 Hurdle Rate= 25.0% Hurdle Rate/365= 0.000684932 Cf0 ($400) 01/01/09 0 0 ($400.00) ($400.00) Start $0 01/01/09 0 0 $0.00 $0.00 Cf1 $100 04/01/09 90 90 $94.65 $94.02 Start $0 01/01/09 0 0 $0.00 $0.00 Cf2 $100 06/30/09 180 180 $89.58 $88.40 Start $0 01/01/09 0 0 $0.00 $0.00 Cf3 $100 09/28/09 270 270 $84.78 $83.12 Start $0 01/01/09 0 0 $0.00 $0.00 Cf4 $100 12/27/09 360 360 $80.24 $78.15 Start $0 01/01/09 0 0 $0.00 $0.00 Cf5 $100 03/27/10 450 450 $75.95 $73.48 Total $25.20 $17.19 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cannot verify XNPV with PV or HP-12C
"ncw" wrote:
Could someone please help me understand the discrepancy between the first two calculations and the second two calculations? Looks like it's the difference between (1+rate/365)^days and (1+rate)^(days/365). Using PV for the purposes of NPV, each discounted cash flow is -PV(rate/365,days,0,CFx), which is computed by CFx / (1+rate/365)^days. For NPV, each discounted cash flow is computed by CFx / (1+rate)^(days/365). If you sum the discounted cash flows in each case, you will see the discrepancy. it seems to me that either my logic is wrong or there is an error in XNPV. Or perhaps neither. (TBD) ----- original message ----- "ncw" wrote in message ... Using both Excel 2003 and Excel 2007, I calculated the net present value of the stream of payments shown below four different ways: 1) Using the XNPV function for the full stream of payments: Result = $25.20 2) Adding the individual XNPVs: Result = $25.20 3) Adding the individual Excel PVs: Result = $17.19 4) Using the HP-12C financial calculator to compute and add the individual PVs: Result = $17.19 The purpose of calculations 3 and 4 was to manually verify the correctness of calculations 1 and 2. Since they don't match, it seems to me that either my logic is wrong or there is an error in XNPV. Could someone please help me understand the discrepancy between the first two calculations and the second two calculations? Pmt Amount Date Days Cum XNPV PV Cf0 ($400) 01/01/09 0 0 ($400.00) ($400.00) Cf1 $100 04/01/09 90 90 $94.65 $94.02 Cf2 $100 06/30/09 90 180 $89.58 $88.40 Cf3 $100 09/28/09 90 270 $84.78 $83.12 Cf4 $100 12/27/09 90 360 $80.24 $78.15 Cf5 $100 03/27/10 90 450 $75.95 $73.48 Total= $25.20 $17.19 =XNPV $25.20 Hurdle Rate= 25.0% Hurdle Rate/365= 0.000684932 Cf0 ($400) 01/01/09 0 0 ($400.00) ($400.00) Start $0 01/01/09 0 0 $0.00 $0.00 Cf1 $100 04/01/09 90 90 $94.65 $94.02 Start $0 01/01/09 0 0 $0.00 $0.00 Cf2 $100 06/30/09 180 180 $89.58 $88.40 Start $0 01/01/09 0 0 $0.00 $0.00 Cf3 $100 09/28/09 270 270 $84.78 $83.12 Start $0 01/01/09 0 0 $0.00 $0.00 Cf4 $100 12/27/09 360 360 $80.24 $78.15 Start $0 01/01/09 0 0 $0.00 $0.00 Cf5 $100 03/27/10 450 450 $75.95 $73.48 Total $25.20 $17.19 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cannot verify XNPV with PV or HP-12C
PS....
I wrote: Using PV for the purposes of NPV, each discounted cash flow is -PV(rate/365,days,0,CFx), which is computed by CFx / (1+rate/365)^days. Alternatively, the discounted cash flow could be computed by -PV(rate,days/365,0,CFx). The sum of those terms has the same result as NPV, within a reason margin of error (due to numerical abberations). Is one formulation right and the other wrong? TBD. ----- original message ----- "JoeU2004" wrote in message ... "ncw" wrote: Could someone please help me understand the discrepancy between the first two calculations and the second two calculations? Looks like it's the difference between (1+rate/365)^days and (1+rate)^(days/365). Using PV for the purposes of NPV, each discounted cash flow is -PV(rate/365,days,0,CFx), which is computed by CFx / (1+rate/365)^days. For NPV, each discounted cash flow is computed by CFx / (1+rate)^(days/365). If you sum the discounted cash flows in each case, you will see the discrepancy. it seems to me that either my logic is wrong or there is an error in XNPV. Or perhaps neither. (TBD) ----- original message ----- "ncw" wrote in message ... Using both Excel 2003 and Excel 2007, I calculated the net present value of the stream of payments shown below four different ways: 1) Using the XNPV function for the full stream of payments: Result = $25.20 2) Adding the individual XNPVs: Result = $25.20 3) Adding the individual Excel PVs: Result = $17.19 4) Using the HP-12C financial calculator to compute and add the individual PVs: Result = $17.19 The purpose of calculations 3 and 4 was to manually verify the correctness of calculations 1 and 2. Since they don't match, it seems to me that either my logic is wrong or there is an error in XNPV. Could someone please help me understand the discrepancy between the first two calculations and the second two calculations? Pmt Amount Date Days Cum XNPV PV Cf0 ($400) 01/01/09 0 0 ($400.00) ($400.00) Cf1 $100 04/01/09 90 90 $94.65 $94.02 Cf2 $100 06/30/09 90 180 $89.58 $88.40 Cf3 $100 09/28/09 90 270 $84.78 $83.12 Cf4 $100 12/27/09 90 360 $80.24 $78.15 Cf5 $100 03/27/10 90 450 $75.95 $73.48 Total= $25.20 $17.19 =XNPV $25.20 Hurdle Rate= 25.0% Hurdle Rate/365= 0.000684932 Cf0 ($400) 01/01/09 0 0 ($400.00) ($400.00) Start $0 01/01/09 0 0 $0.00 $0.00 Cf1 $100 04/01/09 90 90 $94.65 $94.02 Start $0 01/01/09 0 0 $0.00 $0.00 Cf2 $100 06/30/09 180 180 $89.58 $88.40 Start $0 01/01/09 0 0 $0.00 $0.00 Cf3 $100 09/28/09 270 270 $84.78 $83.12 Start $0 01/01/09 0 0 $0.00 $0.00 Cf4 $100 12/27/09 360 360 $80.24 $78.15 Start $0 01/01/09 0 0 $0.00 $0.00 Cf5 $100 03/27/10 450 450 $75.95 $73.48 Total $25.20 $17.19 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cannot verify XNPV with PV or HP-12C
JoeU2004, thank you for pointing out difference between how XNPV rates and PV
rates are calculated. I believe this difference constitutes a nontrivial error in the XNPV formula. The XNPV value can be significantly different from the value computed using the sum of the PVs; in the example I gave, XNPV/PV is greater than 46%. I am confident that the vast majority of financial professionals and academics would agree that XNPV function should use the expression (1+rate/365)^days instead of (1+rate)^(days/365) in the function. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cannot verify XNPV with PV or HP-12C
"ncw" wrote:
I believe this difference constitutes a nontrivial error in the XNPV formula. Does it? (Rhetorical.) The XNPV value can be significantly different from the value computed using the sum of the PVs As I noted, that depends on how you compute the discounted cash flows ("PVs"). I am confident that the vast majority of financial professionals and academics would agree that XNPV function should use the expression (1+rate/365)^days instead of (1+rate)^(days/365) in the function. You're free to have an opinion. It's not uncommon for people to take strong stands on matters they know little about. I am not so confident that there is a consensus, much less a "vast majority", given that financial professionals and academics cannot agree on how to annualize a period IRR (multiply v. compound). And I think that would be much easier to agree upon than your issue. My first impression was similar to yours -- not what a "vast majority" might say, but simply which I would choose to do. However, after a millisecond of thought, several factors lead me to think that XNPV might be doing it correctly -- or at least, one correct way ;-). First, note that the formula used by XNPV is similar to that used by XIRR. That formula seems to make some sense for XIRR since it derives an annual rate. Of course, both could be wrong. But.... Second, the XIRR formula seems somewhat consistent with my interpretation of the (US) Truth in Lending and Truth in Savings regulations -- especially the latter. TIS computes APY by taking the simple rate of return (interest / principal) and amortizing by years and fractions thereof (365 / days). That is analogous to what XNPV does, namely discounting by years and fractions thereof (days / 365). TIL is more ambiguous to apply by analogy. Arguably, it allows for fractional unit rates, for example rate/365 for daily compounding. But for a unit period of a year -- arguably, the unit period for XIRR and XNPV -- fractional periods are computed by days/365. Of course, TIS and TIL have nothing to with NPV and IRR, which are the purview of financial analysis. And since I have neither time nor enthusiasm to research financial analysis texts to see if any demonstrate how to do such exact computations -- and I wouldn't be surprised if none does -- I cannot offer nor dispositively dispute an opinion on what the "vast majority" might conclude, if anything. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NPV vs XNPV | Excel Worksheet Functions | |||
XNPV documentation | Excel Worksheet Functions | |||
XNPV | Excel Discussion (Misc queries) | |||
NPV vs. XNPV | Excel Worksheet Functions | |||
xnpv with actual/360 | Excel Worksheet Functions |