Mastering credit risk modeling for financial and insurance forecasting
When evaluating loan applicants, credit officers, insurance underwriters and financial professionals have the complicated task of accounting for all the factors in the applicant’s profile that could lead to a default. Instead of relying on gut instinct, Lumivero Solutions Consultant Jose Orellana demonstrated how @RISK can support better risk modeling for finance in the webinar, “Mastering Credit Risk Modeling Using @RISK." In this article, we’ll cover the highlights from the presentation and show how to gain deeper insights into credit risks.
Deterministic vs. probabilistic models
Most traditional spreadsheet-based risk models are deterministic: they rely on fixed assumptions about the business or income of the person applying for the loan. Testing assumptions about cash flow and other factors in a deterministic model would require making manual changes – a tall order when evaluating a complex organization.
@RISK, which runs as an add-on in Microsoft Excel, uses Monte Carlo simulation, a probabilistic analysis technique that accounts for uncertainty to help test assumptions more efficiently. With @RISK, users can create different types of models and analyses for nearly every risk assessment situation, including project planning and Six Sigma tolerances as well as approval decisions. The next two sections showcase two examples of financial risk models for credit officers and underwriters.
Financial risk model 1: Stationary uncertainty
The first model evaluates a business applying for a $140,000 loan. It adds one basic layer of uncertainty by running simulations to determine the probability of the business experiencing a capital cost overrun. It also assumes that price will be uncertain, but based on a basic triangular distribution. There are no additional variables like inflation or changes in demand or supply.
Running the model simulates different cash flow scenarios for the business. If the business draws down its working capital and then overruns it by 20%, the model will flag that as bankruptcy. After running the model, it’s also possible to use @RISK to quickly generate trend charts and summaries, including a prediction of the maximum solvency loan. After running this model 5,000 times, the determination was that the business stayed solvent 87% of the time, making it a good candidate for a loan.
Financial risk model 2: Cyclical uncertainty
The next model evaluates the same business but incorporates more uncertainty by assuming revenues will have some degree of seasonality – up cycles and down cycles. Using past data, an analyst can set parameters for price and volume in up cycles and down cycles and also use a discrete function in Excel to describe the duration of business cycles – again, based on historical data.
Running the simulation now includes uncertainty based on seasonality. It’s possible to then pull up a trend graph for cash flow that shows the mean value, interquartile range, and other details. @RISK’s Goal Seek feature can also make it possible to find answers to other questions, such as the maximum loan amount the applicant could borrow and still have a less than 10% probability of going bankrupt during the repayment period.
Best practices when using @RISK for financial risk modeling
To ensure you get the most from your modeling, remember these key takeaways:
- @RISK helps test assumptions that include uncertainty in your model.
- While it isn’t mandatory, it's good practice to set some distribution parameters as model settings.
- Positive correlation between price and sales volumes makes loans riskier due to greater swings in revenues.
- Make us of Repeated Correlations to apply same correlation matrix to multiple variables.
To improve your credit risk modeling skills, download the free credit risk example model and visit the Lumivero Community for tutorials and the knowledge base.
Improve your risk modeling for finance with @RISK
Interested in learning more about how you can master credit risk modeling with Lumivero solutions? Request a demo today!