You walk into a pawnshop with a precious Patek Philippe watch you inherited from your father that is worth around $80,000. The pawnbroker looks at the watch and makes an offer of $64,000 with a 1.5% interest rate. You take up the offer and the pawnbroker takes over your watch. In the event you fail to redeem it in 6 months, the watch will be auctioned off.

What we just described is one of the oldest forms of secured moneylending where the borrower has to pledge items to the lender in exchange for a loan. The pledged item, in this case, the watch, is known as collateral.

Though the market value for your watch is $80,000, the pawnbroker has to assume the risk of fluctuation in its value, especially when it is a second-hand watch. To cushion his risk, the pawnbroker offered a loan at 80% of its current valuation. This meant that a 20% haircut is applied, setting the collateral value of your watch at $64,000.

Now imagine again that you needed only $50,000 and this worked up to a total interest of $4,500 at the end of 6 months. If you loan the full collateral value, you will have to pay a higher interest of $5,760. You decided to go for the $50,000 loan instead. The pawnbroker hence has a cash-out value of $50,000 for this transaction.

Key definitions:

• Market value – Value of asset in the market
• Haircut – percentage reduction in market value to account for the risks involved
• Collateral value – Value of asset after applying haircut to the market value
• Cash-out – Actual amount of the loan

This form of money lending, at a different scale, is still very common in the finance industry. It has evolved to include various forms of assets used as collateral. These could be stocks, bonds, real estate, metals and commodities etc. In this article, we will explore how we can utilize Atoti to monitor the collateral against the market risk.

Secured lending via collateral

Skip to Atoti Analysis Playground or Collateral Dashboard Monitoring if you are already familiar with collateral.

Collateral is a form of credit risk mitigation that is subject to a wide range of risk drivers:
 

Relevant risk management policy (e.g. market risk or liquidity risk)

The collateral value may go below the loan amount, resulting in what is known as a collateral shortfall. The lender may decide to issue a margin call for the borrower to top up the differences in order to cover possible losses.
 

Contract enforceability  

In the above example, if you were to default on the interest repayment for the watch, the pawnbroker has every right to recover his loss by auctioning the watch.
 

Collateral portfolio diversification

Having a diversified portfolio reduces the risk as different assets react differently to market events.

Common ways of managing these risks include:

• Daily valuation
• Concentration risk monitoring
• Liquidity and volatility monitoring
• Portfolio diversification
• Margin calls, coverage etc.

Setting up the analysis playground with Atoti

Skip to Collateral Dashboard Monitoring if you are already familiar with collateral.

In this article, we explore a Jupyter notebook that uses Atoti libraries to generate some charts that help us with a simple use case of collateral shortfall monitoring.

It’s common for data to come in various formats such as csv and parquet, and this is the case here. Using Atoti libraries, we are able to consume the data and create an in-memory multi-dimensional data cube with the following datastores setup

Atoti automatically created all the non-numeric data columns into hierarchies and the numeric data columns into measures for us. As a result, we have these measures:  

We are still missing a few measures such as market value, collateral value and the collateral shortfall value which we want to monitor.

To derive the market value, let’s create the measure in the cube:

Market Value = Price x Quantity

We have to apply the haircut to the market value to get the collateral value:

Collateral Value = Market Value x (1 – Haircut)

Finally, we can calculate the collateral shortfall:

Collateral Shortfall = Collateral Value – Cash out  

Atoti libraries allow us to easily create visualizations of our data in charts, pivot-table, tabular or even a featured values widget.

After we are satisfied with slicing and dicing the data cube, what’s next?  

Jupyter notebooks are easy to use and the visualization Atoti provided is great. However, it is not user-friendly enough for us to show to our end users, who are not interested in seeing all the Python codes.

Collateral Monitoring Dashboards

Many libraries, such as dash or altair, allow interactive visualizations on Jupyter Notebooks just like Atoti. However, Atoti takes one step further to allow the publishing of these widgets into a bundled UI — the Atoti UI. 

From the gif above, we see how easy it is to put a dashboard together. We can now save and share the dashboard with our end users with zero coding from their end!
Let’s dive into the dashboard in the Atoti UI. From the series of built-in widgets, we add a quick page filter on the Account level so we can switch from a global to an account level view on the collateral status on the dashboard.

Atoti’s in-memory data cube plays an important role in the near-zero latency we see during the switch in the account. It enables the real-time data aggregation of the measures, allowing us to drill down to any hierarchies or levels.

In a total cash-out situation, we can see that the bank is still well within the threshold of the total collateral value. The full range of the gauge indicates the total market value of the assets.

