Calculating interest in an annual financial model

When building a financial model, one of the key decisions you need to make is the time periods to use i.e. annual, quarterly, monthly etc. We personally rather build monthly models and then report on the other periods, as inevitably at some stage the client will want to go into more detail which means monthly.

However, there are times when an annual model can be used. The key thing to be careful of is calculating interest charged in an annual financial model in a simplistic way.

Most other areas you can times by 12 and get a fair approximation of the annual costs and income involved. But interest on loans is different, as the number will change during the year depending on payments made, and this will drive tax payments.

Excel has a number of functions that can help you be more accurate with this.

In the example below, in C6, we show a simple interest calculation where we take the opening balance and multiple it with the interest rate of 11%. In C16 we rather use the CUMIPMT formula to calculate what the interest will be given that the loan is repaid monthly and the amount of interest changes every month.

Calculating interest in an annual financial model

The total interest charge for the 2 methods differs (365 versus 305), where the correct method is 20% lower (which means you need to pay more tax then you think).

Notice that the repayments are exactly the same and you end with the correct balance in both methods. All that has happened is that the split between interest and capital is different. The problem is that this affects tax and the related tax payments.