Calculate running interest rate

[askall1000]

Hello to all, Thank you very much for your assistant in advance. I want to calculate interest for customers; for example for customer A like this interest calculate in 2010 with interest rate 15% and calculated interest amount plus amount transferred to 2011 calculated with interest rate of 2011 and then calculated 2011 payments from 20% interest rate and goes on like that for the following years: 2010 interest amount+amount:30.555,94 transferred to 2011, in 2011 (30.555,94*0,2(yearly)=6.111) and plus 2011 amounts and interest amounts 2.199,01. So if there is 2012 the transferred amount would be 38.866,14. Is there any suggestion to this calculation with pivot table or formula arrangement to calculate this for each customer with automated.

Book3
	A	B	C	D	E	F	G	H	I
1	Customer Name	Date	Amount	Maturity Date	Day	Interest Rate	Interest Amount
2	Customer A	04.01.2010	5.000,00	30.06.2013	1.273,00	0,15	2.615,75
3	Customer A	12.01.2010	9.990,00	30.06.2013	1.265,00	0,15	5.193,43
4	Customer A	03.02.2010	533,00	30.06.2013	1.243,00	0,15	272,27
5	Customer A	02.03.2010	533,00	30.06.2013	1.216,00	0,15	266,35		30.555,94
6	Customer A	02.04.2010	533,00	30.06.2013	1.185,00	0,15	259,56		30555,941
7	Customer A	05.05.2010	533,00	30.06.2013	1.152,00	0,15	252,34		6111,1882
8	Customer A	27.05.2010	533,00	30.06.2013	1.130,00	0,15	247,52		2.199,01
9	Customer A	05.07.2010	533,00	30.06.2013	1.091,00	0,15	238,97		38.866,14
10	Customer A	05.08.2010	533,00	30.06.2013	1.060,00	0,15	232,18
11	Customer A	01.09.2010	533,00	30.06.2013	1.033,00	0,15	226,27
12	Customer A	04.10.2010	533,00	30.06.2013	1.000,00	0,15	219,04
13	Customer A	04.11.2010	533,00	30.06.2013	969,00	0,15	212,25
14	Customer A	03.12.2011	533,00	30.06.2013	575,00	0,2	167,93
15	Customer A	03.01.2011	1.000,00	30.06.2013	909,00	0,2	498,08
16	Customer B	14.01.2011	1.000,00	30.06.2013	898,00	0,2	492,05
17	Customer B	18.01.2011	22.340,00	30.06.2013	894,00	0,2	10.943,54
18	Customer B	12.07.2011	778,00	30.06.2013	719,00	0,2	306,51
19	Customer B	23.08.2011	778,00	30.06.2013	677,00	0,2	288,61
20	Customer B	28.09.2011	778,00	30.06.2013	641,00	0,2	273,26
21	Customer B	21.10.2011	778,00	30.06.2013	618,00	0,2	263,45
22	Customer B	23.11.2011	778,00	30.06.2013	585,00	0,2	249,39
23	Customer B	23.12.2011	778,00	30.06.2013	555,00	0,2	236,60
24	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
25	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
26	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
27	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
28	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
29	Customer B	28.10.2011	534,00	30.06.2013	611,00	0,2	178,78
30	Customer B	10.12.2011	1.174,00	30.06.2013	568,00	0,2	365,39
31	Customer B	28.10.2011	1.708,00	30.06.2013	611,00	0,2	571,83
32	Customer B	28.10.2011	1.708,00	30.06.2013	611,00	0,2	571,83
33	Customer B	28.10.2011	1.708,00	30.06.2013	611,00	0,2	571,83
34	Customer B	24.10.2011	866,00	30.06.2013	615,00	0,2	291,83
35	Customer B	28.10.2011	842,00	30.06.2013	611,00	0,2	281,90
36	Customer B	10.12.2011	1.574,00	30.06.2013	568,00	0,2	489,88
37	Customer B	24.10.2011	134,00	30.06.2013	615,00	0,2	45,16
38	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
39	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
40	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
41	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
42	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
43	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
44	Customer B	10.12.2011	1.708,00	30.06.2013	568,00	0,2	531,59
45	Customer B	24.10.2011	1.000,00	30.06.2013	615,00	0,2	336,99
46	Customer B	28.10.2011	6.500,00	30.06.2013	611,00	0,2	2.176,16
47	Customer B	10.12.2011	10.870,00	30.06.2013	568,00	0,2	3.383,10
48	Customer B	10.12.2011	5.886,00	30.06.2013	568,00	0,2	1.831,92
49	Customer B	21.12.2011	8.800,00	30.06.2013	557,00	0,2	2.685,81
50	Customer C	11.01.2010	13.177,00	30.06.2013	1.266,00	0,15	6.855,65
51	Customer C	05.02.2010	1.086,00	30.06.2013	1.241,00	0,15	553,86
52	Customer C	05.03.2010	1.086,00	30.06.2013	1.213,00	0,15	541,36
53	Customer C	11.04.2010	1.086,00	30.06.2013	1.176,00	0,15	524,85
54	Customer C	01.04.2010	21.720,00	30.06.2013	1.186,00	0,15	10.586,27
55	Customer C	29.04.2010	1.086,00	30.06.2013	1.158,00	0,15	516,82
56	Customer C	01.06.2010	1.086,00	30.06.2013	1.125,00	0,15	502,09
57	Customer C	29.06.2010	1.086,00	30.06.2013	1.097,00	0,15	489,59
58	Customer C	02.08.2010	1.086,00	30.06.2013	1.063,00	0,15	474,42
59	Customer C	13.09.2010	1.086,00	30.06.2013	1.021,00	0,15	455,67
60	Customer C	04.10.2010	1.086,00	30.06.2013	1.000,00	0,15	446,30
61	Customer C	08.11.2010	1.086,00	30.06.2013	965,00	0,15	430,68
62	Customer C	08.12.2010	1.086,00	30.06.2013	935,00	0,15	417,29
63	Customer C	30.12.2010	15.566,00	30.06.2013	913,00	0,15	5.840,45
64	Customer C	08.06.2011	40.500,00	30.06.2013	753,00	0,2	16.710,41
65	Customer C	14.05.2011	500,00	30.06.2013	778,00	0,2	213,15
66	Customer C	10.06.2011	500,00	30.06.2013	751,00	0,2	205,75
67	Customer C	28.07.2011	22.000,00	30.06.2013	703,00	0,2	8.474,52
68	Customer C	15.06.2011	59.500,00	30.06.2013	746,00	0,2	24.321,64
69	Customer C	28.12.2011	40.000,00	30.06.2013	550,00	0,2	12.054,79
70	Customer C	21.12.2011	1.000,00	30.06.2013	557,00	0,2	305,21
71	Customer C	23.07.2011	500,00	30.06.2013	708,00	0,2	193,97
72	Customer C	26.12.2011	1.000,00	30.06.2013	552,00	0,2	302,47
73	Customer C	25.11.2011	1.000,00	30.06.2013	583,00	0,2	319,45
74	Customer C	26.10.2011	1.000,00	30.06.2013	613,00	0,2	335,89
75	Customer C	26.09.2011	1.000,00	30.06.2013	643,00	0,2	352,33
76	Customer C	25.08.2011	1.000,00	30.06.2013	675,00	0,2	369,86
77	Customer C	28.07.2011	6.460,00	30.06.2013	703,00	0,2	2.488,43
78	Customer C	10.10.2011	27.600,00	30.06.2013	629,00	0,2	9.512,55
79	Customer C	04.06.2011	500,00	30.06.2013	757,00	0,2	207,40
80	Customer C	27.12.2011	569,00	30.06.2013	551,00	0,2	171,79
81	Customer C	28.11.2011	569,00	30.06.2013	580,00	0,2	180,83
82	Customer C	26.10.2011	569,00	30.06.2013	613,00	0,2	191,12
83	Customer C	26.09.2011	569,00	30.06.2013	643,00	0,2	200,48
84	Customer C	25.08.2011	569,00	30.06.2013	675,00	0,2	210,45
85	Customer C	26.07.2011	569,00	30.06.2013	705,00	0,2	219,81
86	Customer C	27.06.2011	569,00	30.06.2013	734,00	0,2	228,85
87	Customer C	06.06.2011	2.620,00	30.06.2013	755,00	0,2	1.083,89
88	Customer C	26.09.2011	20.280,00	30.06.2013	643,00	0,2	7.145,23
Sheet1

