Mid Period Discounting with the NPV Function

[apubapu82]

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!

 

[Fred Smith]

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 TookPak to make this
accessible (Tools>Addins)

 

[joeu2004]

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.

=FV(rate,0.5,,-NPV(rate,CF0,CF1,...))

should do the trick. Refer to p.76 ("Timing of Benefits and Cost") of
the paper at
http://www.faa.gov/regulations_policies/policy_guidance/benefit_cost/media/ECONOMIC.pdf
..

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.

 

[vandenberg p]

Hello:

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.

Pieter Vandenberg

(e-mail address removed) wrote:
: apubapu82 wrote:
:> 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.

: =FV(rate,0.5,,-NPV(rate,CF0,CF1,...))

: should do the trick. Refer to p.76 ("Timing of Benefits and Cost") of
: the paper at
: http://www.faa.gov/regulations_policies/policy_guidance/benefit_cost/media/ECONOMIC.pdf
: .

: 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.

 

[joeu2004]

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)

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.

Finally, note that the NPV function in a spreadsheet is not the
NPV that finance uses. [....] If you use the NPV including the
initial cash flow you will understate the true NPV.

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.

 

[Pieter Vandenberg]

IHello:

I know of no interpretation of the term Net Present Value (NPV), in any
finance literature or concept, or common usage, that would reasonably
interprete 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 third
etc. 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.

Pieter Vandenberg