Financial Simulation Modeling in Excel

“GARBAGE IN, GARBAGE OUT”

Programmers and modelers frequently deal with data issues. In the financial world, there are several sets of issues that an analyst must take into account. The most obvious one is obtaining data. Models such as Merton or reduced form models force us to find equity or collateral default swap (CDS) market data. However, for many small private companies, such data is simply not available. In some cases where the data is available, it is deficient. Stock prices may not account for dividends and splits, which clearly should impact the trading level of the asset without necessarily impacting the risk of owning the stock. CDS and bonds may trade as infrequently as a few times a month. Whether illiquid markets actually tell us much about the riskiness of a security or company is a reasonable question to ask in these situations.

And even when full data sets are available, there are judgment calls that must be made in any analysis. Should we take historical equity returns from 5 years or 20? Should we weight the data equally or give greater weight to recent events? These choices have very real impacts on model results. These issues gained prominence due to the results of risk models in the 1990s. We understand that a number of models showed a sharp decrease in risk from one day to the next, despite the fact that portfolios had not changed. When investigated, it turned out that the stock market crash of 1987 had rolled out of their historical data set, and as a result ...