Mid period discounting in excel

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

Thanks
SentientGroupAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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
0
SentientGroupAuthor Commented:
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
0
dlmilleCommented:
Please provide a sample workbook with your current end-period NPV so I can answer accurately.

Dave
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

dlmilleCommented:
I take that back.  By my own provided definition, mid-period discounting should be:

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

See attached

Does that help?

Dave
mid-period.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SentientGroupAuthor Commented:
Yes - thanks
0
SentientGroupAuthor Commented:
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
0
dlmilleCommented:
Is there a reason you closed this without awarding points?  Perhaps by mistake?
0
dlmilleCommented:
I believe this had to have been closed incorrectly in error.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.