This Post Looks at Modelling High Yield Cash Flows with Excel with a Free Demo Workbook
Now. This is not the sexiest of topics. BUT it is one that is worthwhile sharing IMHO. The aim of European High Yield Online to become a community site is based around sharing ideas. The spreadsheet used in the post is free to download. The file allows users to create a cash flow schedule for any number of securities. The cash flows can then be integrated with user models easily.
Video: Best Viewed in Full Screen
Before we dive in to the spreadsheet it is worth highlighting a number of resources for Excel that are indispensable. Being proficient at Excel is a great, if geeky, skill. The productivity gains with a little bit of knowledge are great. If you can convince your boss to get you some training then so much the better!
My top resources for Excel:
- https://chandoo.org/ this is probably my go to resource. “Become Awesome in Excel” Chandoo makes Excel enjoyable – if you want to create dashboards this is where to go.
- https://www.mrexcel.com/ Bill Jelen has some of the best and most accessible blogs on Excel out there
- https://peltiertech.com/ Jon Peltier’s site is a bit more technical and focus’ on charting – for those looking to up their geekery
- http://www.cpearson.com/Excel/MainPage.aspx Chip Pearson’s blog is relatively technical, has a ton of downloads and is good for complex tasks
- http://www.rondebruin.nl/ Ron De Bruin’s Blog is all about automation and VBA (code), has great downloads and his books are good
Why Modelling Cash Flows?
- Easily/Automatically setting up model periods can be time consuming
- Dealing with Amortization and Payment in Kind can be tricky
- Having flexibility to add, remove instruments and change PIK and Amortization schedules
- Dealing with cash flow/ expense implications of PIK interest
- Matching instrument cash flows and balances to model periods
- Being able to see individual security cash flows by seniority and period
- Easily incorporating forecast cash flow periods to an existing model structure
Alot of us will manually type in interest amounts, changes in notional etc. This project touches on 2 things – periodicity – automatically generating a model with the right periods – quarterly, semi annual, annual with automatic start and end dates. The second is dealing with changing nationals – that is Payment In Kind Instruments and those that amortize.
Two of the best pieces of advice I have come across when creating a model/ dashboard are 1. “KISS” Keep It Simple Stupid and 2. Always think about the questions you are trying to answer.
Great I hear you say. But how does this translate to a Spreadsheet that models cash flows?
First there is the choice of functionality used. We use excel’s Tables extensively in the spreadsheet. If you haven’t come across them then I strongly recommend you make them your new best friend. They make data look-ups and summarizing easy. Not only that referring to them in formula is easy and comes with free autocomplete. If you are curious check out this article. We also make use of Excel’s new “Xlookup” function, along with “SumIFs” and Pivot tables.
There is a bit of VBA – for those who hear “macro” and die a little inside we aren’t going to get too involved. What we will say is that we use Scripting Dictionaries which will require you to add a reference in your VBA window. Dictionaries are used in other programming languages and essentially allow you to store key and value pairs. You give it a key and it will return the item associated with that key. We also created a securities class as a neat way of handling all of the different characteristics of a security – maturity date, coupon rate, amortization schedule etc.
Spread Sheet Overview
The sheet consists of 7 Sheets:
- Set Up – this is where you set the model up
- Summary – this pulls through the interest and cash flows in the reporting currency. It shows them by period and seniority.
- Pivots – these are simple Pivot tables linked to the Cash flows table. They give examples how to summarize cash flows and capital structure.
- .Securities – this is where you set up/ input the securities you want to model
- Cash flows – this is Table is where the cash flows are displayed once created. It should be a good place to explore table functionality.
- Security Balances – this displays/ stores the security end balances in the reporting currency for each model period.
- lookup – includes all of the tables that make the spreadsheet work.
The Summary sheet is where are able to link cash / expenses to your model. Where to focus: have a look at rows 2:7 and the use of the Xlookup formula – It returns 5 items at once. Also look at the sumifs in the cash pay interest section as this will give you an idea of how to use Tables and how you can incorporate something like this in your own models.
The Pivots sheet again makes use of Tables as a data source. If you select PivotTable Analyze in the Excel Menu bar and change data source you can see how Tables are referenced. The bonus with Tables is that they automatically expand when you add columns or rows. This means Pivot tables will always pick up everything and allows you to create dynamic reports easily. The Capital Structure segment is a simple demo of how you can summarize a Capital structure that changes with your model periods. This allows you to see how EV changes over time. You can extend this to plot forward EV under your model assumptions. You need to provide Cash, Equity and EBITDA from your model.
The interest and principal payment summaries can be extended to create maturity wall and interest payment charts.
The Securities sheet is where you enter your securities. Manual input is in white and drop down selection is Yellow. All of the drop down lists reference a Table on the lookup sheet. Drop-down lists are one of the areas where you can’t make use of Table functionality without a bit of a work around when using them as a source range if you want them to update automatically. The Sheet itself is pretty self explanatory you populate the data and then hit update cash flows and a macro builds the cash flows table and the security balances.
You have the option for floating rate instruments – for this spreadsheet it just uses floating rates that are stored in “tbl_FX”. “tbl_FX” also stores the relevant FX rates vs. the Reporting currency. These are hard coded in the download so you will have to add your own feed or manually override the values in “tbl_FX” after you have downloaded the file. Additionally – you could expand the spreadsheet to use forward rate curves and FX but that is beyond the scope of this demo.
Finally, you have 2 segments that deal with changes in notional. One deals with payment in Kind – the user can specify the period over which an instrument PIKs. When PIK’ ing , PIK interest will go through the interest expense but not through cash interest. The second section deals with prepayment – this can be set to prepay evenly over the life of the instrument, by a % of period starting notional or an arbitrary amount. Reductions in principal flow through as cash items.
Cash Flows Sheet
This is where all of the cash flows generated by the instruments are stored once created. As a Table the data is easy to filter/search/summarize. The table allows you to look at individual cash flows and also maps security cash flows to the relevant model period. The data is reported in instrument currency and Fx’d to the reporting currency. Each cash flow has an accrual factor rate etc.
Security Balance Sheet
This is where we map the Ending balance of our securities to the model reporting date. In a simple model we would just do this manually. Here we interpolate balances when the instrument PIKs or Amortizes. It is this sheet that allows you look at EV over time, get a sense of how leverage will evolve when part of a full model.
This is the “foundation” of the workbook and houses all of the data used in the sheet. Don’t forget to update “tbl_FX” after download. The main table here is tbl_Periods. This is a good table to have in any model and makes automating time series/model period set up super easy. I recommend having a look at how it works and you can adapt it to your model. Look at the summary sheet rows 2:7 to see how this can be pulled through into your model. We only cover forecast periods in this table, you can adapt it so that it includes historic periods by changing the start date. If you do this you can add TRUE/FALSE column which identifies if a period is forecast or historic.
tblPeriods – you can easily implement something like this in your own models and automate model periodicity
Download The File:
Hopefully this post has given you something to think about. Definitely take a look at the resource websites highlighted above. Explore the Excel file and get in touch with thoughts, questions at email@example.com. If you are feeling brave you can also look at the VBA included in the workbook. The spreadsheet is provided as is and is written in the latest edition of excel. We haven’t tested it for compatibility issues so make sure you when you first use it you have nothing else open. The workbook has one set of eyes look at it so if you do come across errors / bugs let me know – hopefully it wont be the case.
How Can You Help European High Yield Online?
- Spread the word – share this article on social media
- Register your interest and Subscribe to the Blog
- Get in touch – email me at firstname.lastname@example.org – views and more importantly help are wanted