Zooming in on the dashboard for two accounts, we can immediately see the differences in their risk levels:

Both Musk and Buffet are having collateral shortfalls of 208,647.52 and 326,666.90 respectively. However, given that Musk only has assets under the Utilities sector in just France alone, the risk of defaulting is very high if the sector crashes. In the case of Buffet, having a well-balanced portfolio helps cushion fluctuations coming in from any single sector at one point in time: the chance of all the sectors crashing at the same time is very low. Therefore, Musk requires closer monitoring compared to Buffet.

What-ifs

There are many events that affect financial markets. Most apparent now is the financial impact caused by COVID-19.

Given the above Asset Concentration map we have for this bank, most of the assets are in the Technology, France and Consumer Cyclical, Italy categories. It makes sense for us to run some simulations on these sectors to see how the bank will be impacted if there is:

• Consumption slowdown in the Consumer Cyclical sector in Italy and France
• Technology slump
• More cash-out

Scenario 1: Higher haircut simulation

Let’s focus on the Consumer Cyclical sector for now, since it makes up the biggest portion of the assets. We can see that the current haircut for this sector is at 10% for both France and Italy:

In a consumption slowdown, lenders will require a greater haircut in order to minimize their risks. In this case, let’s increase the haircut for the sector to 20% in Italy and 15% for France.

We baselined the original data in a Jupyter notebook, and set up the parameters of the simulations simply by running the below:

haircut_simulation = asset_positions_cube.setup_simulation(
    "Haircut Simulation", 
    per=[lvl["Sector"], lvl["Country"]], 
    replace=[m["Haircut.VALUE"]], 
    base_scenario_name = "Current Market Condition"
)

We can now proceed to create scenarios within the premise above:

# create a new scenario with a meaningful name
consumption_slowdown = haircut_simulation.scenarios['Consumption Slowdown']
# parameters in the sequence of levels defined in the "per" during setup_simulation 
consumption_slowdown += (
    "Consumer Cyclical", # Sector
    "Italy",        # Country    
    0.20,         # Haircut.VALUE 
    tt.simulation.Priority.CRITICAL
) 
consumption_slowdown += (
    "Consumer Cyclical", 
    "France", 
    0.15, 
    tt.simulation.Priority.CRITICAL
)

Voila! Let’s visualize the scenario in a chart.

Comparing the Collateral Shortfall between the current market condition (baselined data) and in the consumption slowdown scenario, we can see that Bezos & MacKenzie, Buffet and Gates & Melinda are greatly impacted.

A quick drill-down shows that all 3 accounts have collateral in the Consumer Cyclical sector. However, Bezos & MacKenzie is impacted by the consumption slowdown in both countries, hence incurring the greatest loss.

Scenario 2: Price drop in Technology Sector

The bank’s collateral is also heavily weighted in the Technology sector. We will create a scenario where the prices in the sector dip to 70% of their current value globally.

Note that the premise of this simulation has changed from haircut.VALUE to Price.VALUE. Therefore, we have to set up a new simulation.

market_value_simulation = asset_positions_cube.setup_simulation(
    "Market Value Simulation", 
    per=[lvl["Sector"],lvl["Country"]], 
    multiply=[m["Price.VALUE"]], 
    base_scenario_name = "Current Market Condition"
)

Setting up the scenario is simply:

tech_bubble = market_value_simulation.scenarios["Tech Bubble ?"]
# we are going to simulate a slump technology in all countries, 
# therefore we pass the wildcard value `*` for country level
tech_bubble += ("Technology", "*", 0.70, tt.simulation.Priority.CRITICAL)

Let’s assess the impact of the drop in price globally for Technology.

We see technology giant such as Gates & Melinda suffering greatly as their shortfall goes beyond 1.5 million. Likewise, Buffet with 1/6 of assets in the Technology sector also suffers.

The market values and collateral values in the Technology sector were pulled down by the change in pricing. As we drill-in further to the simulation for Gates vs Buffet, we see that Buffet has a greater shortfall as the collateral under other sectors also contributed heavily to it.

Scenario 3: Increase in cash-out

Finally, let’s run a predictive simulation of 10% increase in the cash-out value across the bank.

Since the collateral value remains unchanged, we see that the shortfall situation is worsened by the additional 10% increase in cash-out.

All in all

This is a simple showcase of what we can do for Collateral Shortfall Monitoring using Atoti.

We created the data cube, measures and visualization easily. The simulations required just a few lines of code. Additionally, users are able to modify the simulations with the widgets from the Atoti UI. This way, analysts can run their scenarios without getting the model builders involved.

Download the Collateral Shortfall Monitoring notebook and have fun playing with Atoti for free!