What is the formula for calculating 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. 

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 as 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 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:

Suffix A represents actual cashflows, while P represents projected cashflows over the mentioned years. 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 a “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 simplest terms,

NPV = (Today’s value of the expected future cash flows) – (Today’s value of invested cash)

Calculating future value from present value involves the following formula,

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

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

Since we are looking to get present value based on the projected future value, the above formula can be rearranged as,

To get $105 (future value) after one year (t), how much should be invested today in a bank account which is offering 5% interest rate? Using above formula,

Present Value = $105 / [(1+5%)^1] = $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 present.

The expanded formula for NPV is

where FV₀, r₀ and t₀ indicate the expected future value, applicable rates and time-periods for year 0 (initial investment), respectively, and  FV_n, r_n and t_n indicate the expected future value, applicable rates and time-periods for year n. Summation of all such factors lead 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 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.

There are two methods to calculate the NPV in excelsheet.

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

Second is to use the in-built excel function which can be accessed using the “NPV” formula.

Using 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.

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.

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

While excel is a great tool to make 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 hard coded. 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 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 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.