Improper use of NPV Formula in Excel & OpenOffice

Posted on 14 August 2009

Improper use of NPV formula in Excel & OpenOffice
I see it time and time again – many times from people making major financial decisions for their company.  Some will argue that it is a bug in Excel, and according to some articles I’ve read the behavior is different between different versions of Excel, I don’t know if that is the case or not.  My argument is that people should understand the concept prior to blindly using the formula – and perhaps some clearer user instructions from the software providers.
What am I talking about?  Improper use of the NPV (net present value) formula in Excel and OpenOffice.
Net Present Value is the comparison of a dollar today versus the value of a dollar in the future.  Presumably if someone offered to give you a dollar today, or a year from now, you would pick today.  If I were given a dollar today, I could invest it at some interest rate and one year from now I would have a dollar and change.  That obviously has more value than being given a dollar a year from now – the difference being the change.  NPV is one of the primary analysis tools for valuing business opportunities.  Those decisions might include plant expansion projects, purchase of a new company, etc.  At the end of the day companies and entrepreneurs typically spend money today to make money in the future and NPV takes those expenditures (cash outflow) and profits (cash inflow) and gives you the “net value of the opportunity”, in today’s dollars accounting for inflation and returns in a discount rate.  Now, it is important to note that without a crystal ball, future cash flow from an investment is typically based on estimation, but that’s a different discussion.
What should be understood about the NPV formula in Excel and OpenOffice is that it gives the net present value of FUTURE cash flow.  If you include time 0 (today) cash flow you are improperly discounting the value of today’s dollar.  The net present value of a dollar today, is a dollar.
See the example below.  This particular example was done using OpenOffice, but you will get the exact same result using Excel.

I see it time and time again – many times from people making major financial decisions for their company.  Some will argue that it is a bug in Excel, and according to some articles I’ve read the behavior is different between different versions of Excel, I don’t know if that is the case or not.  My argument is that people should understand the concept prior to blindly using the formula – and perhaps some clearer user instructions from the software providers.

What am I talking about?  Improper use of the NPV (net present value) formula in Excel and OpenOffice.

Net Present Value is the comparison of a dollar today versus the value of a dollar in the future.  Presumably if someone offered to give you a dollar today, or a year from now, you would pick today.  If I were given a dollar today, I could invest it at some interest rate and one year from now I would have a dollar and change.  That obviously has more value than being given a dollar a year from now – the difference being the change.  NPV is one of the primary analysis tools for valuing business opportunities.  Those decisions might include plant expansion projects, purchase of a new company, etc.  At the end of the day companies and entrepreneurs typically spend money today to make money in the future and NPV takes those expenditures (cash outflow) and profits (cash inflow) and gives you the “net value of the opportunity”, in today’s dollars accounting for inflation and returns in a discount rate.  Now, it is important to note that without a crystal ball, future cash flow from an investment is typically based on estimation, but that’s a different discussion.

What should be understood about the NPV formula in Excel and OpenOffice is that it gives the net present value of FUTURE cash flow.  If you include time 0 (today) cash flow you are improperly discounting the value of today’s dollar.  The net present value of a dollar today, is a dollar.

See the example below.  This particular example was done using OpenOffice, but you will get the exact same result using Excel.

NPV Calculation in OpenOffice

NPV Calculation in OpenOffice

What you see is a series of cash flows, a year 0 (today) cash outflow of $1M, and then a series of outflows and inflows ending in year 7 with an inflow of $1M.  For the sake of this example, a discount rate of 10% will be used.

There are two results shown below the cash flow series, the top NPV result ($175,653) being incorrect and the source of many mistakes that I see from time to time.  The bottom result ($193,218) is the proper calculation for the NPV of this cash flow.  The mistake, which in this case causes for an understatement of the value of the investment, is caused because the cash flow series is being treated as if it were all in the future – and that is not the case.

The proper use of the equation when calculating an NPV on a series of cash flows is shown on the bottom result, ending in a NPV of $193,218.  The NPV formula, when used properly, should include the range of FUTURE cash flow, and that result should be added to the time 0 (today) investment.

Happy NVP analysis!


2 responses to Improper use of NPV Formula in Excel & OpenOffice

  • Dan Lynn says:

    I don’t quite follow. Would not the value of year 0 be negative $1M?

    • Travis Todd says:

      Dan,
      You are correct, it is a value of negative $1M at time zero. I used the terms “cash outflow” and “cash inflow” to attempt to signify negative/positive values.

      An “outflow” (expenditure) would be a negative value on your spreadsheet. An “inflow” (profit) would have a positive value on your spreadsheet.

      Thanks for pointing out the ambiguity.

  • Leave a Response

    *

    Recent Posts

    Tag Cloud

    AT&T BCS Boise State browsers Business Plan Dream-weavers Duffy's Lime Juice Enron Entrepreneur ethics Excel eyes open to possibility Firefox Google Google Chrome Google Chrome Frame Google Voice hard work IE6 IE7 IE8 Incentive iPhone Jeffrey Skilling Jose Cuervo Margarita Margin Markup Mexican Martini Microsoft MMS National Championship NCAA Football Net Present Value NPV OpenOffice pain Pricing revenue Rio Grande size of market stick-to-itiveness target market TCU Trudy's

    Brooding on Matters | Travis Todd is proudly powered by WordPress and the SubtleFlux theme.

    All text & design © 2009 Travi.sT. All rights reserved.