How to Calculate Npv in Excel
Gather the details of the scenario for which you want to calculate NPV. , Launch the Microsoft Excel application. , Open a new workbook and save it with a relevant file name. , Choose an annual discount rate for your series of cash flows before you...
Step-by-Step Guide
-
Step 1: Gather the details of the scenario for which you want to calculate NPV.
You can use the rate of interest your cash outlay could be earning if invested, the inflation rate or the stated rate an investment must meet in order to be approved by your company. , If you will be using more than 3 years of returns, make cell labels for those years as well. , The initial investment represents the cash you will need to contribute in order to begin the project or investment and should be entered as a negative value.
The 1st, 2nd and 3rd year return values will be estimates of what you expect to realize during the first few years of the project.
A net gain should be entered as a positive value, but, if you anticipate a net loss in one or more years, those should be entered as negatives. , If the initial cash outlay occurs at the end of the first period, it will be included as a value in the NPV function.
If it occurs now, or at the beginning of the first period, it will not be included in the NPV function.
Instead, you will add it to the result of the NPV function. , Select the cell and click the function button, labeled "fx." Choose the NPV function.
The function window will launch.
Enter a reference to cell B1 in the "rate" field.
Enter a reference to cell B2 in the first "value" field only if the investment occurs at the end of the first period.
Otherwise, do not enter a reference to cell B2.
Enter references to cells B3, B4 and B5 in the next 3 value fields.
Click the "OK" button. , Otherwise, the NPV is as calculated by the formula. -
Step 2: Launch the Microsoft Excel application.
-
Step 3: Open a new workbook and save it with a relevant file name.
-
Step 4: Choose an annual discount rate for your series of cash flows before you calculate NPV.
-
Step 5: Enter labels in the cells from A1 down to A6 as follows: Annual Discount Rate
-
Step 6: Initial Investment
-
Step 7: 1st Year Return
-
Step 8: 2nd Year Return
-
Step 9: 3rd Year Return and NPV function.
-
Step 10: Input the variables for the Excel function in column B from cells B1 down to B5.
-
Step 11: Determine the timing of your initial investment.
-
Step 12: Create the NPV function in cell B6.
-
Step 13: Add the initial cash investment to the result of the NPV function only if this is made at the beginning of the first period.
Detailed Guide
You can use the rate of interest your cash outlay could be earning if invested, the inflation rate or the stated rate an investment must meet in order to be approved by your company. , If you will be using more than 3 years of returns, make cell labels for those years as well. , The initial investment represents the cash you will need to contribute in order to begin the project or investment and should be entered as a negative value.
The 1st, 2nd and 3rd year return values will be estimates of what you expect to realize during the first few years of the project.
A net gain should be entered as a positive value, but, if you anticipate a net loss in one or more years, those should be entered as negatives. , If the initial cash outlay occurs at the end of the first period, it will be included as a value in the NPV function.
If it occurs now, or at the beginning of the first period, it will not be included in the NPV function.
Instead, you will add it to the result of the NPV function. , Select the cell and click the function button, labeled "fx." Choose the NPV function.
The function window will launch.
Enter a reference to cell B1 in the "rate" field.
Enter a reference to cell B2 in the first "value" field only if the investment occurs at the end of the first period.
Otherwise, do not enter a reference to cell B2.
Enter references to cells B3, B4 and B5 in the next 3 value fields.
Click the "OK" button. , Otherwise, the NPV is as calculated by the formula.
About the Author
Andrew Miller
A passionate writer with expertise in lifestyle topics. Loves sharing practical knowledge.
Rate This Guide
How helpful was this guide? Click to rate: