SunTrust "Banks" on Crystal Ball for assessing the risk of commercial loans

prepared by Decisioneering Staff
(November 1998)

SunTrust Banks, Inc., is a premier financial services company based in the Southeastern United States. SunTrust provides a wide range of services to meet the financial needs of its growing customer base through approximately 700 full-service banking offices in Florida, Georgia, Tennessee and Alabama. SunTrust's primary businesses include traditional deposit and credit services as well as trust and investment services. Through various subsidiaries the Company provides credit cards, mortgage banking, credit-related insurance, data processing and information services, discount brokerage and investment banking services.

A small segment of SunTrust's commercial loan department focuses on lending to the construction industry. SunTrust has recently used Crystal Ball to perform Monte Carlo Simulations on hotel construction loans. This helps quantify the risks involved in evaluating these types of loans. Formerly, bankers would do worst case and best case analysis on construction loans when deciding on providing financing or not. Using Crystal Ball, a method has been developed to obtain a risk-adjusted return on investment. Using this finding and comparing it to the bank's cost of funds helps decision-makers determine if the profitability is worth accepting the risk of approving the loan.

There are five steps to analyzing hotel construction loans using Crystal Ball. For this method, there is assumed to be a take-out lender that will refinance the hotel construction loan provided that a required debt service coverage, DSC, is achieved. These steps involve:

  1. Generating a pro-forma income statement on a spreadsheet including calculations for debt service coverage,
  2. Assign probability distribution functions to the average daily rate obtainable by the hotel, average occupancy rate of the hotel and the projected floating interest rate on the loan,
  3. Run the simulation to determine the probability that the DSC will fall within the level required by the take-out lender,
  4. Calculate the expected return on investment, and
  5. Compare the calculated ROI with the cost of funds to determine if the spread is large enough to warrant accepting the loan.

Of the above steps, step 4 requires further explanation.

In calculating the expected return on investment the spreadsheet "IF" function was utilized. For the cell calculating the expected return, the following equation was used: E{R} = (Loan Amount) x Interest Rate if the DSC was greater than that required by the take-out lender, and E{R} = Liquidation Value - Loan Amount if the DSC failed to meet requirements. The first equation is the expected profit and the second is the expected loss. This of course makes the assumption that there would be a liquidation if the DSC was inadequate, which is not necessarily the case. However, this is the most conservative approach. The "IF" function combines the two formulas above depending on the level required by the debt service coverage upon each iteration of the simulation. This gives the analysis the advantage that when the simulation is complete, one single number for the expected ROI "pops out". This makes it easy for the decision-makers to make a determination by only focusing on one number.

Crystal Ball 2000 is an easy-to-use simulation program that helps users analyze the risks and uncertainties associated with specific Microsoft Excel spreadsheet models. Excel models are deterministic, which means that the inputs are fixed (one value to one cell). You can only see one solution at a time. If you want to view alternative results, you need to manually change the inputs in the model. Simulation is a way to quickly generate and analyze many possible results. To run a simulation within Excel, you need an add-in program like Crystal Ball.

About Decisioneering

Since 1986, Decisioneering, Inc. has been providing decision analysis software and solutions that empower individuals and organizations to make better decisions. Decisioneering's founders recognized that the newly available PC spreadsheet programs, while popular and useful, were poor tools for addressing risk and probability. To address the need for a sophisticated but easy-to-use simulation program, the company designed and marketed its first software product, Crystal Ball. At the time, when Monte Carlo techniques were less well known to the general public, Crystal Ball represented an important advance in the way spreadsheets and PCS could be used to forecast risk. For more information about the company, call 800-289-2550 or visit Decisioneering's web site,

About SunTrust

SunTrust Banks, Inc., headquartered in Atlanta, Georgia, is one of the nation's largest commercial banking organizations. As of December 31, 2002, SunTrust had total assets of $117.3 billion and total deposits of $79.7 billion.

Questions for Analysis and Discussion

  1. What DSS technologies are used?
  2. What is Monte Carlo simulation?
  3. What type of DSS was implemented?
  4. Who is the target user for the DSS?
  5. Why was an Excel add-in needed?
  6. What are the claimed benefits of the system?
  7. Do you anticipate any problems with the system? If so, explain them.

Lawrence Goldman, Web Producer, Decisioneering, Inc., gave permission to use this case study at DSSResources.COM on Wednesday, February 28, 2001. For more information check Posted March 16, 2001. Updated April 6, 2003.

Please cite as:

Decisioneering Staff, "SunTrust "Banks" on Crystal Ball for assessing the risk of commercial loans", Decisioneering, November 1998, at URL DSSResources.COM.

This case study is for informational purposes only. DSSResources.COM makes no warranties, express or implied, in this summary.