Guest blog by Mary Pat Campbell, FSA, MAAA, PRM
Did you know that a simple spreadsheet formula led to a $6 billion trading loss?
Did you know that the 2017 Tax Cuts and Jobs Act caused a considerable amount of work removing hard-coded rates?
Spreadsheets, especially those in Microsoft Excel, are deeply embedded in virtually every business. The near-universal adoption of Excel in the business world comes from its apparently transparent interface and flexibility, compared to more esoteric software systems. But that flexibility has led to some dramatic and even appalling disasters.
The $6 billion in trading losses mentioned above recalls the 2012 London Whale event, in which there was an issue with a derivatives trading department in London. One of the central problems in that department was a mistaken detail in their risk measures formula.
James Kwak, in a post titled “The Importance of Excel,” noted that a formula for calculating a Value at Risk measure was off by a factor of two because the formula divided by the SUM of two numbers as opposed to an AVERAGE of two numbers. Because of this mismeasurement of risk, various risk controls were inadvertently exceeded…which ultimately led to the $6 billion loss in derivatives trades.
Similarly, after decades of a corporate federal income tax rate of 35% in the United States, a rapid tax law passage in December 2017 changed that 35% to 21%. The NAIC (National Association of Insurance Commissioners, a gathering of state-based insurance regulators) had to update their risk-based capital calculations in order to prevent adverse effects for the insurance industry in the United States. These calculations were often built into spreadsheets, among both regulators and insurance companies, leading to a scramble to make sure things would not go too far awry.
These examples serve to illustrate the need for employing robust standards when building and maintaining spreadsheets. Spreadsheets are a reality of business, especially for communicating important financial information with the many arms within large organizations. For actuaries, a reputation for precision and accuracy is paramount, so making sure that your spreadsheets are appropriately developed will help you protect your professional reputation.
Check out my upcoming webinar on Best Practices in Actuarial Spreadsheet Design where I will cover core principles for actuarial spreadsheets and practical tips to reduce the risks associated with spreadsheets. Actuaries have a specific professional standard to live up to from which many other quantitative professionals can also benefit.