šŸ¤–Webinar - From Theory to Practice: Real-World Applications of AI in Asset-Based Finance - Register here

Beyond Excel: Risks of using spreadsheets to manage your structured finance investments

What are the risks of using spreadsheets? Is there a better way to manage your structured finance investments? Is it worth it to switch from traditional but consolidated processes involving spreadsheets to more advanced technologies?

After reading this article, you will understand the main risks related to the use of spreadsheets when managing structured finance investments and explore the real need for modern technologies in the market.

The growth of structured finance

Structured finance has experienced significant growth since its inception in the 1980s. The market has evolved and expanded over time, encompassing various financial instruments such as asset-backed securities (ABS), mortgage-backed securities (MBS),Ā collateralized debt obligationsĀ (CDOs), andĀ collateralized loanĀ obligations (CLOs). As of 2023, theĀ structured finance marketĀ continues to demonstrate resilience and growth, despite periodic setbacks and market adjustments. The future outlook for the market remains positive, with continued growth anticipated due to increased demand for alternative investments,Ā technological advancements,Ā ESGĀ considerations, and the evolving regulatory environment.

But operating in the structured finance industry has different challenges. Operational structures are complex and very frequently non-standard. By definition, you win when you are continuously adapting to dynamic market shifts with innovative financial and deal structures that are very difficult to standardize. In addition to that, with increased competition for deals, the need for speed and intelligence becomes paramount.

Risks of using spreadsheets: billions of dollars managed with outdated tools

Every day, agents in the structured finance market are managing billions of credit investments using spreadsheets, manual workarounds, pdfs, and word documents. 

Spreadsheets have become the go-to tool when managing these complex structures. It is widely used to model data and handle several important processes, from evaluations, pricing, cash-flows reconciliations, portfolio monitoring to capital allocations, and reporting. At the end of the day, spreadsheets are immediate, flexible, simple, and easy to use.While it may seem like an easy and painless solution, there are actually a lot of hidden risks and costs. Letā€™s take a closer look at the top six risks of using spreadsheets.

The initial purpose of the electronic spreadsheet was to replace paper-based systems in the business world. Originally developed for accounting or bookkeeping tasks, spreadsheets provided users with a simple way of calculating values.

Since their invention, spreadsheets have evolved into more complex products with many features and enhancements, which are now used for a vast array of tasks by millions of companies around the world.

However, spreadsheets were not developed for the investment management industry and even less for the level of security needed in handling data.

1. Prone to errors and mistakes

88% of spreadsheets contain errors. A small mistake can have a snowball effect and a very big impact on business. At JP Morgan, one single error resulted in a $6 billion loss when someone copied and pasted from one spreadsheet to another.

When Lehman Brothers collapsed in September 2008, few were aware of one related incident when Barclays Capital almost bought Lehman Brothersā€™ 179 trading contracts by accident. Lehman Brothers filed for bankruptcy on September 15, 2008. Three days later, Barclays Capital offered to acquire a portion of the US bankā€™s assets, including some of Lehmanā€™s trading positions. As part of the deal, Cleary Gottlieb Steen & Hamilton, the law firm representing Barclays, had to submit the purchase offer to the U.S. Bankruptcy Court for the Southern District of New Yorkā€™s website by midnight on Sept. 18.

Barclays sent an Excel file containing assets they intended to acquire to Cleary Gottlieb at 7:50 pm on September the 18th, only a few hours before the deadline. The spreadsheet had 1,000 rows and 24,000 cells, including those listing the 179 trading contracts that Barclays did not want to buy. They were, however, hidden instead of being deleted. 

Cleary Gottlieb was tasked with reformatting the Excel file to a pdf document so it could be uploaded to the courtā€™s website. They didnā€™t pay attention to the hidden rows, which were visible again in the pdf file. The mistake was only spotted on October 1st after the deal had been approved. Cleary Gottlieb then had to file a legal motion to exclude those contracts from the deal.

