Investment repayment and Profit Distribution

[Willharveyjones]

Hi!

I'm new here! I am just working on a repayment and profit distribution schedule for a small budget film that I worked on as a passion project. Money from a sale is meant to be distributed to various people who contributed to the project and we want to calculate what will happen depending on what we sell it for. I can easily work out the formulas up until rows 16 to 18 where the remaining money is to be divided among 3 different investors with 3 different totals to reach. The amounts are to be divided to each of these investors according to the percentages 40%, 10% and 50% until their 'total to reach' is fulfilled. Once fulfilled it gets distributed to the other two etc. The remaining money is a profit and will be divided 50/50 amongst the investors and team. Is there a set of formulas I can use for this if the 'total to reach' may change?

WATERFALL CALCULATIONS.xlsx
	A	B	C	D	E	F	G	H
3					ESTIMATED SALE		$ 15,000.00
4
5		PAYMENTS
6	A	CAM		$ 50.00			$ 14,950.00
7	B	Sales Agent distributors		$ 50.00			$ 14,900.00
8
9
10	C	Recoupment	A) Cast	$ 9,000.00
11	C	Recoupment	B) Overages	$ -
12	C	Recoupment	C) Marketing Gross Receipts	$ -
13				C TOTAL	$ 9,000.00		$ 5,900.00	****Amount to be distributed
14
15			Distribution percentage	Total to Reach
16	1a	ATL Deferral	40%	$ 5,000.00
17	1b	Crew Defferals	10%	$ 8,000.00
18	1c	Investors (120% of investment)	50%	$ 10,000.00
19			100%
20
21
22
23		PROFITS
24	2	Production Team	50%				$ -
25	2	Investors Friends	50%				$ -
RECOUPMENT SCHEDULE

Cell Formulas
Range	Formula
G6	G6	=G3-D6
G7	G7	=G6-D7
E13	E13	=SUM(D10:D12)
G13	G13	=G7-E13
C19	C19	=SUM(C16:C18)
G24:G25	G24	=G$19*C24

 

[KRice]

Try this:

Book1
	A	B	C	D	E	F	G	H
3					ESTIMATED SALE		20000
4
5		PAYMENTS
6	A	CAM		50			19950
7	B	Sales Agent distributors		50			19900
8
9
10	C	Recoupment	A) Cast	9000
11	C	Recoupment	B) Overages	0
12	C	Recoupment	C) Marketing Gross Receipts	0
13				C TOTAL	9000		10900	****Amount to be distributed
14
15			Distribution percentage	Total to Reach
16	1a	ATL Deferral	0.4	500			500
17	1b	Crew Defferals	0.1	8000			1090
18	1c	Investors (120% of investment)	0.5	10000			5450
19			1				7040	<< Total of 1st distribution
20
21							3860	<< Amount for 2nd distribution
22
23		PROFITS
24	2	Production Team	0.5				1930
25	2	Investors Friends	0.5				1930
Sheet1

Cell Formulas
Range	Formula
G6	G6	=G3-D6
G7	G7	=G6-D7
E13	E13	=SUM(D10:D12)
G13	G13	=G7-E13
C19,G19	C19	=SUM(C16:C18)
G16:G18	G16	=MIN(D16,C16*$G$13)
G21	G21	=G13-G19
G24:G25	G24	=G$21*C24

 

[Willharveyjones]

Thanks @KRice that is really helpful but I don't think I explained it properly. All money in 13G 'Amount to be distributed' will be distributed to 1a (ATL Deferral),1b (Crew defferals) and 1c (investors) at a rate of 0.4, 0.1 and 0.5 until they reach their totals - once one reaches their total their percentage of the remained amount to be distributed is distributed evenly among the remaining 2 and so on until they all reach their total. If they don't reach their totals (as in the example above then there will not be any profits = [money going to 2nd distribution]) lines 24/25.

 

[KRice]

I don't follow your description. You have two constraints for each distribution recipient: one based on percentages, the other based on a maximum amount. The maximum amount is absolute and fixed. Suppose in your example for post #1, we change the Estimated Sale is 100,000. That would leave 90,900 that must be distributed to 1a, 1b, and 1c, per your post #3. Yet 1a, 1b, and 1c have distribution ceiling amounts of 500, 8000, 10000, for a total of 18,500. Can you explain how 90,900 should be apportioned among 1a, 1b, and 1c while satisfying the "Total to Reach" constraints? Based on my understanding, the problem is too highly constrained. A hand worked example showing expected results would be very helpful.

 

