Calculate QTD, YTD, 1 Year returns based on monthly percentage returns

[smogtm]

Hi all, I've been having some issues with calculating QTD, YTD, 1 Year, 2 Year (annualized), 3 Year (annualized) and 5 Year (annualized) returns based on monthly percentage returns. I've tried different methods but all yield results with huge differences. What would be the best (and correct) way to calculate?

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Calculate the linked returns below as of 6/30/2024
2
3	Return Type	Return
4	QTD
5	YTD
6	1 Year
7	2 Year (Annualized)
8	3 Year (Annualized)
9	5 Year (Annualized)
10
11
12	Return Stream
13	Effective Date	Fund Return
14	2024-06-30	-0.0226
15	2024-05-31	1.5842
16	2024-04-30	-0.7725
17	2024-03-31	1.6788
18	2024-02-29	1.3427
19	2024-01-31	0.079
20	2023-12-31	2.0848
21	2023-11-30	3.3265
22	2023-10-31	-1.2318
23	2023-09-30	-1.3259
24	2023-08-31	-0.0674
25	2023-07-31	1.3981
26	2023-06-30	1.2983
27	2023-05-31	-0.3528
28	2023-04-30	0.4592
29	2023-03-31	1.0337
30	2023-02-28	-0.8657
31	2023-01-31	2.5849
32	2022-12-31	-0.3671
33	2022-11-30	1.978
34	2022-10-31	1.0943
35	2022-09-30	-3.5067
36	2022-08-31	-2.51
37	2022-07-31	1.6935
38	2022-06-30	-2.4146
39	2022-05-31	-0.7742
40	2022-04-30	-2.7677
41	2022-03-31	0.8651
42	2022-02-28	-0.9296
43	2022-01-31	-2.7082
44	2021-12-31	0.2039
45	2021-11-30	-0.05
46	2021-10-31	1.205
47	2021-09-30	-1.1332
48	2021-08-31	3.8095
49	2021-07-31	0.1383
50	2021-06-30	1.2122
51	2021-05-31	3.0055
52	2021-04-30	2.3394
53	2021-03-31	4.2013
54	2021-02-28	3.2698
55	2021-01-31	-0.5987
56	2020-12-31	3.3248
57	2020-11-30	7.0525
58	2020-10-31	0.2888
59	2020-09-30	-0.6556
60	2020-08-31	4.3817
61	2020-07-31	2.3027
62	2020-06-30	2.0331
63	2020-05-31	0.8836
64	2020-04-30	4.3488
65	2020-03-31	-7.8618
66	2020-02-29	-1.8809
67	2020-01-31	0.0172
68	2019-12-31	1.6215
69	2019-11-30	1.6939
70	2019-10-31	1.3133
71	2019-09-30	0.1902
72	2019-08-31	0.0964
73	2019-07-31	0.3559
Sheet1

 

[JamesCanale]

I'm not very confident that this is the way you want to calculate the returns.

MrExcelPlayground23.xlsx
	A	B
1	Calculate the linked returns below as of 6/30/2024
2	Data Date	6/30/2024
3	Return Type	Return
4	QTD	0.0078
5	YTD	0.0393
6	1 Year	0.0826
7	2 Year (Annualized)	0.0542
8	3 Year (Annualized)	0.0189
9	5 Year (Annualized)	0.0905
10
11
12	Return Stream
13	Effective Date	Fund Return
14	6/30/2024	-0.0226
15	5/31/2024	1.5842
16	4/30/2024	-0.7725
17	3/31/2024	1.6788
18	2/29/2024	1.3427
19	1/31/2024	0.079
20	12/31/2023	2.0848
21	11/30/2023	3.3265
22	10/31/2023	-1.2318
23	9/30/2023	-1.3259
24	8/31/2023	-0.0674
25	7/31/2023	1.3981
26	6/30/2023	1.2983
27	5/31/2023	-0.3528
28	4/30/2023	0.4592
29	3/31/2023	1.0337
30	2/28/2023	-0.8657
31	1/31/2023	2.5849
32	12/31/2022	-0.3671
33	11/30/2022	1.978
34	10/31/2022	1.0943
35	9/30/2022	-3.5067
36	8/31/2022	-2.51
37	7/31/2022	1.6935
38	6/30/2022	-2.4146
39	5/31/2022	-0.7742
40	4/30/2022	-2.7677
41	3/31/2022	0.8651
42	2/28/2022	-0.9296
43	1/31/2022	-2.7082
44	12/31/2021	0.2039
45	11/30/2021	-0.05
46	10/31/2021	1.205
47	9/30/2021	-1.1332
48	8/31/2021	3.8095
49	7/31/2021	0.1383
50	6/30/2021	1.2122
51	5/31/2021	3.0055
52	4/30/2021	2.3394
53	3/31/2021	4.2013
54	2/28/2021	3.2698
55	1/31/2021	-0.5987
56	12/31/2020	3.3248
57	11/30/2020	7.0525
58	10/31/2020	0.2888
59	9/30/2020	-0.6556
60	8/31/2020	4.3817
61	7/31/2020	2.3027
62	6/30/2020	2.0331
63	5/31/2020	0.8836
64	4/30/2020	4.3488
65	3/31/2020	-7.8618
66	2/29/2020	-1.8809
67	1/31/2020	0.0172
68	12/31/2019	1.6215
69	11/30/2019	1.6939
70	10/31/2019	1.3133
71	9/30/2019	0.1902
72	8/31/2019	0.0964
73	7/31/2019	0.3559
Sheet26

