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 the weighted average cost of capital (WAAC)), terminal value, and salvage value.

How to 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 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 onwards. 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-A represents actual cash flows while Years-P 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 a course of many future years.

When multi-year ventures need to be assessed, NPV can assist the financial decision-making, provided the investments, estimates, and projections are accurate.

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 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)

2 Ways to Calculate NPV in Excel

There are two methods to calculate net present value 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.

1. Using Present Value to Calculate NPV

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) onwards, the project starts generating inflows of $100,000. They increase by $50,000 each year till year five when the project is completed.

The WACC is used by the company 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 1's inflow of $100,000 during the second year results in a present value of $90,909. Year 2'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.

2. Using the NPV Function to Calculate NPV

The second Excel method uses the built-in NPV function. It requires the discount rate (again, represented by WACC), and the series of cash flows from year 1 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.

Pros and Cons of the 2 Methods

Analysts, investors, and economists can use either of the methods, after assessing their pros and cons.

Method 1

Pro

The present value method is preferred by many for financial modeling because its calculation and figures are transparent and easy to audit.

Con

Unfortunately, it requires multiple manual steps. This takes time and has the potential for input errors.

Method 2

Pro

Method Two's NPV function method can be simpler and involve less effort than Method One.

What's more, although it assumes unrealistically that all cash flows are received at the end of the year, cash flows can be discounted at mid-year, as needed (the XNPV function can help here). This presents a better view of after-tax cash flows over the course of the year.

Con

On the downside, the initial cash outlay must be netted out manually, a need that can be overlooked by Excel users.

Also, for financial modeling and audit purposes, it's harder with Method Two than with Method One to determine the calculations, figures used, what's hardcoded, and what's input by users.

What Is Net Present Value?

Net present value 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 be profitable. NPV plays an important role in a company's budgeting process and investment decision-making.

How Do I Interpret NPV?

A net present value of $0 or higher is a good sign. It indicates that a project will be profitable. A net present value that's less than $0 means a project isn't financially feasible and perhaps should be avoided.

Can I Calculate NPV Using Excel?

Yes. While you could calculate NPV by hand, 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.

The Bottom Line

Net present value 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.