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

Vas7

New Member
Joined
Feb 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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:

ABCDEFGHIJKLMNO
1
230 Nov 201930 Dec 201931 Jan 202029 Feb 202031 Mar 202030 Apr 202031 May 202030 Jun 202031 Jul 202031 Aug 202030 Sep 202031 Oct 202030 Nov 202031 Dec 2020
31.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%
4101.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
6Date31 Oct 2020
71-0.47%
830.19%
963.03%
10124.33%
Sheet3
Cell Formulas
RangeFormula
B4:O4B4=1+B3
C7:C10C7=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

Board Regular
Joined
May 26, 2018
Messages
196
Hi

Another way may be:

1613689734171.png


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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

Book3.xlsx
ABCDEFGHIJKLMNOP
111/30/201912/31/20191/31/20202/29/20203/31/20204/30/20205/31/20206/30/20207/31/20208/31/20209/30/202010/31/202011/30/202012/31/20201/31/20212/28/2021
21.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
4Months
5Most recent returns3612
610/31/20200.19%3.00%4.37%
72/28/20212.11%4.09%6.61%
81/31/20214.73%4.92%5.36%
Sheet777
Cell Formulas
RangeFormula
B6:D8B6=SUMIFS($A$2:$P$2,$A$1:$P$1,">="&EDATE($A6,-B$5+1),$A$1:$P$1,"<="&$A6)
A7A7=EOMONTH(TODAY(),0)
A8A8=EOMONTH(TODAY(),-1)
 

Vas7

New Member
Joined
Feb 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Like this perhaps:

ABCDEFGHIJKLMN
1
2Series31 Mar 201730 Apr 201731 May 201730 Jun 201731 Jul 201731 Aug 201730 Sep 201731 Oct 201730 Nov 201731 Dec 201731 Jan 201828 Feb 201831 Mar 2018
3A0.36%1.07%2.31%-0.29%2.15%-0.09%3.30%1.00%1.86%1.54%3.92%-5.41%
4B2.62%0.67%1.42%2.55%1.82%3.41%4.95%-1.46%
5C0.09%1.54%6.18%4.14%1.04%1.25%1.03%4.47%6.70%-3.83%
6D1.55%5.48%3.20%-1.04%4.36%-0.28%3.43%7.12%-4.44%
7
8Date31 Aug 2017
9
1013612
11A-0.09%1.76%5.61%-
12B0.67%---
13C4.14%12.28%--
14D3.20%10.54%--
15
16QTD31 Mar 2018---> same as28 Feb 2018
17
1832
19A-1.70%-1.70%
20B3.42%3.42%
21C2.61%2.61%
22D2.36%2.36%
Sheet3
Cell Formulas
RangeFormula
C2:N2C2=EDATE($B2,COLUMNS($C2:C2))
C11:F14C11=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:C22C19=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:E22E19=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

New Member
Joined
Feb 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Try this (assuming YTD is based on years ending 30 June):

ABCDEFGHIJKLM
1
2Series31 Mar 201730 Apr 201731 May 201730 Jun 201731 Jul 201731 Aug 201730 Sep 201731 Oct 201730 Nov 201731 Dec 201731 Jan 201828 Feb 2018
3A0.36%1.07%2.31%-0.29%2.15%-0.09%3.30%1.00%1.86%1.54%3.92%-5.41%
4B2.62%0.67%1.42%2.55%1.82%3.41%4.95%-1.46%
5C0.09%1.54%6.18%4.14%1.04%1.25%1.03%4.47%6.70%-3.83%
6D1.55%5.48%3.20%-1.04%4.36%-0.28%3.43%7.12%-4.44%
7
8Date28 Feb 2018
9
1013612QTDYTD
11A-5.41%-0.19%6.07%12.02%-1.70%8.26%
12B-1.46%6.94%13.25%-3.42%17.00%
13C-3.83%7.20%10.80%-2.61%22.52%
14D-4.44%5.87%9.04%-2.36%18.69%
Sheet3
Cell Formulas
RangeFormula
C2:M2C2=EOMONTH(B2,1)
C11:F14C11=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:G14G11=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:H14H11=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

New Member
Joined
Feb 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Many thanks sir. One last question though ( I promise). What is the best syntax for Since Inception numbers?
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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:

ABCDEFGHIJKLMNO
1Cumulative to
2SeriesInception28 Feb 201731 Mar 201730 Apr 201731 May 201730 Jun 201731 Jul 201731 Aug 201730 Sep 201731 Oct 201730 Nov 201731 Dec 201731 Jan 201828 Feb 2018
3A1 Jul 201512.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%
4B15 Jul 20172.62%0.67%1.42%2.55%1.82%3.41%4.95%-1.46%
5C1 Apr 20170.82%0.09%1.54%6.18%4.14%1.04%1.25%1.03%4.47%6.70%-3.83%
6D26 Jun 20171.55%5.48%3.20%-1.04%4.36%-0.28%3.43%7.12%-4.44%
7
8Date30 Jun 2017
9
1013612QTDYTDSI
11A-0.29%3.10%--3.10%-16.68%
12B-------
13C1.54%2.46%--2.46%-2.46%
14D------1.55%
Sheet1
Cell Formulas
RangeFormula
E2:O2E2=EOMONTH(D2,1)
C11:F14C11=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:G14G11=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:H14H11=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:I14I11=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)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,274
Messages
5,635,232
Members
416,848
Latest member
Excel DG

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
Top