Calculating compounded return on a dataframe

user_19383871 0
Viewed 78

I have a dataframe where one column contains dates, one column contains the price of a stock and one column contains the dividend. I want to add another column that calculates compounded return with this data. Here is the formula I want to follow. enter image description here

Here is an example dataframe and what I would want to do to it:

            price  dividend   
2020-07-31  83.08    0.7125  
2020-08-31  73.35    0.7225 
2020-09-30  74.55    0.7325  
2020-10-31  81.57    0.8400  
2020-11-30  81.85    0.8500  
2020-12-31  79.95    0.8600

Say n = 2, then I would want to use the current and two previous rows to calculate the return for each row. For example, the calculation for 2020-12-31's row would be:

CompoundedReturn = (79.95 / 81.57) * ((1 + 0.84/81.57) * (1 + 0.85/81.85) * (1 + 0.86/79.95)) = 1.0113

The new column would look like this when n=2:

            price  dividend    return
2020-07-31  83.08    0.7125        NA     
2020-08-31  73.35    0.7225        NA     
2020-09-30  74.55    0.7325    0.9229     
2020-10-31  81.57    0.8400    1.1457     
2020-11-30  81.85    0.8500    1.1318     
2020-12-31  79.95    0.8600    1.0113

Are there any built in functions I can use on python / numpy to help me do this?

pandas dataframe python
2 Answers

This might not be the prettiest of ways to do it but this is what I came up with:

import pandas as pd
import numpy as np

########### Recreate DF #############
date = ['2020-07-31', '2020-08-31', '2020-09-30', '2020-10-31', '2020-11-30', '2020-12-31']
quantity = [83.08, 73.35, 74.55, 81.57, 81.85, 79.95]
dividend = [0.7125, 0.7225, 0.7325, 0.8400, 0.8500, 0.8600]
df = pd.DataFrame({"date":date, "quantity":quantity, "dividend":dividend})
#####################################

CRList = []
value1 = 0
n = 2

for x in range(len(df)):
    if x < n:
        CRList.append(np.nan)
    else:
        value1 = (df.quantity[x] / df.quantity[x-n])
        for y in range(n+1):
            value1 = value1 * (1 + df.dividend[x-y]/df.quantity[x-y])
        
        CRList.append(value1)
        
df["Return"] = CRList
df

Output:

date        quantity    dividend    Return
2020-07-31  83.08         0.7125    NaN
2020-08-31  73.35         0.7225    NaN
2020-09-30  74.55         0.7325    0.922918
2020-10-31  81.57         0.8400    1.145732
2020-11-30  81.85         0.8500    1.131758
2020-12-31  79.95         0.8600    1.011279
user_44583690

There are several ways to achieve that. I will present you two, a longer one and a short one. For both cases, you do not really have to loop over each value, neither do you need numpy.

Given your data frame:

import pandas as pd

date = ['2020-07-31', '2020-08-31', '2020-09-30', '2020-10-31', '2020-11-30', '2020-12-31']
price = [83.08, 73.35, 74.55, 81.57, 81.85, 79.95]
dividends = [0.7125, 0.7225, 0.7325, 0.8400, 0.8500, 0.8600]

df = pd.DataFrame({"price":price, "dividend":dividends}, index=date)

Approach 1:

You can just use pd.shift to restructure the data so that you can directly apply your formula column-wise. My code contains some comments to make it clearer.

You could do something simple like this:

N = 2

# Define temporary return column
df["tmp_returns"] = 1 + df["dividend"].div(df["price"])
# Define compounded return column
df["compunded_return"] = df["tmp_returns"]

# Update compunded return column where number of updates is given by N
for i in range(1, N+1):
    df["compunded_return"] *= df["tmp_returns"].shift(i)
# Apply formula
df["compunded_return"] = (df["price"].div(df["price"].shift(N))).mul(df["compunded_return"])
# Drop temporary column
df.drop(columns=["tmp_returns"])

Approach 2:

If you want a one-liner (that is a little bit more complicated) you can also combine pd.shift and rolling to get your desired result:

N = 2
df["compunded_return"] = (df["price"].div(df["price"].shift(N))).mul((1 + df["dividend"].div(df["price"])).rolling(N+1).apply(lambda x: x.prod()))

pd.df.rolling provides a rolling window calculation to which you can apply the product.

Output both approaches:

            price   dividend   compunded_return
2020-07-31  83.08   0.7125     NaN
2020-08-31  73.35   0.7225     NaN
2020-09-30  74.55   0.7325     0.922918
2020-10-31  81.57   0.8400     1.145732
2020-11-30  81.85   0.8500     1.131758
2020-12-31  79.95   0.8600     1.011279
user_139213990
Related