Calculate 3M, 6M etc returns based on monthly returns

[Vas7]

Hope you are well.

I have a return series stretching back just over 3 years. As I add new monthly returns I would like to be able to automatically calculate 3-month, 6-month, 1-year returns using the most recent date as the reference point. I know this can be achieved through a combination of the PRODUCT and OFFSET functions but cannot for the life of me get the formulas right. Could someone point me to the correct syntax? For reference the returns are laid out as below, in a horizontal fashion

30-Nov-19​

31-Dec-19​

31-Jan-20​

28-Feb-20​

31-Mar-20​

30-Apr-20​

29-May-20​

30-Jun-20​

31-Jul-20​

31-Aug-20​

30-Sep-20​

30-Oct-20​

30-Nov-20​

31-Dec-20​

1.06%​

3.13%​

-0.45%​

-1.33%​

-2.72%​

1.68%​

1.07%​

0.75%​

0.99%​

0.75%​

-0.09%​

-0.47%​

2.54%​

1.08%​

Many thanks in advance

 

[StephenCrump]

Welcome to the Forum!

You can use OFFSET, but generally it's better to use non-volatile functions where possible. (Volatile functions like OFFSET recalculate every time the worksheet recalculates).

Here's one way you could do this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		30 Nov 2019	30 Dec 2019	31 Jan 2020	29 Feb 2020	31 Mar 2020	30 Apr 2020	31 May 2020	30 Jun 2020	31 Jul 2020	31 Aug 2020	30 Sep 2020	31 Oct 2020	30 Nov 2020	31 Dec 2020
3		1.06%	3.13%	-0.45%	-1.33%	-2.72%	1.68%	1.07%	0.75%	0.99%	0.75%	-0.09%	-0.47%	2.54%	1.08%
4		101.06%	103.13%	99.55%	98.67%	97.28%	101.68%	101.07%	100.75%	100.99%	100.75%	99.91%	99.53%	102.54%	101.08%
5
6		Date	31 Oct 2020
7		1	-0.47%
8		3	0.19%
9		6	3.03%
10		12	4.33%
Sheet3

Cell Formulas
Range	Formula
B4:O4	B4	=1+B3
C7:C10	C7	=IF(MATCH(C$6,$B$2:$O$2,)>=B7,PRODUCT(1+INDEX($B$3:$O$3,MATCH(C$6,$B$2:$O$2,)-B7+1):INDEX($B$3:$O$3,MATCH(C$6,$B$2:$O$2,)))-1,"#N/A")

 

[Saba Sabaratnam]

Hi

Another way may be:

Enter the following formula in B7

=AVERAGEIFS($A$2:N$2,A$1:N$1,">="&EOMONTH($B$6,-2),$A$1:$N$1,"<="&$B$6)

And B8

=AVERAGEIFS($A$2:N$2,A$1:N$1,">="&EOMONTH($B$6,-5),$A$1:$N$1,"<="&$B$6)

Kind regards

Saba

 

[jtakw]

Hi,

Maybe we can do this with SUMIFS, if that's what you're after:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	11/30/2019	12/31/2019	1/31/2020	2/29/2020	3/31/2020	4/30/2020	5/31/2020	6/30/2020	7/31/2020	8/31/2020	9/30/2020	10/31/2020	11/30/2020	12/31/2020	1/31/2021	2/28/2021
2	1.06%	3.13%	-0.45%	-1.33%	-2.72%	1.68%	1.07%	0.75%	0.99%	0.75%	-0.09%	-0.47%	2.54%	1.08%	1.11%	-0.08%
3
4		Months
5	Most recent returns	3	6	12
6	10/31/2020	0.19%	3.00%	4.37%
7	2/28/2021	2.11%	4.09%	6.61%
8	1/31/2021	4.73%	4.92%	5.36%
Sheet777

Cell Formulas
Range	Formula
B6:D8	B6	=SUMIFS($A$2:$P$2,$A$1:$P$1,">="&EDATE($A6,-B$5+1),$A$1:$P$1,"<="&$A6)
A7	A7	=EOMONTH(TODAY(),0)
A8	A8	=EOMONTH(TODAY(),-1)

 

[Vas7]

Stephen,

