Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

2. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.


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. If you do however use years, then you need to be careful with calculating interest in an annual financial model.

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.

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.

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.

Time Value of Money in Excel online course

Auditing Financial models