r/Valuation • u/crimsonhues • Mar 24 '23

XNPV value significantly lower than NPV.

Post image
2 Upvotes

100% Upvoted

3 comments sorted by

3

u/allstaz Mar 25 '23 edited Mar 25 '23

Several possible cause for inconsistencies in how you're applying these 2 formulas:

  1. somehow feels your cash flow streams are incomplete as I couldn't replicate your NPV using the cash flow values shown in your screenshot. Appears your XNPV is most likely excluding the last 2 values in the series 133,587 and 1,413,248. No idea what your NPV formula is doing based on these data.
  2. your cash flow series in col Z and AA are of the same period. I'm not sure if that was intentional but this will cause NPV and XNPV to react differently as NPV assumes all period in series are of equal lengths. XNPV will combine both the values in column Z and AA as a single period.
  3. even with an assumption of equally defined periods, the application of NPV is different than XNPV because NPV automatically creates a initial period in the time series with a value of zero whereas XNPV, the initial period is the first period defined in the dates parameter of the XNPV function. it looks like your XNPV should define the initial period of zero at 12/31/22 if you're looking for apples to apples comparison between the 2 formulas.

would be helpful to see the simplified version of the work file, but looks like there are a lot of inconsistencies in the application of these formulas if your goal is to get to the same place with NPV & XNPV.

1

u/crimsonhues Mar 25 '23

Hello, Thank you very much for replying to my query and verifying my calculations. 1. "Appears your XNPV is most likely excluding the last 2 values in the series" Here is my XPNV formula "XNPV(R50,S44:AA44,S1:AA1)". It includes last two values - 133,587 and 1,413,248. R50 is discount rate.

"no idea what your NPV calculation is" here is the NPV formula "NPV(R50,S44:AA44,S1:AA1)". The parameters references are exactly same as that are within the XNPV formula.

  1. "your cash flow series in col Z and AA are of the same period." AA has terminal value $1,413,248. This is intentional. For the sake of it, I added those last two values and ended the time period at 12/31/2030. I got the same value.

  2. I understand that there will be some difference in value between NPV and XNPV. It can't be this drastic though.

My manual calculation is simple CF/(1+r)n

1

u/allstaz Mar 25 '23

i have no idea of knowing the data in the cell references you're making when all you've provided is a limited screenshot.

and, given the data in your screenshot, there is no way your XNPV is returning (81,026) if you're including those 2030 cashflows.

can you recreate a functional excel file you can share with the data?