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 nba腾讯体育直播e 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 which needs an initial investment of $100,000 during the first year. Since this is an investment, it is a cash outflow which 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 cashflows of the project are as follows:

Image
Image by Sabrina Jiang 漏 Investopedia聽2020

XXXX-A represents actual cashflows, while XXXX-P represents projected cashflows聽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 cashflows are worth at present and subtracts the initial investment from it to arrive at 鈥渘et 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 simplest terms,

NPV = (Today鈥檚 value of the expected future cash flows) 鈥 (Today鈥檚 value of invested cash)

Calculating future value from present value involves the following formula,

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\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 which is offering a 5% interest rate? nba腾讯体育直播ing 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 are expected to be received in future (one year later) considering 5 percent returns.

NPV uses this core method to bring all such future cashflows to a single point in the present.

The expanded formula for NPV is

NPV=FV0(1+r0)t0+FV1(1+r1)t1+FV2(1+r2)t2++FVn(1+rn)tn\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

where FV0, r0, and t0 indicate the expected future value, applicable rates and time-periods for year 0 (initial investment), respectively, and聽FVn, rn, and tn indicate the expected future value, applicable rates, and time-periods for year n. 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 the 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 Excel

There are two methods to calculate the NPV in the Excel sheet.

First is to use the basic formula, calculate the present value of each component for each year individually, and then sum all of them up together.

Second is to use the in-built Excel function which can be accessed using the 鈥淣PV鈥 formula.

nba腾讯体育直播ing Present Value for NPV Calculation in Excel

nba腾讯体育直播ing the figures quoted in the above example, we assume that the project will need an initial outlay of $250,000 in year zero. 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 cashflows聽from year zero to year five. For example, the cashflow of -$250,000 in the first year leads to same present value during the year zero, while the inflow of $100,000 during the second year (year 1) leads to present value of $90,909. It indicates that 1-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.

nba腾讯体育直播ing Excel NPV Function聽for NPV Calculation in Excel

In 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 cashflows from year 1 to the last year. Care should be taken not to include the year zero cashflow 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 鈥 Video

The following video explains the same steps based on the above example.

Pros and Cons of the Two Methods

While 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聽notnet 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 cashflow 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.