NPV Function

What Is The NPV Function?

The NPV function determines the Net Present Value for a series of cash flows at a specified discount rate. The NPV is fundamental to understanding the concept of money's time value. It is also widely used for financial modeling in Excel.

The NPV function can estimate the worth of investment and determine the project viability.

Using a set of cash flows and a specified discount rate, the NPV Function in Excel may determine the Net Present Value (NPV). Understanding the Time Value of Money is crucial since it forms the basis for many different financial valuation techniques.

The NPV function in financial modeling is helpful in estimating the worth of an investment or comprehending a project's viability. Analysts should typically use the XNPV calculation rather than the standard NPV function.

Since discounted cash flows are included in the analysis, net present value is more accurate than any other capital budgeting technique because it considers time and risk.

Here project creates value:

If NPV > 0 => Project to be accepted

Here project destroys value:

If NPV < 0 => Project to be rejected

Apply the NPV rule: If the NPV of the investment is positive, we should undertake it. On the other hand, if the value is negative, then we should simply reject the project.

If you have two projects and both lead to a positive NPV and have to choose only one, then the investment with the biggest net present value should be selected.

Let's take a look at the formula:

The net present value of an investment is technically the sum of the discounted cash flows minus the initial investment.

However, most companies nowadays compute the NPV via programs. Consequently, we will explain through this article how to handle this function via Microsoft Excel.

First, we should define a discounting rate, an important concept for understanding the time value of money. This notion in finance means that a dollar in your pocket today is worth more than a dollar in your hands in the future.

1. Discounting: This means transforming the future value of a sum of money or a series of cash flows into its present value. For example, selling a car for 10000$ in 5 years is not the same as selling a car for $10,000 today. 
2. Value 1 to Value n of The Free Cash Flows: The negative values indicate that we generated more expenses than revenues this year.

Examples of NPV Function

Suppose you want to purchase a pizza truck for $100,000. This amount will be considered as the initial investment in year 0. You forecast your business to generate the following FCF (free cash flows) for the next 5 years.

To determine whether the pizza truck is a good investment, you need to compute the net present value (NPV) of the stream of cash flows.

We will assume a discounted rate of 6%.

First, you press equal on an empty cell, then type NPV and select the option “NPV,” as highlighted in the picture below.

After selecting the option, select the item “fx” at the top left of your excel sheet.

You select the rate of 6% that we assumed on an item called “Rate.”

Next, drag all the FCF values starting from year 1 and not year 0 in the item “Value1,” and press “OK.”

Finally, you subtract the initial investment from the stream of discounted cash flows to get the NPV of the pizza truck investment after 5 years.

Note that the value is negative in year 0, so we will simply use addition.

In this scenario, the project's net present value is positive, meaning that we will undertake the investment because it creates value.

Realistic Scenario Example of NPV function

This example aims to give you an overview so that you can visualize a project in a more detailed way.

Before reading this example, we strongly suggest readers understand income statement and cash flow statement items such as Revenue, operating expenses, EBITDA, depreciation, taxes, Capex, and delta working capital.

The financial modeling course of Wall Street Oasis is the perfect asset you can have for you to master the DCF in Excel.

Scenario: You want to open a restaurant. You will offer a fixed menu. You plan to run the restaurant for five years. The investment is $500,000, including kitchen equipment and decoration.

• You will sell the assets for $150,000 after 5 years. 
• Number of dinners per night: 100.
• Open 300 nights yearly. 
• On average, customers order food from the menu for $35 and beverages for $15. 
• Food represents 34% of the cost of the menu price. It’s 50% for beverages.
• Wages per night are $2,160. Other taxes and rent are $41,400 per year. 
• Corporate tax rate: 20%. 
• Discounting rate (or cost of capital): 10%. 
• You will also have to invest 50000$ to buy an inventory of wine.

Compute the NPV:

The NPV is computed at the bottom left in green. This example aims to give you an idea of how capital budgeting works for small businesses. 

Other Metrics instead of NPV function

The net present value method's major drawback is that it necessitates some educated judgment on the firm's capital cost.

Making poor investment decisions will arise from assuming a cheap cost of capital. Assuming an excessively high cost of capital will lead to the loss of an excessive number of profitable ventures.

In addition, comparing two projects of different sizes is not a good application of the NPV approach. The amount of the net present value output is mostly influenced by the magnitude of the input because the NPV technique yields a result in dollars.

Payback Period

Suppose you have two choices:

One requires an investment of 2 million dollars and the other only $200,000. 

The first may have a better NPV, but you may not be able to afford the expenses in the first place.

The return on investment (ROI), a crucial factor for anyone with limited cash, is not assessed by the NPV calculation.

Although the NPV formula predicts how much value a project will create, it cannot tell whether your investment money is being used effectively.

An alternative would be the payback period.

The payback technique estimates the time needed to recover an investment. However, the time value of money is not considered, which can lead to computation inaccuracy for projects that require a long-term investment.

Internal Rate Of Return (IRR)

The IRR is the discount rate necessary to bring the NPV to 0. In the first example, we had a discount rate of 6% which led to a positive NPV of $23,569.85. On the other hand, a discount rate of 13% would cause the net present to go to 0.

Type “=” followed by “IRR” and then drag all the FCF to compute the internal rate of return.

Another way to look at it is that NPV and IRR attempt to address two different but connected issues. What is the total amount of money we will make if we proceed with this investment, after accounting for the time value of money, is the query for NPV.

The issue for IRR is, "What would be the equivalent annual rate of return that we would earn if we went forward with this investment?"

Researched and authored by Elio Mehanna |  LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• FV Function
• IPMT Function
• MIRR Function
• PMT Function
• XIRR Function