:max_bytes(150000):strip_icc()/dotdash_Final_Formula_to_Calculate_Net_Present_Value_NPV_in_Excel_Sep_2020-830883b540d44305be234b851f80718e.png)
David Kindness is a Certified Public Accountant (CPA) and an expert in the fields of financial accounting, corporate and individual tax planning and preparation, and investing and retirement planning. David has helped thousands of clients improve their accounting and financial systems, create budgets, and minimize their taxes.
Fact checked by Fact checked by Vikki VelasquezVikki Velasquez is a researcher and writer who has managed, coordinated, and directed various community and nonprofit organizations. She has conducted in-depth research on social and economic issues and has also revised and edited educational materials for the Greater Richmond area.
Net present value (NPV) helps companies determine whether a proposed project will be financially viable. It encompasses many financial topics in one formula (cash flows, the time value of money, the discount rate over the duration of the project (usually the weighted average cost of capital (WACC)), terminal value, and salvage value) and is a core component of corporate budgeting.
Most analysts use Excel to calculate NPV. There are two ways to do this. You can input the present value formula, apply it to each year's cash flows, and then add together each year's discounted cash flows, minus expenditures, to get the final figure or use Excel’s built-in NPV function.
To understand NPV in the simplest forms, think about how a project or investment works in terms of money inflow and outflow.
Say you are contemplating setting up a factory that needs initial funds of $250,000 during the first year. Since this is an investment, it is a cash outflow that can be taken as a net negative value. It is also called an initial outlay.
You expect that after the factory is successfully established in the first year with the initial investment, it will start generating the output (products or services) by the second year and onward. That will result in net cash inflows in the form of revenues from the sale of the factory output.
The factory generates $100,000 during the second year. That amount increases by $50,000 each year over five years. The actual and expected cash flows of the project are as follows:
Year 0 represents actual cash flows, while years one to five represent projected cash flows over the mentioned years. A negative value indicates cost or investment, while a positive value represents inflow, revenue, or receipt.
Now, how do you decide whether this project is profitable or not? The challenge is that you are making investments during the first year and realizing the cash flows over the course of many future years.
When multiyear ventures need to be assessed, NPV can assist the financial decision making, provided that the investments, estimates, and projections are accurate.
NPV is just one metric used along with others by a company to decide whether to invest.
NPV calculations bring all cash flows (present and future) to a fixed point in time in the present—hence, the term present value. NPV essentially works by figuring out what the expected future cash flows are worth at present. Then, it subtracts the initial investment from that present value to arrive at net present value. If this value is positive, the project may be profitable and viable. If this value is negative, the project may not be profitable and should be avoided.
In the simplest terms:
NPV = (Today’s value of expected future cash flows) - (Today’s value of invested cash)
An NPV of greater than $0 indicates that a project has the potential to generate net profits. An NPV of less than $0 indicates a losing proposition.
There are two methods to calculate NPV in Excel. You can use the basic formula, calculate the present value of each component for each year individually, and then sum all of them up. Or, you can use Excel’s built-in NPV function.
Using the figures from the above example, assume that the project will need an initial outlay of $250,000 in year zero. From the second year (year one) onward, the project starts generating inflows of $100,000. They increase by $50,000 each year until year five, when the project is completed.
The company uses the WACC as the discount rate when budgeting for a new project. For this project, it’s 10%.
The present value formula is applied to each of the cash flows from year zero to year five. For example, the cash flow of -$250,000 results in the same present value during year zero. Year one’s inflow of $100,000 during the second year results in a present value of $90,909. Year two’s inflow of $150,000 is worth $123,967, and so on.
Calculating present value for each of the years and then summing those up produces an NPV of $472,169, as shown above.
The second Excel method uses the built-in NPV function. It requires the discount rate (again, represented by the WACC), and the series of cash flows from year one to the last year. Be sure that you don’t include the year zero cash flow (the initial outlay) in the formula.
The result using the NPV function for the example comes to $722,169. Then, to compute the final NPV, subtract the initial outlay from the value obtained by the NPV function. NPV = $722,169 - $250,000, or $472,169.
This computed value matches that obtained using the first method.
While Excel is a great tool for making rapid calculations with precision, errors can occur. Since a simple mistake can lead to incorrect results, it’s important to take care when inputting data.
Analysts, investors, and economists can use either of the methods, after assessing their pros and cons.
Net present value (NPV) is the difference between the present value of cash inflows and the present value of cash outflows over a certain period of time. It’s a metric that helps companies foresee whether a project or investment will increase company value. NPV plays an important role in a company’s budgeting process and investment decision making.
A net present value of $0 or higher is a good sign. It indicates that a project will increase company value. A net present value that’s less than $0 means a project isn’t financially feasible and should be avoided.
Yes. You can use an NPV formula in Excel or use the NPV function to get a value more quickly. There’s also an XNPV function that’s more precise when you have various cash flows occurring at different times.
Net present value (NPV) can be very useful to companies for effective corporate budgeting. Excel can also be useful in helping a business calculate NPV.
Whichever Excel method one uses, the result obtained is only as good as the values inserted in the formulas. Therefore, be sure to be as precise as possible when determining the values to be used for cash flow projections before calculating NPV.
Article SourcesThe offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.
Related Articles:max_bytes(150000):strip_icc()/NAVPSv1-71ad2ba0a0354f5f81d20f806737b008.jpg)
:max_bytes(150000):strip_icc()/GettyImages-1211231333-a026ecaac8a84c5fb77b99f19df884c1.jpg)
:max_bytes(150000):strip_icc()/DCF-6ffe1cf5424148e5a77656250c0a772d.png)
:max_bytes(150000):strip_icc()/thinkstockphotos_493208894-5bfc2b9746e0fb0051bde2b8.jpg)
:max_bytes(150000):strip_icc()/equitymultiplier_final-2af505394e33454ba63357f4289c25eb.png)
:max_bytes(150000):strip_icc()/Final4236-a0715ffe24a64156951e5f2340550179.jpg)
Net asset value per share (NAVPS) is an expression for net asset value that represents the value per share of a mutual fund, an exchange-traded fund (ETF), or a closed-end fund.
Discounted cash flow (DCF) is a valuation method used to estimate the attractiveness of an investment opportunity. Learn how it is calculated and when to use it.
The equity multiplier is a calculation of how much of a company’s assets is financed by stock rather than debt. For investors, it is a risk indicator.
Combined loan-to-value (CLTV) ratio is the ratio of all loans on a property to the property's value. Lenders use it to determine risk of default.
The asset coverage ratio determines a company's ability to cover debt obligations with its assets after all liabilities have been satisfied.
The fixed asset turnover ratio measures how efficiently a company is generating net sales from its fixed-asset investments.
We and our 100 partners store and/or access information on a device, such as unique IDs in cookies to process personal data. You may accept or manage your choices by clicking below, including your right to object where legitimate interest is used, or at any time in the privacy policy page. These choices will be signaled to our partners and will not affect browsing data.
Store and/or access information on a device. Use limited data to select advertising. Create profiles for personalised advertising. Use profiles to select personalised advertising. Create profiles to personalise content. Use profiles to select personalised content. Measure advertising performance. Measure content performance. Understand audiences through statistics or combinations of data from different sources. Develop and improve services. Use limited data to select content. List of Partners (vendors)
