Thread: Calculate commisson

I'm in Real Estate, I need help figuring out how to calculate agent commissions. Here is my issue:
If a house sells for $100,000, we charge a 3% commission = $3000. The agent receives 50% of that commission = $1500 (so far I've managed this much) the problem I run in to is with what we call a Cap. From the $1500 the agent makes, we charge an additional 36% franchise fee = $540, until they have paid a total of $10,000 in Franchise fees, then we no longer charge the 36%. I tried a formula like this - =IF(H21<10000,H3*0.36,"0") where H21 was the sum of the cap paid and H3 was the commission for the sale, but once the H21 cell reaches $10,000, then all of my values on in the cap column revert to 0. Is there a cumulative method I can use? Or am I going about this all wrong? I've attached the Spreadsheet,, and any help would be greatly appreciated! Commissions1.xlsx

Originally Posted by Jessica Rolfe

I'm in Real Estate, I need help figuring out how to calculate agent commissions. Here is my issue:
If a house sells for $100,000, we charge a 3% commission = $3000. The agent receives 50% of that commission = $1500 (so far I've managed this much) the problem I run in to is with what we call a Cap. From the $1500 the agent makes, we charge an additional 36% franchise fee = $540, until they have paid a total of $10,000 in Franchise fees, then we no longer charge the 36%. I tried a formula like this - =IF(H21<10000,H3*0.36,"0") where H21 was the sum of the cap paid and H3 was the commission for the sale, but once the H21 cell reaches $10,000, then all of my values on in the cap column revert to 0. Is there a cumulative method I can use? Or am I going about this all wrong? I've attached the Spreadsheet,, and any help would be greatly appreciated! Attachment 117044

Try using a "Cap left" column instead. This is the only way I could think of to avoid a circular reference. I modified your spreadsheet. Take a look at it.

I think you'll like the way it works. Let me know if you need an explanation on any of the formulas.

Originally Posted by seikowatchesusa

Try using a "Cap left" column instead. This is the only way I could think of to avoid a circular reference. I modified your spreadsheet. Take a look at it.

I think you'll like the way it works. Let me know if you need an explanation on any of the formulas.

This is great, thank you so much! I've just got one little issue, the Cap stops calculating at $9190 instead of $10,000? I can't figure out why? (I've added a referral column to this sheet from the original, based on what I learned from the changes you made. The table method is GREAT - so I've attached an updated copy)

Thanks SO MUCH!

Originally Posted by nd4spd

I have added a new column franchise fee in order to calculate how much you have received and if the total goes above 10,000 it will stop to calculate the additional charge at 36% with message no charge. hope this helps !!!

Thank you SOOOO MUCH! I can't believe how helpful everyone always is here! have a great day!

One step further would be to simply replace SUM(($H$3:$H$20)) with $H$21 which is the said sum ( or am I missing something?)

If I understand correctly, all you have to do is change the I3 to 10k. Then you have to modify the cap column to
=if([cap left]>g3*.36,g3*.36,[cap left])
I think that is right. I'm on my cell phone with no access to excel, so please forgive me if it doesn't work. I'm doing the calculations off the top of my head. If it doesn't work, let me know, and I'll take a look at it again when I get to a computer.

Ok, I 'm back on a computer.

Forget the formula. All you have to do is change I3 to $10,000. That should fix the problem.

Let me know if you need any more help.

If it works, don't forget to add to my reputation.

@ seikowatchesusa

Thanks! That fixed me Everything has been working perfectly, until today when I added two columns. (Concessions, and effective commission) they are fine, as are all of the other items I've made changes to, with the exception of the highlighted row in the attached sheet. The Referral percentage isn't calculating - it just returns a 0.

Can you please help again?
Thanks!
Jessica

Why not use H3*G3 ?

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save