Pro-Rata Costs Between Dates

[*shudder*]

Hi Guys,

I am hoping somebody can help with a formula to re-allocate contract costs over a given period. This will need to take the contract costs when they straddle two or more periods.

The data I have for each contract is set out like this:

Start	End	Price
01/03/2016	28/02/2017	40,316
01/03/2017	28/02/2018	42,395
01/03/2018	28/02/2019	43,988
01/03/2019	29/02/2020	31,223
01/03/2020	29/06/2020	13,199
30/06/2020	29/06/2021	39,809
30/06/2021	29/06/2022	49,371

And these are the expected results with my workings (I have colour coded relevant costs from the periods):

Results				Workings
Start	End	Total Costs		Days	Costs		Days	Costs		Total Days	Total Costs
06/04/2016	05/04/2017	40,521.09​		329	36,339.63​		36	4,181.47​		365	40,521.09​
06/04/2017	05/04/2018	42,552.51​		329	38,213.96​		36	4,338.54​		365	42,552.51​
06/04/2018	05/04/2019	42,720.57​		329	39,649.46​		36	3,071.11​		365	42,720.57​
06/04/2019	05/04/2020	32,078.79​		330	28,151.89​		36	3,926.91​		366	32,078.79​
06/04/2020	05/04/2021	39,810.29​		85	9,271.87​		280	30,538.42​		365	39,810.29​

Hoping somebody can give me a steer on this.

Thanks in advance.

 

[hrayani]

Hi Shudder,

TEST FILE.xlsm
	A	B	C	D	E	F	G	H	I
1	Start	End	Price
2	1-Mar-16	28-Feb-17	40,316
3	1-Mar-17	28-Feb-18	42,395
4	1-Mar-18	28-Feb-19	43,988
5	1-Mar-19	29-Feb-20	31,223
6	1-Mar-20	29-Jun-20	13,199
7	30-Jun-20	29-Jun-21	39,809
8	30-Jun-21	29-Jun-22	49,371
9
10	Results			Workings
11	Start	End	Price	Days	Costs	Days	Costs	Total Days	Total Costs
12	6-Apr-16	5-Apr-17	40,521.05	329	36,339.63	36	4,181.42	365	40,521.05
13	6-Apr-17	5-Apr-18	42,552.12	329	38,213.58	36	4,338.54	365	42,552.12
14	6-Apr-18	5-Apr-19	42,720.57	329	39,649.46	36	3,071.11	365	42,720.57
15	6-Apr-19	5-Apr-20	32,078.86	330	28,151.89	36	3,926.98	366	32,078.86
16	6-Apr-20	5-Apr-21	39,810.44	85	9,272.02	280	30,538.41	365	39,810.44
Sheet5 (3)

Cell Formulas
Range	Formula
C12:C16	C12	=E12+G12
D12:D16	D12	=ABS(A12-INDEX($B$2:$B$8,MATCH(INDEX($C$2:$C$8,MATCH(B12,$B$2:$B$8,1)),$C$2:$C$8,0)))+1
E12:E16	E12	=D12*INDEX($C$2:$C$8,MATCH(A12,$A$2:$A$8,1))/(OFFSET(INDEX($C$2:$C$8,MATCH(A12,$A$2:$A$8,1)),0,-1)-OFFSET(INDEX($C$2:$C$8,MATCH(A12,$A$2:$A$8,1)),0,-2)+1)
F12:F16	F12	=ABS(B12-INDEX($B$2:$B$8,MATCH(INDEX($C$2:$C$8,MATCH(B12,$B$2:$B$8,1)),$C$2:$C$8,0)))
G12:G16	G12	=INDEX($C$2:$C$8,MATCH(B12,$A$2:$A$8,1))/(OFFSET(INDEX($C$2:$C$8,MATCH(B12,$A$2:$A$8,1)),0,-1)-OFFSET(INDEX($C$2:$C$8,MATCH(B12,$A$2:$A$8,1)),0,-2)+1)*F12
H12:I16	H12	=D12+F12

 

[Toadstool]

Hi Shudder,
I see @hrayani has already given you a solution so I'll take a different approach.
It appears you're splitting the costs across UK tax years so I'm using column headings to denote which year (e.g. 2016 = 6th April 2016 to 5th April 2017).

Shudder.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Start	End	Price	Daily Price		2016	2017	2018	2019	2020	2021	2022	2023
2	01-Mar-16	28-Feb-17	40,316	110.4547945		36,339.63	0.00	0.00	0.00	0.00	0.00	0.00	0.00
3	01-Mar-17	28-Feb-18	42,395	116.1506849		4,181.42	38,213.58	0.00	0.00	0.00	0.00	0.00	0.00
4	01-Mar-18	28-Feb-19	43,988	120.5150685		0.00	4,338.54	39,649.46	0.00	0.00	0.00	0.00	0.00
5	01-Mar-19	29-Feb-20	31,223	85.30874317		0.00	0.00	3,071.11	28,151.89	0.00	0.00	0.00	0.00
6	01-Mar-20	29-Jun-20	13,199	109.0826446		0.00	0.00	0.00	3,926.98	9,272.02	0.00	0.00	0.00
7	30-Jun-20	29-Jun-21	39,809	109.0657534		0.00	0.00	0.00	0.00	30,538.41	9,270.59	0.00	0.00
8	30-Jun-21	29-Jun-22	49,371	135.2630137		0.00	0.00	0.00	0.00	0.00	37,873.64	11,497.36	0.00
9
10						40,521.05	42,552.12	42,720.57	32,078.86	39,810.44	47,144.23	11,497.36	0.00
Sheet2 (2)

Cell Formulas
Range	Formula
G1:L1	G1	=F1+1
F2:M8	F2	=MAX(MIN($B2,DATE(F$1+1,4,5))-MAX($A2,DATE(F$1,4,6))+1,0)*$D2
D2:D8	D2	=(C2/(B2-A2+1))
F10:M10	F10	=SUM(F2:F9)