Pro-Rata Costs Between Dates

*shudder*

Well-known Member
Joined
Aug 20, 2009
Messages
510
Office Version
  1. 2016
Platform
  1. Windows
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:

StartEndPrice
01/03/201628/02/201740,316
01/03/201728/02/201842,395
01/03/201828/02/201943,988
01/03/201929/02/202031,223
01/03/202029/06/202013,199
30/06/202029/06/202139,809
30/06/202129/06/202249,371

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

ResultsWorkings
StartEndTotal CostsDays CostsDaysCostsTotal DaysTotal Costs
06/04/201605/04/2017
40,521.09​
329
36,339.63
36
4,181.47
365
40,521.09​
06/04/201705/04/2018
42,552.51​
329
38,213.96
36
4,338.54
365
42,552.51​
06/04/201805/04/2019
42,720.57​
329
39,649.46
36
3,071.11
365
42,720.57​
06/04/201905/04/2020
32,078.79​
330
28,151.89
36
3,926.91
366
32,078.79​
06/04/202005/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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi Shudder,

TEST FILE.xlsm
ABCDEFGHI
1StartEndPrice
21-Mar-1628-Feb-1740,316
31-Mar-1728-Feb-1842,395
41-Mar-1828-Feb-1943,988
51-Mar-1929-Feb-2031,223
61-Mar-2029-Jun-2013,199
730-Jun-2029-Jun-2139,809
830-Jun-2129-Jun-2249,371
9
10ResultsWorkings
11StartEndPriceDaysCostsDaysCostsTotal DaysTotal Costs
126-Apr-165-Apr-17 40,521.05 32936,339.63364,181.4236540,521.05
136-Apr-175-Apr-18 42,552.12 32938,213.58364,338.5436542,552.12
146-Apr-185-Apr-19 42,720.57 32939,649.46363,071.1136542,720.57
156-Apr-195-Apr-20 32,078.86 33028,151.89363,926.9836632,078.86
166-Apr-205-Apr-21 39,810.44 859,272.0228030,538.4136539,810.44
Sheet5 (3)
Cell Formulas
RangeFormula
C12:C16C12=E12+G12
D12:D16D12=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:E16E12=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:F16F12=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:G16G12=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:I16H12=D12+F12
 
Upvote 0
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).

Cell Formulas
RangeFormula
G1:L1G1=F1+1
F2:M8F2=MAX(MIN($B2,DATE(F$1+1,4,5))-MAX($A2,DATE(F$1,4,6))+1,0)*$D2
D2:D8D2=(C2/(B2-A2+1))
F10:M10F10=SUM(F2:F9)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top