Real Estate Commission formula help

I’m trying to build a worksheet to help me track my real estate business. I’ve gotten the worksheet to calculate most of my data but I’m having on issue. When I complete a sale I get a commission which I then have to share part of it with the Office. This commission is capped, Once I reach that cap I just pay a simple $100 fee to the office. this is what I need to happen.

This cell can have 3 results

1. Commission * Split = Result
   1. =D2*$K$5
2. Commission * Split + Sum of Current Commission > CAP = CAP-Sum
   1. =$J$2-SUM(E$2:E2)
3. $100
   1. This will happen once the cap has been met.

This is the formula I have created:

=IF(SUM(E$2:E2)+D3*$K$5<=$J$2,D3*$K$5,IF(SUM(E$2:E2)+D3*$K$5>=$J$2,$J$2-SUM(E$2:E2),100))

The problem I have is that I’m getting either one of the 1st two IF results of a Zero result after the CAP has been met.

To add an additional variable I want this to track my yearly business results but my CAP is not based on the Calendar year. For example if I my CAP year starts 4/1 and I CAP in August the next year I’ll have to only pay the fee from January-March and then the CAP starts all over again. I have not started to try to figure this out yet. I wanted to get the basic CAP formula worked out first.

I’ve attached a sample spreadsheet that I’m using to work this out.