Cell Formulas
Range	Formula
B4	B4	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,DATE(YEAR(datadate),INT((MONTH(datadate)-1)/3)*3+1,1),returns,FILTER(fundreturn,effectivedate>=periodstart),tot,PRODUCT(1+returns/100)-1,tot)
B5	B5	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,DATE(YEAR(datadate),1,1),returns,FILTER(fundreturn,effectivedate>=periodstart),tot,PRODUCT(1+returns/100)-1,tot)
B6	B6	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,EDATE(datadate,-12)+1,returns,FILTER(fundreturn,effectivedate>=periodstart),tot,PRODUCT(1+returns/100)-1,tot)
B7	B7	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,EDATE(datadate,-24)+1,returns,FILTER(fundreturn,effectivedate>=periodstart),tot,(PRODUCT(1+returns/100)-1)/2,tot)
B8	B8	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,EDATE(datadate,-36)+1,returns,FILTER(fundreturn,effectivedate>=periodstart),tot,(PRODUCT(1+returns/100)-1)/3,tot)
B9	B9	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,EDATE(datadate,-60)+1,returns,FILTER(fundreturn,effectivedate>=periodstart),tot,(PRODUCT(1+returns/100)-1)/5,tot)

 

[StephenCrump]

@smogtm, welcome to the Forum!

Here's a more general formula. The differences highlighted below are arithmetic vs geometric average - the convention here is to use geometric, i.e. column E.

(Like James, I have assumed YTD means from 1 January)

	A	B	C	D	E
1
2	Data Date	30 Jun 2024
3	Return Type	Period	Post#2	Total	Annualised
4	QTD	3	0.78%	0.78%	3.14%
5	YTD	6	3.93%	3.93%	8.01%
6	1 Year	12	8.26%	8.26%	8.26%
7	2 Year (Annualized)	24	5.42%	10.83%	5.28%
8	3 Year (Annualized)	36	1.89%	5.68%	1.86%
9	5 Year (Annualized)	60	9.05%	45.26%	7.75%
10
11
12	Return Stream
13	Effective Date	Fund Return
14	30 Jun 2024	-0.0226
15	31 May 2024	1.5842
16	30 Apr 2024	-0.7725
17	31 Mar 2024	1.6788
18	29 Feb 2024	1.3427
19	31 Jan 2024	0.0790
20	31 Dec 2023	2.0848
21	30 Nov 2023	3.3265
22	31 Oct 2023	-1.2318
23	30 Sep 2023	-1.3259
24	31 Aug 2023	-0.0674
25	31 Jul 2023	1.3981
26	30 Jun 2023	1.2983
27	31 May 2023	-0.3528
28	30 Apr 2023	0.4592
29	31 Mar 2023	1.0337
30	28 Feb 2023	-0.8657
31	31 Jan 2023	2.5849
32	31 Dec 2022	-0.3671
33	30 Nov 2022	1.9780
34	31 Oct 2022	1.0943
35	30 Sep 2022	-3.5067
36	31 Aug 2022	-2.5100
37	31 Jul 2022	1.6935
38	30 Jun 2022	-2.4146
39	31 May 2022	-0.7742
40	30 Apr 2022	-2.7677
41	31 Mar 2022	0.8651
42	28 Feb 2022	-0.9296
43	31 Jan 2022	-2.7082
44	31 Dec 2021	0.2039
45	30 Nov 2021	-0.0500
46	31 Oct 2021	1.2050
47	30 Sep 2021	-1.1332
48	31 Aug 2021	3.8095
49	31 Jul 2021	0.1383
50	30 Jun 2021	1.2122
51	31 May 2021	3.0055
52	30 Apr 2021	2.3394
53	31 Mar 2021	4.2013
54	28 Feb 2021	3.2698
55	31 Jan 2021	-0.5987
56	31 Dec 2020	3.3248
57	30 Nov 2020	7.0525
58	31 Oct 2020	0.2888
59	30 Sep 2020	-0.6556
60	31 Aug 2020	4.3817
61	31 Jul 2020	2.3027
62	30 Jun 2020	2.0331
63	31 May 2020	0.8836
64	30 Apr 2020	4.3488
65	31 Mar 2020	-7.8618
66	29 Feb 2020	-1.8809
67	31 Jan 2020	0.0172
68	31 Dec 2019	1.6215
69	30 Nov 2019	1.6939
70	31 Oct 2019	1.3133
71	30 Sep 2019	0.1902
72	31 Aug 2019	0.0964
73	31 Jul 2019	0.3559
74
Sheet1

