+ Reply to Thread
Results 1 to 6 of 6

Mid Period Discounting with the NPV Function

  1. #1
    Registered User
    Join Date
    04-05-2006
    Posts
    4

    Mid Period Discounting with the NPV Function

    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!

  2. #2
    Fred Smith
    Guest

    Re: Mid Period Discounting with the NPV Function

    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)

    --
    Regards,
    Fred


    "apubapu82" <apubapu82.27pe8y_1147452301.1502@excelforum-nospam.com> wrote in
    message news:apubapu82.27pe8y_1147452301.1502@excelforum-nospam.com...
    >
    > 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!
    >
    >
    > --
    > apubapu82
    > ------------------------------------------------------------------------
    > apubapu82's Profile:
    > http://www.excelforum.com/member.php...o&userid=33201
    > View this thread: http://www.excelforum.com/showthread...hreadid=541622
    >




  3. #3
    joeu2004@hotmail.com
    Guest

    Re: Mid Period Discounting with the NPV Function

    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_polic...a/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.


  4. #4
    vandenberg p
    Guest

    Re: Mid Period Discounting with the NPV Function

    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

    joeu2004@hotmail.com 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_polic...a/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.


  5. #5
    joeu2004@hotmail.com
    Guest

    Re: Mid Period Discounting with the NPV Function

    vandenberg p wrote:
    > 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.


  6. #6
    Pieter Vandenberg
    Guest

    Re: Mid Period Discounting with the NPV Function

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1