Hi there,
I cannot get monthly interest amounts to reconcile to annual interest amount. Suppose we assume that interest for a given period = rate% x opening balance. There are two problems with reconciling the monthlies vs annual calcs:
1.In a given year, you might have a lot of drawdowns in the last month of the year (e.g. Dec 23), which will not be reflected in opening balance in that same year (i.e. there is a month lag, so int is calculated in jan 24 so within the next year), so interest will not reflect this big drawdown in the last month of the year, for the same year. 2. annual interest takes the convenient assumption that it is just opening balance * rate%, but it does not consider the timing of the drawdowns / repayments within that year.
Does this mean there will always be differences between the two calculations? What are people doing to mitigate these differences?
Thanks!
If you're already calculating monthly interest expense, just sum this across all 12 months to get annual. Don't calculate annual separately, it won't be accurate like you said.
Also use % interest rate * average(starting balance, ending balance) so you're capturing fluctuations within the period.
NoCap - thanks for the reply.
The trouble is we have two separate models doing what should be the same thing:
1. Annual model NOT based on monthlies, a 100year forecasting big boy model.
2. Monthlies just for this year (12 months to Dec 23), to give a detailed profile of the cash.
The trouble then is that for 12 months to Dec 23, #1 and #2 are giving two different int amounts. Naturally this is because the annual model is not considering the timing issue, and it is also not considering the different rates of the month (sonia). I wondered if there are any measures to patch this, although differences are immaterial.
As for your second note, I have ALWAYS been told that interest should be based on opening balance to avoid circularities. This is purely academia stuff - I've never actually run into circularities to do with this. I can talk to you about in concept in detail if you'd like, but will spare the details assuming you dont want granularities of boredom. Saying that, because i've never run into such issues, i am considering to do averages from now on.
Got it. In that case would suggest that for just for this year, your annual model link to the monthly model. More generally, if you have more granularity on a time period (e.g., quarters or months) you should sum those up into annual view. This is very common in sell-side research, where analysts will model next 1 or 2 years on a quarterly basis for a company, summing to the annual totals, then switch to just annual after.
I have heard the circularity comment before from the corporate side too. Using beginning of period balance avoids circularity and is technically more conservative, but can be quite inaccurate if your debt amount is fluctuating significantly from beginning to end of period (e.g. in an LBO where you are sweeping cash flow to pay down debt). Also circularity is not a bad thing, and necessary in a lot of situations. Just have iterative calculations turned on in Excel and you'll be fine.
It’s because you’re compounding the one annually and the other one monthly. They’re different assumptions and results altogether. To make it reconcile you can take interest against the opening balance for 12 months and then compound to start the next year. That should solve it
Thanks for the suggestion - but i dont see how this would consider the timing of any intra year movements?
Will give you a small table so we can reference: would you please explain how one can reconcile to another?
2022 2023 2024 bal bf 100 1200 bal cf 100 1200Annual:
Monthly:
Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23 bal bf 100 100 200 100 100 200 200 200 200 200 200 700 drawdown 100 100 500 500 repayment -100 bal cf 100 200 100 100 200 200 200 200 200 200 700 1200My bad, didn’t read the full question. I’d just build the monthlies like you’ve done, sum them and then link that to the yearlies for the period where you have monthly detail.
Convert annual interest rate to monthly interest rate.
Monthly Interest Rate = (1 + Annual Interest Rate)^(1/12) - 1
For example, if you have an annual interest rate of 5%, Monthly Interest Rate = (1 + 0.05)^(1/12) - 1
Calculate the monthly interest rate: Monthly Interest Rate = (1.05)^(1/12) - 1 ≈ 0.004074 or 0.4074%
Double check in your spreadsheet - add monthly interest at 4.074% and at the end of the year, $100 grows to $105.
Hi there. Thanks for the note but again, this won't solve the timing issue?
Assuming that we are not adding interest to the closing balance, if we multiply monthly (opening / closing / average / whatever the method you take) balances by 0.004074, you get around £11-12 interest cost for 12 months.
If you multiply the annual opening balance by 5%, it does not produce the same result. This is because annual format ignores timing differences in terms of repayments and drawdowns. See an extreme example attached. Op bal / closing bal at the end of the year is the same as the table above, but because you've borrowed so much more at beginning of the period, you will incur far more interest costs. Annual format fails to recognise this.
2022 2023 2024 bal bf 100 700 bal cf 100 700 int cost 5 35 Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23 bal bf 100 1000100 1000100 1000100 1000100 1000100 1000100 1000100 1000100 1000100 1000100 700 drawdown 1000000 repayment -999400 bal cf 1000100 1000100 1000100 1000100 1000100 1000100 1000100 1000100 1000100 1000100 700 700 5% pa 0.004074 pm int cost 0.407412 4074.531 4074.531 4074.531 4074.531 4074.531 4074.531 4074.531 4074.531 4074.531 4074.531 2.851887Iure doloremque maxime sint inventore. Aperiam et neque at nihil qui. Qui dolore consectetur placeat quos.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...