[Willharveyjones]

Hi KRice, Your description sounds spot on. The main thing is that money towards 1a 1b and 1c is apportioned until all 3 meet their ceiling (total to reach) and then the remainder si divided amonst the investors (2). The percentages are the rate or how it is divided towards 1a (40%) 1b (10%) and 1c (50%).
For example for each $10 that comes from the 90,900

• $4 is given to 1a (40%),
• $1 is given 1b (10%)
• $5 is given to1c (50%)

Until their ceiling or total to reach is achieved. Then the money is divided towards the other 2. In the case below 1a would likely achieve their ceiling first as they have the lowest ceiling but a high percentage rate. The money would then got o 1b and 1c. 1c likely to be fulfilled as their rate is 50% - 5x faster than 1b (10%)

WATERFALL CALCULATIONS.xlsx
	A	B	C	D	E	F	G	H
1
2
3						SALE	$ 100,000.00
4
5
6	A	CAM		$ 50.00			$ 99,950.00
7	B	Sales Agent distributors		$ 50.00			$ 99,900.00
8
9
10	C	Recoupment	A) Cast	$ 9,000.00
11	C	Recoupment	B) Overages	$ -
12	C	Recoupment	C) Marketing Gross Receipts	$ -
13				C TOTAL	$ 9,000.00		$ 90,900.00	****Amount to be distributed
14
15			Distribution percentage	Total to Reach
16	1a	ATL Deferral	40%	$ 5,000.00			$ 5,000.00
17	1b	Crew Defferals	10%	$ 8,000.00			$ 8,000.00
18	1c	Investors (120% of investment)	50%	$ 10,000.00			$ 10,000.00
19		TOTAL	100%	$ 23,000.00			$ 23,000.00	<< Total of 1st distribution
20
21							$ 67,900.00	<< Amount for 2nd distribution
22
23	2	Production Yeam	50%				$ 33,950.00
24	2	Investors Friends	50%				$ 33,950.00
RECOUPMENT SCHEDULE

Cell Formulas
Range	Formula
G6	G6	=G3-D6
G7	G7	=G6-D7
E13	E13	=SUM(D10:D12)
G13	G13	=G7-E13
C19:D19,G19	C19	=SUM(C16:C18)
G16:G18	G16	=D16
G21	G21	=G13-D19
G23:G24	G23	=G$21*C23

 

[Willharveyjones]

WATERFALL CALCULATIONS.xlsx
	A	B	C	D	E	F	G	H
1
2
3						SALE	$ 20,000.00
4
5
6	A	CAM		$ 50.00			$ 19,950.00
7	B	Sales Agent distributors		$ 50.00			$ 19,900.00
8
9
10	C	Recoupment	A) Cast	$ 9,000.00
11	C	Recoupment	B) Overages	$ -
12	C	Recoupment	C) Marketing Gross Receipts	$ -
13				C TOTAL	$ 9,000.00		$ 10,900.00	****Amount to be distributed
14
15			Distribution percentage	Total to Reach
16	1a	ATL Deferral	40%	$ 5,000.00			$ 4,360.00
17	1b	Crew Defferals	10%	$ 8,000.00			$ 1,090.00
18	1c	Investors (120% of investment)	50%	$ 10,000.00			$ 5,450.00
19		TOTAL	100%	$ 23,000.00			$ 10,900.00	<< Total of 1st distribution
20
21							$ -	<< Amount for 2nd distribution
22
23	2	Production Yeam	50%				$ -
24	2	Investors Friends	50%				$ -
RECOUPMENT SCHEDULE

Cell Formulas
Range	Formula
G6	G6	=G3-D6
G7	G7	=G6-D7
E13	E13	=SUM(D10:D12)
G13	G13	=G7-E13
G17	G17	=C17*G13
G18	G18	=G13*C18
C19:D19	C19	=SUM(C16:C18)

 

[KRice]

Thanks, could you put this through some trials and tell me if it produces correct results? I've added a formula in E16 to facilitate some discussion. For the conditions described, the maximum distributions based on the percentages in column C are shown in the new column E formula. The upper cap on distribution amounts (not based on any percentage of some other value) is given in column D. For any given row (i.e., distribution recipient), you want the minimum of these two values...that's what the formula in G16 does. In this example, 1a hits the cap of 1000 quickly, so is the first out. Then 1b and 1c, while they have a much higher cap of 8000 and 10000, respectively, they are limited by their distribution percentage (only 20% and 30%, respectively)...so they both top out on their distribution amounts based on the percentage limit. If this is how the distribution scheme works, then the formula and text I added in column E can be deleted, and the worksheet should work fine. If I'm missing something, please let me know.

