Calculate monthly and quarterly returns for different Symbols based on their monthly close

Sachin Chhittarka 0
Viewed 45

I'm looking for a way to calculate monthly and quarterly returns for different Symbols based on their monthly close.

In column D I want to subtract the close value from the latest found date in the previous month from the close value in the current row provided that the symbol equals.

In column E I want the same, but for the value of column C of the last date in the month 3 months before the month of the current row.

image of excel

finance excel-formula
1 Answers

In D2 you could use:

=IFERROR($C2-INDEX($C$2:$C$21,
                MATCH(
                   MAXIFS($B$2:$B$21,
                      $A$2:$A$21,$A2,
                      $B$2:$B$21,"<="&EOMONTH(DATE(YEAR($B2),MONTH($B2)-1,1),0), 
                      $B$2:$B$21,">="&DATE(YEAR($B2),MONTH($B2)-1,1)),
                   $B$2:$B$21,0)),
         "")

Where MAXIFS calculates the max date found in the previous month;

MATCH returns the nth instance where the date matches the calculated max value, which is used in the INDEX function which indexes the values in column C.

Finally the value in column C minus the calculated value is the outcome.

If no value is present for the previous month it throws an error, which is covered with IFERROR that then returns a blank.

For D2 you could use a variant of this, replacing both the MONTH($B2)-1's with MONTH($B2)-3 for looking back 3 months (as explained in your comment). If you ment actual quarters then change it to look for the max value for the last month in the previous quarter.

enter image description here

Or if you have Office 365 you can use LET:

=LET(d,DATE(YEAR($B2),MONTH($B2)-1,1),

IFERROR(C2-INDEX($C$2:$C$21,
              XMATCH(                                                          
                 MAXIFS($B$2:$B$21,                    
                   $A$2:$A$21,$A2,                       
                   $B$2:$B$21,"<="&EOMONTH(d,0),                
                   $B$2:$B$21,">="&d),
                 $B$2:$B$21)),
        ""))
P.b0
Related