Financial Ratios and Modelling for Credit Analysis

This page reviews some fundamental credit analysis principles in corporate finance and project finance.  Different credit analysis ratios are contrasted such as the DSCR in project finance and the Debt to EBITDA ratio in corporate finance.  A file that summarizes the difference between corporate finance is introduced first.  Then analysis that you can use to put in benchmarks and come up with simulated credit ratings is described.

Contrast between Credit Analysis in Corporate Finance and Project Finance

In project finance it is often appropriate to assert that the source of repayment for a loan is cash flow.  For corporate finance this is generally not the case. If a company is growing, it will continue to grow debt on the balance sheet and re-finance its loans. When lenders lose confidence in the quality of a companies assets, management or ability to generate future cash flow, the lenders may refuse to make new loans.  This is when bankruptcies like for Enron, Worldcom, Lehman Brothers and GM have occurred.  For these companies it is difficult to find obvious declines in cash flow or get much information from the DSCR ratio.

In the very simple model, you can put in different levels of volatility in both the corporate finance model and the project finance model.  The volatility takes one simple equation in excel to simulate — NORMSINV(RAND()).  The project finance model is simulated with volatility cash flow which is the fundamental theory behind the DSCR.  Of course, in the real world cash flow does not follow a normal distribution.  For the corporate model, the EBITDA and the future capital expenditures are modeled using a similar equation.  Here the financial ratios at the time of re-financing cash flow can be examined to evaluate whether lenders are willing to re-finance.  The corporate finance analysis is more difficult to simulate in a similar manner to project finance even though both are driven by volatility.  The manner in which I have attempted to simulate the different approaches is illustrated in the except below.  The associated file with the simple model that you can download is included as well.

 

 

The second excerpt demonstrates the model.  For the corporate finance simulation I assume the loans must be re-financed after a given period and I compute the EV/EBITDA ratio at that period.  The DSCR does not make sense (nor LLCR or PLCR).  If the Debt to EBITDA ratio grows too much you can assume that the corporate finance cannot be re-financed.  The spreadsheet items for this are illustrated below.

 

 

Simple Credit Simulation Used to Demonstrate Short-cut Keys in Excel

Power Point Slides the Provide and Overiview of Credit Analysis and the Associated Modelling Issues

Credit Benchmark Ratios and Simulated Credit Ratings

Credit analysis has become a mixture of magic potion and BS like many other things in finance. Banks now buy a program from Moody’s that spits out a credit rating.  You end up spending a lot of time manipulating soft inputs related to management to push the rating to your desired level.  To go back to old fashioned credit analysis where you determine credit ratings through analysis of various financial ratios I have included a couple files below that you can download.

 

Power Point Slides that Work Through Various Different Financial Ratios Used in Credit Analysis and the Underlying Theory

 

Power Point Slides that Work Through Option Pricing Models and Other Mathematical Models Used in Credit Analysis

Using Option Pricing Models and the Merton Model to Evaluate PD and LGD

 

 

Merton Model Exercise for Computing the Probability of Default and the Loss Given Default

Financial Model Used to Illustrate Results of Merton Model with Monte Carlo Simulation Using Macro

Excel File with Exercise on How to Develop the Merton Model for Evaluating Credit Spreads, PD and LGD

Excel Finle with Merton Model for Computing Credit Spreads with Completed Formulas and Analysis

Implied PD from Credit Spreads

 

Excel File with Analysis of How to Compute the Implied Probability of Default Given the Credit Spread

 

 

Short-term Analysis with Risk of Value Decline in Inventories and Changes in Demand