Cell Formulas
Range	Formula
I5	I5	=SUM(C2:C13,G2:G13)
I7	I7	=+I6*0.2
I8	I8	=+SUM(C14:C15,G14:G15)
I9	I9	=+I5+I7+I8
E2:E88	E2	=+D2-B2
G2:G88	G2	=+F2*E2*C2/365

 

[earthworm]

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Customer Name	Date	Amount	Maturity Date	Day	Interest Rate	Interest Amount	Unique Customer	15%	20%	Result
2	Customer A	4-Jan-10	5000	30-Jun-13	1273	15%	2615.753425	Customer A	30,555.94	8,310.20	38,866.14
3	Customer A	12-Jan-10	9990	30-Jun-13	1265	15%	5193.431507	Customer B	-	125,022.83	125,022.83
4	Customer A	3-Feb-10	533	30-Jun-13	1243	15%	272.2680822	Customer C	91,044.31	335,152.13	426,196.43
5	Customer A	2-Mar-10	533	30-Jun-13	1216	15%	266.3539726						30555.94
6	Customer A	2-Apr-10	533	30-Jun-13	1185	15%	259.5636986						30555.94
7	Customer A	5-May-10	533	30-Jun-13	1152	15%	252.3353425						6111.188
8	Customer A	27-May-10	533	30-Jun-13	1130	15%	247.5164384						2199.014
9	Customer A	5-Jul-10	533	30-Jun-13	1091	15%	238.9738356						38866.14
10	Customer A	5-Aug-10	533	30-Jun-13	1060	15%	232.1835616
11	Customer A	1-Sep-10	533	30-Jun-13	1033	15%	226.2694521
12	Customer A	4-Oct-10	533	30-Jun-13	1000	15%	219.0410959
13	Customer A	4-Nov-10	533	30-Jun-13	969	15%	212.2508219
14	Customer A	3-Dec-11	533	30-Jun-13	575	20%	167.9315068
15	Customer A	3-Jan-11	1000	30-Jun-13	909	20%	498.0821918
16	Customer B	14-Jan-11	1000	30-Jun-13	898	20%	492.0547945
17	Customer B	18-Jan-11	22340	30-Jun-13	894	20%	10943.53973
18	Customer B	12-Jul-11	778	30-Jun-13	719	20%	306.5106849
19	Customer B	23-Aug-11	778	30-Jun-13	677	20%	288.6060274
20	Customer B	28-Sep-11	778	30-Jun-13	641	20%	273.2591781
21	Customer B	21-Oct-11	778	30-Jun-13	618	20%	263.4542466
22	Customer B	23-Nov-11	778	30-Jun-13	585	20%	249.3863014
23	Customer B	23-Dec-11	778	30-Jun-13	555	20%	236.5972603
24	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
25	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
26	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
27	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
28	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
29	Customer B	28-Oct-11	534	30-Jun-13	611	20%	178.780274
30	Customer B	10-Dec-11	1174	30-Jun-13	568	20%	365.3873973
31	Customer B	28-Oct-11	1708	30-Jun-13	611	20%	571.8290411
32	Customer B	28-Oct-11	1708	30-Jun-13	611	20%	571.8290411
33	Customer B	28-Oct-11	1708	30-Jun-13	611	20%	571.8290411
34	Customer B	24-Oct-11	866	30-Jun-13	615	20%	291.830137
35	Customer B	28-Oct-11	842	30-Jun-13	611	20%	281.8969863
36	Customer B	10-Dec-11	1574	30-Jun-13	568	20%	489.8805479
37	Customer B	24-Oct-11	134	30-Jun-13	615	20%	45.15616438
38	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
39	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
40	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
41	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
42	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
43	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
44	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
45	Customer B	24-Oct-11	1000	30-Jun-13	615	20%	336.9863014
46	Customer B	28-Oct-11	6500	30-Jun-13	611	20%	2176.164384
47	Customer B	10-Dec-11	10870	30-Jun-13	568	20%	3383.10137
48	Customer B	10-Dec-11	5886	30-Jun-13	568	20%	1831.916712
49	Customer B	21-Dec-11	8800	30-Jun-13	557	20%	2685.808219
50	Customer C	11-Jan-10	13177	30-Jun-13	1266	15%	6855.650137
51	Customer C	5-Feb-10	1086	30-Jun-13	1241	15%	553.86
52	Customer C	5-Mar-10	1086	30-Jun-13	1213	15%	541.3635616
53	Customer C	11-Apr-10	1086	30-Jun-13	1176	15%	524.850411
54	Customer C	1-Apr-10	21720	30-Jun-13	1186	15%	10586.26849
55	Customer C	29-Apr-10	1086	30-Jun-13	1158	15%	516.8169863
56	Customer C	1-Jun-10	1086	30-Jun-13	1125	15%	502.0890411
57	Customer C	29-Jun-10	1086	30-Jun-13	1097	15%	489.5926027
58	Customer C	2-Aug-10	1086	30-Jun-13	1063	15%	474.4183562
59	Customer C	13-Sep-10	1086	30-Jun-13	1021	15%	455.6736986
60	Customer C	4-Oct-10	1086	30-Jun-13	1000	15%	446.3013699
61	Customer C	8-Nov-10	1086	30-Jun-13	965	15%	430.6808219
62	Customer C	8-Dec-10	1086	30-Jun-13	935	15%	417.2917808
63	Customer C	30-Dec-10	15566	30-Jun-13	913	15%	5840.448493
64	Customer C	8-Jun-11	40500	30-Jun-13	753	20%	16710.41096
65	Customer C	14-May-11	500	30-Jun-13	778	20%	213.1506849
66	Customer C	10-Jun-11	500	30-Jun-13	751	20%	205.7534247
67	Customer C	28-Jul-11	22000	30-Jun-13	703	20%	8474.520548
68	Customer C	15-Jun-11	59500	30-Jun-13	746	20%	24321.64384
69	Customer C	28-Dec-11	40000	30-Jun-13	550	20%	12054.79452
70	Customer C	21-Dec-11	1000	30-Jun-13	557	20%	305.2054795
71	Customer C	23-Jul-11	500	30-Jun-13	708	20%	193.9726027
72	Customer C	26-Dec-11	1000	30-Jun-13	552	20%	302.4657534
73	Customer C	25-Nov-11	1000	30-Jun-13	583	20%	319.4520548
74	Customer C	26-Oct-11	1000	30-Jun-13	613	20%	335.890411
75	Customer C	26-Sep-11	1000	30-Jun-13	643	20%	352.3287671
76	Customer C	25-Aug-11	1000	30-Jun-13	675	20%	369.8630137
77	Customer C	28-Jul-11	6460	30-Jun-13	703	20%	2488.427397
78	Customer C	10-Oct-11	27600	30-Jun-13	629	20%	9512.547945
79	Customer C	4-Jun-11	500	30-Jun-13	757	20%	207.3972603
80	Customer C	27-Dec-11	569	30-Jun-13	551	20%	171.7912329
81	Customer C	28-Nov-11	569	30-Jun-13	580	20%	180.8328767
82	Customer C	26-Oct-11	569	30-Jun-13	613	20%	191.1216438
83	Customer C	26-Sep-11	569	30-Jun-13	643	20%	200.4750685
84	Customer C	25-Aug-11	569	30-Jun-13	675	20%	210.4520548
85	Customer C	26-Jul-11	569	30-Jun-13	705	20%	219.8054795
86	Customer C	27-Jun-11	569	30-Jun-13	734	20%	228.8471233
87	Customer C	6-Jun-11	2620	30-Jun-13	755	20%	1083.890411
88	Customer C	26-Sep-11	20280	30-Jun-13	643	20%	7145.227397
Sheet1

