How to calculate Best SIP Date with Highest Returns

How to calculate Best SIP Date with Highest Returns

This is a simple analysis to find out which SIP date had yielded highest returns for Parag Parikh Long Term Equity Fund – Direct Plan – Growth. The standard SIP dates considered are 1st, 5th, 10th, 15th, 20th and 25th of calendar month. The following analysis is illustrated using python programming language. 

If you are not a programmer, you can skip this and read Full Report.

Load Dependencies

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
from babel.numbers import format_currency
from datetime import datetime
from IPython.display import display, HTML

Initial Setup

The following is the initial setup made for the analysis.

  • An SIP amount of ₹10,000
  • Standard SIP dates considered are 1st, 5th, 10th, 15th, 20th and 25th of a calendar month
  • Number of years to calculate retruns for. Maximum number of years to which the returns can be calculated is limited back to the year 1993. Because, the Private sector mutual funds started in the year 1993.
  • Scheme: Parag Parikh Long Term Equity Fund - Direct Plan - Growth
  • Scheme Code: 122639
sip=10000
standardSIPDates = [1,5,10,15,20,25]
maxYears = datetime.now().year - 1993 # private sector mutual funds in India started in the year 1993
years = [1, 3, 5, maxYears]

schemeName='Parag Parikh Long Term Equity Fund - Direct Plan - Growth'
schemeCode = 122639

Load Data

Load the data into a pandas data frame.

def loadData(filepath):
df = pd.read_csv('data.txt')
return df

Data wrangling

Handling Missing NAV

As the NAV is not available on weekends and holidays, the back fill approach is follwed. This means, the NAVs for weekends are back filled with that of succeeding Monday's NAVs. Other missing days' Nav such as holidays are filled with NAV of the next available day.

By default all the data loaded into a pandas dataframe will be in string format. To smoothen the data access and analysis easier, cast the 'Date' column to DateTime and 'Net Asset Value' column to float.

Also, control the range of NAVs to be processed by a numeric variable 'yearsToConsider'. This is to calculate the seperate of highest SIP returns yielding date for tenures of 1 year, 3 years, 5 years and so on. For example, set yearsToConsider value to 3 to know the best SIP date that had yielded the highest returns if the SIP had been initiated back 3 years from today (or from latest available NAV date in the dataset).

def preprocessing(df, schemeCode, yearsToConsider):
    df['Date'] =  pd.to_datetime(df['Date'], format='%d-%b-%Y')
    df['Net Asset Value'] = pd.to_numeric(df['Net Asset Value'], errors='coerce')
    df1 = df[df['Scheme Code']==schemeCode][['Net Asset Value','Date']]
    #fill navs on weekends with the monday's nav. Other missing days' Nav such as holidays will be filled with the nav for the next available date
    df1 = df1.set_index('Date').asfreq('d', method='bfill')
    df1 = df1[df1.index.max()-np.timedelta64(yearsToConsider,'Y'):df1.index.max()]
    return df1
#Absolute Return
def AbsReturn(L,F):
    absRet = round(100*(L-F)/F, 3)
    return absRet
#Simple Annualised Return
def SAR(L,F,Terms):
    absRet = round((AbsReturn(L,F)/100), 3)
    noOfDays = Terms * 30
    sar = ((1+ absRet) ** (365/ noOfDays)) - 1
    return round(sar, 3) * 100
import scipy.optimize

def XNPV(rate, values, dates):
    if rate <= -1.0:
        return float('inf')
    d0 = dates[0]    # or min(dates)
    return sum([ vi / (1.0 + rate)**((di - d0).days / 365.0) for vi, di in zip(values, dates)])

def XIRR(values, dates):
    try:
        return round(scipy.optimize.newton(lambda r: XNPV(r, values, dates), 0.0)*100, 3)
    except RuntimeError:   
        return (scipy.optimize.brentq(lambda r: XNPV(r, values, dates), -1.0, 1e10)*100, 3)

Basic SIP Calculation Methods

The returns by date invested in sip are calculated for each calendar date starting from 1 to 30. Calculation for Date 31 is excluded for brevity.

