Interest calculation in an integrated model

How do i calculate interest on an overdraft in an integrated model ? Excel ( 13 ) tells me its circular and will not allow the action .


Joe Considine
  • Now there's a coincidence, I was thinking about that in a different context earlier today. When you say that you end up with a circular reference, is this because you are calculating the interest based on the average of the opening and closing balances (the closing balance of course including the interest that it is part of calculating)?
  • Just in case my assumption is correct, you could try this - it only uses the opening balance:


    (Do check carefully that this does perform the calculation that you want)
  • Other routes:

    One which works well if your interest calculations are at the bottom or ideally after a sub-total, is to take the (opening balance + (the month's cashflow movements excluding the interest)/2) x the interest rate

    A second is to have the interest cashflow in the month after the charge.