Article

Inflation versus Dollar Cost Averaging on Pension Fund | Refinitiv
Chavalit Jintamalit
Developer Advocate Developer Advocate

When investing in a retirement fund using DCA(Dollar-Cost Averaging) strategy, does it perform better than the inflation rate?

In this Article, we will find out what is the past inflation rate in Thailand.

Then we will find out how the DCA on a fund performs.

And then we will plot a bar chart to compare the average return against inflation.

 

Step 1: Import Eikon Data APIs and libraries

    	
            
#Import Python library


import pandas as pd


import eikon as ek


 


#Set app key and set display properties


ek.set_app_key('__YOUR_API_APP_KEY__')


pd.set_option('display.max_rows', 14)


pd.options.display.float_format = '{:,.2f}'.format

Step 2: Find out the inflation rate

    	
            
#Overview on Thailand Consumer Price Index in the past 10+ years


CPIs_RIC = ['aTHCPI', #Thailand Consumer Price Index


            'aTHCPIBK', #Thailand Bangkok Price Index


            'aTHCPIFB', #Food and Non-Alcohol Price Index


            'aTHCPIHOSF/C', #Housing and Furnishing Price Index


            'aTHHPSDHHE/C', #Single House Price Index


            'aTHCPIPMCF/C'] #Personal and Medical Care Price Index


CPIs = ek.get_timeseries(CPIs_RIC,interval='yearly',start_date='2008-01-01',end_date='2020-03-03')


CPIs = CPIs.rename(columns={'aTHCPI': 'Thailand',


                    'aTHCPIBK': 'Bangkok',


                    'aTHCPIFB': 'Food & Non-Alcohol',


                    'aTHCPIHOSF/C': 'Housing and Furnishing',


                    'aTHHPSDHHE/C': 'Single-Detached House',


                    'aTHCPIPMCF/C':'Personal and Medical Care'})


CPIs

These are the price indexes. 

Convert it to percent changes per year.

    	
            
#Show percent change compare to previous year


#This is actually the inflation


CPIs = CPIs.pct_change()*100


CPIs.dropna(inplace=True)


CPIs

Step 3: Retrieve Fund NAV for the past years

    	
            
#Get Fund NAV from RIC


FundRIC = 'LP63500988'


df1, e = ek.get_data(FundRIC,['TR.FundNAV.date','TR.FundNAV'],{'SDate':'2008-01-01','EDate':'2020-03-03'})


df1

Step 4: Define your past contribution.

Please note that these are sample numbers which include random increment per year.

    	
            
#Define DCA contribution to your fund, please note that this is a random sample numbers


contributions = []


contributions.append([pd.Timestamp('2008-11-01'),100])


contributions.append([pd.Timestamp('2008-12-01'),100])


contributions.append([pd.Timestamp('2009-01-01'),100])


contributions.append([pd.Timestamp('2009-02-01'),100])


contributions.append([pd.Timestamp('2009-03-01'),100])


contributions.append([pd.Timestamp('2009-04-01'),100])


#...


#...monthly contributions...


#...


contributions.append([pd.Timestamp('2019-08-01'),189])


contributions.append([pd.Timestamp('2019-09-01'),189])


contributions.append([pd.Timestamp('2019-10-01'),189])


contributions.append([pd.Timestamp('2019-11-01'),189])


contributions.append([pd.Timestamp('2019-12-01'),189])


contributions.append([pd.Timestamp('2020-01-01'),189])

Step 5: Fill contribution data into NAV dataframe using the date.

If the date could not be found in NAV dataframe, add 1 day to the date (until the NAV is found).

Then remove all the rows without a contribution

    	
            
#Add Amount Invested column and set its value to 0


df1['AmountInvested'] = 0.0


 


#Convert Date column from object to datetime64


df1['Date'] =  pd.to_datetime(df1['Date'], infer_datetime_format=True)


 


#Loop through contributions and add Amount Invested to dataframe


#If the NAV of the date contributed could not be found, add 1 day until NAV is found


for contribution in contributions:


    searchingDay = contribution[0]


    while (len(df1.loc[df1['Date'] == searchingDay, 'AmountInvested'])==0):


        searchingDay += pd.DateOffset(1)


    df1.loc[df1['Date'] == searchingDay, 'AmountInvested'] = contribution[1]


 