SIP Amount Consideration

For an SIP payment, consider the fixed amount for each term, lets say ₹5,000. However, changing this amount will not affect the calculation of returns for a given mutual fund data.

Calculation of Units Alloted per SIP Term

The units alloted per SIP term is the ratio of SIP amount to the NAV of mutual fund on the date of allotment.

unitsForCurrTerm = round(sip/nav, 4)

As per the SEBI guidelines, The mutual fund units alloted are calculated upto 4 decimals places.

Total number of units alloted for SIP till date

The total units alloted for a particular SIP date is the sum of units alloted across all the SIP terms falling in the duration of years considered.

totalUnits = round(totalUnits + unitsForCurrTerm,4)

Total Amount Invested in SIP till date

The total amount invested in SIP till date is just the product of SIP amount and number of terms SIP is paid.

amtInvested = round(terms * sip, 3)

Average NAV across all SIP payments

The average NAV across all SIP payments for a mutual fund is the ratio of total amount invested to the total number of units alloted till date.

avgNav = round(amtInvested / totalUnits, 4)

Absolute Return

Absolute return is calculated by the product of the Total number of units and latest NAV of the mutual fund.

Absolute Return = round(totalUnits * latestNav, 3)

As per the SEBI guidelines, the mutual fund redemption amount will be calculate up to 3 decimal places.

Simple Annualised Return

Simple Annualised Returns are calculated when the total number of SIP terms of a mutual fund investment is exactly equals to 12. The formula for Simple annualised return is

XIRR Calculation

XIRR is an acronym for eXtended Internal Rate of Return. This Annualization technique, XIRR in case of SIP is calculated only when the total number of SIP terms are more than 12. XIRR uses Newton Raphson method.

Following table describes when to use which annualization technique.

MonthsCalcuation
<12Absolute Return
12Simple Annualised Return
>12XIRR

Absolute Returns Percentage Calculation

In case of SIP, the absolute returns percentage is calculated as the percentage change between the Average NAV and the Latest NAV.

absReturn = round(100*(latestNav-avgNav)/avgNav, 3)

Terms

Terms is the total count of SIP payments made in the duration of years selected. As we are calculating the returns for same date of each month, the term count in this analysis is referred as the number of months the SIP was invested.

Best SIP Date calculation approach

For each calendar date falling in the duration of years considered, the units alloted to the investor are calculated by dividing the SIP amount by the nav of that particular date. Likewise, the total units alloted for a particular SIP date is the sum of units alloted across all the SIP terms falling in the duration of years considered.

Now, calculate the total amount invested, absolute returns, average NAV and XIRR as per the basic SIP calculation methods mentioned above. Now you will have all these metrics for SIP dates 1 to 30. Filter the results based on the the standard SIP dates defined. Filtering could be made optional if you are interested in looking across all the calendar dates 1 through 30. Notice the highest return yielding SIP date by sorting the XIRR in the descending order.

Example

Consider a calendar date say 3rd of every month. Suppose 3 year duration is considered. Add up the units alloted to the mutual fund investor for 3rd date of every month (weekends, holidays falling on 3rd will have back filled NAV). Repeat this for all the calendar days 1 to 30. Now consider the standard sip dates defined and look at the highest return yielding SIP date among 1, 5, 10, 15, 20 and 25.

