The Indian 70/30 equity/bond portfolio

In [1]:
library(DBI)
library(plutoDbR)
library(plutoR)
library(tidyverse)
options("scipen"=999)
options(stringsAsFactors = FALSE)
source("/usr/share/pluto/config.R")
source("/usr/share/pluto/goofy/plot.common.R")
source("/usr/share/pluto/goofy/misc.common.R")

library(ggthemes)
library(quantmod)
library(PerformanceAnalytics)

options(repr.plot.width=16, repr.plot.height=8)

indices <- Indices()

In [2]:
# what are the begin and end dates that we have bond info for?

indices$IndiaGsecTimeSeries() %>%
    group_by(NAME) %>%
    summarize(ST = min(TIME_STAMP), ET = max(TIME_STAMP)) %>%
    print()

# Source:   lazy query [?? x 3]
# Database: NORWAY:StockViz:R
  NAME  ST         ET        
  <chr> <chr>      <chr>     
1 0_5   2003-12-31 2020-02-17
2 10_15 2003-12-31 2020-02-17
3 15_20 2003-12-31 2020-02-17
4 20_30 2003-12-31 2020-02-17
5 5_10  2003-12-31 2020-02-17
In [3]:
# what are the begin and end dates that we have equity index info for?

indicesOfInterest <- c('NIFTY 50 TR', 'NIFTY MIDCAP 100 TR', 'NIFTY MIDCAP 150 TR')

indices$NseTimeSeries() %>%
    filter(NAME %in% indicesOfInterest) %>%
    group_by(NAME) %>%
    summarize(ST = min(TIME_STAMP), ET = max(TIME_STAMP)) %>%
    print()
# Source:   lazy query [?? x 3]
# Database: NORWAY:StockViz:R
  NAME                ST         ET        
  <chr>               <chr>      <chr>     
1 NIFTY 50 TR         1999-06-30 2020-02-17
2 NIFTY MIDCAP 100 TR 2003-01-01 2020-02-17
3 NIFTY MIDCAP 150 TR 2005-04-01 2020-02-17
In [18]:
startDate <- as.Date('2004-01-01')
endDate <- as.Date('2020-02-15')

eqIndex1 <- "NIFTY 50 TR" 
eqIndex2 <- "NIFTY MIDCAP 100 TR" # maximum overlap is for MIDCAP 100 TR

bndIndex <- '0_5' #gsec. shortest tenure

eqDt1 <- indices$NseTimeSeries() %>%
    filter(NAME == eqIndex1 & TIME_STAMP >= startDate & TIME_STAMP <= endDate) %>%
    select(TIME_STAMP, CLOSE) %>%
    collect() %>%
    mutate(TIME_STAMP = as.Date(TIME_STAMP))

eqDt2 <- indices$NseTimeSeries() %>%
    filter(NAME == eqIndex2 & TIME_STAMP >= startDate & TIME_STAMP <= endDate) %>%
    select(TIME_STAMP, CLOSE) %>%
    collect() %>%
    mutate(TIME_STAMP = as.Date(TIME_STAMP))

bndDt <- indices$IndiaGsecTimeSeries() %>%
    filter(NAME == bndIndex & TIME_STAMP >= startDate & TIME_STAMP <= endDate) %>%
    select(TIME_STAMP, TRI) %>%
    collect() %>%
    mutate(TIME_STAMP = as.Date(TIME_STAMP))

#rebalance monthly
eqXts1 <- Common.NormalizeMonthlyDates(monthlyReturn(xts(eqDt1$CLOSE, eqDt1$TIME_STAMP)))
eqXts2 <- Common.NormalizeMonthlyDates(monthlyReturn(xts(eqDt2$CLOSE, eqDt2$TIME_STAMP)))
bndXts <- Common.NormalizeMonthlyDates(monthlyReturn(xts(bndDt$TRI, bndDt$TIME_STAMP)))

port1 <- eqXts1*0.7 + bndXts*0.3
port2 <- eqXts2*0.7 + bndXts*0.3

#rebalance annualy
eqAnnXts1 <- Common.NormalizeMonthlyDates(yearlyReturn(xts(eqDt1$CLOSE, eqDt1$TIME_STAMP)))
eqAnnXts2 <- Common.NormalizeMonthlyDates(yearlyReturn(xts(eqDt2$CLOSE, eqDt2$TIME_STAMP)))
bndAnnXts <- Common.NormalizeMonthlyDates(yearlyReturn(xts(bndDt$TRI, bndDt$TIME_STAMP)))

