How to Calculate Net Present Value (NPV) in Excel

Reviewed by David KindnessFact checked by Vikki VelasquezReviewed by David KindnessFact checked by Vikki Velasquez

Net present value (NPV) helps companies determine whether a proposed project will be financially viable. It 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 (WACC)), terminal value, and salvage value) and is a core component of corporate budgeting.

Most analysts use Excel to calculate NPV. There are two ways to do this. You can input the present value formula, apply it to each year’s cash flows, and then add together each year’s discounted cash flows, minus expenditures, to get the final figure or use Excel’s built-in NPV function.

Key Takeaways

• Net present value (NPV) is an essential tool for corporate budgeting.
• It can help companies determine the financial viability of a potential project.
• It’s especially useful when comparing more than one potential project or investment.
• You can use Excel to calculate NPV instead of figuring it out manually.
• An NPV of zero or higher forecasts profitability for a project or investment; projects with a negative NPV forecast loss.

How to Use Net Present Value (NPV)

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 onward. 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 0 represents actual cash flows, while years one to five 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 the course of many future years.

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

NPV is just one metric used along with others by a company to decide whether to invest.

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

NPV

An NPV of greater than $0 indicates that a project has the potential to generate net profits. An NPV of less than $0 indicates a losing proposition.

2 Ways to Calculate NPV in Excel

There are two methods to calculate NPV 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) onward, the project starts generating inflows of $100,000. They increase by $50,000 each year until year five, when the project is completed.

The company uses the WACC 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 one’s inflow of $100,000 during the second year results in a present value of $90,909. Year two’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 the WACC), and the series of cash flows from year one 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.

Warning

While Excel is a great tool for making rapid calculations with precision, errors can occur. Since a simple mistake can lead to incorrect results, it’s important to take care when inputting data.

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

Pros

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

Cons

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

Method 2

Pros

• 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 midyear, as needed (the XNPV function can help here). This presents a better view of after-tax cash flows over the course of the year.

Cons

• The user must make sure the inputs include the initial outlay as well as all inflows in a structured table format.

  For financial modeling and audit purposes, it’s harder with Method Two than with Method One to determine the calculations, figures used, what’s hard-coded, and what’s input by users. The NPV calculation is a ‘black box’ and the underlying math is not clear unless a person knows the math already.

What Is Net Present Value (NPV)?

Net present value (NPV) 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 increase company value. 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 increase company value. A net present value that’s less than $0 means a project isn’t financially feasible and should be avoided.

Can I Calculate NPV Using Excel?

Yes. 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 (NPV) 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.

Read the original article on Investopedia.