Cell Formulas
Range	Formula
G2:G88	G2	=+F2*E2*C2/365
H2:H88	H2	=IFERROR(INDEX($A$2:$A$88,SMALL(IF(FREQUENCY(IF($A$2:$A$88<>"",MATCH($A$2:$A$88,$A$2:$A$88,0)),ROW($A$2:$A$88)-ROW($A$2)+1),ROW($A$2:$A$88)-ROW($A$2)+1),ROWS(H$2:H2))),"")
I2:I4	I2	=SUMIFS($G$2:$G$88,$A$2:$A$88,$H2,$F$2:$F$88,I$1)+SUMIFS($C$2:$C$88,$A$2:$A$88,$H2,$F$2:$F$88,I$1)
J2:J4	J2	=SUMIFS($G$2:$G$88,$A$2:$A$88,$H2,$F$2:$F$88,J$1)+SUMIFS($C$2:$C$88,$A$2:$A$88,$H2,$F$2:$F$88,J$1)+I2*J$1
K2:K4	K2	=SUM(I2:J2)
M5	M5	=SUM(C2:C13,G2:G13)
M7	M7	=+M6*0.2
M8	M8	=+SUM(C14:C15,G14:G15)
M9	M9	=+M5+M7+M8
E2:E88	E2	=+D2-B2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[askall1000]

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Customer Name	Date	Amount	Maturity Date	Day	Interest Rate	Interest Amount	Unique Customer	15%	20%	Result
2	Customer A	4-Jan-10	5000	30-Jun-13	1273	15%	2615.753425	Customer A	30,555.94	8,310.20	38,866.14
3	Customer A	12-Jan-10	9990	30-Jun-13	1265	15%	5193.431507	Customer B	-	125,022.83	125,022.83
4	Customer A	3-Feb-10	533	30-Jun-13	1243	15%	272.2680822	Customer C	91,044.31	335,152.13	426,196.43
5	Customer A	2-Mar-10	533	30-Jun-13	1216	15%	266.3539726						30555.94
6	Customer A	2-Apr-10	533	30-Jun-13	1185	15%	259.5636986						30555.94
7	Customer A	5-May-10	533	30-Jun-13	1152	15%	252.3353425						6111.188
8	Customer A	27-May-10	533	30-Jun-13	1130	15%	247.5164384						2199.014
9	Customer A	5-Jul-10	533	30-Jun-13	1091	15%	238.9738356						38866.14
10	Customer A	5-Aug-10	533	30-Jun-13	1060	15%	232.1835616
11	Customer A	1-Sep-10	533	30-Jun-13	1033	15%	226.2694521
12	Customer A	4-Oct-10	533	30-Jun-13	1000	15%	219.0410959
13	Customer A	4-Nov-10	533	30-Jun-13	969	15%	212.2508219
14	Customer A	3-Dec-11	533	30-Jun-13	575	20%	167.9315068
15	Customer A	3-Jan-11	1000	30-Jun-13	909	20%	498.0821918
16	Customer B	14-Jan-11	1000	30-Jun-13	898	20%	492.0547945
17	Customer B	18-Jan-11	22340	30-Jun-13	894	20%	10943.53973
18	Customer B	12-Jul-11	778	30-Jun-13	719	20%	306.5106849
19	Customer B	23-Aug-11	778	30-Jun-13	677	20%	288.6060274
20	Customer B	28-Sep-11	778	30-Jun-13	641	20%	273.2591781
21	Customer B	21-Oct-11	778	30-Jun-13	618	20%	263.4542466
22	Customer B	23-Nov-11	778	30-Jun-13	585	20%	249.3863014
23	Customer B	23-Dec-11	778	30-Jun-13	555	20%	236.5972603
24	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
25	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
26	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
27	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
28	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
29	Customer B	28-Oct-11	534	30-Jun-13	611	20%	178.780274
30	Customer B	10-Dec-11	1174	30-Jun-13	568	20%	365.3873973
31	Customer B	28-Oct-11	1708	30-Jun-13	611	20%	571.8290411
32	Customer B	28-Oct-11	1708	30-Jun-13	611	20%	571.8290411
33	Customer B	28-Oct-11	1708	30-Jun-13	611	20%	571.8290411
34	Customer B	24-Oct-11	866	30-Jun-13	615	20%	291.830137
35	Customer B	28-Oct-11	842	30-Jun-13	611	20%	281.8969863
36	Customer B	10-Dec-11	1574	30-Jun-13	568	20%	489.8805479
37	Customer B	24-Oct-11	134	30-Jun-13	615	20%	45.15616438
38	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
39	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
40	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
41	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
42	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
43	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
44	Customer B	10-Dec-11	1708	30-Jun-13	568	20%	531.5857534
45	Customer B	24-Oct-11	1000	30-Jun-13	615	20%	336.9863014
46	Customer B	28-Oct-11	6500	30-Jun-13	611	20%	2176.164384
47	Customer B	10-Dec-11	10870	30-Jun-13	568	20%	3383.10137
48	Customer B	10-Dec-11	5886	30-Jun-13	568	20%	1831.916712
49	Customer B	21-Dec-11	8800	30-Jun-13	557	20%	2685.808219
50	Customer C	11-Jan-10	13177	30-Jun-13	1266	15%	6855.650137
51	Customer C	5-Feb-10	1086	30-Jun-13	1241	15%	553.86
52	Customer C	5-Mar-10	1086	30-Jun-13	1213	15%	541.3635616
53	Customer C	11-Apr-10	1086	30-Jun-13	1176	15%	524.850411
54	Customer C	1-Apr-10	21720	30-Jun-13	1186	15%	10586.26849
55	Customer C	29-Apr-10	1086	30-Jun-13	1158	15%	516.8169863
56	Customer C	1-Jun-10	1086	30-Jun-13	1125	15%	502.0890411
57	Customer C	29-Jun-10	1086	30-Jun-13	1097	15%	489.5926027
58	Customer C	2-Aug-10	1086	30-Jun-13	1063	15%	474.4183562
59	Customer C	13-Sep-10	1086	30-Jun-13	1021	15%	455.6736986
60	Customer C	4-Oct-10	1086	30-Jun-13	1000	15%	446.3013699
61	Customer C	8-Nov-10	1086	30-Jun-13	965	15%	430.6808219
62	Customer C	8-Dec-10	1086	30-Jun-13	935	15%	417.2917808
63	Customer C	30-Dec-10	15566	30-Jun-13	913	15%	5840.448493
64	Customer C	8-Jun-11	40500	30-Jun-13	753	20%	16710.41096
65	Customer C	14-May-11	500	30-Jun-13	778	20%	213.1506849
66	Customer C	10-Jun-11	500	30-Jun-13	751	20%	205.7534247
67	Customer C	28-Jul-11	22000	30-Jun-13	703	20%	8474.520548
68	Customer C	15-Jun-11	59500	30-Jun-13	746	20%	24321.64384
69	Customer C	28-Dec-11	40000	30-Jun-13	550	20%	12054.79452
70	Customer C	21-Dec-11	1000	30-Jun-13	557	20%	305.2054795
71	Customer C	23-Jul-11	500	30-Jun-13	708	20%	193.9726027
72	Customer C	26-Dec-11	1000	30-Jun-13	552	20%	302.4657534
73	Customer C	25-Nov-11	1000	30-Jun-13	583	20%	319.4520548
74	Customer C	26-Oct-11	1000	30-Jun-13	613	20%	335.890411
75	Customer C	26-Sep-11	1000	30-Jun-13	643	20%	352.3287671
76	Customer C	25-Aug-11	1000	30-Jun-13	675	20%	369.8630137
77	Customer C	28-Jul-11	6460	30-Jun-13	703	20%	2488.427397
78	Customer C	10-Oct-11	27600	30-Jun-13	629	20%	9512.547945
79	Customer C	4-Jun-11	500	30-Jun-13	757	20%	207.3972603
80	Customer C	27-Dec-11	569	30-Jun-13	551	20%	171.7912329
81	Customer C	28-Nov-11	569	30-Jun-13	580	20%	180.8328767
82	Customer C	26-Oct-11	569	30-Jun-13	613	20%	191.1216438
83	Customer C	26-Sep-11	569	30-Jun-13	643	20%	200.4750685
84	Customer C	25-Aug-11	569	30-Jun-13	675	20%	210.4520548
85	Customer C	26-Jul-11	569	30-Jun-13	705	20%	219.8054795
86	Customer C	27-Jun-11	569	30-Jun-13	734	20%	228.8471233
87	Customer C	6-Jun-11	2620	30-Jun-13	755	20%	1083.890411
88	Customer C	26-Sep-11	20280	30-Jun-13	643	20%	7145.227397
Sheet1

