Unformatted text preview: The Average Interest The Average Interest Trigger
Chapter 17 What is Average Interest?
What is Average Interest? Calculating Interest Expense (or Interest Income) based on the average of the beginning and ending Debt balances for that period Called Running "Average Interest". Avoiding the Circular Reference
Avoiding the Circular Reference Average Interest causes a circular reference in Excel When you first build the model, calculate Interest Expense/Income using the beginning of period balance (noncircular) until it's time to print. Doing it right
Doing it right Is it more correct to use the average of the beginning and endofyear balances for each debt piece Interest expense is owed based on the average debt outstanding during the year – not the January 1 balance. Excel can work through the Excel can work through the Average Interest circular reference Excel will need to "iterate" until it finds a solution to the circular reference. Select Excel's 'Tools>Options' dialog and click the 'Calculation' tab. Then check the Iteration box. Click 'OK' to finalize your changes. Now Excel won't warn you about circular references and will instead try to solve them. Your only notification that there's a circ will be the word "Iteration" shown in the status bar at the bottom left of the Excel application. For now..
For now.. To avoid working in a model with a circular reference, it is best to turn Average Interest off except when you want to print. Use an On/Off trigger to allow us to quickly toggle Average Interest on or off. Name the average interest cell "AI" to make this trigger easier to refer to. Here is the logic…
Here is the logic… If Average Interest is on, multiply the interest rate by the average debt balance (we'll discuss the ROUND function in a moment); otherwise, multiply the interest rate by the beginning of year debt balance Average Interest Expense
Average Interest Expense =Interest Rate * IF(ai=1,ROUND((D211+E211)/2,2),D211) a1 =1, if average interest is set on
Add in a ROUND function so Excel can resolve the circular reference more quickly
Then take the average of the beginning and ending debt balances Otherwise, if Average Interest is off, multiply by the beginning debt balance instead. Switch Average Interest on
Switch Average Interest on Hit F5 and type "AI" to quickly go to the Average Interest cell. Enter a 1 to turn it on. Your model will iterate. When you're done checking out the trigger, turn Average Interest back off. It's best to work without a circular reference in your model (otherwise, your model would be recalculating every time you made a change). ...
View Full Document