MrExcel_20220419.xlsx
	A	B	C	D	E	F	G	H
1
2
3					ESTIMATED SALE		25000
4
5		PAYMENTS
6	A	CAM		50			24950
7	B	Sales Agent distributors		50			24900
8
9
10	C	Recoupment	A) Cast	9000
11	C	Recoupment	B) Overages	0
12	C	Recoupment	C) Marketing Gross Receipts	0
13				C TOTAL	9000		15900	<< Amount to be distributed
14
15			Distribution percentage	Total to Reach	Ceiling due to dist %
16	1a	ATL Deferral	50%	1000	7950		1000
17	1b	Crew Defferals	20%	8000	3180		3180
18	1c	Investors (120% of investment)	30%	10000	4770		4770
19			100%				8950	<< Total of 1st distribution group
20
21							6950	<< Amount for 2nd distribution group
22
23		PROFITS
24	2	Production Team	50%				3475
25	2	Investors Friends	50%				3475
Sheet1

Cell Formulas
Range	Formula
G6	G6	=G3-D6
G7	G7	=G6-D7
E13	E13	=SUM(D10:D12)
G13	G13	=G7-E13
E16:E18	E16	=C16:C18*G13
G16:G18	G16	=IF(C16:C18*G13<D16:D18,C16:C18*G13,D16:D18)
C19,G19	C19	=SUM(C16:C18)
G21	G21	=G13-G19
G24:G25	G24	=C24*$G$21
Dynamic array formulas.

 

[Willharveyjones]

Hi KRice,

