Calculating Cumulative Compounded Returns in Pandas

Question

I have a series of daily percentage returns returns:

                   Returns
Date
2003-03-03         0.0332
2003-03-04         0.0216
2003-03-05         0.0134
...
2010-12-29         0.0134
2010-12-30         0.0133
2010-12-31        -0.0297

I can calculate a return index by setting the value of the initial value to 1 and using cumprod()

ret_index = (1 + returns).cumprod()

ret_index[0] = 1

which gives me something like this:

Date
2003-03-03         1.0000
2003-03-04         1.0123
2003-03-05         1.1334
...
2010-12-29         2.3344
2010-12-30         2.3544
2010-12-31         2.3643

So my cumulative compounded percentage return for the whole series is about 236%.

My question: I want to calculate cumulative compound percentage return for each year in the series (2003, 2004...2010).

The only way I can think of doing it is to iterate through my initial series, slice it by year, set the first element to 1, and calculate the return for each year. I would think there is any easier way using datetime (the index is a Datetimeindex) and resampling.

Can anyone help?

Answer 1

For me it return a bit different results, but I think you need groupby:

a = df.add(1).cumprod()
a.Returns.iat[0] = 1
print (a)
             Returns
Date                
2003-03-03  1.000000
2003-03-04  1.055517
2003-03-05  1.069661
2010-12-29  1.083995
2010-12-30  1.098412
2010-12-31  1.065789

def f(x):
    #print (x)
    a = x.add(1).cumprod()
    a.Returns.iat[0] = 1
    return a

print (df.groupby(df.index.year).apply(f))

             Returns
Date                
2003-03-03  1.000000
2003-03-04  1.055517
2003-03-05  1.069661
2010-12-29  1.000000
2010-12-30  1.026878
2010-12-31  0.996380

Answer 2

I figured it out:

returns_y = ret_index.resample('A-DEC', how='last').pct_change()

give:

Out[206]: 
              return
date                
2003-12-31       NaN
2004-12-31  0.108821
2005-12-31  0.049118
2006-12-31  0.157945
2007-12-31  0.054937
2008-12-31 -0.369976
2009-12-31  0.264642
2010-12-31  0.150634

If I change my daily series value to start on the 2002-12-30 (the last date for which I have a value I get:

Out[206]: 
              return
date                
2003-12-31  0.057843
2004-12-31  0.108821
2005-12-31  0.049118
2006-12-31  0.157945
2007-12-31  0.054937
2008-12-31 -0.369976
2009-12-31  0.264642
2010-12-31  0.150634

Answer 3

I couldn't get this answer to work returns_y = ret_index.resample('A-DEC', how='last').pct_change()

I think how='last' has been deprecated?

Here is my solution, starting with a dataframe of S&P500 daily returns:

    print(daily_df)
    
    daily_df['Return'] = (1 + daily_df).cumprod()
    annual_df = pd.DataFrame(daily_df['Return'].resample('A').last().pct_change())

    print(daily_df)
    print(annual_df)

Output:

            Daily Return
Date                    
2002-12-31      0.000489
2003-01-02      0.033200
2003-01-03     -0.000484
2003-01-06      0.022474
2003-01-07     -0.006545
                  ...
2010-12-27      0.000613
2010-12-28      0.000771
2010-12-29      0.001009
2010-12-30     -0.001508
2010-12-31     -0.000191
[2016 rows x 1 columns]
            Daily Return    Return
Date                              
2002-12-31      0.000489  1.000489
2003-01-02      0.033200  1.033705
2003-01-03     -0.000484  1.033205
2003-01-06      0.022474  1.056425
2003-01-07     -0.006545  1.049512
                  ...       ...
2010-12-27      0.000613  1.430014
2010-12-28      0.000771  1.431117
2010-12-29      0.001009  1.432561
2010-12-30     -0.001508  1.430401
2010-12-31     -0.000191  1.430128
[2016 rows x 2 columns]
              Return
Date                
2002-12-31       NaN
2003-12-31  0.263804
2004-12-31  0.089935
2005-12-31  0.030010
2006-12-31  0.136194
2007-12-31  0.035296
2008-12-31 -0.384858
2009-12-31  0.234542
2010-12-31  0.127827