5 great tips for modelling bonds

By on March 10 2011

A bond is a contract to repay borrowed money with interest at fixed intervals. Conceptually, the main difference between a bond and a loan is that bonds are highly tradeable, but in terms of building them into a project finance model, you will find that they are quite similar.

In this blog, I will highlight the differences between bonds and bank debt when modelling and key challenges to consider.

Differences between bank debt and bonds

5 tips for modelling bonds are

If the model timing is not in line with repayments, it will require a lot of extra work i.e if model is quarterly and repayment is semi-annual as is with most bonds
Remember to sum the cash flows for the semi annual periods for DSCR calculations
If calculating DSCR including a cash balance, there is a risk of double counting cash flows in non repayment periods
An ‘escrow’ account will need to be built to keep aside the cashflows that the bonds will raise at issue before they are used for construction costs
Interest will not be capitalised during construction

Advanced considerations for building bonds into a project model

Dealing with semi annual repayment profile

Ideally you want the timing resolution of the model be the same as the bond repayment profile. This is easy if the bond repayments occur quarterly, which will match the timing of most bank debt facilities. It becomes challenging when you have a semi annual bond facility in a quarterly financial model.

Based on our best-practice methodology we recommend applying counters and binary flags in this situation. For a comprehensive demonstration on how to model this refer to our tutorial on “Semi-annual debt repayment in a quarterly model”.

Adjusting CFADS to calculate debt ratios

In calculating DSCR, applicable CFADS need to be adjusted so the cash in the non repayment quarters are added to the semi annual periods. We recommend using SUM(OFFSET) function in this situation for its flexibility. and is explained in detail in our “OFFSET Function in Excel” tutorial.

Additional bond modelling resources

As you can see there are a few major differences between modelling bonds to modelling bank debt. Another useful tutorial to get you started is Financial Modelling of Convertible Notes. But before you dive right in you need to structure the drawdown of various funding sources correctly. In our popular Project Finance Modelling (A) course we will show you how to do this, followed by an interactive demonstration of how to model debt repayment using the traditional annuity / mortgage repayment method.