Many thanks and that worked really well! Two more questions if I may. If I want to calculate QTD what would be the correct syntax? In addition if I have several data points with different start dates how can I reflect this properly? An example below:

31-Mar-17​

30-Apr-17​

31-May-17​

30-Jun-17​

31-Jul-17​

31-Aug-17​

30-Sep-17​

31-Oct-17​

30-Nov-17​

31-Dec-17​

31-Jan-18​

28-Feb-18​

31-Mar-18​

30-Apr-18​

31-May-18​

30-Jun-18​

31-Jul-18​

31-Aug-18​

30-Sep-18​

31-Oct-18​

30-Nov-18​

31-Dec-18​

31-Jan-19​

28-Feb-19​

31-Mar-19​

30-Apr-19​

31-May-19​

30-Jun-19​

31-Jul-19​

31-Aug-19​

30-Sep-19​

31-Oct-19​

30-Nov-19​

31-Dec-19​

31-Jan-20​

28-Feb-20​

31-Mar-20​

30-Apr-20​

29-May-20​

30-Jun-20​

31-Jul-20​

31-Aug-20​

30-Sep-20​

30-Oct-20​

30-Nov-20​

31-Dec-20​

0.36%​

1.07%​

2.31%​

-0.29%​

2.15%​

-0.09%​

3.30%​

1.00%​

1.86%​

1.54%​

3.92%​

-5.41%​

-0.92%​

2.00%​

0.87%​

-0.13%​

2.97%​

-0.20%​

1.51%​

-7.58%​

0.87%​

-6.89%​

6.77%​

3.41%​

1.18%​

1.31%​

-5.95%​

7.02%​

-0.64%​

-3.08%​

2.47%​

2.45%​

2.34%​

3.55%​

-2.64%​

-9.06%​

-11.57%​

10.12%​

4.67%​

0.25%​

3.30%​

5.22%​

-2.82%​

-4.64%​

12.30%​

4.55%​

2.62%​

0.67%​

1.42%​

2.55%​

1.82%​

3.41%​

4.95%​

-1.46%​

-0.11%​

-1.46%​

2.64%​

-1.74%​

1.86%​

0.75%​

-1.39%​

-9.65%​

-1.75%​

-4.85%​

5.91%​

3.65%​

0.67%​

2.27%​

-3.40%​

5.49%​

-0.16%​

-1.45%​

-1.22%​

1.64%​

1.58%​

5.42%​

-1.74%​

-9.97%​

-11.48%​

13.27%​

7.37%​

2.58%​

2.52%​

3.53%​

-1.53%​

-3.11%​

10.56%​

10.30%​

0.09%​

1.54%​

6.18%​

4.14%​

1.04%​

1.25%​

1.03%​

4.47%​

6.70%​

-3.83%​

-2.87%​

-1.43%​

-1.95%​

-3.79%​

1.36%​

-2.85%​

-1.20%​

-8.27%​

1.54%​

-1.81%​

7.73%​

0.31%​

0.93%​

1.85%​

-8.33%​

6.36%​

0.30%​

-3.72%​

1.07%​

4.07%​

0.48%​

7.35%​

-4.73%​

-5.63%​

-14.11%​

9.97%​

5.44%​

5.32%​

11.23%​

1.26%​

-1.52%​

0.66%​

10.06%​

9.92%​

1.55%​

5.48%​

3.20%​

-1.04%​

4.36%​

-0.28%​

3.43%​

7.12%​

-4.44%​

-0.89%​

1.11%​

-4.16%​

-4.86%​

3.55%​

-0.73%​

-1.74%​

-9.59%​

3.65%​

-4.04%​

12.13%​

-0.66%​

0.23%​

-0.44%​

-9.20%​

7.64%​

-2.22%​

-5.28%​

1.75%​

2.42%​

1.74%​

8.70%​

-6.70%​

-7.88%​

-14.94%​

9.89%​

2.01%​

2.58%​

6.07%​

2.75%​

1.05%​

-0.32%​

14.18%​

9.57%​

-1.24%​

-4.39%​

11.31%​

6.31%​

-3.63%​

11.80%​

6.51%​

 

[StephenCrump]

