These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, December 7, 2010

NPV calculation

This VBA project will allow to reduce time spend on mechanical calculation of NPV, CAPM, WACC and price per share and spent more time on decision making aspects of projects, like preferred capital structure, analysis of growth of EBIT, CAPEX and NWC.
In order to calculate NPV of the project, an analyst needs to create a table and calculate following data for every single period:
- Earnings before income tax
- Tax
- Net Operating profit after tax
- Depreciation
- Capital expenditures
- Change in Net Working Capital
- Future cash flow
- Total Future cash flow
In order to find NPV, the analyst should calculate terminal value and WACC. Data for CAPM could be obtained from and Then WACC can be calculated using information about capital structure and CAPM:

In accordance with particular project or particular assumptions some of the mentioned above data can grow/decline at a constant rate or change in accordance with the established pattern.
I have created NPV calculation macro, which will allow inputting available information into the series of forms and calculating NPV without manually creating a spreadsheet.
VBA macro automatically creates a final table with required information on the separate sheet “Report”.

No comments:

Post a Comment

Blog Archive