Mid period discounting in excel

NPV uses end-period discounting.  E.g.,

=NPV(periodic_rate,cash_flows)+initial_cash_flow

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

=NPV(periodic_rate,cash_flows)*(1+periodic_rate)^.5 + initial_cash_flow

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=

A bit on XNPV as an alternative:

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

Hello, the discounted cash flow I am working with has the following formula.

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)^(0.5)

or

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


Thanks

Please provide a sample workbook with your current end-period NPV so I can answer accurately.

Dave

Yes - thanks

I've requested that this question be closed as follows:

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

for the following reason:

No particular reason

Is there a reason you closed this without awarding points?  Perhaps by mistake?

I believe this had to have been closed incorrectly in error.