How to compute cumulative return and investment on a portfolio

Question

Here is my problem :

I have a DataFrame with monthly investment :

df = pd.DataFrame({'Dates':['2018-07-31','2018-07-31','2018-07-31','2018-08-31','2018-08-31','2018-08-31',
                               '2018-09-30','2018-09-30','2018-09-30'],
                      "Name":["Apple",'Google','Facebook','JP Morgan','IBM','Netflix',"Apple","Tesla","Boeing"],
                     "Monthly Return":[-0.018988,-0.028009,0.111742,-0.034540,-0.025806,-0.043647,0.001045,
                                       0.155379,0.011644],
                     "Total Weight":[0.7,0.2,0.1,0.5,0.3,0.2,0.6,0.2,0.2]})

And I would like to compute the cumulative investment but I have difficulties in doing it : Let's assume we have an initial investment of 1000$

If we take into consideration the Monthly Return and the Weight of each asset, we have this for 2018-07-31 :

Dates        Name     Return    Weight Investment Pofit/loss
2018-07-31   Apple    -0.018988  0.7       700       -13.29     
2018-07-31   Google   -0.028009  0.2       200       -5.60
2018-07-31   Facebook  0.111742  0.1       100       11.17

So for July 2018 I started with 1000$ and at the end of the month I have 992.28$ (1000 - 13.29 - 5.60 + 11.17) This amount will be reinvested in August 2018 and at the end of this month I will have : 992.28$ +/- the Total Profit/Loss of August 2018.

My goal is the have the final amount by taking into account the Profit/Loss of each month but I don't really know how to do it.

If somebody has an idea on that, you are welcome! If it wasn't really clear for you, please tell me

Answer 1

Here's a solution, split into several steps for clarity:

df = pd.DataFrame({'Dates':['2018-07-31','2018-07-31','2018-07-31','2018-08-31','2018-08-31','2018-08-31',
                               '2018-09-30','2018-09-30','2018-09-30'],
                      "Name":["Apple",'Google','Facebook','JP Morgan','IBM','Netflix',"Apple","Tesla","Boeing"],
                     "Monthly Return":[-0.018988,-0.028009,0.111742,-0.034540,-0.025806,-0.043647,0.001045,
                                       0.155379,0.011644],
                     "Total Weight":[0.7,0.2,0.1,0.5,0.3,0.2,0.6,0.2,0.2]})

df["weighted_return"] = df["Monthly Return"] * df["Total Weight"]
# df.groupby("Dates", freq="1M")
df["Dates"] = pd.to_datetime(df.Dates)
df.set_index("Dates", inplace=True)
t = df.groupby(pd.Grouper(freq="M")).sum()

at this point, t is:

            Monthly Return  Total Weight  weighted_return  eom_value
Dates                                                               
2018-07-31        0.064745           1.0        -0.007719   0.992281
2018-08-31       -0.103993           1.0        -0.033741   0.966259
2018-09-30        0.168068           1.0         0.034032   1.034032

Now, we can use cumprod to calculate return over time:

t["eom_value"] = 1 + t.weighted_return
t.eom_value.cumprod()

Result:

Dates
2018-07-31    0.992281
2018-08-31    0.958800
2018-09-30    0.991430