Cell Formulas
Range	Formula
C4	C4	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,DATE(YEAR(datadate),INT((MONTH(datadate)-1)/3)*3+1,1),returns,FILTER(fundreturn,effectivedate>=periodstart),tot,PRODUCT(1+returns/100)-1,tot)
D4:D9	D4	=LET(N,B4,r,B$14:B$73,m,MATCH(B$2,A$14:A$73,),IF(m+N-1>ROWS(r),"n/a",PRODUCT(1+INDEX(r,m):INDEX(r,m+N-1)/100)-1))
E4:E9	E4	=IF(D4="n/a","n/a",(1+D4)^(12/B4)-1)
C5	C5	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,DATE(YEAR(datadate),1,1),returns,FILTER(fundreturn,effectivedate>=periodstart),tot,PRODUCT(1+returns/100)-1,tot)
C6	C6	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,EDATE(datadate,-12)+1,returns,FILTER(fundreturn,effectivedate>=periodstart),tot,PRODUCT(1+returns/100)-1,tot)
C7	C7	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,EDATE(datadate,-24)+1,returns,FILTER(fundreturn,effectivedate>=periodstart),tot,(PRODUCT(1+returns/100)-1)/2,tot)
C8	C8	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,EDATE(datadate,-36)+1,returns,FILTER(fundreturn,effectivedate>=periodstart),tot,(PRODUCT(1+returns/100)-1)/3,tot)
C9	C9	=LET(fundreturn,B14:B73,effectivedate,A14:A73,datadate,B2,periodstart,EDATE(datadate,-60)+1,returns,FILTER(fundreturn,effectivedate>=periodstart),tot,(PRODUCT(1+returns/100)-1)/5,tot)
B4	B4	=1+MOD(MONTH(B2)-1,3)
B5	B5	=MONTH(B2)

The Post#2 formulae don't work correctly for other end dates, for example:

Data Date	29 Feb 2024
Return Type	Period	Post#2	Total	Annualised
QTD	2	3.93%	1.42%	8.85%
YTD	2	3.93%	1.42%	8.85%
1 Year	12	10.91%	8.24%	8.24%
2 Year (Annualized)	24	2.63%	2.72%	1.35%
3 Year (Annualized)	36	5.83%	14.66%	4.67%
5 Year (Annualized)	60	9.05%	n/a	n/a

 

[smogtm]

Thank you both! However for some reason when I try to use same formulas I keep getting error messages that the function is not valid I'm sure I'm messing something up as I am very new to all this. Should these formulas work copy/paste if sheet structure matches with what was posted above?

 

[smogtm]

This is what my formula bar looks like, but when I try to enter it I get an error message that there is an error with this formula. I've also tried changing argument names but with no luck as well.

 

[StephenCrump]

This looks like an assignment? You won't learn much if you simply ask someone to give you answers.

Your formula looks OK to me, but it's hard to tell from just a picture.

What formula separators do you use in Excel, e.g. perhaps my "," should be ";" for your Excel version?