Sign In

A Member of the CFA Institute Global Network of Societies

 Credit Risk Modeling in Excel & VBA: Default Risk and Prepayment Modeling

Course Goals & Overview:

This course provides an in-depth introduction to credit risk.  Techniques for modeling credit transition matrices are covered in great detail, while several statistical techniques for modeling default probabilities and correlations are explored in depth.  Methodologies for modeling credit portfolio risk are covered, including the asset value approach and the structural approach.  Prepayment models are developed for Mortgage-Backed Securities (MBS).  All models are developed in Excel/VBA.

Learning Objectives:

  • Excel - learn several of Excel's specialized functions.  Understand how to use Excel's add-in tools to implement advanced statistical techniques, such as regression analysis.  Learn how to use Solver, Excel's optimization package.
  • Visual Basic for Applications (VBA) - learn the fundamental programming structures of the VBA language, and how it can be used to extend Excel's capabilities.
  • Statistical foundations - learn to implement Monte Carlo simulation using Excel/VBA.  Learn techniques for improving the speed of convergence, including importance sampling and low-discrepancy sequences.  Understand the binomial and Poisson distributions.  Learn the fundamental principles of linear regression analysis, as well as Poisson regression.  Understand the maximum likelihood and method of moments approaches to statistical estimation.
  • Merton's model – understand Merton's model of credit risk; learn how it is related to the Black-Scholes model and how it can be used to compute default probabilities.
  • Credit ratings transition matrices - understand the structure of a transition matrix.  Learn how to estimate a transition matrix with the cohort approach and the hazard rate approach.
  • Estimating default probabilities and correlations - understand how to use linear regression analysis to estimate default probabilities.  Learn how to apply Poisson regression to estimate default probabilities.  Understand how the asset value approach can be used to estimate default correlations using the method of moments approach and maximum likelihood approach.
  • Credit portfolio risk models - understand different approaches to modeling credit portfolio risk.  Learn how to use Monte Carlo and Quasi-Monte Carlo simulation to implement the asset value approach.  Learn how the structural approach is used to explain the sources of credit risk, and how it can be implemented as an extension of the Black-Scholes option pricing model.
  • Prepayment modeling – understand the structure of Mortgage-Backed Securities (MBS) and MBS derivatives, such as Interest-Only (IO) strips and Principal-Only (PO) strips.  Understand different measures of prepayment speed, such as Single Monthly Mortality (SMM), Conditional Prepayment Rate (CPR) and Absolute Prepayment Speed (ABS).  Learn how to implement these measures in Excel.

Course Sections:

  • Implement statistical foundations, including Monte Carlo simulation using built-in native Excel functions and tools
  • Understand the structure of a credit ratings transition matrix and estimate using the cohort approach and the hazard rate approach
  • Estimate default probabilities and correlations, using Merton's model of credit risk, linear & Poisson regression analysis, the asset value approach (method of moments and maximum likelihood approaches)
  • Simulate and model prepayment rates, incorporating the structure of MBS & related derivatives, including IO and PO strips
  • Model different measures of prepayment speed, such as Single Monthly Mortality (SMM), Conditional Prepayment Rate (CPR) and Absolute Prepayment Speed (ABS)
  • Utilize Excel's specialized functions, including advanced statistical techniques, and Excel's built-in optimization tools
  • Code in Excel VBA: learn the fundamental programming structures and how it can be used to extend Excel's capabilities in Credit Risk Modeling

 

**IMPORTANT - PLEASE NOTE**

To maximize the educational value of these programs, we strongly recommend that you have an intermediate understanding of Excel. Lack of basic Excel skills will impede your ability to effectively acquire and implement the techniques and shortcuts that are presented in this program.  Our courses are extremely interactive, hands-on with intensive focus on Excel shortcuts and efficiency.

Bring a PC laptop with Microsoft Excel installed, and a working USB port (in case our email containing in-class materials gets lost in your junk/spam folder, we can distribute them via flash drive). If you can only bring a Mac, please avoid Office 2008 and ideally set up a Windows environment via Boot Camp, Parallels, or VMware.

 Registration Fees

​CFAW Members: $899.00

Non-Members: $999.00

 Dates and Location

May 16 - 17, 2018

9:00 am - 5:00 pm

Click here​​ to register.