Circular Interest – Interest on average balances

When authoring formulas in Excel, a user may often encounter a circular reference. In this article we demonstrate how to solve a common problem related to interest using high school mathematics rather than Visual Basic.

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 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 so here we demonstrate how we have solved this problem algebraically.

Application

A classic example is that of 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. It is fair to say that this simple situation has stumped the Project Finance industry for the past several decades.

It 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
• It masks additional circular references if introduced
• Reputable model review firms will qualify their formal opinion
• It breaches the in-house modelling risk policies in reputable institutions

The problem can be solved 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.

Drawbacks of Circular Models

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 Project Finance there is a simple way and a hard way. Reflecting on what one is really trying to solve usually leads to a more elegant analytical rather than iterative approach.

The Mathematics

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

The above formula can be rearranged by simultaneous substitution. Let,

• CB = Closing Balance
• OB = Opening Balance
• I = Interest Earned
• r = Deposit Interest Rate

Then we have:

Thus, the algebraic solution for interest is:

The Modelling

To demonstrate this method we have put 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 ‘copy-and-paste’ routine and then algebraically. You will see they yield the same result.

Copy-and-paste approach:
Interest is firstly calculated based on the formula:

E.g. in period ending 31-Dec-08:

To avoid circular reference, the calculated interest needs to be copied and pasted using a macro. (The “copy-and-paste” macro button is built in the accompanied workbook.)

The above is performed until the difference between the “calculated interest” and the “value copied interest” (or “Delta”) is below a level of tolerance in all periods.

The algebraic solution for interest is:

E.g. in period ending 31-Dec-08:

 Screen shot: Solving interest calculated on the average balance 

The Benefits

As shown in the workbook example, the interest could be solved using the mathematics without the VBA macro. The algebraic solution has some benefits over the traditional approach:

• Using an algebraic solution is transparent and easier to follow
• Errors in the formula can be traced to the source rather than to values
• Not everyone can author or read VBA script– an algebraic formula can be created and tested by anyone familiar with Excel.

COMMENTS