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

#### Vas7

##### New Member
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%​

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### StephenCrump

##### MrExcel MVP
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
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

##### Well-known Member
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

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
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

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
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
Many thanks sir. One last question though ( I promise). What is the best syntax for Since Inception numbers?

#### StephenCrump

##### MrExcel MVP
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)

Replies
1
Views
224
Replies
3
Views
255
Replies
3
Views
167
Replies
3
Views
100
Replies
14
Views
280

1,130,122
Messages
5,640,242
Members
417,131
Latest member
Seanr19871

### 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.

### Which adblocker are you using?

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

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