Working with NPV functions on Microsoft Excel

[Irritator]

I am building a discounted cash flow model and was wondering if there was a way to incorporate mid-period discounting using the NPV function. Any assistance regarding this matter would be greatly appreciated. Thanks.

[Go!pee]

I don't know what "mid period discounting" is, but if it results in irregular cash flows, the answer is no. In this case, XNPV is the function which would work for you. You may need to install the Analysis ToolPak to make this accessible (Tools>Addins).

[Panop]

With normal NPV, both benefits and costs are assumed to occur at the end of each period. With midpoint discounting, both benefits and costs are assumed to occur at the midpoint of the each period. But midpoint discounting can be modeled using the end-period model, then shifting the end-period NPV forward half a period. That is the purpose of the FV() formula above.

[Shurod]

Actually if the cashflows are in A1 to E1 for periods 0 through 4 (and use a discount rate of 10%) -$985 $75 $120 $100 $1,200. The NPV (adjusted) for half period would be: =NPV(0.1,B1:E1)*(1+0.1)^(0.5)+A1 ($128.94). This assume the customary situation that you lay out the initial expenditure at the end of period 0 and that you inflows occur on average in the middle of each future period. This effectively discounts the first $75 for one half period, the $120 for 1.5 periods, the $100 for 2.5 periods and the $1,200 for 3.5 periods. Different from what =FV(0.1,0.5,,-NPV(0.1,A1:E1)) would provide. ($73.51). The normal NPV would be: =NPV(0.1,B1:E1)+A1 ($77.10).

Finally, note that the NPV function in a spreadsheet is not the NPV that finance uses. The NPV function in a spreadsheet is more like a PV function. Hence the use of only the cash inflows (b1 to e1) and then just adding the zero period cash flow. If you are in doubt just try it the old fashion way by taking the present value of each cash flow then adding them up. If you use the NPV including the initial cash flow you will understate the true NPV. Getting the IRR for the half year assumptions is a little trickier. The
easiest way is to use the NPV formula above with the half year adjustment, make the discount rate (.1 in example) a variable and use Solver or Goal seek to find the rate that makes the NPV zero. In this case it is about 14.55% verses the normal IRR of 12.37% So using the half year convention increases both NPV and IRR as you would expect.

[AnitG]

That is the same as FV(10%,0.5,,-NPV(10%,B1:E1)) + A1. In other words, whether you use FV() or the exponential expression, the result is the same, as long as you make the same assumptions in formulation. I beg to differ. The difference arises from varying assumptions about when the present value is rolled back to relative to CF0, the initial cash flow. You assume that CF0 corresponds to the date for the present value, which is one reasonable assumption. The inclusion of CF0 among the NPV "value" parameters assumes that today is the present value, and CF0 occurs one period later, which is simply another reasonable(?) assumption. Carefully read the example on the Excel NPV help page.

An example of the latter is when you are comparing putting $100 in the bank today versus a cash flow where you put $10 into the bank each of 10 periods starting one period from now. In the latter case, the first cash flow ($10) is one period from the present value date, when you otherwise put $100 into the bank. Arguably, we could characterize the latter case as $0 for CF0. Personally, I prefer that; so I prefer your formulation, albeit using FV(), which is a just a matter of taste. I was simply following the assumptions and instructions of the cite US govt doc because I referred the OP to that doc. That doc says to multiply by 1.034408. I wanted to show where that mysterious number comes from. To be honest, I did not give much considerations to the assumptions about cash flow timing implicit in the doc.

It is good that you bring up that point. In summary, I think that both formulations are correct. You simply need to look carefully at the cash flow diagram with respect to when the present value is intended to be with respect to CF0 and use the Excel financial functions consistently with the same set of assumptions.

[Koman]

I know of no interpretation of the term Net Present Value (NPV), in any finance literature or concept, or common usage, that would reasonably interpret NPV as one period before the present. The present is the present, not one period before today. (The dictionary says: "A moment or period in time perceptible as intermediate between past and future; now.") So the point is that the use of the term "NPV" by Excel is not consistent with finance concepts; practice or the definition of the word "present." In fact definition of NPV in the Excel help file (The equation, under remarks) is only place in the universe (and I know the finance literature quite well) where NPV is defined that way. In fact their example is convoluted to make it work.

What they call the return from the "first year" is from the second year (it is true they could have said, the first year of operation, but they did not) since one year from now is when they make the investment, the second is the thirdetc. This all happen, I think, because that is the way Lotus 123 got it financially wrong and MS copied it. So the issue with NPV function is that it utterly fails to use the commonly agreed upon definition for the term, not that you could dream up a circumstance when their definition would work which they did. But that is equivalent to arguing that street lights are on during the day, if you define night as day and day as night.