Formula to Calculate Net Present Value (NPV) in Excel
Net present value (NPV) is a core component of corporate budgeting. It is a comprehensive way to calculate whether a proposed project will be financially viable 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.
How to Calculate Net Present Value (NPV) How Do I Use Net Present Value?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 an initial investment of $100,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) second year onwards. It will result in net cash inflows in the form of revenues from the sale of the factory output. Say, the factory generates $100,000 during the second year, which increases by $50,000 each year till the next five years. The actual and expected cash flows of the project are as follows:
Image by Sabrina Jiang © Investopedia 2020XXXX-A represents actual cashflows, while XXXX-P represents projected cash flows over the mentioned years. A negative value indicates cost or investment, while positive value represents inflow, revenue, or receipt.
How do you decide whether this project is profitable or not? The problem in such calculations is that you are making investments during the first year, and realizing the cashflows over a course of many future years. To assess such ventures that span multiple years, NPV comes to the rescue for financial decision-making, provided the investments, estimates, and projections are accurate to a high degree.
NPV methodology facilitates bringing all the cashflows (present as well as future) to a fixed point in time, at present, hence the name “present value.” It essentially works by taking how much the expected future cash flows are worth at present and subtracts the initial investment from it to arrive at “net present value.” If this value is positive, the project is profitable and viable. If this value is negative, the project is loss-making and should be avoided.
In the simplest terms:
Calculating future value from present value involves the following formula,
Future Value = Present Value × ( 1 + r ) t where: Future Value = net cash inflow-outflows expected during a particular period r = discount rate or return that could be earned in alternative investments t = number of time periods \begin{aligned} &\text{Future Value} = \text{Present Value} \times ( 1 + r ) ^ t \\ &\textbf{where:} \\ &\text{Future Value} = \text{net cash inflow-outflows expected during} \\ &\text{a particular period} \\ &r = \text{discount rate or return that could be earned in} \\ &\text{alternative investments} \\ &t = \text{number of time periods} \\ \end{aligned}Future Value=Present Value×(1+r)twhere:Future Value=net cash inflow-outflows expected duringa particular periodr=discount rate or return that could be earned inalternative investmentst=number of time periods
As a simple example, $100 invested today (present value) at a rate of 5 percent (r) for 1 year (t) will increase to:
$ 100 × ( 1 + 5 % ) 1 = $ 105 \begin{aligned} &\$100 \times (1 + 5\%) ^ 1 = \$105 \\ \end{aligned}$100×(1+5%)1=$105
Since we are looking to get present value based on the projected future value, the above formula can be rearranged as:
Present Value = Future Value ( 1 + r ) t \begin{aligned} &\text{Present Value} = \frac { \text{Future Value} }{( 1 + r ) ^ t } \\ \end{aligned}Present Value=(1+r)tFuture Value
To get $105 (future value) after one year (t), how much should be invested today in a bank account that is offering a 5% interest rate? Using the above formula:
Present Value = $ 105 ( 1 + 5 % ) 1 = $ 100 \begin{aligned} &\text{Present Value} = \frac { \$105 }{ (1 + 5\%) ^ 1} = \$100 \\ \end{aligned}Present Value=(1+5%)1$105=$100
Put another way, $100 is the present value of $105 that is expected to be received in the future (one year later) considering 5 percent returns.
NPV uses this core method to bring all such future cash flows to a single point in the present. The expanded formula for NPV is as follows:
NPV = F V 0 ( 1 + r 0 ) t 0 + F V 1 ( 1 + r 1 ) t 1 + F V 2 ( 1 + r 2 ) t 2 + ⋯ + F V n ( 1 + r n ) t n \begin{aligned} \text{NPV} = &\frac {FV_0}{(1 + r_0) ^ {t_0} } + \frac {FV_1}{(1 + r_1) ^ {t_1} } + \frac {FV_2}{(1 + r_2) ^ {t_2} } + \dots + \\ &\frac {FV_n}{(1 + r_n) ^ {t_n} } \\ \end{aligned} NPV=(1+r0)t0FV0+(1+r1)t1FV1+(1+r2)t2FV2+⋯+(1+rn)tnFVn
Here, FV0, r0, and t0 indicate the expected future value, applicable rates, and time periods for year 0 (initial investment), respectively. FVn, rn, and tn indicate the expected future value, applicable rates, and time periods for year n. The summation of all such factors leads to the net present value.
One must note that these inflows are subject to taxes and other considerations. Therefore, the net inflow is taken on a post-tax basis—that, is, only the net after-tax amounts are considered for cash inflows and are taken as a positive value.
One pitfall in this approach is that while financially sound from a theory point of view, an NPV calculation is only as good as the data driving it. It is therefore recommended to use the projections and assumptions with the maximum possible accuracy, for items of investment amount, acquisition and disposition costs, all tax implications, the actual scope and timing of cash flows.
Steps to Calculate NPV in ExcelThere are two methods to calculate the NPV in the Excel sheet. First, you can use the basic formula, calculate the present value of each component for each year individually, and then sum all of them up together. Second, you can use the in-built Excel function which can be accessed using the “NPV” formula.
Using Present Value for NPV Calculation in ExcelUsing the figures quoted in the above example, we assume that the project will need an initial outlay of $250,000 in year zero. From the second year (year one) onwards, the project starts generating inflows of $100,000, and they increase by $50,000 each year till the year five when the project gets over. The WACC, or weighted average cost of capital, is used by the companies as the discount rate when budgeting for a new project and is assumed to be 10 percent all throughout the project tenure.
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 in the first year leads to same present value during year zero, while the inflow of $100,000 during the second year (year 1) leads to a present value of $90,909. It indicates that a one-year future inflow of $100,000 is worth $90,909 at year zero, and so on.
Calculating present value for each of the years and then summing those up gives the NPV value of $472,169, as shown in the above screenshot of the Excel with the described formulas.
Using Excel NPV Function for NPV Calculation in ExcelIn the second method, the in-built Excel formula "NPV" is used. It takes two arguments, the discounting rate (represented by WACC), and the series of cash flows from year 1 to the last year. Care should be taken not to include the year zero cash flow in the formula, also indicated by initial outlay.
The result of the NPV formula for the above example comes to $722,169. To compute the final NPV, one needs to decrease the initial outlay from the value obtained from the NPV formula. It leads to NPV = ($722,169 - $250,000) = $472,169.
This computed value matches with the one obtained from the first method using PV value.
Calculating NPV in Excel: VideoThe following video explains the same steps based on the above example.
Pros and Cons of the Two MethodsWhile Excel is a great tool to make a rapid calculation with high precision, its usage is prone to errors and as a simple mistake can lead to incorrect results. Depending upon the expertise and convenience, analysts, investors, and economists use either of the methods as each offers pros and cons.
The first method is preferred by many as 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 hardcoded. The other big problem is that the built-in Excel formula does not net out the initial cash outlay, and even expert Excel users often forget to adjust the initial outlay value in the NPV value. On the other hand, the first method needs multiple steps in the calculation which may also be prone to user-induced errors.
Irrespective of which method one uses, the result obtained is only as good as the values plugged in the formulas. One must try to be as precise as possible when determining the values to be used for cash flow projections while calculating NPV.
Additionally, the NPV formula assumes that all cash flows are received in one lump sum at the year-end which is obviously unrealistic. To fix this issue and get better results for NPV, one can discount the cash flows at the middle of the year as applicable, rather than the end. This better approximates the more realistic accumulation of after-tax cash flows over the course of the year.
While assessing the viability of a single project, an NPV of greater than $0 indicates a project that has the potential to generate net profits. While comparing multiple projects based on NPV, the one with the highest NPV should be the obvious choice as that indicates the most profitable project.
Post a Comment