What is the formula to calculate Net Present Value (NPV) in Excel?

Net present value (NPV) is an essential part of a company’s budget. It is a comprehensive method to calculate whether a proposed project is financially viable or not. The NPV calculation incorporates many financial topics into one formula: cash flow, time value of money, discount rate over the life of the project (usually WACC), terminal value, and value of recovery.

To understand NPV in its simplest form, think about how a project or investment performs in terms of cash inflow and outflow. Example: You are planning to build a factory that requires an initial investment of $100,000 in the first year. Since it is an investment, it is a cash outflow that can be viewed as negative net worth. This is also known as the initial deposit. After the factory has been successfully set up with the initial investment in the first year, it is scheduled to start producing products or services in the second year. This results in net cash inflows in the form of proceeds from the sale of the works. For example, the plant makes $100,000 in year two, which increases by $50,000 each year for the next five years. The actual and expected project cash flows are as follows:

XXXX-A represents the actual cash flows, while XXXX-P represents the projected cash flows over the years mentioned. A negative value indicates costs or investments, while a positive value represents inputs, income, or earnings.

How do you decide if this project is profitable or not? The problem with these calculations is that you make investments in the first year and realize the cash flows in subsequent years. To evaluate such ventures that span multiple years, the NPV helps in financial decision-making, provided investments, estimates, and forecasts are very accurate.

The NPV method allows all cash flows (present and future) to be brought back to a fixed point in time, the present moment, hence the name ‘present value’. It basically consists of taking the present value of the expected future cash flows and subtracting the initial investment to get the “Net Present Value”. If this value is positive, the project is profitable and viable. If this value is negative, the project is in deficit and must be avoided.

in simple words,

NPV = (present value of expected future cash flows) – (present value of cash invested)

Calculating future value from present value involves the following formula:

Future Value=Present Value×(1r)t where: Future Value=the expected net cash inflows/outflows during a given period=discount rate or rate of return that could be achieved through alternative investments st=number of period starts{aligned} &text {Future value} = text {current value} times ( 1 + r ) ^ t \\ &textbf{where:} \\ &text{Future value} = text{expected net cash inflows/outflows during} \N- &N- text {a specific period} \ \N- &r = text{discount rate or rate of return that could be earned in} \N – &N – text (alternative investments) \N &t = text{number of periods}

Future Value=Present Value×(1r)t where: Future Value=the net cash inflows/outflows expected during a given period=discount rate or rate of return that alternative investments could generate=number of periods

As a simple example, $100 (present value) is invested today at a rate of 5% (r) for 1 year

1×(15%)1=15begin{aligned} &$100 times (1+5%)^1=$105\end{aligned}


Since we want to get a present value based on expected future value, the formula above can be rearranged as follows:

CurrentValue=FutureValue(1r)tbegin{aligned} &text{CurrentValue} = frac { Text{FutureValue} }{( 1 + r ) ^ t }

Current Value=(1r)tFuture Value

To get $105 (future value) after one year

Value actual=15(15%)1=1begin{aligned} &text{Present Value} = frac { $105 }{ (1 + 5%) ^ 1} = $100 \ end{aligned}

Current value=(15%)115=1

In other words, $100 is the present value of $105 that should be received in the future (one year later) assuming a 5% return.

NPV uses this basic method to bring all of these future cash flows down to a single point in the present.

The advanced formula for NPV is as follows

NPV=FV(1r)tFV1(1r1)t1FV2(1r2)t2⋯FVn(1rn)tnbegin{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} }


where FV, r0, and t indicate the expected future value, applicable rates, and periods, respectively, for year 0 (initial investment), and VFn, rn, and tn indicate the expected future value, applicable rates, and periods, for year n. The sum of all these factors results in the present value.

It should be noted that these flows are subject to taxes and other considerations. Therefore, net cash flows are accounted for on an after-tax basis – ie only net amounts after tax are considered for cash cash flows and are considered a positive value.

One of the pitfalls of this approach is that an NPV calculation, while financially sound from a theoretical point of view, is only valid based on the data behind it. It is therefore recommended to use the forecasts and assumptions for the following elements with the greatest possible accuracy: the size of the investment, the acquisition and disposal costs, any tax implications, the actual scope and the cash flow plan.

Steps to calculate present value in Excel


There are two methods to calculate present value in Excel spreadsheet.

First, using the basic formula, calculate the present value of each component for each year individually, and then add them together.

The second is to use the built-in Excel function, which can be accessed through the “NPV” formula.

Using Present Value for NPV Calculation in Excel

Using the numbers provided in the example above, we assume that the project will require an initial investment of $250,000 in year zero. Beginning in year two (year one), the project generates inflows of $100,000, increasing by $50,000 each year until year five when the project ends. WACC, or Weighted Average Cost of Capital, is used by companies as a discount rate when budgeting for a new project and is assumed to be 10% for the life of the project.

The present value formula is applied to each cash flow from year zero through year five. For example, the cash flow of -$250,000 in year one results in the same present value in year zero, while the inflow of $100,000 in year two (year 1) results in a present value of $90.909. This shows that the inflow of $100,000 in one year is worth $90,909 in year zero, and so on.

Calculating the present value for each of the years and summing them up gives a net present value of $472,169 as shown in the above screenshot of the Excel using the formulas described.

Using Excel’s NPV function to calculate NPV in Excel

The second method uses Excel’s built-in NPV formula. It is based on two arguments, the discount rate (represented by the WACC) and the range of cash flows from year one to last year. Care must be taken not to include year-zero cash flow in the formula, which is also indicated by the initial expenses.

The result of the net present value formula for the example above is $722,169. To calculate the final NPV, the initial expenses must be reduced by the value obtained from the NPV formula. We then get the NPV = ($722,169 – $250,000) = $472,169.

This calculated value corresponds to that obtained by the first method using the PV value.

Net Present Value Calculation in Excel – video

The video below explains the same steps using the example above.

Pros and cons of both methods

Although Excel is a great tool for making quick calculations with high accuracy, using it is error-prone and a single mistake can lead to incorrect results. Depending on their expertise and convenience, analysts, investors, and economists use one of these methods, as each has its advantages and disadvantages.

The first method is preferred by many because financial modeling best practices require transparent and easily verifiable calculations. The problem with piling all the calculations into one formula is that you can’t easily tell which numbers go where, or which numbers are user input or hard-coded. The other big problem is that the built-in Excel formula doesn’t account for the initial deposit, and even experienced Excel users often forget to adjust the value of the deposit in the NPV value. On the other hand, the first method requires several calculation steps, which can also be subject to user-induced errors.

Regardless of the method used, the result obtained is only valid according to the values ​​entered in the formulas. One should try to be as accurate as possible in determining the values ​​to be used for cash flow projections when calculating present value. Also, the NPV formula assumes that all cash flows will come in at once at the end of the year, which is obviously unrealistic. To solve this problem and get better results for the NPV, one can discount the cash flows mid-year, if necessary, instead of at the end. This is more in line with the more realistic accumulation of after-tax cash flows over the year.

When assessing the feasibility of an individual project, an NPV greater than $0 indicates a project that has the potential to generate net profits. When comparing multiple projects based on NPV, the one with the highest NPV should be the obvious choice as it indicates the most profitable project.


  Calculate loans or savings plans