TSI Special Training plus: Cash Flow Modelling


The workshop will guide participants through the cash flow modelling approaches of securitisations, with a specific focus on Auto and Consumer ABS. Besides explaining the various elements and alternatives of typical structures, the workshop follows a step-by-step approach how to create your own cash flow model in Excel from scratch.



Objectives for participants:

  • Setting up you own cash flow models for Auto and Consumer ABS
  • Modelling CPR, delinquency and default rate vectors and analysing the impact
    on cash flows
  • Creating output sheets and visualisations including WAL calculations and payment
  • Understanding and modelling of extensions such as revolving periods, call features,
    principal deficiency ledgers and overcollateralization structures with no excess
  • Modelling rating agency and stress scenarios
  • Better understanding the sensitivities and drivers when using web-based models
    from commercial vendors

Who should attend:

    Securitisation professionals who want to learn and understand cash flow modelling in a ‘bootcamp’ environment. While in practice many cash-flow models are provided by commercial vendors, the workshop deliberately follows a transparent approach in order to give participants a bottom-up understanding of cash flow issues. Given the focus on modelling in Excel, experience in Excel is a prerequisite for the workshop although VBA skills are not required. Participants need to bring their own laptops for the modelling sessions.



    •   Programme

      Tuesday, 25. June 2019

      8.45 – 8.55

      Welcome by TSI

      8.55 – 9.40

      Introduction and overview of Cash Flow Modelling (CFM)

      • Key concepts: Asset versus liability modelling
      • Combined versus separate cash flow waterfalls
      • Typical elements of waterfalls and logic
      • Variations and customisation requirements
      • Legal definition challenges in practice

      9.40 – 10.40

      CFM in Excel – Getting started

      • Preparation of the Excel model
      • Explanation of Auto ABS Silver Arrow 9 as example structure

      10.40 – 11.00

      Coffee break

      11.00 – 12.40

      CFM in Excel – Module 1

      • Timing and frequency
      • Creating a model input sheet
        • Key ‘balance sheet’ inputs
        • Performance vectors
      • Modelling asset cash flows
        • Scheduled interest and principal
        • Prepayments and defaults/losses

      12.40 – 14.00


      14.00 – 16.00

      CFM in Excel – Module 2

      • Creating a base waterfall
        • Calculating available revenue and principal
        • Calculating the basic waterfall items
      • Creating summary statistics and graphs

      16.00 – 16.20

      Coffee break

      16.20 – 18.20

      CFM in Excel – Module 3

      • Inclusion of additional key features
        • Interest rate swap
        • Cash reserve account
        • Clean-up and optional calls
      • Modelling trigger and events
      • Post enforcement waterfall
      • Introducing separate interest and principle waterfalls


      Get together



      Wednesday, 26. June 2019

      8.45 – 10.30

      CFM in Excel – Module 4

      • Updates and Versioning
      • Extension: Auto ABS Globaldrive 2018
        • Separate waterfalls for interest and principal
        • Principal deficiency ledgers
      • Revolving periods including triggers
      • Different payment frequencies assets / liabilities

      10.30 – 10.50

      Coffee break

      10.50 – 12.35

      CFM in Excel – Module 5

      • Extension: Auto ABS VW Driver Transactions
        • OC structures with no excess spread
        • Switch sequential/pro-rata
      • Class X notes (turbo notes)
      • Checks and quality assurance

      12.35 – 14.00


      14.00 – 15.45

      Rating agency aspects and approaches for CFM

      • Asset versus cash flow stresses
      • Examples for typical stress vectors and rating agency assumptions
      • Implementation into the cash flow models




    •   Speakers


      Markus Schaber has 20+ years experience in the European structured finance markets and is currently a Managing Partner of investment advisory firm Integer Advisors LLP. Prior to co-founding Integer Advisors, he led the build-out of the European Datawarehouse GmbH (ED), Europe‘s first centralised securitisation data repository initiated by the ECB, serving as its CEO until January 2017.
      Before ED, Markus had various senior management roles in structured finance at the European Investment Fund, Deutsche Bank AG London and Fitch Ratings Ltd. Markus holds a Masters degree in Banking and Accounting (Diplom-Kaufmann) from
      Philipps-University Marburg and a Masters degree in Business Analytics and Big Data from IE University Madrid.

      Benjamin Gaitskell is a Senior Analyst and he previously worked  at Integer Adviser, an independent advisory firm specialising in European alternative and private credit markets.
      Before that, Ben worked as an analyst at Bloomberg LP specialising in equities and M&A modelling and software development. He graduated from Imperial College with an MEng in engineering and has subsequently read postgraduate economics at the University of Cambridge.