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. If you do however use years, then you need to be careful with calculating interest in an annual financial model.
Table of contents
YouTube Interest in Annual Financial Models
Non Interest Annualisation
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.
Calculate total monthly interest in a single annual calculation
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.
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.
Reviewing interest calculations in a Financial Model
Besides the issue noted above, interest calculations are often subject to spreadsheet errors. Whether it is a logic error as above, or the amortisation table looking at the incorrect interest rate, you should always review any interest based calculations in a financial model.
See how in our free online Financial Modelling Auditing course.