Financial Analysis and Modeling Using Excel and VBA

Chapter 28. Analyzing Market History

In this chapter, we will use VBA to develop several models that we developed earlier using Excel to measure and show the performance of various asset classes both over time and relative to each other. The models will aiso plot the results in a few different types of charts commonly used to display them.

The key advantage of the models in this chapter over those we developed with Excel is that these models are more flexible. Even users who do not know Excel or VBA will be able to do a broader range of analysis with them.

This chapter does not introduce any new theory or concepts. Unless you are familiar with the material, you may want to first review the theory and concepts (as well as the models) in the chapter by the same name in Patt Two.

Modeling Examples

MODEL 1: NOMINAL AND REAL GROWTH OF $1 INVESTMENT IN STOCKS

The Problem

Given the monthly price and dividend data for an index of large-cap stocks for the period from December 1945 to December 1999 and the corresponding CPI values, calculate in nominal and real terms the growth of an initial $1 investment over time. Make the starting and ending months input variables of the model so that the user can specify the period. Also calculate the nominal and real effective annual returns over the specified period.

Your model should provide a table showing the month-by-month growth of the investment and plot a chart to show the growth over time and compare nominal and real growths similar to Figures 28.1 ...