def ReturnsByDateInvestedInSIP(data, sip=5000, filterStandardSIPDates = True):
    latestNav = data.loc[data.index == data.index.max(),'Net Asset Value'].iloc[0]
    firstNav = data.loc[data.index == data.index.min(),'Net Asset Value'].iloc[0]
    recentNavDate = data.index.max()
    
    #for dates 1 through 30 collect units allotted and returns gained
    sipdate_units = []
    for day in range(1,31):
        totalUnits = 0
        dfvar = data[data.index.day == day]

        terms = dfvar.shape[0]
        for nav in dfvar['Net Asset Value']:
            unitsForCurrTerm = round(sip/nav, 4)
            totalUnits = round(totalUnits + unitsForCurrTerm, 4)
        
        amtInvested = round(terms * sip, 3)
        returns = round(totalUnits * latestNav, 3)
        
        avgNav = round(amtInvested / totalUnits, 4)
        
        xirr = 0
        if terms &amp;lt; 12 :
            #xirr = AbsReturn(returns,amtInvested)
            xirr = AbsReturn(latestNav,avgNav)
        elif terms == 12 :
            xirr = SAR(latestNav, avgNav, terms)
        elif terms &gt; 12 :
            ## START XIRR
            xirrdataset=[]
            for sipdate in dfvar.index.values:
                xirrdataset.append({'Date':sipdate,'Cashflow': float(-sip)})
            # Finally add final cashflow date
            xirrdataset.append({'Date':recentNavDate,'Cashflow': returns})
            xirrdataset = pd.DataFrame(xirrdataset,columns=('Date','Cashflow'))
            #print(xirrdataset)
            xirr = XIRR(xirrdataset['Cashflow'], xirrdataset['Date'])
            ## END XIRR
        sipdate_units.append({'SIP Date': day,
                              'Average Nav': avgNav,
                              'Units': totalUnits,
                              'Terms': terms, 
                              'Amt Invested': amtInvested,
                              'Absolute Returns': returns,
                              'XIRR %': xirr
                             })
    rets = pd.DataFrame(sipdate_units, columns=('SIP Date','Average Nav','Units', 'Terms', 'Amt Invested', 'Absolute Returns', 'XIRR %'))
    rets.index += 1 # some novice pandas expertiese ^_^
    if filterStandardSIPDates:
        rets = rets[rets['SIP Date'].isin(standardSIPDates)]
    rets = rets.sort_values('XIRR %',ascending =False)
    return rets

Data Visualisation

The histogram shows SIP dates on X-axis and corresponding returns on Y axis.

def ShowPlot(d, toDate, fromDate):
    best = d[d['XIRR %']==d['XIRR %'].max()]
    bestSIPDate = best['SIP Date'].iloc[0]
    bestReturn = best['XIRR %'].iloc[0]
    yearsObserved = round(d['Terms'].iloc[0]/12, 2)
    yearsObserved= &quot;{0}&quot;.format(str(yearsObserved if yearsObserved % 1 else int(yearsObserved)))
    plt.figure(num=None, figsize=(10, 6), dpi=150)
    plt.hist(d['SIP Date'], weights=d['XIRR %'], facecolor= 'r' if d['XIRR %'].mean() &amp;lt; 0 else 'g', bins = 25)
    plt.xlabel('SIP Date')
    plt.ylabel('XIRR %')
    plt.title(schemeName)
    legend1 = 'Best SIP Date: {} \nYear(s) Observed: {} years \nAbsolute returns: {}%\n'
    legend2 = 'Dates Considered\nFrom: {} \nTo: {}\n'
    if d['XIRR %'].mean() &amp;lt; 0 :
        plt.text(0, .07, legend1.format(bestSIPDate, yearsObserved, bestReturn), fontsize=9)
        plt.text(21, .07, legend2.format(fromDate,toDate), fontsize=9)
    elif any(x &amp;lt; 0 for x in d['XIRR %']) and any(x&gt;0 for x in d['XIRR %']):
        plt.text(1, .003, legend1.format(bestSIPDate, yearsObserved, bestReturn), fontsize=9)
        plt.text(20.5, .003, legend2.format(fromDate,toDate), fontsize=9)
    else:
        plt.text(1, 2, legend1.format(bestSIPDate, yearsObserved, bestReturn), fontsize=9)
        plt.text(20.5, 2, legend2.format(fromDate,toDate), fontsize=9)
    plt.annotate('The past {} year(s) of observations indicate that, having SIP date set to {} of every month would have given the\n highest XIRR of {}%'.format(yearsObserved,bestSIPDate,bestReturn),
                 (0,0), (0, -35), 
                 xycoords='axes fraction', 
                 textcoords='offset points', va='top')
    plt.annotate('www.MFRepublic.com', (0,0), (250, 150), 
             xycoords='axes fraction',fontsize=20,
             textcoords='offset points', ha='center', va='bottom', alpha = 0.25)
    plt.show()
    plt.close()
