Restatement of unrealised gains due to financial modelling errors.
Error Type: Incorrect calculation logic.
Reported on: October 2003
Fannie Mae (the Federal National Mortgage Association) were forced to restate their unrealised gains by $1.2 billion after discovering “honest mistakes" in one of their spreadsheet-based financial models.
Fannie Mae's share price fell from $73.10 to $2.25 after the spreadsheet error was announced.
What caused the error?
The errors were caused by a financial modeller using the incorrect logic in a formula in the Excel spreadsheet being used to calculate the changes in the value of commitments the company had made to purchase mortgages or mortgage-backed securities.
The logic error related to "complicated calculations required in the implementation of FAS 149".
These errors were not picked up during the review and test phase of the build of the financial model, or before Fannie Mae issued their earning statement.
The Fannie Mae team did however unearth the mistakes during their filing process. This lead to the restatement and public announcement of the error.
How could the error have been avoided?
The error was one of accounting logic rather than a simple formula or input error.
Formula logic errors can be the most difficult to find during testing. The best way to manage formula logic risk is to detail out the logic for all critical formulae within the financial model's guide. This logic can then be more easily validated during the test phase.
This is a great example of where Excel's LAMBDA custom functions could be applied.
Creating a LAMBDA for the calculation in question would have enabled the calculation to be created and tested in isolation and then consistently applied in the financial model.
Read the article published by the New York Times here.
Full Stack's Financial Modelling Errors Series
See our complete financial modelling error series here.