This looks more like it! Getting close. The only issue I can see is that there should be no profit in this case (as in G21 should be zeero and hence G24 and G25 also zero. The "amount to be distributed in G13" is divided amongst 1a,1b and 1c until they get to their 'total to reach' and if they don't there is no money going to G21. Their ceiling to reach will always be the figures in D16-18 not a ceiling due to percentages. The percentages are purely how the amount in G13 is divided among the 3. You could consider this the speed or rate that that amount is apportioned to each 1a,1b and 1c. If it was water rather than money and 1a,1b and 1c were containers the water from G13 going into 1a would be flowing at 50x speed the water going into 1b would be at 20x speed and into 1c at 30x speed. Hence 1a would be filling the fastest and in your case since it also has the smallest ceiling (1000) it would fill up the fastest. Once it is full the remaining amount in G13 would then be divided among the other two in the same relative proportion 20x1b speed and 30x1c speed. (20%1b and 40%1c)

 

[KRice]

I'll post an idea for tackling this soon.

 

[KRice]

My earlier recommendation wasn't close. Based on your descriptions, the distribution percentage" really isn't. Rather it represents the initial relative distribution payment rate. Solving this involves an iterative process that is probably better handled by VBA. In this working example, I show how the iterations can be done in helper columns to the right of your table.

We begin with the rose-colored block and seed it with the current amount of each recipient's distribution amount (0) and the current amount to be distributed from G13. The we move into the 1st iteration...the green block.

We determine the "headroom", the amount remaining before each recipient hits their distribution ceiling. Then we normalize these amounts, expressing them in terms of a ceiling proportion (Cp) that sums to 1. Next we express the payment rates in terms of relative proportions that also sum to 1 (Pp)...this seems trivial in iteration 1 because the relative proportions are the same as those originally described in C16:C18, but it matters later as recipients drop out. Then we use the ratio of Cp/Pp as a measure for how quickly each recipient reaches their ceiling---smaller ratios mean the ceiling is reached sooner (i.e., a low ceiling and a high payment rate each contribute toward lowering this ratio). Then we identify the smallest Cp/Pp ratio---this is the 1st recipient who hits their ceiling. We identify the position of this recipient in the list (their index number). Then we need to assess whether there is sufficient money available that can be apportioned to all of the remaining recipients at this moment. This assessment involves calculating how much money would be needed for the mini-distribution considered in this iteration and comparing it to the current amount of money still available for distributing. The minimum of these two amounts limits what can be distributed. We choose whichever is the smaller amount and then perform the distribution for this iteration (shown in the rightmost column in the lower part of the table for this iteration). Finally, we update the current amount of money that is still available for distributing (upper right corner of iteration block), and move to the next iteration.

This process repeats for iteration 2 (yellow blocks) and possibly for iteration 3 (blue blocks) before terminating. In the iterations after iteration 1--after a recipient has dropped out because their ceiling was reached---the remaining recipients then begin accruing distributions at rates that are consistent with their initial relative payment rates, but renormalized to a total basis factor of 1. So initial relative rates of 0.5, 0.2, and 0.3---and let's assume 0.5 drops out in iteration 1---would produce new relative rates of 0.2/(0.2+0.3)=0.4 and 0.3/(0.2+0.3)=0.6. The same relative rates are maintained, but normalized to a sum of 1. The initial spreadsheet doesn't really describe this idea about the "percentages", so I would recommend relabeling these quantities as "Initial relative payment rates".

During any of the iterations, it is conceivable that sufficient money is not available to make all of the distributions and carry some amount forward to the next iteration. In that case, the available funds are simply distributed according to the current payment proportions.

Finally, all of the mini-distributions are summed (far right) and those sums are copied back into your table.

MrExcel_20220419.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
3					ESTIMATED SALE		20000
4
5		PAYMENTS
6	A	CAM		50			19950
7	B	Sales Agent distributors		50			19900
8
9
10	C	Recoupment	A) Cast	9000
11	C	Recoupment	B) Overages	0					Current Amt to be dist	10900					6614.29					4900					0
12	C	Recoupment	C) Marketing Gross Receipts	0					Amt to Dist					4285.71					1714.29					4900
13				C TOTAL	9000		10900	<< Amt to be distributed	Index of Min Cp/Pp					3					1					2
14									Next to hit ceilting, Min Cp/Pp					0.35714					0.22222					1
15			Initial relative payment rates	Distribution Ceiling						distribution	amt remaining to reach ceiling	ceiling proportion, Cp	payment proportion Pp	Cp/Pp: smaller hits ceiling sooner	distribution	amt remaining to reach ceiling	ceiling proportion, Cp	payment proportion Pp	Cp/Pp: smaller hits ceiling sooner	distribution	amt remaining to reach ceiling	ceiling proportion, Cp	payment proportion Pp	Cp/Pp: smaller hits ceiling sooner	distribution	amt remaining to reach ceiling		Total distributions
16	1a	ATL Deferral	10%	1000			1000			0	1000	0.08333	0.10	0.83333	428.571	571.429	0.07407	0.33	0.22222	571.429	0				0	0		1000
17	1b	Crew Defferals	20%	8000			6900			0	8000	0.66667	0.20	3.33333	857.143	7142.86	0.92593	0.67	1.38889	1142.86	6000	1	1.00	1	4900	1100		6900
18	1c	Investors (120% of investment)	70%	3000			3000			0	3000	0.25	0.70	0.35714	3000	0				0	0				0	0		3000
19			100%				10900	<< Total 1st distribution group
20
21							0	<< Amt for 2nd distribution group
22
23		PROFITS
24	2	Production Team	50%				0
25	2	Investors Friends	50%				0
Sheet1

Cell Formulas
Range	Formula
G6	G6	=G3-D6
G7	G7	=G6-D7
J11	J11	=G13
O11,Y11,T11	O11	=J11-N12
E13	E13	=SUM(D10:D12)
G13	G13	=G7-E13
N12,X12,S12	N12	=MIN(INDEX(K16#,N13)*SUM(M16#)/INDEX(M16#,N13),J11)
N13,X13,S13	N13	=MATCH(N$14,N16#,0)
N14,X14,S14	N14	=SMALL(N16#,1)
K16:K18	K16	=D16:D18-J16:J18
L16:L18,V16:V18,Q16:Q18	L16	=IF(K16#<>0,K16#/SUM(K16#),"")
M16:M18,W16:W18,R16:R18	M16	=IF(K16#<>0,($C$16:$C$18*(K16#<>0))/SUM($C$16:$C$18*(K16#<>0)),"")
N16:N18,X16:X18,S16:S18	N16	=IF(K16#<>0,L16#/M16#,"")
O16:O18	O16	=IF(K16#<>0,N12*M16#,"")
P16:P18,Z16:Z18,U16:U18	P16	=K16#-O16#
T16:T18,Y16:Y18	T16	=IF(P16#<>0,S12*R16#,0)
AB16:AB18	AB16	=SUM(IF(J$15:Z$15="distribution",J16:Z16))
C19,G19	C19	=SUM(C16:C18)
G16:G18	G16	=AB16
G21	G21	=G13-G19
G24:G25	G24	=C24*$G$21
Dynamic array formulas.