Solution to Circular Interest - Calculate 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.

Why do you get Circular References?

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.

Why Circular reference models are unacceptable in project finance

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 of circular references

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

Interest = ½ * (Opening Balance + Closing Balance) * Interest Rate

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:

1. CB = OB + I

2. I = ½ * (OB + OB + I ) * r

3. 2* I = 2 * OB* r + I * r

4. I = 2 * OB * r / (2 - r)

Thus, the algebraic solution for interest is:

Interest = (2 * Opening Balance * Interest Rate) / (2 – Interest Rate)

Modelling without circular references

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 solves 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 solution 

• Interest is firstly calculated based on the formula:

Interest = ½ * (Opening Balance + Closing Balance) * Interest Rate

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

I = ½ * (108,333 + 118,543) * 9% = 10,209 

• To avoid circular reference, the calculated interest need 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:

Interest = (2 * Opening Balance * Interest Rate) / (2 – Interest Rate)

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

I = (2 * 108,333 * 9%) / (2 – 9%) = 10,209

Screenshot: 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.

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...