Pro Rata a Sumproduct Calculation

[Sundance_Kid]

Hi,

I have included a sample to help explain.

I have been able to work out the overall cumulative/effective discount over the number of years to be 14.5%.

However, I also want to factor into the calculation for the 14.5% that for 2024 and 2027 the discounted price is only available for 6 months of each year whereas for 2025 & 2026 the discount price is available for the 12 months.

Is there a way to pro rate the overall calculation to take into account the 6 & 12 months?

Thanks

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
2			Months		6	12	12	6
3	Price		Year	% Share	2024	2025	2026	2027
4			Product A	20	500	510	520	530
5			Product B	30	400	410	420	430
6			Product C	20	300	310	320	330
7			Product D	20	200	210	220	230
8			Product E	10	100	110	120	130
9
10
11			Months		6	12	12	6
12	Discounted Price		Year	% Share	2024	2025	2026	2027
13			Product A	20	450	460	470	480
14			Product B	30	350	360	370	380
15			Product C	20	250	260	270	280
16			Product D	20	150	160	170	180
17			Product E	10	50	60	70	80
18
19			Months		6	12	12	6
20	Discount		Year	% Share	2024	2025	2026	2027
21			Product A	20	-10%	-10%	-10%	-9%
22			Product B	30	-13%	-12%	-12%	-12%
23			Product C	20	-17%	-16%	-16%	-15%
24			Product D	20	-25%	-24%	-23%	-22%
25			Product E	10	-50%	-45%	-42%	-38%		-14.5%		Cumulative Discount
Sheet1

Cell Formulas
Range	Formula
E13:E17	E13	=E4
D21:D25	D21	=D13
E21:E25	E21	=E4
F21:I25	F21	=SUM(F13-F4)/F4
K25	K25	=(SUMPRODUCT((F13:I17)*(E13:E17))-SUMPRODUCT((F4:I8)*(E4:E8)))/SUMPRODUCT((F4:I8)*(E4:E8))

 

[myall_blues]

Could you just annualise the discounted price to represent the 'average' price over 12 months?

i.e Formula in O13 is

=((F4*(1-ABS(F21))*F$11)+(F4*(12-F$11)))/12

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2			Months		6	12	12	6
3	Price		Year	% Share	2024	2025	2026	2027
4			Product A	20	500	510	520	530
5			Product B	30	400	410	420	430
6			Product C	20	300	310	320	330
7			Product D	20	200	210	220	230
8			Product E	10	100	110	120	130
9
10
11			Months		6	12	12	6
12	Discounted Price		Year	% Share	2024	2025	2026	2027		Annualised Discounted Price		Year	% Share	2024	2025	2026	2027
13			Product A	20	450	460	470	480				Product A	20	475	460	470	505
14			Product B	30	350	360	370	380				Product B	30	375	360	370	405
15			Product C	20	250	260	270	280				Product C	20	275	260	270	305
16			Product D	20	150	160	170	180				Product D	20	175	160	170	205
17			Product E	10	50	60	70	80				Product E	10	75	60	70	105
18
19			Months		6	12	12	6
20	Discount		Year	% Share	2024	2025	2026	2027
21			Product A	20	-0.1	-0.09804	-0.09615	-0.09434
22			Product B	30	-0.125	-0.12195	-0.11905	-0.11628
23			Product C	20	-0.16667	-0.16129	-0.15625	-0.15152
24			Product D	20	-0.25	-0.2381	-0.22727	-0.21739
25			Product E	10	-0.5	-0.45455	-0.41667	-0.38462		-10.9%		Cumulative Discount
Sheet1

Cell Formulas
Range	Formula
N13:N17	N13	=E13
O13:R17	O13	=((F4*(1-ABS(F21))*F$11)+(F4*(12-F$11)))/12
E13:E17	E13	=E4
D21:D25	D21	=D13
E21:E25	E21	=E4
F21:I25	F21	=SUM(F13-F4)/F4
K25	K25	=(SUMPRODUCT((O13:R17)*(E13:E17))-SUMPRODUCT((F4:I8)*(E4:E8)))/SUMPRODUCT((F4:I8)*(E4:E8))