def Summary(returnsByYear):
    summary = []
    display(HTML('&amp;lt;h2&gt;Summary&amp;lt;/h2&gt;'))
    display(HTML('&amp;lt;h4&gt;Best SIP Date for {}&amp;lt;/h4&gt;'.format(schemeName)))
    
    for key, value in returnsByYear.items():
        summary.append({'Investment Made':'{} Year(s) Ago'.format(key), 'Best SIP Date':value['SIP Date'].iloc[0], 'XIRR %': value['XIRR %'].iloc[0]})

    summary = pd.DataFrame(summary, columns=('Investment Made', 'Best SIP Date', 'XIRR %'))
    display(HTML(summary.to_html(index=False)))
    display(HTML('&amp;lt;p&gt;&amp;lt;small&gt;&amp;lt;i&gt;*Returns above 1 year are annualized&amp;lt;/i&gt;&amp;lt;small&gt;&amp;lt;/p&gt;'))
    summarytext = '&amp;lt;p&gt;If you have started an SIP of {} in the scheme {} : &amp;lt;ul&gt;'.format(format_currency(sip, 'INR', locale = 'en_IN'),schemeName)
    for key, value in returnsByYear.items():
        sipDate = value['SIP Date'].iloc[0]
        thORst = 'st' if sipDate == 1 else 'th'
        summarytext +='&amp;lt;li&gt;{} year(s) ago on every {}{} of a month, then it would have yielded highest XIRR of {}% than they would have yielded on any other SIP initiation date.&amp;lt;/li&gt;'.format(key, sipDate, thORst, value['XIRR %'].iloc[0])
    summarytext +='&amp;lt;/ul&gt;&amp;lt;/p&gt;'
    summarytext += '&amp;lt;p&gt;Best SIP date for {} is {} of every month which has generated higher annualized returns.&amp;lt;/p&gt;'.format(schemeName, summary['Best SIP Date'].mode().iloc[0])
    summarytext += '&amp;lt;p&gt;&amp;lt;i&gt;&amp;lt;strong&gt;Disclaimer:&amp;lt;/strong&gt; All analysis is based on the past performances. And this may not guarantee future returns. Please read the mutual fund documents carefully before investing.&amp;lt;/i&gt;&amp;lt;/p&gt;'

    display(HTML(summarytext))
