Pro rata formula

[MrSupreme]

Hi All,

Hoping someone can help me figure this out.

I have been provided with accounts that run from January to December for each year with total revenues of $8,000 in Year One and $10,000 in Year Two. Fees are listed as 4.75% of revenue from April 2024 to March 2025 (Year One) and 5.00% of revenue from April 2025 to March 2026. I want to understand what the pro rata fees as of today (from August 2024) going forward as from August 2024 to August 2025 fees would essentially be 8 months at 4.75% and 4 months at 5.00%. Is there a formula that I can use to accurately calculate this please?

 

[6StringJazzer]

To calculate the fees, you have to know the revenue. How are you going to project revenue from now through August 2025? Do you have monthly revenue figures, or just sum by year?

If monthly revenue is steady from August 2024 - August 2025 and all you have is a yearly total, you can calculate a weighted average of the fee rate:

$scratch.xlsm
	E	F	G	H
1	Months	Rate	Weighted sum	Weighted Average
2	8	4.75%	38.00%
3	4	5.00%	20.00%
4	12		58.00%	4.83%
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=E2*F2
E4,G4	E4	=SUM(E2:E3)
H4	H4	=G4/E4

It's hard to say anything more without seeing what data you have.

 

[MrSupreme]

Thank you for your response.

In answer to your question, I do not have monthly revenue figures and only the sum for each year: total revenues of $8,000 in Year One and $10,000 in Year Two.

The issue for me of course is that I am looking at it from August to August each year so I want to accurately apportion the fees of 4.75% and 5.00%. Any help would be much appreciated. Thank you.

 

[felixstraube]

Something like this maybe?

Book5.xlsx
	A	B	C	D	E
1	Month	Annual revenue	Estimated Monthly revenue	Fee %	Fee amount
2	Jan-24	8000	666.666667
3	Feb-24	8000	666.666667
4	Mar-24	8000	666.666667
5	Apr-24	8000	666.666667	4.75%	31.6666667
6	May-24	8000	666.666667	4.75%	31.6666667
7	Jun-24	8000	666.666667	4.75%	31.6666667
8	Jul-24	8000	666.666667	4.75%	31.6666667
9	Aug-24	8000	666.666667	4.75%	31.6666667
10	Sep-24	8000	666.666667	4.75%	31.6666667
11	Oct-24	8000	666.666667	4.75%	31.6666667
12	Nov-24	8000	666.666667	4.75%	31.6666667
13	Dec-24	8000	666.666667	4.75%	31.6666667
14	Jan-25	10000	833.333333	4.75%	39.5833333
15	Feb-25	10000	833.333333	4.75%	39.5833333
16	Mar-25	10000	833.333333	4.75%	39.5833333
17	Apr-25	10000	833.333333	5%	41.6666667
18	May-25	10000	833.333333	5%	41.6666667
19	Jun-25	10000	833.333333	5%	41.6666667
20	Jul-25	10000	833.333333	5%	41.6666667
21	Aug-25	10000	833.333333	5%	41.6666667
22	Sep-25	10000	833.333333	5%	41.6666667
23	Oct-25	10000	833.333333	5%	41.6666667
24	Nov-25	10000	833.333333	5%	41.6666667
25	Dec-25	10000	833.333333	5%	41.6666667
26	Jan-26			5%
27	Feb-26			5%
28	Mar-26			5%
29	Apr-26
30	May-26
31	Jun-26
32	Jul-26
33	Aug-26
34	Sep-26
35	Oct-26
36	Nov-26
37	Dec-26
Sheet1

Cell Formulas
Range	Formula
C2:C25	C2	=B2/12
E5:E25	E5	=C5*D5

 

[6StringJazzer]

If you apply 4.83% (as calculated in my post #2) to your 12-month total from August 2024 - August 2025 you will get the desired prorated fee.

If you had shown your data I would have set it up for you but I have no idea what your sheet looks like.