Another case involves the outsourcing specialists Mouchel that had to endure a Ā£4.3 million profits write down due to a spreadsheet error in a pension fund deficit caused by an outside firm of actuaries. Not only did Mouchelā€™s profits take a huge hit, but it also caused their share price to drop and their chairman to resign amid fears they would break their banking agreements.

Axa Rosenberg, the global equity investment manager, was fined Ā£150 million for covering up a spreadsheet error back in 2011.

Documents detailing the collapse of Enron in 2001, released after the conclusion of all legal proceedings, showed that 24% of the corporationā€™s spreadsheet formulas contained errors.

Fidelity’s $2.6bn ā€œminus signā€ error. Fidelityā€™s ā€˜Magellanā€™ fund estimated that they would make a $4.32 per share distribution at the end of 1994. This incorrect forecast happened because an in-house tax accountant missed out on the minus sign on a net capital loss of $1.3 billion. This made the net capital loss a net capital gain. This caused the dividend estimate to be off by $2.6 billion.

More often than not just one person in a company has the knowledge of how the financial spreadsheet models are constructed. Other people are unable to understand and therefore check the analysis. The potential for errors is massive.

2. Vulnerable to security threats

Security risks make spreadsheets inefficient for storing clientsā€™ investment data. Critical information cannot be encrypted with spreadsheets – which exposes sensitive data (financial information, social security numbers, etc.) to security breaches. 

As Microsoft itself underlines on the Excel support page ā€œWorksheet level protection is not intended as a security feature. It simply prevents users from modifying locked cells within the worksheet.ā€

Excel is not immune to cybersecurity attacks. In 2019, it was discovered that hackers could attack Excel files through Power Query

This is also confirmed by Cisco, which stated that Microsoft Office formats, including Excel, make up the most prevalent group of malicious file extensions in emails, as attackers can use VBA (Visual Basic for Applications) scripts to create macro malware. 

3. No integration capability

Only those with the right form of data can successfully navigate the market, make future predictions, and adjust their business to fit market trends.

There is no doubt about the acceleration of the digital transformation of our economies and our daily jobs. A lot of data is made available to investors, issuers and servicers in the structured finance market. But most of the data we handle today is unstructured, which means it comes in different forms, sizes, and even shapes. And spreadsheets cannot manage this type of data. 

Excel was mainly built for independent analysis and single files. Mixing data sources that come from different systems is almost impossible to do in excel. Importing, exporting, and updating data from other platforms or databases can become an extremely tedious and time-consuming task. 

4. No real-time update

With spreadsheets, teams are often operating and making decisions on outdated or simply inconsistent information, as infinite versions of the same file are created and saved on each local computer day after day. Furthermore, there is no way of tracking changes to the files – when mistakes occur they can be difficult to identify and correct in time.

5. No permission controls

When it comes to structured finance investment data, not all members of the organization need to access the same information. Specific roles and people within the organization need to be able to access a particular spreadsheet, but not others. Spreadsheets donā€™t come with tools for granting permissions on a single user level. 

Another drawback of Excel is that you have no visibility of who accesses your data and when. A topic that becomes very relevant considering recent GDPR regulations regarding data privacy.

6. Cannot handle large volumes of data

Spreadsheets are not a natural fit for handling large amounts of data. With just ten thousand rows, the program starts to perform poorly and slows down calculations each time a new formula or macro is added.  In other words, the more information you enter into spreadsheets, the more complicated it becomes to manage it all.

Data has always been an essential asset to the growth of any organization. There are 2.5 quintillion bytes of data created every day. Once analyzed, this data can help the structured finance industry in a multitude of ways. As in healthcare, data helps avoid preventable diseases by detecting them in their early stages. It could be immensely useful in the structured finance sector, to predict different patterns of behavior and increase performance or decrease losses. It can also aid in recognizing illegal activities such as money laundering or fraud cases.

Does any of these issues sound familiar?

