Column I. Adding Interest Expense, Income & Circular References.
- Interest Expense (I17). The formula here assumes that the interest rate input is annual and the period is monthly (% / 12). The average debt on the balance sheet between the prior period and the current period is used because the assumption is that the payment is made in the middle of the period. The prior period balance sheet is what you start the period with in terms of debt (June 30 is effectively also July 1stwith $4,917 in debt). If you paid the bank in the middle of the month your interest cost for that month will be figured on the average balance during the month (beginning: $4,917 end of month: $4,833). If you pay the bank at the end of the period, the interest formula should refer wholly to the prior period debt. If you pay the bank at the beginning of the period, it should refer only to the current period debt. In an annual model, it is a pretty safe bet that the bank is being paid in some even manner (quarterly, monthly, etc.) in which case, averaging the current and prior periods of debt is correct. More advanced debt / interest interaction, including modeling a revolver, is in the Advanced Modeling section.
- Interest Income (I18). It is assumed that changes in cash occur evenly over the current period. This is generally a good assumption in modeling. As such, just like debt, this formula averages the current and prior period cash for this reason.
A word about circular references. You may have noted that the cash at the end of the period depends on interest earnings, but you are also earning interest on that new cash. In this example (ignoring tax), you would have the average of $7,427 and $7,120 of cash to earn interest on. Once you earned the interest, you now have the average of $7,427 and $7,126 to earn interest on. This is called a circular reference. Excel is designed to iterate (calculate over and over) until it settles on a solution, but only if iteration is turned on. Tools>Options>Calculation (pre-2007) or Excel/Windows button>Excel Options>Formulas in 2007. If you make an error building a model with circular references, you can create a “trapped” error that won’t go away. An easy fix is to copy the interest formula into an adjacent cell (to preserve it, or copy the text of the formula into a comment) and then hard code a zero into the interest line. This will reset the sheet. Then, just copy your formula back into the cell.