Circular interest on average balances

When interest is calculated on the average balance of an account and added to that same account, the formula depends on itself: a circular reference. This tutorial solves the problem with basic algebra instead of an iterative macro or Visual Basic for Applications (VBA).

A circular reference arises when a formula depends on itself, directly or indirectly: C = A + B, but A or B is in turn a function of C. It can be solved by iteration, but that breaks a basic rule of efficient financial modelling, and the usual fix in the market has real shortcomings. The algebraic approach below avoids both.

Why circular references in a financial model are a problem

Take interest on a deposit or a loan account, where interest is calculated on the average balance and builds up inside the account, for example during construction. It is a simple situation, but it has troubled the finance industry for decades.

A spreadsheet can calculate the interest if it allows a circular reference, but that has consequences:

• It breaks a firm rule of best practice modelling: no circular references
• It hides any further circular references added later
• A model auditor may qualify their opinion
• It breaches the in-house modelling risk policies of most institutions

Breaking circular interest with a copy-paste macro

The circularity can be broken, crudely, with a copy-paste macro. The macro copies the calculated interest and pastes it into the account as a value, the interest recalculates and the loop runs again.

It repeats until the difference between the calculated interest and the pasted interest is within a set tolerance in every period. Whenever the model’s inputs change, the macro must be run again. This is common practice in the market, but it has further drawbacks:

• It does not work readily with data tables, a useful scenario tool
• It relies on the user knowing when and how to run the macro
• It relies on well-written VBA, or the cell references may move

Like many finance calculations, there is a simple way and a hard way. Thinking about what you are really trying to solve usually leads to an analytical answer instead of an iterative one.

Using algebra to solve circular interest

The circular reference forms because interest is a function of the average balance of the account. Take:

• CB: closing balance
• OB: opening balance
• I: interest for the period
• r: interest rate for the period

The closing balance is the opening balance plus the interest:

CB=OB + I

and the interest is the rate applied to the average of the opening and closing balances:

I=r × OB + CB2

Substitute the first equation into the second and rearrange, and the interest no longer depends on the closing balance:

I=2 × r × OB2 − r

The interest now depends only on the opening balance and the rate, so the account calculates in one pass with no circular reference.

Iteration and algebra compared in the workbook

The workbook shows a simple debt account where interest is calculated on the average balance and builds up inside the account. It solves the interest twice, first with a traditional iterative routine and then with the algebra above, and the two give the same result. Withdrawals from and deposits to the account can be handled the same way.