Net present value (better known as Net Present Value aka NPV) is a term in financial accounting that allows managers to take into account the time value of money. For example, money you receive today has a greater value than money received next year. NPV is sometimes expressed as a factor that will result in an internal rate of return (IRR) of 0 for a series of positive and negative cash flows. You can analyze investment opportunities and make informed decisions with the help of NPV through Excel.
Step
Method 1 of 1: Calculating NPV in Excel
Step 1. Gather the required scenario details
Step 2. Open the Microsoft Excel program
Step 3. Open a new workbook and save it with the desired file name
Step 4. Select the annual discount rate for the cash flow series before calculating the NPV
You can use the investment interest rate, inflation rate, or the company's desired rate of return on investment
Step 5. Enter the following labels in cells A1 to A6:
Annual Discount Rate, Initial Investment, First Year Return, Second Year Return, Third Year Return, and NPV.
If you're counting to more than 3 years, add labels for those years
Step 6. Enter the variables for the Excel function in column B, starting from cells B1 to B5
- The initial investment is the amount of cash required to start the project or investment and the value entered must be negative.
- The first, second, and third year returns are the estimated total returns received during the initial few years of the investment. The net profit value entered must be positive. However, if the company is expected to incur a loss, the value entered must be negative.
Step 7. Determine the timing of your initial investment
- If the initial investment is given at the end of the first period, the amount will be included in the NPV function.
- If the initial investment deposit is made now, or at the beginning of the first period, the value is not included in the NPV function. You will add up the value with the result of the NPV function.
Step 8. Create an NPV function in cell B6
- Select a cell and click the function button labeled "fx", then select the NPV function. The function window will appear on the screen.
- Enter a reference to cell B1 in the " rate " box.
- Enter the reference to cell B2 in the first " value " box only if the investment is given at the end of the first period. Otherwise, don't include it.
- Enter references to cells B3, B4 and B5 in the next 3 value boxes. After that, click the “OK” button.
