How to Calculate NPV in Excel

This pages teach you how to calculate the Net Present Value (NPV) of an investment project, using Microsoft Excel.

1- Have the Cash Flow and the Discount Rate Available

To calculate the NPV you will need the initial investment and all the future cash flows. Usually, the NPV is used to analyze the profitability of an investment that will happen in the future. This is why all the future cash flows are usually estimations or projections.

In the NPV, the Discount Rate should be the highest rate of return available to invest, with a similar risk to the current investment project. The discount rate is very important in the calculation of the NPV, but at the same time, it can be very difficult to estimate, because it's usually very difficult to estimate the risk of an investment project.

If the project is financed using external capital, companies can chose the Weighted Average Cost of Capital (WACC) as a discount rate.

2- Insert the Data in Excel

2.1 - Insert the Years

Select a cell (e.g. A2) ant type “Year”. Below the cell, insert 1,2,3… in each cell. For example, if your investment project lasts 5 years, you will end with a column like the following:

##image-1

Note the number “0”. It's because the initial investment is done in the year “0”.

If you have the data by months, you have to put the months in the first column. But take into account that the discount rate must be a monthly rate.

2.2 - Insert the Cash Flow

In the second column, you will input the cash flow corresponding to each year. For the “0” year, insert the initial investment.

A project can have many years with a negative cash flow. The calculation doesn't change.

If you plan to sell the business in the future, in the last year the cash flow must include the inflow of funds resulting from the sale.

# Image 2

3- Insert the Discount Rate

Select a cell and insert “Discount Rate”. In the cell below or next, insert the discount rate. For example, if the discount rate is 6%, type “0.06”.

# image 3

4- Insert the Excel NPV Formula

Click in a cell in which you want to calculate the NPV and type the following: