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()
# 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
# 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
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
#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")
#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")
assume transaction costs are zero
bndTax <- 0.3
eqTax <- 0.1
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])
allXts <- na.omit(merge(eqXts1y, eqXts2y, bndXtsy)) #1,2,3
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
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")
Common.PlotCumReturns(portAnn1Xp, sprintf("70/30 %s/bond", eqIndex1), "total return; annual rebalance")
Common.PlotCumReturns(portAnn2Xp, sprintf("70/30 %s/bond", eqIndex2), "total return; annual rebalance")
#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))))
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")
Common.PlotCumReturns(portAnn1Xp, sprintf("70/30 %s/bond", eqIndex1), "total return; annual rebalance")
Common.PlotCumReturns(portAnn2Xp, sprintf("70/30 %s/bond", eqIndex2), "total return; annual rebalance")
This notebook was created using pluto. Thank you for playing fair!