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