Cody > said:
I work in finance and have used XL for calculating NPV's for some time
now. Recently, I have heard that several finance people don't like
using the NPV calculations on XL and prefer instead to use the actual
formula (1/(1+i)^2, etc.). Does anyone have any feedback on this?
Additionally, someone recently told me that XL calculated for "Year
One" only and fails to compensate for "Year Zero" - and that you have
to add "Year Zero" back into the equation if you use XL to calculate
NPV's. Does anyone know the best way to do this? Or if you can edit
the baseline formula that XL uses to compensate for this inaccuracy?
Cody,
As far as I know, Excel's NPV function does exactly what it claims to
do (even though it is not what comes to mind when I think of NPV, some
info from the help file is below). The function calculates NPV by
assuming that the cash flows occur at the end of each period. If the
cash flows occur at the beginning of each period, you could either
pass the entire set of cash flows into NPV and discount the result by
one period or pass the future cash flow into NPV and add on the cash
flow from time '0'.
If you wanted to, you could create a custom function that determines
NPV this way. It looks like you might be able to use the XNPV
function that comes with the Analysis Tool Pack. XNPV also needs the
dates that correspond to the cash flows. Because of this, XNPV does
not require evenly spaced cash flows.
It is always a good idea to look through the help file for functions.
This is especially true for the financial functions where the final
answer can change significantly depending on the assumptions behind
the interest rates and timing of the cash flows.
Hope this helps,
Mike
¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
NPV
Calculates the net present value of an investment by using a discount
rate and a series of future payments (negative values) and income
(positive values).
Syntax
NPV(rate,value1,value2, ...)
Rate is the rate of discount over the length of one period.
Value1, value2, ... are 1 to 29 arguments representing the payments
and income.
Value1, value2, ... must be equally spaced in time and occur at the
end of each period.
NPV uses the order of value1, value2, ... to interpret the order of
cash flows. Be sure to enter your payment and income values in the
correct sequence.
Remarks
The NPV investment begins one period before the date of the value1
cash flow and ends with the last cash flow in the list. The NPV
calculation is based on future cash flows. If your first cash flow
occurs at the beginning of the first period, the first value must be
added to the NPV result, not included in the values arguments.
¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤¤
