Solved

Mid period discounting in excel

Posted on 2012-03-18
10
435 Views
Last Modified: 2012-03-31
Hello - how do I modify a NPV formula in excel to achieve mid period discounting?

Thanks
0
Comment
Question by:SentientGroup
  • 5
  • 3
10 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
 

Author Comment

by:SentientGroup
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Please provide a sample workbook with your current end-period NPV so I can answer accurately.

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:SentientGroup
Comment Utility
Yes - thanks
0
 

Author Comment

by:SentientGroup
Comment Utility
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
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Is there a reason you closed this without awarding points?  Perhaps by mistake?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I believe this had to have been closed incorrectly in error.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now