portAnn1 <- eqAnnXts1*0.7 + bndAnnXts*0.3
portAnn2 <- eqAnnXts2*0.7 + bndAnnXts*0.3
In [34]:
#plot the different scenarios
port2plot <- merge(port1, port2, eqXts1, eqXts2)
names(port2plot) <- c(sprintf("%s (70%%)", eqIndex1), sprintf("%s (30%%)", eqIndex2), eqIndex1, eqIndex2)
Common.PlotCumReturns(port2plot, "70/30 Indian equity/bond; monthly rebalance", "total return")
In [36]:
#plot the different scenarios
port2plot <- merge(portAnn1, portAnn2, eqAnnXts1, eqAnnXts2)
names(port2plot) <- c(sprintf("%s (70%%)", eqIndex1), sprintf("%s (30%%)", eqIndex2), eqIndex1, eqIndex2)
Common.PlotCumReturns(port2plot, "70/30 Indian equity/bond; annual rebalance", "total return")

Tax impact

assume transaction costs are zero

In [21]:
bndTax <- 0.3
eqTax <- 0.1
In [22]:
eqXts1y <- Common.NormalizeMonthlyDates(to.yearly(xts(eqDt1$CLOSE, eqDt1$TIME_STAMP))[,4])
eqXts2y <- Common.NormalizeMonthlyDates(to.yearly(xts(eqDt2$CLOSE, eqDt2$TIME_STAMP))[,4])
bndXtsy <- Common.NormalizeMonthlyDates(to.yearly(xts(bndDt$TRI, bndDt$TIME_STAMP))[,4])
In [38]:
allXts <- na.omit(merge(eqXts1y, eqXts2y, bndXtsy)) #1,2,3
In [39]:
allXts <- merge(allXts, as.numeric(allXts[, 1])/as.numeric(stats::lag(allXts[, 1], 1)) - 1) #pct profit/loss 4
allXts <- merge(allXts, as.numeric(allXts[, 2])/as.numeric(stats::lag(allXts[, 2], 1)) - 1) # 5
allXts <- merge(allXts, as.numeric(allXts[, 3])/as.numeric(stats::lag(allXts[, 3], 1)) - 1) # 6

allXts <- merge(allXts, ifelse(allXts[, 4] > 0, (1-eqTax)*allXts[, 4], allXts[, 4])) #after-tax pct profit/loss 7
allXts <- merge(allXts, ifelse(allXts[, 5] > 0, (1-eqTax)*allXts[, 5], allXts[, 5])) #8
allXts <- merge(allXts, ifelse(allXts[, 6] > 0, (1-bndTax)*allXts[, 6], allXts[, 6])) #9

names(allXts) <- c(eqIndex1, eqIndex2, bndIndex, 
                   paste0(eqIndex1, ".G"), paste0(eqIndex2, ".G"), paste0(bndIndex, ".G"), 
                   paste0(eqIndex1, ".N"), paste0(eqIndex2, ".N"), paste0(bndIndex, ".N"))
print(allXts)
           NIFTY 50 TR NIFTY MIDCAP 100 TR      0_5 NIFTY 50 TR.G
2004-12-20     2418.88             3161.88 1012.632            NA
2005-12-20     3353.37             4349.37 1079.232   0.386331691
2006-12-20     4758.45             5691.83 1129.664   0.419005359
2007-12-20     7461.48            10208.72 1214.386   0.568048419
2008-12-20     3635.87             4212.21 1383.805  -0.512714636
2009-12-20     6456.97             8516.52 1428.086   0.775907829
2010-12-20     7698.29            10254.53 1490.614   0.192244969
2011-12-20     5865.49             7174.58 1572.659  -0.238078846
2012-12-20     7591.99            10147.36 1711.796   0.294348810
2013-12-20     8204.85             9770.41 1811.439   0.080724553
2014-12-20    10904.18            15424.98 2007.492   0.328991999
2015-12-20    10575.63            16597.93 2192.375  -0.030130647
2016-12-20    11040.41            17977.22 2427.919   0.043948209
2017-12-20    14381.92            26836.68 2557.478   0.302661767
2018-12-20    15048.98            22918.72 2745.111   0.046381846
2019-12-20    17077.06            22140.56 3013.144   0.134765280
2020-02-20    17002.89            23293.20 3077.511  -0.004343253
           NIFTY MIDCAP 100 TR.G      0_5.G NIFTY 50 TR.N NIFTY MIDCAP 100 TR.N
