Calculate equity changes using percentage change of price

Question

I have a data frame that looks like this:

I need to create a column named 'Equity' that shows how equity changes from a starting amount every time the percentage change (return %) of the first column is applied to this amount. The result should look like this assuming that the starting amount is 100:

       Ret %   Equity
   0    0.02   102.00
   1    0.01   103.02
   2    0.04   107.14
   3   -0.02   105.00
   4   -0.01   103.95
   5    0.04   108.11
   6    0.02   110.27
   7   -0.01   109.17
   8    0.04   113.53
   9   -0.02   111.26
   10   0.01   112.37
   11   0.04   116.87

I found a solution using a "for" loop however I need to increase the performance so I'm looking for a vectorized solution with a panda/numpy method. Is there a way to do this?

Thank you in advance

user_18470692 · Answer

Try using df.cumprod:

base = 100

df['Equity'] = (1 + df['Ret %']).cumprod()*base
print(df)

    Ret %      Equity
0    0.02  102.000000
1    0.01  103.020000
2    0.04  107.140800
3   -0.02  104.997984
4   -0.01  103.948004
5    0.04  108.105924
6    0.02  110.268043
7   -0.01  109.165362
8    0.04  113.531977
9   -0.02  111.261337
10   0.01  112.373951
11   0.04  116.868909

Or np.cumprod if you prefer:

import numpy as np

df['Equity'] = np.cumprod(1+df['Ret %'])*base

You could round these values of course to end up with your expected output:

df['Equity'] = df['Equity'].round(2)

user_19255749 · Answer

This also returns the equities you want.

df.index.to_series().apply(lambda x: 100*np.product(1 + df.loc[:x, 'Ret %']))

which gives

0     102.000000
1     103.020000
2     107.140800
3     104.997984
4     103.948004
5     108.105924
6     110.268043
7     109.165362
8     113.531977
9     111.261337
10    112.373951
11    116.868909

Calculate equity changes using percentage change of price

2 Answers