You can use the =NPV formula to help you calculate Net Present Value using Microsoft Excel. The only draw-back to the formula is that it tries to discount the initial year investment amount which is usually not discounted. So when using the =NPV formula, include the years or periods which are subject to discounting and then include your initial investment outflow into the calculation to arrive at the Net Present Value.
If you go to the Lesson 1 Workbook, click on Tab 1 – Cash Flow Example. In cell I81 you will see how the =NPV calculation was incorrectly calculated and in cell I82 you will see the correct calculation.
In the example above, the two parameters for the =NPV function are the hurdle rate and the net cash flows that should be discounted. The cell range noted in green ignores the initial year 0 since the =NPV mistakenly discounts this year. So you need to append the calculation by adding the initial year as noted in purple (+E59).
“. . . a financial model is designed to represent in mathematical terms the relationships among the variables of a financial problem so that it can be used to answer “what if” questions or make projections. In creating financial models, you always have to keep in mind that you want to capture as many of the interdependencies among the variables of the model as possible. In addition, you want to structure your models in such a way that it is easy to ask “what if” questions, that is, change the values of the independent variables and observe how they affect the values of the key dependent variables.” – Financial Modeling using Excel and VBA by Chandan Sengupta