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


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'})


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



Step 3: Retrieve Fund NAV for the past years


#Get Fund NAV from RIC

FundRIC = 'LP63500988'

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


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 = []








#...monthly contributions...








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)



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']


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'])

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']


Append it to the previous dataframe


#Append df2 to df1

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


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


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


                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


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


#Plot Bar Chart to compare inflation and average return,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.



XIRR function

XIRR Python function