Calculating Net Present Value (NPV) with Excel

 

In the past, when I have taught the concept of NPV (Net Present Value), I often ask if everyone understands it. If not, then I go over it in basic terms. Some training modules can make it sound like a difficult concept. But it is actually much easier than it initially comes across.

Net Present Value (NPV)

NPV is a function in Excel that helps you to calculate the difference between money you receive (cash inflows) and money you pay (cash outflows) for a project or investment. A positive result from the function would mean a profitable project/investment. A negative result would indicate a net loss.

NPV Function

In Excel, the function syntax is NPV(rate,value1,[value2],…). The rate and value1 are required. Value 2, etc. are not required, but you can have up to 254 values. The rate is the discount rate, inflation, or rate of a competing investment. Each value in the function represents an individual payment or income for the project/investment. If you already have the payments/income in cells in Excel, you can use those in the function.

Example

I created a very simple example. The rate is 6% and the payments/income amounts are listed in Excel for the initial outlay and then 4 years of returns. See Figure 1.

NPV data screenshot

Figure 1

The initial outlay and year 4 return are negative since they are cash outflows. The returns for years 1-3 are positive indicating cash inflows. Since the NPV function is based on future cash flows, you cannot include the initial outlay at the beginning of the first year/period in the function arguments. You add it at the end. If you have the value of the initial outlay at the end of year 1, then you could include it in the NPV function arguments.

The formula for NPV would be =NPV(B3,B7:B10)+B6. If you had additional amounts, you can use the comma to separate different cells or cell ranges. See the results in Figure 2.

NPV with formula showing result screenshot

Figure 2

Finding the NPV for a project or investment can help with making decisions with budgets and project options. It is worth the time to analyze to make sure you are making the best choice for your organization.

 

Image credit: https://www.flickr.com/photos/pgasston/

Angela Nino - Versitas Editor

Leave a Reply

Your email address will not be published. Required fields are marked *