In this post we would like to share our thoughts on the most debated topic in financial modelling – interest rate charges and circular references in Excel. As all modellers know, ideal calculation of interest charges in a financial model requires beginning and end balances for funding positions: average debt * rate = period charge.
However, the formula is by definition circular: to calculate end balance one needs to know how much interest is paid out during the period. Fortunately, Microsoft Excel deals with this issue handsomely. Enable iterative calculation in Excel Options menu and voilà.
However, most our colleagues would say it is a big no-no. Why? Running models on iterations is rather dangerous. The reason is that Excel can not tell which circular references are present by design and which are mistakes where user accidentally “looped” some cells: for example, A1 = A9 and Excel would run on the last value inputted into either A1 or A9.
Hence, many best practice guides strongly advise against running Excel on iterations. Instead, modellers come up with clever code which runs the iterations to balance interest charges and debt end balances and then return Excel back to non-iterative standard setting.
We personally think such approach is an overkill and not very convenient to work with. Every time a new operating assumption is introduced into the model, iterations code needs to be rerun to make balance sheets balance. If the capital structure is complex it may even take some time to complete its job. Pain in the neck.
Is there a better solution? We think there is. Our Models run in dual mode. At the top of ‘Financials’ tab there is a switch to turn on/off interest rate iterations. Both Base Model and Case Builder Model are equipped with ‘Iterate Interest’ button is shown below.
Set the switch to ‘Yes’ and your Model will run iteratively. Don’t forget to set Excel Options to iterations too. If Excel is set to linear calculations you would immediately see a warning that circular references are present in the model. Ignore and do not chase them. Change Excel settings. The warnings would go away.
Want to make sure there are no erroneous loops in the model? No problem, set the switch to ‘No’. Turn off iterations in Excel Options. If no circular reference warnings are showing it means the model is clean from any accidental loops.
That’s it. No extra code. No twisted guesstimates of interest charges. Plain and simple. Enjoy!