Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mid period discounting in excel

Posted on 2012-03-18
10
Medium Priority
?
552 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37736519
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
ID: 37745667
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 42

Expert Comment

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

Dave
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37748160
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
 

Author Comment

by:SentientGroup
ID: 37750884
Yes - thanks
0
 

Author Comment

by:SentientGroup
ID: 37750909
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 42

Expert Comment

by:dlmille
ID: 37750907
Is there a reason you closed this without awarding points?  Perhaps by mistake?
0
 
LVL 42

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

610 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