Sign In
Manage PermissionsManage Permissions
Export Event


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

Event Description

Course Goals & Overview:

This 2-day 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

**Please Note: Participants will need to bring a laptop with them to the course.

Event Type


Education Topic


Start Time

5/16/2018 9:00 AM

End Time

5/17/2018 5:00 PM





Event Country


Event Region


Location Info




CE Credits


SER Credit




Member Price


Non-Member Price


Candidate Price



​Click here to register.

If you are having trouble with the online registration, please register here with a manual form: ​CRM Modeling in Excel and VBA Payment Form 05 16 18.pdf

All Day Event











5/16/2018 9:00 AM


Content Type: MyCFA Calendar
Created at 11/2/2017 11:59 AM by[CASMSTS:username]
Last modified at 11/15/2017 8:40 AM by[CASMSTS:username]