WHY and HOW to stop using circular references to calculate interest

It's a widespread convention to use circular references to calculate bank or revolver interest in Excel:
* The average balance determines the interest paid (based on the convention of assuming cash flows occur in the middle of the period)
* The interest paid adds to the closing balance
* The higher closing balance leads to a higher average balance, leads to higher interest, and repeat
In this video, I explain some of the challenges you create when you turn on iterative calculations in Excel, show you how to calculate 'circular' interest without a circular reference, and explain why I think you might be better off to not even do that...
Sections
00:00 What is a circular reference?
03:30 Problems with circular references
9:27 Circularities to calculate interest
10:45 Mathematical solution
16:27 Business sense solution

[@WebberJason]

This is a brilliant explanation Dim! Thanks very much! I always find it hard to explain to people why a circular reference may result in an answer that's not yet resolved, but without you knowing. Now I can just point them to your video!

[@flexyourdata]

Very informative as usual. Thanks!

[@garyknott681]

Great video, thanks with clear examples and explanations. I have been a fan of "no circularity" for many years except perhaps in annual models, but your explanation of why also here you would not recommend it is great. And the car drive to work analogy works for me!

[@DimEarly]

Ah, I had forgotten that analogy… always nice to stroll down memory lane! : )

[@rameshkumarpenmetsa]

Thanks so Much.

[@spilledgraphics]

Minute 7:23 = NO to pollution! Brilliant explanations Diarmuid.

[@spilledgraphics]

Thanks for the transcendent truths shared at 18:50 and onward...🔥😁👌

[@DimEarly]

How did I not know you have a KZbin channel?? I might have to go binge your archives now... : )

[@spilledgraphics]

@@DimEarly 🙌 thanks!!!

[@DimEarly]

It's nice to know that some people watch all the way to the end when I stop modelling and start getting philosophical! : )

[@spilledgraphics]

@@DimEarly it's where the hidden gems hang out at 🔥!

[@coreyharlan6375]

Such a great explanation! I have referred back to this several times. Any chance you might be able to send through your model?

[@siddhant867]

Hey! I have just recently started watching your videos. They are truly amazing I learned so much from them. Formulas I didn’t know existed. My only request to you is, if you can, also share the excel files.

[@DimEarly]

Thanks, glad to hear you're finding them helpful! I'd love to share more of the files, but most of the problems I look at on my channel aren't mine to give away, because the FMWC (organizers of the tournament) sell the cases, which is part of how they cover the costs of running the tournament. If people do buy them, I'm always happy to share my worked solution - just ping me a message (here or on LinkedIn) if you want any of them. I've got some plans for next year that will hopefully mean I have more shareable content, but still TBD... watch this space...

[@siddhant867]

@@DimEarly very well! Sure, would love to follow you on LinkedIn.

[@chestnuts4444]

Ive followed your logic, but at the end when i go to link the interest expense to the Income Statement, i get a circular reference, and im not sure why

[@chestnuts4444]

Can you share the excel. it would be helpful to see the flow first hand

[@user-zy2cq6gl4t]

Hi! Cash flow pre-revolver takes into account tax expenses. But the tax expenses assumes that we've paid interest expenses including revolver. It looks like one more circular reference

[@DimEarly]

Yes, you’re spot on 👍🏻 I think it didn’t come up in this case - probably the revolver draws were in loss-making periods so there was no tax - but it can definitely happen in general. I think you could do some more funky maths to still get a closed-form solution (?), but the bigger picture point is the same: if your go / no go decision on a project or investment changes depending on when in the year the cash flows happen, that might be a sign that you need a quarterly / monthly model rather than annual.

[@sanazdaniellefotoohi]

This is very good, could we please have the excel sheet too?

[@DimEarly]

Hi Sanaz - you can access the file with the small examples at the link below. The financial model I was working on is from a FMWC question, which I don't publish, since they're not my IP. I've put the link below to where you can buy the case from the FMWC if you want. If you do buy it, I'd be happy to send you my model directly as well. Example file: theexcelements.files.wordpress.com/2022/06/circ-examples.xlsx FMWC case: www.fmworldcup.com/product/stage-1-2020-warm-up-with-afm/

[@annog6673]

Have you noticed the following: if you calculate one value with a data table and feed that into a calculation with another data table, the second data table will be calculated at the same time as the first one. Meaning it is broken till you press F9 or enable a simple macro. That bugs me a little bit... :D I have not found a stable way to force excel to recalculate, other than pressing F9 or a macro.

[@DimEarly]

Data tabling the output of a data table! 😮 That’s dark magic. (I remember trying it once or twice before, but I haven’t done it often enough to have thought through this issue…)

[@annog6673]

@@DimEarly I just determined the optimal value of one calculation and then calculated the second function with that value. Now I had to determine the optimal value of a second parameter, based on the first. 2 1d datatables and a little bit of lookup magic instead of 2 goal seeks. But it's pretty fun and probably uncommon.