Like this perhaps:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	Series	31 Mar 2017	30 Apr 2017	31 May 2017	30 Jun 2017	31 Jul 2017	31 Aug 2017	30 Sep 2017	31 Oct 2017	30 Nov 2017	31 Dec 2017	31 Jan 2018	28 Feb 2018	31 Mar 2018
3	A	0.36%	1.07%	2.31%	-0.29%	2.15%	-0.09%	3.30%	1.00%	1.86%	1.54%	3.92%	-5.41%
4	B					2.62%	0.67%	1.42%	2.55%	1.82%	3.41%	4.95%	-1.46%
5	C			0.09%	1.54%	6.18%	4.14%	1.04%	1.25%	1.03%	4.47%	6.70%	-3.83%
6	D				1.55%	5.48%	3.20%	-1.04%	4.36%	-0.28%	3.43%	7.12%	-4.44%
7
8		Date	31 Aug 2017
9
10			1	3	6	12
11		A	-0.09%	1.76%	5.61%	-
12		B	0.67%	-	-	-
13		C	4.14%	12.28%	-	-
14		D	3.20%	10.54%	-	-
15
16		QTD	31 Mar 2018	---> same as	28 Feb 2018
17
18			3		2
19		A	-1.70%		-1.70%
20		B	3.42%		3.42%
21		C	2.61%		2.61%
22		D	2.36%		2.36%
Sheet3

Cell Formulas
Range	Formula
C2:N2	C2	=EDATE($B2,COLUMNS($C2:C2))
C11:F14	C11	=IFERROR(IF(MATCH($C$8,$B$2:$N$2,)>=C$10,PRODUCT(1+INDEX($B3:$N3,MATCH($C$8,$B$2:$N$2,)-C$10+1):INDEX($B3:$N3,MATCH($C$8,$B$2:$N$2,)))-1,"-"),"-")
C19:C22	C19	=IFERROR(IF(MATCH($C$16,$B$2:$N$2,)>=C$18,PRODUCT(1+INDEX($B3:$N3,MATCH($C$16,$B$2:$N$2,)-C$18+1):INDEX($B3:$N3,MATCH($C$16,$B$2:$N$2,)))-1,"-"),"-")
E19:E22	E19	=IFERROR(IF(MATCH($E$16,$B$2:$N$2,)>=E$18,PRODUCT(1+INDEX($B3:$N3,MATCH($E$16,$B$2:$N$2,)-E$18+1):INDEX($B3:$N3,MATCH($E$16,$B$2:$N$2,)))-1,"-"),"-")

 

[Vas7]

Once again, that worked really well! The only thing I am still not sure is the QTD. For example if it was the end of November 2020, how could I calculate the numbers? Obviously the 3M numbers would be September to November but the QTD is in fact October and November only...

 

[StephenCrump]

Try this (assuming YTD is based on years ending 30 June):

	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Series	31 Mar 2017	30 Apr 2017	31 May 2017	30 Jun 2017	31 Jul 2017	31 Aug 2017	30 Sep 2017	31 Oct 2017	30 Nov 2017	31 Dec 2017	31 Jan 2018	28 Feb 2018
3	A	0.36%	1.07%	2.31%	-0.29%	2.15%	-0.09%	3.30%	1.00%	1.86%	1.54%	3.92%	-5.41%
4	B					2.62%	0.67%	1.42%	2.55%	1.82%	3.41%	4.95%	-1.46%
5	C			0.09%	1.54%	6.18%	4.14%	1.04%	1.25%	1.03%	4.47%	6.70%	-3.83%
6	D				1.55%	5.48%	3.20%	-1.04%	4.36%	-0.28%	3.43%	7.12%	-4.44%
7
8		Date	28 Feb 2018
9
10			1	3	6	12	QTD	YTD
11		A	-5.41%	-0.19%	6.07%	12.02%	-1.70%	8.26%
12		B	-1.46%	6.94%	13.25%	-	3.42%	17.00%
13		C	-3.83%	7.20%	10.80%	-	2.61%	22.52%
14		D	-4.44%	5.87%	9.04%	-	2.36%	18.69%
Sheet3

