Intro to Financial Modelling - Part 17: Calculation Techniques - Loan Calculations

Broadly speaking a financial model can be separated into two quite distinct parts: firstly the operations of the business, which generates the cash and profits that are utilised by the second part: the finance and equity of the business.

Loan calculations are perhaps the most finicky of all the calculations in a model. Whereas the operational forecast is often built up through intellectual analysis of the machinery of business and judgements about micro- and macro-economics now and in the future, the loan calculations are no more than the application of detailed contractual terms (or an estimate of those terms for loans that are hypothetical at the forecast date). As a result, loan calculations can have many detailed assumptions, both explicitly expressed in model inputs or implicitly implied in the logic of the calculations, which makes loan calculations difficult to make transparent.

Use the right building blocks

Fortunately, we have already covered many of the building blocks that we need for loan calculations.

Structure and consistency

As is hopefully abundantly clear if you have been reading all of this series, all calculations are built upon consistent structure, and loan calculations are no exception. The core structural rules all apply: like using the same columns for the same dates across all sheets, and making formulas consistent across the row.

Formulas

As for all financial model calculations, we follow the rule of thumb and decompose down calculations into small parts, the most important of which are timing flags. For example, we might break an interest calculation into separate rows for base rates, margin, time apportionment before leading to the final interest calculation row.

Flags

We use flags to drive the timing, separating out the timing elements of calculations and re-using the results multiple times. Key terms such as the availability period, the grace period and repayment period need to implemented in a single set of flags. It is important to avoid falling into the trap of overloading loan flags. The truth is that while every loan in a business nominally has different terms and payment dates, the reality in almost all real world cases is that the payment timing simply follows the most senior loan so usually only a single set of flags is required to cover the whole of finance.

Checks

All the usual checks apply. Does my balance sheet balance, for instance? But in addition to the logical checks of correctness, loans are a rich source of commercial checks that are very informative for the user. Does my loan repay? Does cash go negative? Do loans meet the covenants?

Corkscrew accounts

The key construction for a loan account is the “corkscrew account”. This accounting invention is a standardised way to present a balance over time. It is constructed as a series of rows: the top row is the opening balance; the middle rows movements; and the last row the closing balance. The opening balance is simply the closing balance from the previous period – and this is what gives it that “corkscrew” shape. For those of you who are accountants this will be quite recognisable.

Know your implicit assumptions

Let us for a moment consider the sort of implicit assumptions that are typically found in loan calculations:

• Timing of drawdowns – beginning or end of period?
• Interest calculation days convention (e.g. 30/360, or Actual/365)
• Repayment method (e.g. annuity, sculpted)
• Are repayment profiles based on percentage or absolute repayments?
• How future repayments change due to current over- or under-repayments
• The order that cash is utilised, expressed in a cash waterfall or cascade.

Of all sections of a model, loan calculations are most stuffed with implicit assumptions. The problem with implicit assumptions is they are not obvious to the end user; they are not transparent. Only through a very detailed examination of the calculations can someone work out these implicit assumptions.

Without explicit inputs telling the user what is going on, the only sensible way to explain these assumptions is to annotate the model – and that is exactly what a modeller should do. This could be in a guide, a note in the calculations or maybe a footnote system.

Be prepared for the clever stuff!

Loan calculations often contain some of the most advanced logic of any part of a model. 

For a start, financial calculations can be inherently circular. The facility size affects the commitment fee. The commitment fee affects the drawdown requirements. The drawdown requirements affects the facility size, and around we go! The spectre of copy/paste macros rears its ugly head, out of simple necessity.

Throw in some more advanced calculations like debt sculpting, and a modeller needs to understand VBA and macros to truly get to grips with most loan calculations. 

Then there is the optimisation. How to find that financial structure that gets the best returns for investors? Without going into details here, I would say that these sort of calculations are more of an art than a science. What does optimal look like? What are the constraining factors? How can I get the model to converge to a stable answer? These are all typical questions that face modellers when modelling loans. It is not for the uninitiated or faint hearted.

How do I get to there?

Our series of blogs only can guide and influence how you build models. Once you begin looking at the details of specific logic can you understand the practical complexity of building models, and loan calculations are often some of the most difficult logic you will come across.

So how – you may ask – do I get to the position of modelling them? Well, in addition to articles like this, I suggest gleaning what you can from online resources –YouTube videos, webinars, tutorials (many provided free from significant modelling firms). Alternatively you can attend an intensive training course with one of many training companies. (Of course, the carefully considered ICAEW Financial Modelling Code always takes precedence if there is any disagreement in other resources)

But here is a secret: most modellers actually gain this expertise through sheer practical experience, working at their models until 2am before a deal, and thinking, testing and innovating until they get it right, and then – and this is key – writing down their lessons for the future. Do not underestimate the power of just having a go – even for something as complicated as loans.