=NPV(0.14,D18:M18)+C13 how do you break this down when work with capital budgets?

Question

how do you read this formula...

Answer 1

This looks like a formula to calculate the NPV of an annual  cash flow stream where the first payment is made today as opposed to one year from now.  The NPV function assumes that the first payment comes in one year from now, second two years from now, etc.  If the first payment is really made today, then Excel's NPV function will be assuming each payment in the stream comes in one year later than it actually does, and the NPV will be incorrect.  Since a payment made today shouldn't be discounted, the answer is to add the full value of today's payments to the NPV of the second through 11th payment.  Since the second payment is occuring one year from now, and the NPV function assumes the first payment in the function is one year from now, the calculation matches up with the actual timing of the cash flows.  

An Excel note...there is a better formula for NPV in Excel called XNPV.  Lets say you have two columns...column A has dates cash flows come in, and column B has the amounts of the cash flows.  You can do an XNPV(.14,B6:B10,A6:A10), and it will calculate an NPV based on the actual dates cash is received as opposed to a generic one period per line assumption.  First line should be the date that you want the NPV as of (can be a $0 amount).  Very useful.