Cell Formulas
Range	Formula
C2:M2	C2	=EOMONTH(B2,1)
C11:F14	C11	=IFERROR(PRODUCT(1+INDEX($B3:$M3,MATCH(EOMONTH($C$8,1-C$10),$B$2:$M$2,)):INDEX($B3:$M3,MATCH($C$8,$B$2:$M$2,)))-1,"-")
G11:G14	G11	=IFERROR(PRODUCT(1+INDEX($B3:$M3,MATCH(EOMONTH($C$8,MOD(-MONTH($C$8),3)-2),$B$2:$M$2,)):INDEX($B3:$M3,MATCH($C$8,$B$2:$M$2,)))-1,"-")
H11:H14	H11	=IFERROR(PRODUCT(1+INDEX($B3:$M3,MATCH(DATE(YEAR($C$8+184)-1,7,31),$B$2:$M$2,)):INDEX($B3:$M3,MATCH($C$8,$B$2:$M$2,)))-1,"-")

 

[Vas7]

Many thanks sir. One last question though ( I promise). What is the best syntax for Since Inception numbers?

 

[StephenCrump]

What is the best syntax for Since Inception numbers?

That depends on how your data is organised. To date, I have assumed that quarterly returns are for the full quarter. If we allow for inception dates and fractional quarters, then perhaps something like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Cumulative to
2	Series	Inception	28 Feb 2017	31 Mar 2017	30 Apr 2017	31 May 2017	30 Jun 2017	31 Jul 2017	31 Aug 2017	30 Sep 2017	31 Oct 2017	30 Nov 2017	31 Dec 2017	31 Jan 2018	28 Feb 2018
3	A	1 Jul 2015	12.76%	0.36%	1.07%	2.31%	-0.29%	2.15%	-0.09%	3.30%	1.00%	1.86%	1.54%	3.92%	-5.41%
4	B	15 Jul 2017						2.62%	0.67%	1.42%	2.55%	1.82%	3.41%	4.95%	-1.46%
5	C	1 Apr 2017			0.82%	0.09%	1.54%	6.18%	4.14%	1.04%	1.25%	1.03%	4.47%	6.70%	-3.83%
6	D	26 Jun 2017					1.55%	5.48%	3.20%	-1.04%	4.36%	-0.28%	3.43%	7.12%	-4.44%
7
8		Date	30 Jun 2017
9
10			1	3	6	12	QTD	YTD	SI
11		A	-0.29%	3.10%	-	-	3.10%	-	16.68%
12		B	-	-	-	-	-	-	-
13		C	1.54%	2.46%	-	-	2.46%	-	2.46%
14		D	-	-	-	-	-	-	1.55%
Sheet1

Cell Formulas
Range	Formula
E2:O2	E2	=EOMONTH(D2,1)
C11:F14	C11	=IFERROR(IF(EOMONTH($B3-1,0)<EOMONTH($C$8,1-C$10),PRODUCT(1+INDEX($D3:$O3,MATCH(EOMONTH($C$8,1-C$10),$D$2:$O$2,)):INDEX($D3:$O3,MATCH($C$8,$D$2:$O$2,)))-1,"-"),"-")
G11:G14	G11	=IFERROR(IF(EOMONTH($B3-1,0)<EOMONTH($C$8,MOD(-MONTH($C$8),3)-2),PRODUCT(1+INDEX($D3:$O3,MATCH(EOMONTH($C$8,MOD(-MONTH($C$8),3)-2),$D$2:$O$2,)):INDEX($D3:$O3,MATCH($C$8,$D$2:$O$2,)))-1,"-"),"-")
H11:H14	H11	=IFERROR(IF(EOMONTH($B3-1,0)<DATE(YEAR($C$8+184)-1,7,31),PRODUCT(1+INDEX($D3:$O3,MATCH(DATE(YEAR($C$8+184)-1,7,31),$D$2:$O$2,)):INDEX($D3:$O3,MATCH($C$8,$D$2:$O$2,)))-1,"-"),"-")
I11:I14	I11	=IF(B3<=C$8,PRODUCT(1+C3:INDEX($D3:$O3,MATCH($C$8,$D$2:$O$2,)))-1,"-")

I haven't tested this, so please check carefully that it does what you want.

For the example above, a 3-month return is shown for Series C, because inception date was the first day of the period. However, a 1-month return is not shown for D, because it started later than 1 June.

(Note that in this case, YTD figures aren't shown at all, because the data is incomplete - the formulae can't find start month returns for the month ending 31 July 2016)