def Report(df,isFilterStandardSIPDates):
    returnsByYear={}
    for yr in years:
        df1 = preprocessing(df, schemeCode,yr)
        if( df1.index.max().day == df1.index.min().day):
            df1 =df1[df1.index!=df1.index.min()]
        latestNav = df1.loc[df1.index == df1.index.max(),'Net Asset Value'].iloc[0]
        firstNav = df1.loc[df1.index == df1.index.min(),'Net Asset Value'].iloc[0]
        fromDate = df1.index.min().strftime('%d-%b-%Y')
        toDate = df1.index.max().strftime('%d-%b-%Y')
        dxirr = ReturnsByDateInvestedInSIP(df1,sip,isFilterStandardSIPDates)
        
        #best xirr
        best = dxirr[dxirr['XIRR %']==dxirr['XIRR %'].max()]
        bestSIPDate = best['SIP Date'].iloc[0]
        bestReturn = best['XIRR %'].iloc[0]
        absReturnsValue = best['Absolute Returns'].iloc[0]
        yearsObserved = round(dxirr['Terms'].iloc[0]/12, 2)
        yearsObserved= &quot;{0}&quot;.format(str(yearsObserved if yearsObserved % 1 else int(yearsObserved)))
        legend1 = '&amp;lt;strong&gt;Scheme:&amp;lt;/strong&gt; {}&amp;lt;br&gt;&amp;lt;strong&gt;Scheme Code:&amp;lt;/strong&gt; {}&amp;lt;br&gt;&amp;lt;strong&gt;Best SIP Date:&amp;lt;/strong&gt; {} &amp;lt;br&gt;&amp;lt;strong&gt;Years Observed:&amp;lt;/strong&gt; {} year(s) &amp;lt;br&gt;&amp;lt;strong&gt;XIRR:&amp;lt;/strong&gt; {}%&amp;lt;br&gt;'.format(schemeName, schemeCode, bestSIPDate, yearsObserved, bestReturn)
        legend2 = '&amp;lt;strong&gt;Dates Considered&amp;lt;/strong&gt;&amp;lt;br&gt;From: {} &amp;lt;br&gt;To: {}&amp;lt;br&gt;'.format(fromDate,toDate)
        allOrStandardDates = '1st, 5th, 10th, 15th, 20th and 25th' if isFilterStandardSIPDates else 'all the 30'
        returnsByYear[str(yearsObserved)] = dxirr
        display(HTML('&amp;lt;h2&gt;Returns for {} if the SIP was started {} year(s) ago&amp;lt;/h2&gt;'.format(schemeName, yearsObserved)))
        #Show plot
        if isFilterStandardSIPDates:
            ShowPlot(dxirr, toDate, fromDate)
            
        display(HTML(legend1))
        display(HTML(legend2))
        display(HTML(dxirr.to_html(index=False)))
        display(HTML('&amp;lt;p&gt;&amp;lt;small&gt;&amp;lt;i&gt;*Returns above 1 year are annualized&amp;lt;/i&gt;&amp;lt;small&gt;&amp;lt;/p&gt;'))
        display(HTML('&amp;lt;p&gt;The past {} years of observations indicate that, having SIP date set to {} of every month would have given the\n highest XIRR of {}%&amp;lt;/p&gt;'.format(yearsObserved,bestSIPDate,bestReturn)))
        display(HTML('&amp;lt;p&gt;An SIP of {} scheduled {} year(s) ago on every month\'s {} would have become {} now.&amp;lt;/p&gt;'.format(
            format_currency(sip, 'INR', locale = 'en_IN'),
            yearsObserved,
            bestSIPDate,
            format_currency(absReturnsValue, 'INR', locale = 'en_IN'))))
    return returnsByYear
if __name__ == &quot;__main__&quot;:
    isFilterStandardSIPDates = True
    display(HTML('&amp;lt;h1&gt;Best SIP Date for {}&amp;lt;/h1&gt;'.format(schemeName)))
    df = loadData(fpath)
    rep = Report(df,isFilterStandardSIPDates)
    Summary(rep)

Full Report

Returns for Parag Parikh Long Term Equity Fund – Direct Plan – Growth if the SIP was started 1 year(s) ago

Scheme: Parag Parikh Long Term Equity Fund – Direct Plan – Growth
Scheme Code: 122639
Best SIP Date: 5 
Years Observed: 1 year(s) 
XIRR: -3.0%
Dates Considered
From: 15-Nov-2017 
To: 14-Nov-2018

SIP DateAverage NavUnitsTermsAmt InvestedAbsolute ReturnsXIRR %
524.26092473.1186126000058174.427-3.0
124.35012464.0530126000057961.180-3.4
2024.36502462.5454126000057925.717-3.5
2524.38782460.2447126000057871.598-3.5
1024.39802459.2175126000057847.435-3.6
1524.40732458.2837126000057825.470-3.6

*Returns above 1 year are annualized

The past 1 years of observations indicate that, having SIP date set to 5 of every month would have given the highest XIRR of -3.0%

An SIP of ₹ 5,000.00 scheduled 1 year(s) ago on every month's 5 would have become ₹ 58,174.43 now.

Returns for Parag Parikh Long Term Equity Fund – Direct Plan – Growth if the SIP was started 3 year(s) ago

Scheme: Parag Parikh Long Term Equity Fund – Direct Plan – Growth
Scheme Code: 122639
Best SIP Date: 20 
Years Observed: 3 year(s) 
XIRR: 9.856%
Dates Considered
From: 15-Nov-2015 
To: 14-Nov-2018

SIP DateAverage NavUnitsTermsAmt InvestedAbsolute ReturnsXIRR %
2020.30958862.843536180000208478.0099.856
1520.30048866.827736180000208571.7289.799
2520.35808841.740536180000207981.6099.780
520.44888802.488736180000207058.3019.655
1020.47628790.704136180000206781.0959.649
120.46598795.127836180000206885.1539.525

