How to Avoid Circular Reference with Late Fee Calc. Problem

[fhxfhx]

Hi, I'm trapped in a circular reference problem since I need to calculate "late fee interest" :

* Rent
* Late Pay Interest calculated on Rent balance
* Payment
* Payment Part Rent
* Payment Part Late Fee (to be deducted from payment preferrentially)
* Rent Balance
* Late fee Balance
* Combined Balance


* as soon as I want to calculate the Late Fee based on the Rent Balance, the circular reference happens and nothing calculates right any more. I didn't figure out yet how to avoid this.
* Do I have to do this completely different ?

http://www.herber.de/bbs/user/69698.xls


Thx for help.
Regards,
Frank

[squiggler47]

as a style suggestion =sum(d12,e13) change to d12+d13 it makes it much clearer to read!

[ernestgoh]

hi fhxfhx,

i did the following:
1) without complicating the calculation, i removed part rent and part late fee
2) changed your formulae for balance rent to Rent Accumlated - Payment Accumulated i.e D19 = D5 - D11 across 1,2,3,4.

Results read
.........................1.......2......3......4
Balance rent... 1000.... 0...1000.. 0
Balance late fee.. 10...10...... 20..20
Total balance...1010... 10...1020..20

[fhxfhx]

Hi ernestgoh, thx for reply but I don't understand what the solution is you suggest. Can you send me the modified .xls so I can examine pls.

Thx,
Frank