Circular Interest on Average Balances

Solve common problems with calculating interest using basic algebra instead of VBA.

You may have encountered a circular reference when writing formulas in Excel. In this tutorial, we demonstrate how to solve a common problem related to interest using high school mathematics rather than visual basic application (VBA). You can download the accompanying Excel workbook which illustrates this tutorial by clicking on the download Excel workbook button.

A circular reference is created when the formula is directly or indirectly dependent on itself. Circular logic is when C=A+B but in turn A or B is a function of C.

Although it is possible to solve, using an iterative approach, this breaks a fundamental rule in efficient financial modelling. The widely used solution in the market is strewn with shortcomings. That’s why we decided to demonstrate how we have solved this problem using a simple algebra formula.

Why are circular references a bad idea?

In this tutorial, we will look at interest on a deposit or a loan account, where the interest is calculated on the average balance and the interest accumulates within the account, e.g., during construction period. This is a simple situation, but it has stumped the finance industry for the past several decades.

The interest can readily be calculated in a spreadsheet by accommodating a circular reference however this action has several consequences:

• It breaks a firm rule of best practice modelling – ‘no circular references’
• It masks additional circular references if introduced subsequently
• Reputable model review firms will qualify their formal opinion
• It breaches the in-house modelling risk policies in most reputable institutions

Alternative approach to solving circular interest

The problem can be solved, albeit crudely, by isolating the circular reference using a copy-and-paste macro. This involves copying the calculated interest and pasting it into the account, where upon the interest calculation is updated and the loop happens once more.

This is performed until the difference between the ‘calculated interest’ and the ‘value copied interest’ is below a level of tolerance in all periods. As soon as the model parameters change, this macro will need to be run again. This is the general practice in the market; however, it has the following additional drawbacks:

• It cannot be used readily with ‘data tables’ – a powerful scenario tool
• It relies on the user to know when and how to execute the macro
• It relies on ‘solid’ VBA coding otherwise the cell references may move

Like many calculations found in finance there is a simple way and a hard way. Reflecting on what you are really trying to solve usually leads to a more elegant analytical rather than iterative approach.

Using algebra to solve circular interest

A circular reference is formed when interest is a function of the average balance of the loan. Consider this:

• CB = Closing balance
• OB = Opening balance
• I = Interest earned
• r = Deposit interest rate

and we have:

The above formulae can be rearranged by simultaneous substitution, so the algebraic solution for interest is:

Financial model workbook available for download

To demonstrate this method we have included a simple workbook example where the interest on the debt account is calculated on the average balance and the interest accumulates within the debt account.

To illustrate the problem above, we solve the interest in this example both with a traditional iterative routine and then algebraically. You will see in the attached workbook that they yield the same result. Withdrawals from and deposits to the account can also be incorporated using a similar approach. Download the Excel workbook to see more.

Corality Academy: Corality Financial Modelling Campus

There are numerous other tutorials and free resources related to financial modelling in the Corality Financial Modelling Campus.

Some of the more popular courses that relate to this topic include:

• Best Practice Project Finance Modelling
• Financial Modelling for Valuation Analysis