This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
en:how-to-calculate-the-npv-with-excel [2018/07/20 18:11] federico |
en:how-to-calculate-the-npv-with-excel [2018/08/10 10:53] (current) federico |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== How to Calculate NPV in Excel ====== | + | {{htmlmetatags> |
| + | metatag-description=(An easy way to calculate the Net Present Value in Excel [and formula]) | ||
| + | }} | ||
| + | |||
| + | ====== NPV in Excel ====== | ||
| This pages teach you how to calculate the Net Present Value (NPV) of an investment project, using Microsoft 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 | + | ===== 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. | 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. | ||
| Line 11: | Line 15: | ||
| If the project is financed using external capital, companies can chose the Weighted Average Cost of Capital (WACC) as a discount rate. | 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- Insert the Years ===== |
| - | + | ||
| - | 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: | 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 | + | {{:en:npv-excel-1.png?nolink|}} |
| Note the number "0". It's because the initial investment is done in the year "0". | Note the number "0". It's because the initial investment is done in the year "0". | ||
| Line 23: | Line 25: | ||
| 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. | 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 | + | ===== 3 - 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. | In the second column, you will input the cash flow corresponding to each year. For the "0" year, insert the initial investment. | ||
| Line 31: | Line 34: | ||
| 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. | 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 | + | {{:en:npv-excel-2.png?nolink|}} |
| - | 3- Insert the Discount Rate | + | ===== 4- 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". | 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 | + | {{:en:npv-excel-3.png?nolink|}} |
| - | 4- Insert the Excel NPV Formula | + | ===== 5- Insert the Excel NPV Formula ===== |
| - | Click in a cell in which you want to calculate the NPV and type the following: | + | Click in a cell in which you want to calculate the NPV and: |
| + | - type "=NPV(" | ||
| + | - select the cell where you typed the discount rate ("E2" in our example) | ||
| + | - select the cell range that contains the future cash flows ("C5:C9" in our example). Do not select the initial investment. | ||
| + | - close the formula, insert ")". If you press enter, you will have the present value of all future cash flows at period zero. | ||
| + | - add the initial investment: at the end of the previous formula, in the same cell, insert "+" and select the cell containing the initial investment. (B3 in our example). Press enter and you will have calculated the NPV in Excel. | ||
| - | + | {{:en:npv-excel-4.png?nolink|}} | |
| + | |||
| + | The final formula looks like this: | ||
| + | |||
| + | =NPV(E2,B4:B8)+B3 | ||
| + | |||
| + | |||
| + | {{:en:npv-excel-5.png?nolink|}} | ||
| + | |||
| + | |||
| + | ===== Important Note ===== | ||
| + | Actually, the Excel NPV formula does not calculate the Net Present Value, but the value of a series of cash flows at period zero. That is why we enter only the future cash flows in the formula and then subtract the initial investment, that is, the cash flow at period zero. | ||
| + | If someone includes the initial investment in the NPV formula, it's wrong. | ||
| + | ===== Video ===== | ||
| {{ :en:net-present-value-excel.mp4?728x640}} | {{ :en:net-present-value-excel.mp4?728x640}} | ||