To reconcile this timing difference, add a line item titled “Accrued Interest” to you balance sheet under current liabilities. This will pull from the supporting debt schedule, which will be amended as follows. First, add three line items just below the formula for Ending Principal Balance:
- Interest Expense
- Interest Accrued
- Interest Paid
Next, calculate interest expense for each period (see image). This will be similar to the approach used to project interest expense in a model with annual periods with two exceptions:
- There is no need to take an average of the principal balance.
- The interest rate needs to be adjusted to reflect the period of time.
Then calculate interest accrued on the line that follows. This is the line item that will link to the balance sheet to project accrued interest. To complete this calculation sum interest expense in the current period with interest accrued in the previous period, and then subtract interest paid in the current period (see image).
Finally, in the last step related to the debt supporting schedule, we will calculate interest paid using the =IF() and =MOD() functions. The =MOD() function is a great way to confirm if the month is divisible by three, which makes it easy to identify a quarter (=MOD() video explanation). When the formula identifies a quarter end period it will return the sum of the previous three months of interest expense (see image).
Interest expense will link to the income statement in precisely the same way it does in an annual model, and now the Interest Accrued line item can link to Accrued Interest on the Balance sheet.
The final step is to add a line item to the cash flow statement under changes in working capital. Title it “Accrued Interest” and subtract the current period from the prior period to reflect a cash outflow when the current balance declines from one period to the next.
Download: Link to Monthly Three Statement Model Template
Note: FASB requires that this sum be included in cash flow from operations. It should never be included under cash flow from financing activities.