Do You Have an Average Interest in Interest?

This month we look at a more advanced issue and yet a common cause of consternation in the financial modelling community: how to calculate interest received on the average cash balance for the period. By Liam Bastick, director with SumProduct Pty Ltd.

Query

I need to forecast the interest received on my company’s average cash balance for certain periods of time. Unfortunately, no matter how hard I try to construct the equation, Excel tells me I have a circular argument in my model and then calculates the interest as zero. Is there any way of doing this without just reverting to calculating interest on the opening balance (which is not ideal)?

Advice

[I apologise in advance that this month’s frequently asked query is going to be a little more technical than usual, but this is such a common question in the modelling world it is useful to know how to do it as most modellers will encounter this at some stage of their careers.] The problem shared by our reader can be summarised as follows. Interest is calculated as a function of both the opening and closing cash balances. The problem is that the closing cash balance includes interest – hence the inevitable circular reference.

Circular References

When a formula refers back to its own cell, either directly or indirectly, it is called a circular reference. Microsoft Excel cannot automatically calculate all open workbooks when one or more of them contains a circular reference and usually will put zero as the default value in the cell(s) instead (as described by our reader above). This is not the answer, simply Excel’s way of saying it cannot calculate the formula. You can remove a circular reference, or you can have Excel calculate each cell involved in the circular reference using the ‘calculate iterations’ feature. This requires you switching this feature on. Unless you change the default settings for iteration, Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first. To switch iterations on or to change the default settings, simply access Excel’s (Tools) Options (ALT + T + O) and then:

Various problems arise with circular references:

• Many users will agree that circular arguments can cause the Excel file to become unstable and even crash;
• When solved, Excel may give one solution when there may be several, only one of which is correct in the given circumstances. If the problem is not fully understood, the danger is an incorrect solution may be accepted;
• If Excel stops calculating after a given number of iterations / when the difference between iterations becomes miniscule, users expect the resulting values to be a solution when it may not be (you must always verify that the value reported provides the result required).

Therefore, circular arguments are not recommended, although it is conceded on occasion there may be no alternative viable solution. This month’s query, however, is not such a circumstance.

Goal Seek

When you know the desired result of a single formula but not the input value the formula needs to determine the result, you can use the Excel’s Goal Seek feature located as follows: Excel 2003 and earlier Excel 2007 • From the dropdown menus, go to Tools -> Goal Seek (ALT + T + G) • Go to the ‘Data’ tab on the Ribbon • In the ‘Data Tools’ section, click on the ‘What-If Analysis’ icon • Select ‘Goal Seek’ • ALT + T + G will also work Goal Seek works by changing an input cell iteratively to get a selected output cell to equal a particular value. It only permits one cell to be changed by iteration (the Solver add-in is used for more complex problems) and, like circular references, may give a different answer to the one required if there is more than one solution.

Algebraic Approach (recommended in this instance)

This month’s problem can be solved algebraically in, er, a relatively straightforward manner without the issues discussed above – and is therefore my recommended approach. However, I will begin by apologising for the following mathematical assault (for those not interested in the derivation, simply skip to the end) – unfortunately, Excel modelling sometimes boils down to solving simultaneous equations! Let:

• If x = 0%, this means the movement occurred at the start of the period
• If x = 100%, this means that the movement occurred at the end of the period
• If x = 50%, this means that the movement occurred midway through the period

We can therefore derive our equation required.

Hence, we can calculate interest from this final equation and have no circular references or goal seek. I endeavour to make this a little clearer in the linked Excel file, Interest Revenue Example.

If you have a query for this section, please feel free to drop Liam a line at liam.bastick@sumproduct.com or visit the website www.sumproduct.com