Accounting Snafu

Only if tenants paid the exact amount every time I wouldnt have to do all this extra work! lol

Well this is my situation, most of the time the tenants will pay over or pay under their monthly rental amount. The total they owe is split up between rent and tax. I would like to just type in the total they give me and it will automatically break it down into rent amount and tax amount based on that rent amount, not necessarily what they SHOULD be paying. The reason for the breakdown is I must pay taxes each month and need to know the taxable amount and have a running balance for the next month. I actually made a pretty ok formula but will work for if they pay exact or under and the other formula i made works for if they pay exact or over! cant get it exactly right.

Example, Advance Auto owes a total of 491.28 each month

if they give me $500 I would like to pay taxes out of the $500 for the $460 owed but also on whatever his balance would be: $500-$460-$31.28=$8.72 So I would like taxes taken out of the $8.72 as well ($8.72 x 7%= .59) so the balance would be $8.13

here are screen shots of what im doing and what i need help with, thanks in advance for all your assistance!