Calculate Npv in Excel

Net Present Value (NPV) is a term in financial accounting that allows managers to consider the time value of money. For instance, the dollar you receive today is worth more than the dollar you won't see until next year. The NPV is sometimes expressed as such: the factor that will produce an internal rate of return (IRR) of 0 for a series of positive and negative cash flows. Knowing how to calculate NPV in Excel can help you analyze potential investments and make sound decisions.

Steps

Sample Calculator

Doc:NPV Calculator

Calculating NPV in Excel

  1. Gather the details of the scenario for which you want to calculate NPV.
  2. Launch the Microsoft Excel application.
  3. Open a new workbook and save it with a relevant file name.
  4. Choose an annual discount rate for your series of cash flows before you 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.
  5. Enter labels in the cells from A1 down to A6 as follows: Annual Discount Rate, Initial Investment, 1st Year Return, 2nd Year Return, 3rd Year Return and NPV function.
    • If you will be using more than 3 years of returns, make cell labels for those years as well.
  6. Input the variables for the Excel function in column B from cells B1 down to B5.
    • 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.
  7. Determine the timing of your initial investment.
    • If the initial cash outlay occurs at the end of the first period, it will be included as a value in th​e 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.
  8. Create the NPV function in cell B6.
    • 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.
  9. Add the initial cash investment to the result of the NPV function only if this is made at the beginning of the first period.
    • Otherwise, the NPV is as calculated by the formula.

Tips

  • You can see the difference that time makes in the value of money by adding the estimated returns for each year, subtracting the initial investment and comparing this to the NPV for your scenario. For instance, an initial investment of $5,000 at the end of period 1, followed by returns of $2,000, $3,000 and $4,000 for each of the following 3 years returns an NPV of $2,093.44. Summing the same investment and estimated gains returns a sum of $4,000.

Warnings

  • Keep in mind that the proposed annual returns are only estimates. Every investment or venture carries risk of loss and your actual return may vary greatly from the estimated amounts.

Things You'll Need

  • Computer
  • Microsoft Excel
  • Project or investment details

Related Articles

  • Calculate an Irr on Excel

Sources and Citations

Retrieved from "https://kipkis.com/index.php?title=Calculate_Npv_in_Excel&oldid=272963"