If you have ever had to work with spreadsheets to manage your structured finance investments, you probably have come across these problems before.

But is there anything better in 2023? What if you could apply the advances in technology and data science to this market? From cloud computing to big data, from machine learning to artificial intelligence, there are many ways technology can make your life easier and better. 

The need for advanced technology in the structured finance market

As an operator in the structured finance market, you likely have two main objectives: making the right investment decisions and scaling your organization in the most efficient way possible.

However, the tools and technology you are using can have a significant impact on these goals. How so? Keep reading to find out:

Making the right investment decisions

When it comes to structured finance, speed is a decisive factor. With the competition going up and fewer deals available in the market, you need to act fast and with confidence.

When using traditional tools like spreadsheets, you spend too much time concentrating on how to get and analyze the data, rather than focusing on what the data is telling you. By the time you get the right information, it might already be too late. How can you make timely decisions when you have data that is several days old?

Scaling the organization

As volume increases, so does operationsā€™ complexity, making it very difficult to scale. While hiring more people is not a sustainable and efficient solution, technology can come to the rescue and support your operations teams with more automated tasks. 

As a result, teams will be able to focus on the operations strategy, rather than crunching and reconciling data in excel files. 

Specific tools for complex activities

When managing structured finance investments, there are several critical processes that you just cannot handle efficiently with spreadsheets. Letā€™s dive into two of the most common activities structured finance market actors encounter in their day-to-day: credit risk assessment and reporting.

Credit Risk Assessment for investors

When it comes to credit risk assessment for innovative credit products (such as buy now pay later, revenue and inventory financing, salary financing) product-based risk modeling is needed.

Rating classes, risk scores, or probability of default estimations on a yearly basis are not enough. Credit Risk estimates have to follow the structure of the product itself, with the same speed of innovation. 

What market players need are specialized models that target the product risk, such as delay prediction models, propensity to pay back models and revenue limit estimations, etc. This is what enables them to make quick and timely decisions, moving with the speed of the market.

Producing reports for external stakeholders

Structured finance reporting needs have become quite complex, especially considering disruptive events like the Covid-19 and the Russia-Ukraine conflict. Asset owners are increasingly demanding more frequent reporting deliveries and more custom-made structures.

In addition, as the asset class grows so does the scrutiny of regulators, which are increasing the reporting requirements with detailed look-through demands. 

To meet these requirements you need to have a flexible approach to data and reporting deliveries. We will look at two examples of common reporting activities: periodic reports from contractual provisions and regulatory reporting.

Contractual provisions require periodic reports that need to be produced monthly or quarterly. In some cases, reports are a prerequisite to making payments or acquiring new assets. Therefore, speed is critical.

In addition, custom requests arrive from time to time, driven by specific external events (e.g. what is the exposure to industries more exposed to the drawbacks of the pandemic? Or towards the Russia-Ukraine conflict?) or even for internal purposes such as investment committees, audit exercises, etc. 

It can become difficult to accommodate such needs quickly by using spreadsheets as they require custom adjustments each time. 

Regulatory reporting (like ESMA transparency reports) is another activity that must be carried out periodically and requires specific processes, standards, formats, and a dedicated tool to produce the report timely and with quality. In some cases, this activity is outsourced externally to third parties that still handle most tasks manually, continuing to have potential errors and data breaching risks.  

Beyond spreadsheets: a better way to manage structured finance investments

In this article, we have analyzed the risks of using spreadsheets and the reasons that make them an unsuitable tool for the structured finance industry. 

The good news? There is now a better way to manage your structured finance investments.

At Cardo AI, we have been working since 2018 to provide investors, issuers, and fintech lenders with the speed and accuracy they need to succeed in the market. 

With Cardo AIā€™s proprietary software, intelligence and infrastructure, our clients and partners are now able to focus on what really matters, on the real work that has to be done: that is taking good investment decisions.

If you are ready to harness the power of technology and abandon your outdated spreadsheets, discover our products today!