A:

Net present value (NPV) is a core component of corporate budgeting. It is a comprehensive way to calculate whether a proposed project will be value added or not. The calculation of NPV encompasses many financial topics in one formula:  cash flows, the time value of money, the discount rate over the duration of the project (usually WACC), terminal value and salvage value

This is not a beginner's topic, and it is recommended you read the above topics first. (For related reading, see What are the disadvantages of using net present value as an investment criterion?

Net Present Value Defined

Let's examine each step of NPV in order. The formula is: 

NPV= ∑ {After-Tax Cash Flow / (1+r)^t} - Initial Investment

Broken down, each period's after-tax cash flow at time t is is discounted by some rate, r.  The sum of all these discounted cash flows is then offset by the initial investment, which equals the current NPV.

Any NPV greater than $0 is a value-added project, but in the decision-making process among competing projects, the one with the highest NPV is the one that should be chosen. One pitfall in this approach is that while financially sound from theory point of view, an NPV calculation is only as good as the data driving it. Coming up with the correct assumptions (acquisition and disposition costs, all tax implications, the actual scope and timing of cash flows) is extremely difficult. This is where the majority of the work actually takes place. If you have the data, plugging it in is easy.  

Calculating NPV in Excel

There are two ways to calculate NPV in Excel, one is by just using one of the built in NPV formulas, the second is by breaking out the component cash flows and calculating each step individually, then using those calculations to produce NPV.  

The second method is preferable because financial modeling best practices require calculations to be transparent and easily auditable. The trouble with piling all of the calculations into a formula is that you can't easily see what numbers go where, or what numbers are user inputs or hard coded. The other big problem is that the built in Excel formula will not net out the initial cash outlay. Believe it or not, you actually have to manually add it back in if you used the built-in formula. This is why we recommend and demonstrate the first approach.   

Here is a simple example. What makes this example simple among other things is that the timing of cashflows is both known and consistent (discussed further below).  

Assume a company is assessing the profitability of Project X.  Project X requires $250,000 in funding and is expected to generate $100,000 in after-tax cash flows the first year, and then grow by $50,000 for each of the next four years.

You can break out a schedule as follows:

[Right-Click and open image in new window if hard to read]

 

NPV Complications

The above example covers all the steps, but there are a few simplifications. First, the assumption above is that all cash flows are received in one lump sum at year end. This is obviously unrealistic. In the real world, not only is it unlikely that you would only perform this analysis at year-end, but it's also improbable you'd receive 100% of after-tax cash flows on that date. (Learn more formulas by taking Investopedia's excel course online.

The correct method to fix the first problem is by creating a stub discount factor (adjust the t, in years) by the amount of time elapsed since the last completed year, and the second problem is corrected by assuming cash flows are discounted at the middle of the period, rather than the end. This better approximates the more realistic accumulation of after-tax cash flows over the course of the year.   And example of creating a stub year can be found in the CAGR Article.  

RELATED FAQS
  1. Do you discount working capital in net present value (NPV)?

    Learn why changes in net working capital (NPV) should be included in net present value calculations for analyzing a project's ... Read Answer >>
  2. Do you include working capital in net present value (NPV)?

    Discover why it is important to include changes to working capital as a component in calculating the net present value (NPV) ... Read Answer >>
  3. How do you use discounted cash flow to calculate a capital budget?

    Learn how discounted cash flows are used in creating capital budgets as a part of the net present value and internal rate ... Read Answer >>
  4. Calculate your portfolio's investment returns

    Learn the basic principles underlying the data and calculations used to perform personal rates of return on investment portfolios. Read Answer >>
  5. What is the formula for calculating return on investment (ROI) in Excel?

    Find out more about return on investment (ROI) and the formula used for calculating return on investment for a company in ... Read Answer >>
  6. How do I calculate compound interest using Excel?

    Learn how to calculate compound interest using three different techniques in Microsoft Excel. Read Answer >>
Related Articles
  1. Small Business

    How to Calculate Net Present Value (NPV) in Excel

    Learn how to calculating the net present value (NPV) of your investment projects using built-in functions from Excel.
  2. Investing

    Analyze cash flow the easy way

    Learn the key components of the cash flow statement, how to analyze and interpret changes in cash, and what improved free cash flow means to shareholders.
  3. Investing

    Evaluating A Statement Of Cash Flows

    The metrics for the Statement of Cash Flows is best viewed over time.
  4. Investing

    Time Value Of Money: Determining Your Future Worth

    Determining monthly contributions to college funds, retirement plans or savings is easy with this calculation.
  5. Investing

    Corporate Cash Flow: Understanding the Essentials

    Tune out the accounting noise and see whether a company is generating the stuff it needs to sustain itself. Learn how to read the cash flow statement.
  6. Tech

    Cash Flow Is King: How to Keep it Running

    Why is cash flow so important, and what steps can a business take to improve it?
  7. Small Business

    Capital Budgeting: Which is Better, IRR or NPV?

    Using internal rate of return and net present value for capital budgeting evaluations often end in the same result. But there are times when using NPV to discount cash flows makes more sense.
RELATED TERMS
  1. Adjusted Present Value - APV

    The adjusted present value is the net present value (NPV) of ...
  2. Net Present Value Rule

    The net present value rule (NPV) states an investment should ...
  3. Profitability Index

    The profitability index is a technique used to measure a proposed ...
  4. Initial Cash Flow

    Initial cash flow is the amount of money paid out or received ...
  5. Present Value - PV

    Present value is the current value of a future sum of money or ...
  6. Unconventional Cash Flow

    An unconventional cash flow is a series of inward and outward ...
Hot Definitions
  1. Current Assets

    Current assets is a balance sheet account that represents the value of all assets that can reasonably expected to be converted ...
  2. Volatility

    Volatility measures how much the price of a security, derivative, or index fluctuates.
  3. Money Market

    The money market is a segment of the financial market in which financial instruments with high liquidity and very short maturities ...
  4. Cost of Debt

    Cost of debt is the effective rate that a company pays on its current debt as part of its capital structure.
  5. Depreciation

    Depreciation is an accounting method of allocating the cost of a tangible asset over its useful life and is used to account ...
  6. Ratio Analysis

    A ratio analysis is a quantitative analysis of information contained in a company’s financial statements.
Trading Center