Lisa Pollack:

A tempest in a spreadsheet: But none of that is the spreadsheet error. This is the spreadsheet error (on the last two pages of the 129-page Report — way to save the best for last?):

…further errors were discovered in the Basel II.5 model, including, most significantly, an operational error in the calculation of the relative changes in hazard rates and correlation estimates. Specifically, after subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two….

Did we say “error”? We meant errors:

The West End analytic had two options for calculating hazard rates and correlations: a traditional Gaussian Copula model and a so-called Uniform Rate…. The Model Review Group employee discovered that West End defaulted to running Uniform Rate rather than Gaussian Copula… contrary to the language in the Model Review Group approval….

Now, we’re not exactly big fans of the Gaussian Copula, but it is quite a concern when the spreadsheet doesn’t even do what you think it does. There’s more juicy background to this than you think. It concerns the guy who developed the new VaR model. First, there’s the fact that he was overworked (“join the club”, we hear you think):

The modeler is a London-based quantitative expert, mathematician and model developer…. On a number of occasions, he asked the trader to whom he reported for additional resources to support his work on the VaR model, but he did not receive any… increased operational risk and artificially low volatility numbers:

For example, the model operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another. In addition, many of the tranches were less liquid, and therefore, the same price was given for those tranches on multiple consecutive days, leading the model to convey a lack of volatility….

These two things (manual entry and artificial lack of volatility) came up in the review of the model. The report says there is “some evidence” that pressure was put on the reviewers to get on with approving the model in January because of the risk limit breaches being incurred with the old model…. Hence the Model Review Group “may have been more willing to overlook the operational flaws apparent during the approval process.”… The Numerix analytic suite had been approved by the Model Review Group. But the modeler, when developing the new VaR model, developed his own suite — called “West End”. This suite was not reviewed in advance of the new VaR model being rolled out, but rather only had a limited amount of backtesting completed on it. All of which makes the little errors (in the sense of single-day fat fingers)…

…a spreadsheet error caused the VaR for April 10 to fail to reflect the day’s $400 million loss in the Synthetic Credit Portfolio. This error was noticed, first by personnel in the Investment Bank…

And the more systemic ones…

…the new CIO VaR model for the Synthetic Credit Portfolio in late January 2012 were flawed, and the model as implemented understated the risks presented by the trades in the first quarter of 2012.

…considerably less surprising.

## Recent Comments