Simpler way to calculate uneven payments over time

[juanbolas]

Hello,

I'm trying to calculate the total monthly payment amounts for 10 products. Payments vary from 50% in the first and second months, to 25% in the first four months, to 20% starting on the third month, etc. The idea is to calculate to total payable amount by month for each of the products.

I set up a couple of simplified tables for two products over twelve months (I need to do it over 10 years -- 120 months) but am wondering if there is a simpler and more elegant way to do this.

I should have paid more attention to the classes on matrices.

Thanks in advance.

example matrix.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Type 1
2
3		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
4	Payable	50%	25%	10%	15%	0%	0%
5
6		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
7	Sales	10	20	30
8
9		1	2	3	4	5	6	7	8	9	10	11	12	Test
10	1	5	2.5	1	1.5	0	0	0	0	0	0	0	0	10
11	2		10	5	2	3	0	0	0	0	0	0	0	20
12	3			15	7.5	3	4.5	0	0	0	0	0	0	30
13	4													0
14	5													0
15	6													0
16	7													0
17	8													0
18	9													0
19	10													0
20	11													0
21	12													0
22	Payable	5	12.5	21	11	6	4.5	0	0	0	0	0	0	60
23
24
25	Type 2
26
27		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
28	Payable	20%	20%	20%	40%
29
30		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
31	Sales	50	20	30
32
33		1	2	3	4	5	6	7	8	9	10	11	12	Test
34	1	10	10	10	20	0	0	0	0	0	0	0	0	50
35	2		4	4	4	8	0	0	0	0	0	0	0	20
36	3			6	6	6	12	0	0	0	0	0	0	30
37	4													0
38	5													0
39	6													0
40	7													0
41	8													0
42	9													0
43	10													0
44	11													0
45	12													0
46	Payable	10	14	20	30	14	12	0	0	0	0	0	0	100
Sheet1

Cell Formulas
Range	Formula
F33:P33,F9:P9	G9	=F9+1
G10:P12,F10:F11,E10	G10	=OFFSET($E$7,0,$D10-1)*(OFFSET($E$4,0,G$9-$D10))
D11:D21,D35:D45	D11	=+D10+1
Q34:Q46,Q10:Q22	Q10	=SUM(E10:P10)
E22:P22,E46:P46	E22	=SUM(E10:E21)
G34:P36,F34:F35,E34	G34	=OFFSET($E$31,0,$D34-1)*(OFFSET($E$28,0,G$33-$D34))

 

[Cubist]

Not sure what you deem as "simpler" but here's an array option.

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Type 1
2
3		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
4	Payable	0.5	0.25	0.1	0.15	0	0
5
6		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
7	Sales	10	20	30	0	0	0
8
9		1	2	3	4	5	6	7	8	9	10	11	12	Test
10	1	5	2.5	1	1.5	0	0	0	0	0	0	0	0	10
11	2	0	10	5	2	3	0	0	0	0	0	0	0	20
12	3	0	0	15	7.5	3	4.5	0	0	0	0	0	0	30
13	4	0	0	0	0	0	0	0	0	0	0	0	0	0
14	5	0	0	0	0	0	0	0	0	0	0	0	0	0
15	6	0	0	0	0	0	0	0	0	0	0	0	0	0
16	7	0	0	0	0	0	0	0	0	0	0	0	0	0
17	8	0	0	0	0	0	0	0	0	0	0	0	0	0
18	9	0	0	0	0	0	0	0	0	0	0	0	0	0
19	10	0	0	0	0	0	0	0	0	0	0	0	0	0
20	11	0	0	0	0	0	0	0	0	0	0	0	0	0
21	12	0	0	0	0	0	0	0	0	0	0	0	0	0
22	Payable	5	12.5	21	11	6	4.5	0	0	0	0	0	0	60
23
24
25	Type 2
26
27		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
28	Payable	0.2	0.2	0.2	0.4
29
30		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
31	Sales	50	20	30
32
33		1	2	3	4	5	6	7	8	9	10	11	12	Test
34	1	10	10	10	20	0	0	0	0	0	0	0	0	50
35	2	0	4	4	4	8	0	0	0	0	0	0	0	20
36	3	0	0	6	6	6	12	0	0	0	0	0	0	30
37	4	0	0	0	0	0	0	0	0	0	0	0	0	0
38	5	0	0	0	0	0	0	0	0	0	0	0	0	0
39	6	0	0	0	0	0	0	0	0	0	0	0	0	0
40	7	0	0	0	0	0	0	0	0	0	0	0	0	0
41	8	0	0	0	0	0	0	0	0	0	0	0	0	0
42	9	0	0	0	0	0	0	0	0	0	0	0	0	0
43	10	0	0	0	0	0	0	0	0	0	0	0	0	0
44	11	0	0	0	0	0	0	0	0	0	0	0	0	0
45	12	0	0	0	0	0	0	0	0	0	0	0	0	0
46	Payable	10	14	20	30	14	12	0	0	0	0	0	0	100
Sheet3