Cell Formulas
Range	Formula
G2:G88	G2	=+F2*E2*C2/365
H2:H88	H2	=IFERROR(INDEX($A$2:$A$88,SMALL(IF(FREQUENCY(IF($A$2:$A$88<>"",MATCH($A$2:$A$88,$A$2:$A$88,0)),ROW($A$2:$A$88)-ROW($A$2)+1),ROW($A$2:$A$88)-ROW($A$2)+1),ROWS(H$2:H2))),"")
I2:I4	I2	=SUMIFS($G$2:$G$88,$A$2:$A$88,$H2,$F$2:$F$88,I$1)+SUMIFS($C$2:$C$88,$A$2:$A$88,$H2,$F$2:$F$88,I$1)
J2:J4	J2	=SUMIFS($G$2:$G$88,$A$2:$A$88,$H2,$F$2:$F$88,J$1)+SUMIFS($C$2:$C$88,$A$2:$A$88,$H2,$F$2:$F$88,J$1)+I2*J$1
K2:K4	K2	=SUM(I2:J2)
M5	M5	=SUM(C2:C13,G2:G13)
M7	M7	=+M6*0.2
M8	M8	=+SUM(C14:C15,G14:G15)
M9	M9	=+M5+M7+M8
E2:E88	E2	=+D2-B2
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you very much for your help, it does what I want very well.