#Drop any row without contribution from dataframe


df1 = df1[df1["AmountInvested"] > 0]


df1 = df1.reset_index(drop=True)


 


df1

Step 6: Add calculated data to the dataframe. They are Total Amount Invested, Unit Invested, Total Unit Invested and Gain/Loss columns.

Once you complete this step, you are able to see how much you gain(or loss).

But this still does not give you the idea of how much it grows per year on average.

    	
            
#Add Total Amount Invested column and set its value to cumulative summation of Amount Invested


df1['TotalAmountInvested'] = df1['AmountInvested'].cumsum()


 


#Add Unit Invested column and set its value to Amount Invested / NAV


df1['UnitInvested'] = df1['AmountInvested']/df1['NAV']


 


#Add Total Unit Invested column and set its value to cumulative summation of Unit Invested


df1['TotalUnitInvested'] = df1['UnitInvested'].cumsum()


 


#Add Total Value column and set its value to Total Unit Invested * NAV


df1['TotalValue'] = df1['NAV']*df1['TotalUnitInvested']


 


#Add Absolute Gain or Loss and set its value to Total Value - Total Amount Invested


df1['Gain/Loss'] = df1['TotalValue']-df1['TotalAmountInvested']


df1

Step 7: Get current NAV

    	
            
#Get current NAV and calculate Total Unit Invested, Total Value and Absolute Gain/Loss


df2, e = ek.get_data(FundRIC,['TR.FundNAV.date','TR.FundNAV'])


df2['Date'] =  pd.to_datetime(df2['Date'], infer_datetime_format=True)


df2["AmountInvested"] = 0


df2["TotalAmountInvested"] = df1.tail(1)["TotalAmountInvested"].values[0]


df2["UnitInvested"] = 0


df2["TotalUnitInvested"] = df1.tail(1)["TotalUnitInvested"].values[0]


df2['TotalValue'] = df2['NAV']*df2['TotalUnitInvested']


df2['Gain/Loss'] = df2['TotalValue']-df2['TotalAmountInvested']


df2

Append it to the previous dataframe

    	
            
#Append df2 to df1


df1 = df1.append(df2, ignore_index = True) 


df1

Step 8: Define xirr() function

XIRR is the internal rate of return for a schedule of cash flows that is not necessarily periodic.

    	
            
#Define xirr function


#https://stackoverflow.com/questions/8919718/financial-python-library-that-has-xirr-and-xnpv-function


def xirr(transactions):


    years = [(ta[0] - transactions[0][0]).days / 365.0 for ta in transactions]


    residual = 1


    step = 0.05


    guess = 0.05


    epsilon = 0.0001


    limit = 10000


    while abs(residual) > epsilon and limit > 0:


        limit -= 1


        residual = 0.0


        for i, ta in enumerate(transactions):


            residual += ta[1] / pow(guess, years[i])


        if abs(residual) > epsilon:


            if residual > 0:


                guess += step


            else:


                guess -= step


                step /= 2.0


    return guess-1

Step 9: Prepare data from dataframe for xirr function

    	
            
#Extract data from Dataframe and prepare it for xirr function


 


#For xirr function, invested money is negative


df1['AmountInvested'] = df1['AmountInvested']*-1


#Get Date and amount


tas = df1[['Date','AmountInvested']].values.tolist()


#The current date(last row) value is a positive number and it is the Total Value


tas[-1][1] = df1.tail(1)["TotalValue"].values[0]

Calculate xirr

    	
            
AverageReturn = xirr(tas) * 100


print("%.2f" % AverageReturn)

Step 10: Compare between Inflation and Average Return

First, let add "Return" to the CPIs dataframe

    	
            
CPIs['Return'] = AverageReturn


CPIs

Next, plot bar chart to compare the inflation rates and average return

    	
            
#Plot Bar Chart to compare inflation and average return


CPIs.plot.bar(figsize=(15,12))

Here is the chart:

You can see from the bar chart that the average return from the sample DCA investing on "LP63500988" fund usually wins against inflation.

Except in 2011(against Food) and 2013(against House).

You can try running this Jupyter Notebook sample using your own contribution data and use the actual fund you are DCA investing in.

 

Reference:

XIRR function

XIRR Python function