*Returns above 1 year are annualized

The past 3 years of observations indicate that, having SIP date set to 20 of every month would have given the highest XIRR of 9.856%

An SIP of ₹ 5,000.00 scheduled 3 year(s) ago on every month's 20 would have become ₹ 2,08,478.01 now.

Returns for Parag Parikh Long Term Equity Fund – Direct Plan – Growth if the SIP was started 5 year(s) ago

Scheme: Parag Parikh Long Term Equity Fund – Direct Plan – Growth
Scheme Code: 122639
Best SIP Date: 15 
Years Observed: 5 year(s) 
XIRR: 12.25%
Dates Considered
From: 15-Nov-2013 
To: 14-Nov-2018

SIP DateAverage NavUnitsTermsAmt InvestedAbsolute ReturnsXIRR %
1517.293817347.288160300000408055.05412.250
2517.386317254.924560300000405882.41312.161
2017.387017254.248260300000405866.50412.096
1017.544717099.143360300000402218.01811.987
517.529117114.400460300000402576.90611.959
117.516417126.806560300000402868.73111.938

*Returns above 1 year are annualized

The past 5 years of observations indicate that, having SIP date set to 15 of every month would have given the highest XIRR of 12.25%

An SIP of ₹ 5,000.00 scheduled 5 year(s) ago on every month's 15 would have become ₹ 4,08,055.05 now.

Returns for Parag Parikh Long Term Equity Fund – Direct Plan – Growth if the SIP was started 5.42 year(s) ago

Scheme: Parag Parikh Long Term Equity Fund – Direct Plan – Growth
Scheme Code: 122639
Best SIP Date: 15 
Years Observed: 5.42 year(s) 
XIRR: 13.259%
Dates Considered
From: 28-May-2013 
To: 14-Nov-2018

SIP DateAverage NavUnitsTermsAmt InvestedAbsolute ReturnsXIRR %
1516.389419829.938165325000466453.68513.259
1016.443020069.367066330000472085.69913.245
2516.455919749.706765325000464566.42613.234
516.424420092.045466330000472619.15613.224
116.410620108.977166330000473017.43613.204
2016.472819729.484165325000464090.73613.133

*Returns above 1 year are annualized

The past 5.42 years of observations indicate that, having SIP date set to 15 of every month would have given the highest XIRR of 13.259%

An SIP of ₹ 5,000.00 scheduled 5.42 year(s) ago on every month's 15 would have become ₹ 4,66,453.68 now.

Summary

Best SIP Date for Parag Parikh Long Term Equity Fund – Direct Plan – Growth

Investment MadeBest SIP DateXIRR %
1 Year(s) Ago5-3.000
3 Year(s) Ago209.856
5 Year(s) Ago1512.250
5.42 Year(s) Ago1513.259

*Returns above 1 year are annualized

If you have started an SIP of ₹ 5,000.00 in the scheme Parag Parikh Long Term Equity Fund – Direct Plan – Growth :

  • 1 year(s) ago on every 5th of a month, then it would have yielded highest XIRR of -3.0% than they would have yielded on any other SIP initiation date.
  • 3 year(s) ago on every 20th of a month, then it would have yielded highest XIRR of 9.856% than they would have yielded on any other SIP initiation date.
  • 5 year(s) ago on every 15th of a month, then it would have yielded highest XIRR of 12.25% than they would have yielded on any other SIP initiation date.
  • 5.42 year(s) ago on every 15th of a month, then it would have yielded highest XIRR of 13.259% than they would have yielded on any other SIP initiation date.

Best SIP date for Parag Parikh Long Term Equity Fund – Direct Plan – Growth is 15 of every month which has generated higher annualized returns.

Disclaimer: All analysis is based on the past performances. And this may not guarantee future returns. Please read the mutual fund documents carefully before investing.

Here is the dataset used for the analysis. If you find anything interesting that needs to be added feel free to message us on our facebook page

Leave a Reply

Close Menu