CODEWITHSUNDEEP
pythonpandasdataframefinancequantitative-finance
niezesrajsie
niezesrajsie

Reputation: 115

How to calculate future value of investment with Pandas when interest rates and payments vary over time?

Let's assume that I would like to know how the value of my invested money changes over time. I have the following data in Pandas DataFrame:

            contribution    monthly_return
2020-01-01  91.91           np.Nan
2020-02-01  102.18          0.037026
2020-03-01  95.90          -0.012792
2020-04-01  117.89         -0.009188    
2020-05-01  100.44          0.011203
2020-06-01  98.89           0.053917
2020-07-01  106.10         -0.049397
2020-08-01  112.55          0.062375
2020-09-01  103.16         -0.063198

...and so on. Every month I invest additional sum of money to my "fund" (contribution). Monthly return shows how the value of my money changed during last month.

I would like to add additional column, where I could find information on current value of my investment in every month (so I can plot it on a graph). As far as I know, I cannot use any of numpy financial functions (such as np.fv()) because contributions and rates changes over time. I can cumulative sum contributions, but I don't know how to add profit and loss on investment.

This may be a trivial question, but I am completely stuck and I have wasted more hours on this problem than I could ever admit. Any help would be appreciated!

Upvotes: 2

Views: 1416

Answers (1)

Sergey Bushmanov
Sergey Bushmanov

Reputation: 25199

Suppose you have a df:

print(df)
            contribution  monthly_return
2020-01-01         91.91             NaN
2020-02-01        102.18        0.037026
2020-03-01         95.90       -0.012792
2020-04-01        117.89       -0.009188
2020-05-01        100.44        0.011203
2020-06-01         98.89        0.053917
2020-07-01        106.10       -0.049397
2020-08-01        112.55        0.062375
2020-09-01        103.16       -0.063198

Then let's find a multiplier your money grows monthly:

df['monthly_multiplier'] = 1 + df['monthly_return'].shift(-1)
print(df)
            contribution  monthly_return  monthly_multiplier
2020-01-01         91.91             NaN            1.037026
2020-02-01        102.18        0.037026            0.987208
2020-03-01         95.90       -0.012792            0.990812
2020-04-01        117.89       -0.009188            1.011203
2020-05-01        100.44        0.011203            1.053917
2020-06-01         98.89        0.053917            0.950603
2020-07-01        106.10       -0.049397            1.062375
2020-08-01        112.55        0.062375            0.936802
2020-09-01        103.16       -0.063198                 NaN

Finally we can iterate over rows and see how your wealth grows:

df['fv'] = 0
fv = 0
for index, row in df.iterrows():
    fv = (fv+row['contribution'])*row['monthly_multiplier']
    df.loc[index,'fv']=fv
print(df)
            contribution  monthly_return  monthly_multiplier          fv
2020-01-01         91.91             NaN            1.037026   95.313060
2020-02-01        102.18        0.037026            0.987208  194.966728
2020-03-01         95.90       -0.012792            0.990812  288.194245
2020-04-01        117.89       -0.009188            1.011203  410.633607
2020-05-01        100.44        0.011203            1.053917  538.629162
2020-06-01         98.89        0.053917            0.950603  606.027628
2020-07-01        106.10       -0.049397            1.062375  756.546589
2020-08-01        112.55        0.062375            0.936802  814.171423
2020-09-01        103.16       -0.063198                 NaN         NaN

df['fv'] is your wealth at the end of the stated month, or just prior your next contribution.

Upvotes: 2

Related Questions