2004-12-20                    NA         NA            NA                    NA
2005-12-20            0.37556454 0.06576829   0.347698522            0.33800808
2006-12-20            0.30865620 0.04673001   0.377104823            0.27779058
2007-12-20            0.79357430 0.07499788   0.511243577            0.71421687
2008-12-20           -0.58739098 0.13950939  -0.512714636           -0.58739098
2009-12-20            1.02186501 0.03200004   0.698317047            0.91967851
2010-12-20            0.20407514 0.04378425   0.173020472            0.18366762
2011-12-20           -0.30035019 0.05504120  -0.238078846           -0.30035019
2012-12-20            0.41434899 0.08847211   0.264913929            0.37291409
2013-12-20           -0.03714759 0.05820945   0.072652098           -0.03714759
2014-12-20            0.57874439 0.10823099   0.296092799            0.52086995
2015-12-20            0.07604224 0.09209629  -0.030130647            0.06843801
2016-12-20            0.08310012 0.10743801   0.039553388            0.07479011
2017-12-20            0.49281591 0.05336215   0.272395590            0.44353432
2018-12-20           -0.14599272 0.07336609   0.041743661           -0.14599272
2019-12-20           -0.03395303 0.09764011   0.121288752           -0.03395303
2020-02-20            0.05206011 0.02136211  -0.004343253            0.04685410
                0_5.N
2004-12-20         NA
2005-12-20 0.04603780
2006-12-20 0.03271101
2007-12-20 0.05249851
2008-12-20 0.09765657
2009-12-20 0.02240003
2010-12-20 0.03064898
2011-12-20 0.03852884
2012-12-20 0.06193048
2013-12-20 0.04074662
2014-12-20 0.07576170
2015-12-20 0.06446740
2016-12-20 0.07520660
2017-12-20 0.03735351
2018-12-20 0.05135627
2019-12-20 0.06834808
2020-02-20 0.01495348
In [40]:
portAnn1X <- allXts[,7]*0.7 + allXts[,9]*0.3
portAnn2X <- allXts[,8]*0.7 + allXts[,9]*0.3

portAnn1Xp <- na.omit(merge(portAnn1, portAnn1X))
portAnn2Xp <- na.omit(merge(portAnn2, portAnn2X))

names(portAnn1Xp) <- c('GROSS', "AFTER TAX")
names(portAnn2Xp) <- c('GROSS', "AFTER TAX")
In [41]:
Common.PlotCumReturns(portAnn1Xp, sprintf("70/30 %s/bond", eqIndex1), "total return; annual rebalance")
In [42]:
Common.PlotCumReturns(portAnn2Xp, sprintf("70/30 %s/bond", eqIndex2), "total return; annual rebalance")

Add a equity-oriented balanced fund into the mix

In [43]:
#sc <- 112936 #Reliance Regular Savings Fund - Balanced Option; Nippon India Equity Hybrid Fund - Growth Plan
sc <- 102885 #SBI Magnum Balanced Fund; SBI EQUITY HYBRID FUND

mfi <- MutualFundsIndia()
nav <- mfi$NavTimeSeries() %>%
        filter(SCHEME_CODE == sc) %>%
        select(TIME_STAMP, NAV) %>%
        collect() %>%
        mutate(TIME_STAMP = as.Date(TIME_STAMP))
    
navRet <- Common.NormalizeMonthlyDates(yearlyReturn((xts(nav$NAV, nav$TIME_STAMP))))
In [44]:
portAnn1Xp <- na.omit(merge(portAnn1, portAnn1X, navRet))
portAnn2Xp <- na.omit(merge(portAnn2, portAnn2X, navRet))

names(portAnn1Xp) <- c('GROSS', "AFTER TAX", "FUND")
names(portAnn2Xp) <- c('GROSS', "AFTER TAX", "FUND")
In [45]:
Common.PlotCumReturns(portAnn1Xp, sprintf("70/30 %s/bond", eqIndex1), "total return; annual rebalance")