Cell Formulas
Range	Formula
E10:P21,E34:P45	E10	=LET( p,MAKEARRAY(12,12,LAMBDA(r,c,IF(r<=c,INDEX(E4:J4,1,(c-r)+1),0))), s,MAKEARRAY(12,12,LAMBDA(r,c,IF(r<=c,INDEX(E7:J7,1,r),0))), IFERROR(p*s,0))
Q10:Q21,Q34:Q45	Q10	=BYROW(E10#,LAMBDA(r,SUM(r)))
E22:P22,E46:P46	E22	=BYCOL(E10#,LAMBDA(c,SUM(c)))
Q22,Q46	Q22	=SUM(Q10#)
Dynamic array formulas.

 

[Cubist]

The above can be simplified further.

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2
3		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
4	Payable	0.5	0.25	0.1	0.15	0	0
5
6		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
7	Sales	10	20	30	0	0	0
8
9		1	2	3	4	5	6	7	8	9	10	11	12	Test
10	1	5	2.5	1	1.5	0	0	0	0	0	0	0	0	10
11	2	0	10	5	2	3	0	0	0	0	0	0	0	20
12	3	0	0	15	7.5	3	4.5	0	0	0	0	0	0	30
13	4	0	0	0	0	0	0	0	0	0	0	0	0	0
14	5	0	0	0	0	0	0	0	0	0	0	0	0	0
15	6	0	0	0	0	0	0	0	0	0	0	0	0	0
16	7	0	0	0	0	0	0	0	0	0	0	0	0	0
17	8	0	0	0	0	0	0	0	0	0	0	0	0	0
18	9	0	0	0	0	0	0	0	0	0	0	0	0	0
19	10	0	0	0	0	0	0	0	0	0	0	0	0	0
20	11	0	0	0	0	0	0	0	0	0	0	0	0	0
21	12	0	0	0	0	0	0	0	0	0	0	0	0	0
22	Payable	5	12.5	21	11	6	4.5	0	0	0	0	0	0	60
23
24
25	Type 2
26
27		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
28	Payable	0.2	0.2	0.2	0.4
29
30		Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
31	Sales	50	20	30
32
33		1	2	3	4	5	6	7	8	9	10	11	12	Test
34	1	10	10	10	20	0	0	0	0	0	0	0	0	50
35	2	0	4	4	4	8	0	0	0	0	0	0	0	20
36	3	0	0	6	6	6	12	0	0	0	0	0	0	30
37	4	0	0	0	0	0	0	0	0	0	0	0	0	0
38	5	0	0	0	0	0	0	0	0	0	0	0	0	0
39	6	0	0	0	0	0	0	0	0	0	0	0	0	0
40	7	0	0	0	0	0	0	0	0	0	0	0	0	0
41	8	0	0	0	0	0	0	0	0	0	0	0	0	0
42	9	0	0	0	0	0	0	0	0	0	0	0	0	0
43	10	0	0	0	0	0	0	0	0	0	0	0	0	0
44	11	0	0	0	0	0	0	0	0	0	0	0	0	0
45	12	0	0	0	0	0	0	0	0	0	0	0	0	0
46	Payable	10	14	20	30	14	12	0	0	0	0	0	0	100
Sheet3

Cell Formulas
Range	Formula
E10:P21,E34:P45	E10	=IFERROR(MAKEARRAY(12,12,LAMBDA(r,c,IF(r<=c,INDEX(E4:J4,1,(c-r)+1)*INDEX(E7:J7,1,r),0))),0)
Q10:Q21,Q34:Q45	Q10	=BYROW(E10#,LAMBDA(r,SUM(r)))
E22:P22,E46:P46	E22	=BYCOL(E10#,LAMBDA(c,SUM(c)))
Q22,Q46	Q22	=SUM(Q10#)
Dynamic array formulas.

 

[juanbolas]

Thank you!