Commission Tier base on the age of the account

[Pallico21]

Hello,
I need help with calculating commission tier base on the age of the account. I've search for this type of question on the thread but don't have any luck.
So the commission will be calculated based on the age of the account.
Year 1 = 5%
Year 2 = 4%
Year 3 = 2%
Year 4 = 0%

The age of the account is from the first invoice date.

I calculated it based on the Quarterly ending date. However, I'm struggling with the partial month. For example: the screenshot below shows that the the commission ending date is 10/22/2022. But my formula doesn't pickup the October 2022 for Q4.

Please help! I've tried Sumproduct and the result is zero

Thank you very much!

 

[Alex Blakenburg]

Your formula precentages are different to your table percentages. Also are you using column I ?
Is year completed years from Column C-First Invoice to Column F ?
Is Year 1 = 5% from 0-1 years or from 1-2 years ?

 

[Alex Blakenburg]

Here is a starting point.

20211228 Commission Calculation Tiers.xlsx
	A	B	C	D	E	F	G	H	I	J
1			First Invoice		2021 - YTD Revenue	Qtry2022 Invoice Assumption		2022 Commission EST	Commission %	Full Year Check
2			22/10/2019		92,763.14	31/03/2022		3,710.53	4%	2
3			22/10/2019		92,763.14	30/06/2022		3,710.53	4%	2
4			22/10/2019		92,763.14	30/09/2022		3,710.53	4%	2
5			22/10/2019		92,763.14	31/12/2022		1,855.26	2%	3
6
7
8				Year	Percent
9				1	5%
10				2	4%
11				3	2%
12				4	0%
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=IFERROR(VLOOKUP(DATEDIF(C2,F2,"y"),$D$9:$E$12,2,TRUE),"Less than 1 year")*E2
I2:I5	I2	=IFERROR(VLOOKUP(DATEDIF(C2,F2,"y"),$D$9:$E$12,2,TRUE),"Less than 1 year")
J2:J5	J2	=DATEDIF(C2,F2,"y")

 

[Pallico21]

Your formula precentages are different to your table percentages. Also are you using column I ?
Is year completed years from Column C-First Invoice to Column F ?
Is Year 1 = 5% from 0-1 years or from 1-2 years ?

Hi Alex,
Thank you for your assistance! I just realize my question is all over the place

My company has 4 entities and each entity has their own commission structure. In this case, the commission tier is:
Year 0 -1: 2%
Year 2: 1%
Year 3: 1%
Year 4: 0%

Column C: this is the first invoice issues date. The commission is starting from this date
Column F: this is the quarterly ending for 2022. We will pay out commission on the last day of the quarter.

I copy your formula and it works for accounts that over 1 year old. How can I include the commission for the accounts that less than a year?

I sincerely appreciated your help!

 

[Alex Blakenburg]

The way you have the table is leaving me with a 1 year gap. Please correct the figures using my format below.
Year 0-1: 2%
Year 1-2: 1%
Year 2-3: 1%
Year 3+: 0%

20211228 Commission Calculation Tiers.xlsx
	A	B	C	D	E	F	G	H	I	J
1			First Invoice		2021 - YTD Revenue	Qtry2022 Invoice Assumption		2022 Commission EST	Commission %	Full Year Check
2			22/10/2019		92,763.14	31/03/2022		927.63	1%	2
3			22/10/2019		92,763.14	30/06/2022		927.63	1%	2
4			22/10/2019		92,763.14	30/09/2022		927.63	1%	2
5			22/10/2019		92,763.14	31/12/2022		0.00	0%	3
6
7
8				Year From	Year To (Exclusive)	Percent
9				0	1	2%
10				1	2	1%
11				2	3	1%
12				3	4	0%
13				4	0	0%
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=IFERROR(VLOOKUP(DATEDIF(C2,F2,"y"),$D$9:$F$12,3,TRUE),"Less than 1 year")*E2
I2:I5	I2	=IFERROR(VLOOKUP(DATEDIF(C2,F2,"y"),$D$9:$F$12,3,TRUE),"Less than 1 year")
J2:J5	J2	=DATEDIF(C2,F2,"y")
E9:E13	E9	=D10

 

[Pallico21]

Hi Alex,
Thank you very much for your help on this!
I applied your method and twisted it a little bit to get the number I wanted.

I used DATEIF to get the age of the account by month
Then I calculated the commissions based on the month

This way I can capture the in-between month. For example: the Ending Commission date is 10/22/2022. So the Sales Rep should earn 1 month of commission on Q4-2022.

Thank you!!! This forum is very awesome and I'm sure I will bother you all again ?

 

[Alex Blakenburg]

I am glad you got what you needed. I don't think I managed to convince you to use a lookup table in preference to a nested If statement but maybe next time.