How to Calculate a Net Present Value Using Excel

by Robert Shaftoe; Updated September 26, 2017
chart

Financial analysts typically use the net present value analysis to estimate the feasibility of a project or investment based on the projected cash outflows relative to its inflows. In the classic, simplified NPV models, if the present value of cash inflows is higher than the present value of cash outflows, the project or investment is considered worth taking on.

Using the Formula Toolbar in Excel

Enter your assumptions into the spreadsheet. Enter a discount rate, which is synonymous with an interest rate, to account for the time value of money, and the projected cash outflows and inflows. Be sure to enter your positive and negative cash flows in the same cell. For example, suppose in Year 1 there is a cash outflow of $100 and a cash payment into the investment, or inflow of $50. You enter this in the cell as "= -100+50". Open the Formula toolbar and select "XNPV." A "Function Arguments" box will open, in which you enter your assumptions: Rates, Values and Dates. Click in each assumption box, and then click on the cell in which you have already entered the assumption. Press Enter and you will see the calculated NPV.

Calculating NPV Manually in Excel

Enter your assumptions just as you would if you were to use the Formula toolbar. You have more flexibility in that you don't have to sum up each time period's cash flows. You can show outflows and inflows separately. Use one column for outflows and one for inflows. Calculate the present value of each cash flow using the present value formula by entering "=1/(1+r)^n", where "r" equals the discount, or interest rate, and "n" equals the time period. For "r" and "n", you can simply press on the cells in which you've already entered your assumptions. This formula results in the present value factor, which is multiplied by the cash flow to arrive at the cash flow's present value. The final step is to take the sum of the present value of all cash inflows vs. outflows.

Photo Credits

  • lucky336/iStock/Getty Images
Cite this Article A tool to create a citation to reference this article Cite this Article