The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Hello - how do I modify a NPV formula in excel to achieve mid period discounting?

Thanks

Thanks

=NPV(periodic_rate,cash_fl

Mid-Period Discounting can utilize the existing NPV formula, with one modification:

See attached workbook example where this is proven by comparing the NPV calc (above) with a more "manual" set of periodic DCFs that are added up to equal the same result.

Here's a confirming link:

See Pg 48, re: http://my.safaribooksonline.com/book/office-and-productivity-applications/9780735623965/evaluating-investments-by-using-net-present-value-criteria/48#X2ludGVybmFsX0ZsYXNoUmVhZGVyP3htbGlkPTk3ODA3MzU2MjM5NjUvNDg=

Also, you could use XNPV for this, though it would not exactly match the NPV enhanced calculation, primarily because leap year is taken into account, # days in months are different, etc., so if you have a different starting year, you'd get a slightly different result, than just working with plain vanilla periods, as with the NPV calculation.

However, I also share the XNPV approach in the attached, as well, for completeness.

IMHO for straight-forward enterprise valuation methodologies, I suggest keeping things simple and using the enhanced NPV approach, bringing the cash flows forward 1/2 year with the NPV * (1+r)^.5 as opposed to dealing with XNPV. The differences in the calculation should not be material.

Again, this is primarily talking about even cash flows, where we're just doing mid-period discounting, not irregular cash flows which would require a DCF calculated approach if not for XNPV.

Further discussion on using NPV versus XNPV: http://www.finance30.com/forum/topics/1987892:Topic:38950

Cheers,

Dave

NPV-Calcs-r1.xls

It is ^ (1/4) as the cashflows are quarterly

=NPV(8%)^(1/4),F96:CP96)

As such should I change it to

=NPV(8%)^(1/8),F96;CP96)?

Or will it be

=NPV(8%,F96;CP96)*(1.08)^(

or

=NPV(2%,F96;CP96)*(1.02)^(

Thanks

Accepted answer: 0 points for SentientGroup's comment #37750884

for the following reason:

No particular reason

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

NPV(end-Period) * (1+periodic_rate)^.5

It doesn't matter whether your cashflows are quarterly or yearly, what matters is what the periods are that you are using.

If your periods are quarterly, then mid-period would still be the equation I cited above.

The point is, to go from end to mid-period discounting is moving the DCF's up by half a period, which is what the *(1+periodic_rate)^.5 does.

If your annual rate is 8% and periodic rate is then 8%/4, or 2%, then it would be:

*(1+8%/4)^.5

So if your end-period is:

=NPV(2%,F96:CP96)

then mid-period would be:

=NPV(2%,F96:CP96)*(1.02)^(

See attached

Does that help?

Dave

mid-period.xls