Financial Modeling
Ch3 Portfolio Returns
Daniel Lee
Chapter 3: Portfolio Returns
Construct an equal-weighted (EW) and value-weighted (VW) portfolio, consisting of AMZN, MSFT, AAPL, and, TSLA.
In this section, prepare an object, port, which will be used later to build EW and VW portfolio.
Step 0: Import Price Data
# To clean up the memory of your current R session run the following line
rm(list=ls(all=TRUE))
library(quantmod)
library(xts)
#Importing Price Data
data.AMZN <- getSymbols("AMZN", from = "2016-12-30", to = "2017-07-01", auto.assign = FALSE)
data.MSFT <- getSymbols("MSFT", from = "2016-12-30", to = "2017-07-01", auto.assign = FALSE)
data.AAPL <- getSymbols("AAPL", from = "2016-12-30", to = "2017-07-01", auto.assign = FALSE)
data.TSLA <- getSymbols("TSLA", from = "2016-12-30", to = "2017-07-01", auto.assign = FALSE)
#to = "2017-06-30" in the book was replaced by to = "2017-07-01"
#B/c that code doesn't produce the output that includes stock price of 2013-12-31the date. Step 1: Importing the Price Data
data.AMZN[c(1:3, nrow(data.AMZN)), ]
## AMZN.Open AMZN.High AMZN.Low AMZN.Close AMZN.Volume
## 2016-12-30 766.47 767.40 748.28 749.87 4139400
## 2017-01-03 757.92 758.76 747.70 753.67 3521100
## 2017-01-04 758.39 759.68 754.20 757.18 2510500
## 2017-06-30 980.12 983.47 967.61 968.00 3390300
## AMZN.Adjusted
## 2016-12-30 749.87
## 2017-01-03 753.67
## 2017-01-04 757.18
## 2017-06-30 968.00
data.MSFT[c(1:3, nrow(data.MSFT)), ]
## MSFT.Open MSFT.High MSFT.Low MSFT.Close MSFT.Volume
## 2016-12-30 64.366 64.396 63.415 62.14 25579900
## 2017-01-03 64.192 64.243 63.517 62.58 20694100
## 2017-01-04 63.875 64.151 63.507 62.30 21340000
## 2017-06-30 69.494 70.100 69.453 68.93 24161100
## MSFT.Adjusted
## 2016-12-30 60.78280
## 2017-01-03 61.21319
## 2017-01-04 60.93930
## 2017-06-30 68.22208
data.AAPL[c(1:3, nrow(data.AAPL)), ]
## AAPL.Open AAPL.High AAPL.Low AAPL.Close AAPL.Volume
## 2016-12-30 118.526 119.085 117.286 115.82 30586300
## 2017-01-03 117.662 118.201 116.605 116.15 28781900
## 2017-01-04 117.713 118.384 117.611 116.02 21118100
## 2017-06-30 145.539 146.052 144.864 144.02 23024100
## AAPL.Adjusted
## 2016-12-30 113.9870
## 2017-01-03 114.3118
## 2017-01-04 114.1838
## 2017-06-30 142.9428
data.TSLA[c(1:3, nrow(data.TSLA)), ]
## TSLA.Open TSLA.High TSLA.Low TSLA.Close TSLA.Volume
## 2016-12-30 216.30 217.50 211.68 213.69 4642600
## 2017-01-03 214.86 220.33 210.96 216.99 5923300
## 2017-01-04 214.75 228.00 214.31 226.99 11213500
## 2017-06-30 363.71 366.77 359.62 361.61 5848500
## TSLA.Adjusted
## 2016-12-30 213.69
## 2017-01-03 216.99
## 2017-01-04 226.99
## 2017-06-30 361.61Step 2: Create Object with Only the Relevant Data
port <- data.AMZN[, c(4, 6)]
port <- merge(port, data.MSFT[, c(4, 6)])
port <- merge(port, data.AAPL[, c(4, 6)])
port <- merge(port, data.TSLA[, c(4, 6)])
#port <- cbind(port, data.MSFT[, c(4, 6)], data.AAPL[, c(4, 6)], data.TSLA[, c(4, 6)])
port[c(1:3, nrow(port)), ]
## AMZN.Close AMZN.Adjusted MSFT.Close MSFT.Adjusted AAPL.Close
## 2016-12-30 749.87 749.87 62.14 60.78280 115.82
## 2017-01-03 753.67 753.67 62.58 61.21319 116.15
## 2017-01-04 757.18 757.18 62.30 60.93930 116.02
## 2017-06-30 968.00 968.00 68.93 68.22208 144.02
## AAPL.Adjusted TSLA.Close TSLA.Adjusted
## 2016-12-30 113.9870 213.69 213.69
## 2017-01-03 114.3118 216.99 216.99
## 2017-01-04 114.1838 226.99 226.99
## 2017-06-30 142.9428 361.61 361.61Step 3: Calculate Returns of Each Security
port$AMZN.ret <- Delt(port$AMZN.Adjusted)
port$MSFT.ret <- Delt(port$MSFT.Adjusted)
port$AAPL.ret <- Delt(port$AAPL.Adjusted)
port$TSLA.ret <- Delt(port$TSLA.Adjusted)
port[c(1:3, nrow(port)), ]
## AMZN.Close AMZN.Adjusted MSFT.Close MSFT.Adjusted AAPL.Close
## 2016-12-30 749.87 749.87 62.14 60.78280 115.82
## 2017-01-03 753.67 753.67 62.58 61.21319 116.15
## 2017-01-04 757.18 757.18 62.30 60.93930 116.02
## 2017-06-30 968.00 968.00 68.93 68.22208 144.02
## AAPL.Adjusted TSLA.Close TSLA.Adjusted AMZN.ret
## 2016-12-30 113.9870 213.69 213.69 NA
## 2017-01-03 114.3118 216.99 216.99 0.005067529
## 2017-01-04 114.1838 226.99 226.99 0.004657224
## 2017-06-30 142.9428 361.61 361.61 -0.008125576
## MSFT.ret AAPL.ret TSLA.ret
## 2016-12-30 NA NA NA
## 2017-01-03 0.007080902 0.002849238 0.015442945
## 2017-01-04 -0.004474330 -0.001119264 0.046085072
## 2017-06-30 0.006424249 0.002366525 0.002383881Step 4: Convert to data.frame Object and Subset Data
port <- cbind(index(port), data.frame(port))
names(port)[1] <- "date"
port[c(1:3, nrow(port)), ]
## date AMZN.Close AMZN.Adjusted MSFT.Close MSFT.Adjusted
## 2016-12-30 2016-12-30 749.87 749.87 62.14 60.78280
## 2017-01-03 2017-01-03 753.67 753.67 62.58 61.21319
## 2017-01-04 2017-01-04 757.18 757.18 62.30 60.93930
## 2017-06-30 2017-06-30 968.00 968.00 68.93 68.22208
## AAPL.Close AAPL.Adjusted TSLA.Close TSLA.Adjusted AMZN.ret
## 2016-12-30 115.82 113.9870 213.69 213.69 NA
## 2017-01-03 116.15 114.3118 216.99 216.99 0.005067529
## 2017-01-04 116.02 114.1838 226.99 226.99 0.004657224
## 2017-06-30 144.02 142.9428 361.61 361.61 -0.008125576
## MSFT.ret AAPL.ret TSLA.ret
## 2016-12-30 NA NA NA
## 2017-01-03 0.007080902 0.002849238 0.015442945
## 2017-01-04 -0.004474330 -0.001119264 0.046085072
## 2017-06-30 0.006424249 0.002366525 0.002383881
port <- subset(port,
port$date >= "2016-12-30" &
port$date <= "2017-12-31")
port[c(1:3, nrow(port)), ]
## date AMZN.Close AMZN.Adjusted MSFT.Close MSFT.Adjusted
## 2016-12-30 2016-12-30 749.87 749.87 62.14 60.78280
## 2017-01-03 2017-01-03 753.67 753.67 62.58 61.21319
## 2017-01-04 2017-01-04 757.18 757.18 62.30 60.93930
## 2017-06-30 2017-06-30 968.00 968.00 68.93 68.22208
## AAPL.Close AAPL.Adjusted TSLA.Close TSLA.Adjusted AMZN.ret
## 2016-12-30 115.82 113.9870 213.69 213.69 NA
## 2017-01-03 116.15 114.3118 216.99 216.99 0.005067529
## 2017-01-04 116.02 114.1838 226.99 226.99 0.004657224
## 2017-06-30 144.02 142.9428 361.61 361.61 -0.008125576
## MSFT.ret AAPL.ret TSLA.ret
## 2016-12-30 NA NA NA
## 2017-01-03 0.007080902 0.002849238 0.015442945
## 2017-01-04 -0.004474330 -0.001119264 0.046085072
## 2017-06-30 0.006424249 0.002366525 0.0023838813.3.1 Equal-Weighted Portfolio
considerd as an approach to capture the small capitalization stock premeum (the belief that small cap stock yield higher returns over larger cap stocks) An example is the S&P 500 Equal Weight Index
Step 1: Keep Only Variables We Need to Construct EW Portfolio
ewport <- port[, c(1, 10:13)]
ewport[c(1:3, nrow(ewport)), ]
## date AMZN.ret MSFT.ret AAPL.ret TSLA.ret
## 2016-12-30 2016-12-30 NA NA NA NA
## 2017-01-03 2017-01-03 0.005067529 0.007080902 0.002849238 0.015442945
## 2017-01-04 2017-01-04 0.004657224 -0.004474330 -0.001119264 0.046085072
## 2017-06-30 2017-06-30 -0.008125576 0.006424249 0.002366525 0.002383881
names(ewport)[2:5] <- c("AMZN", "MSFT", "AAPL", "TSLA")
rownames(ewport) <- seq(1, nrow(ewport), 1)
ewport[c(1:3, nrow(ewport)), ]
## date AMZN MSFT AAPL TSLA
## 1 2016-12-30 NA NA NA NA
## 2 2017-01-03 0.005067529 0.007080902 0.002849238 0.015442945
## 3 2017-01-04 0.004657224 -0.004474330 -0.001119264 0.046085072
## 126 2017-06-30 -0.008125576 0.006424249 0.002366525 0.002383881Step 2: Convert Net Returns to Gross Returns
ewport[2:5] <- ewport[2:5] + 1
ewport[c(1:3, nrow(ewport)), ]
## date AMZN MSFT AAPL TSLA
## 1 2016-12-30 NA NA NA NA
## 2 2017-01-03 1.0050675 1.0070809 1.0028492 1.015443
## 3 2017-01-04 1.0046572 0.9955257 0.9988807 1.046085
## 126 2017-06-30 0.9918744 1.0064242 1.0023665 1.002384Step 3: Calculate EW Portfolio Values for 1Q 2013
# Subset ewport by data from December 30, 2016 to March 31, 2017
ewq1 <- subset(ewport,
ewport$date >= "2016-12-30" &
ewport$date <= "2017-03-31")
ewq1[c(1:3, nrow(ewq1)), ]
## date AMZN MSFT AAPL TSLA
## 1 2016-12-30 NA NA NA NA
## 2 2017-01-03 1.005068 1.0070809 1.0028492 1.015443
## 3 2017-01-04 1.004657 0.9955257 0.9988807 1.046085
## 63 2017-03-31 1.011639 1.0022828 0.9981242 1.001367
# Calculate the cumulative gross returns fro each security for Q1
ewq1[1, 2:5] <- 1
ewq1[2:5] <- cumprod(ewq1[2:5])
ewq1[c(1:3, nrow(ewq1)), ]
## date AMZN MSFT AAPL TSLA
## 1 2016-12-30 1.000000 1.000000 1.000000 1.000000
## 2 2017-01-03 1.005068 1.007081 1.002849 1.015443
## 3 2017-01-04 1.009748 1.002575 1.001727 1.062240
## 63 2017-03-31 1.182258 1.066290 1.245751 1.302354
# Calculate the index value for each security for Q1
num.sec <- 4
ewq1$AMZN.ind <- ewq1$AMZN / num.sec
ewq1$MSFT.ind <- ewq1$MSFT / num.sec
ewq1$AAPL.ind <- ewq1$AAPL / num.sec
ewq1$TSLA.ind <- ewq1$TSLA / num.sec
ewq1[c(1:3, nrow(ewq1)), ]
## date AMZN MSFT AAPL TSLA AMZN.ind MSFT.ind
## 1 2016-12-30 1.000000 1.000000 1.000000 1.000000 0.2500000 0.2500000
## 2 2017-01-03 1.005068 1.007081 1.002849 1.015443 0.2512669 0.2517702
## 3 2017-01-04 1.009748 1.002575 1.001727 1.062240 0.2524371 0.2506437
## 63 2017-03-31 1.182258 1.066290 1.245751 1.302354 0.2955646 0.2665726
## AAPL.ind TSLA.ind
## 1 0.2500000 0.2500000
## 2 0.2507123 0.2538607
## 3 0.2504317 0.2655599
## 63 0.3114377 0.3255885
# Calculate the agrregate portfolio value on each day
q1.val <- data.frame(rowSums(ewq1[, 6:9]))
q1.val[c(1:3, nrow(q1.val)), ]
## [1] 1.000000 1.007610 1.019072 1.199163
names(q1.val) <- paste("port.val")
q1.val$date <- ewq1$date
q1.val[c(1:3, nrow(q1.val)),]
## port.val date
## 1 1.000000 2016-12-30
## 2 1.007610 2017-01-03
## 3 1.019072 2017-01-04
## 63 1.199163 2017-03-31
# Pass the aggregate portfolio value at the end of Q1 to Q2
q2.inv <- q1.val[nrow(q1.val), 1]
q2.inv
## [1] 1.199163Step 4: Calculate EW Portfolio Values for 2Q 2017
# Subset ewport by data from April 1, 2017 to June 30, 2017
ewq2 <- subset(ewport,
ewport$date >= "2017-04-01" &
ewport$date <= "2017-06-30")
ewq2[c(1:3, nrow(ewq2)), ]
## date AMZN MSFT AAPL TSLA
## 64 2017-04-03 1.0056061 0.9952930 1.0002784 1.0726554
## 65 2017-04-04 1.0171843 1.0027460 1.0074461 1.0173523
## 66 2017-04-05 1.0027017 0.9974135 0.9948194 0.9713533
## 126 2017-06-30 0.9918744 1.0064242 1.0023665 1.0023839
# Calculate the cumulative gross returns fro each security for Q2
#ewq2[1, 2:5] <- 1 This is the difference from Q1
ewq2[2:5] <- cumprod(ewq2[2:5])
ewq2[c(1:3, nrow(ewq2)), ]
## date AMZN MSFT AAPL TSLA
## 64 2017-04-03 1.005606 0.9952930 1.000278 1.072655
## 65 2017-04-04 1.022887 0.9980261 1.007727 1.091269
## 66 2017-04-05 1.025650 0.9954448 1.002506 1.060007
## 126 2017-06-30 1.091885 1.0526131 1.006644 1.299353
# Calculate the index value for each security for Q2
ewq2$AMZN.ind <- (q2.inv / num.sec) * ewq2$AMZN #a difference from Q1
ewq2$MSFT.ind <- (q2.inv / num.sec) * ewq2$MSFT
ewq2$AAPL.ind <- (q2.inv / num.sec) * ewq2$AAPL
ewq2$TSLA.ind <- (q2.inv / num.sec) * ewq2$TSLA
ewq2[c(1:3, nrow(ewq2)), ]
## date AMZN MSFT AAPL TSLA AMZN.ind MSFT.ind
## 64 2017-04-03 1.005606 0.9952930 1.000278 1.072655 0.3014715 0.2983797
## 65 2017-04-04 1.022887 0.9980261 1.007727 1.091269 0.3066521 0.2991991
## 66 2017-04-05 1.025650 0.9954448 1.002506 1.060007 0.3074806 0.2984252
## 126 2017-06-30 1.091885 1.0526131 1.006644 1.299353 0.3273372 0.3155637
## AAPL.ind TSLA.ind
## 64 0.2998743 0.3215723
## 65 0.3021072 0.3271523
## 66 0.3005421 0.3177804
## 126 0.3017826 0.3895342
# Calculate the agrregate portfolio value on each day
q2.val <- data.frame(rowSums(ewq2[, 6:9]))
q2.val[c(1:3, nrow(q2.val)), ]
## [1] 1.221298 1.235111 1.224228 1.334218
names(q2.val) <- paste("port.val")
q2.val$date <- ewq2$date
q2.val[c(1:3, nrow(q2.val)), ]
## port.val date
## 64 1.221298 2017-04-03
## 65 1.235111 2017-04-04
## 66 1.224228 2017-04-05
## 126 1.334218 2017-06-30
#$1.121 at the end of Q1 and 1.125
#Means the portfolio grown from $1.121 at the end of Q1 to $1.125 at the end of Q2
#Note that it doesn't mean that the portfolio grew 12.5% during Q2 but 12.5% during Q1-Q2
#b/c this is a cumulative measure Step 7: Combine Quarterly EW Portfolio Values into One Data Object
ew.portval <- rbind(q1.val, q2.val)
ew.portval[c(1:3, nrow(ew.portval)), ]
## port.val date
## 1 1.000000 2016-12-30
## 2 1.007610 2017-01-03
## 3 1.019072 2017-01-04
## 126 1.334218 2017-06-303.3.2 Value-Weighted Portfolio
It’s considerd as an approach to track the changes in the size of the sector. Larger firms are given more weight in VW, whereas equal weights are given in EW portfolio. An example is the S&P 500 Index. It rebalances only at the beginning of each quarter.
Step 1: Keep Only Variables We Need to Construct EW Portfolio
vwport <- port[, c(1, 2, 4, 6, 8, 10:13)] #port is from 3.3 Constructing Benchmark Portfolio Returns
vwport[c(1:3, nrow(vwport)), ]
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close
## 2016-12-30 2016-12-30 749.87 62.14 115.82 213.69
## 2017-01-03 2017-01-03 753.67 62.58 116.15 216.99
## 2017-01-04 2017-01-04 757.18 62.30 116.02 226.99
## 2017-06-30 2017-06-30 968.00 68.93 144.02 361.61
## AMZN.ret MSFT.ret AAPL.ret TSLA.ret
## 2016-12-30 NA NA NA NA
## 2017-01-03 0.005067529 0.007080902 0.002849238 0.015442945
## 2017-01-04 0.004657224 -0.004474330 -0.001119264 0.046085072
## 2017-06-30 -0.008125576 0.006424249 0.002366525 0.002383881
rownames(vwport) <- seq(1:nrow(vwport))
vwport[c(1:3, nrow(vwport)), ]
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close AMZN.ret
## 1 2016-12-30 749.87 62.14 115.82 213.69 NA
## 2 2017-01-03 753.67 62.58 116.15 216.99 0.005067529
## 3 2017-01-04 757.18 62.30 116.02 226.99 0.004657224
## 126 2017-06-30 968.00 68.93 144.02 361.61 -0.008125576
## MSFT.ret AAPL.ret TSLA.ret
## 1 NA NA NA
## 2 0.007080902 0.002849238 0.015442945
## 3 -0.004474330 -0.001119264 0.046085072
## 126 0.006424249 0.002366525 0.002383881Step 2: Convert Net Returns to Gross Returns
vwport$AMZN.ret <- vwport$AMZN.ret + 1
vwport$MSFT.ret <- vwport$MSFT.ret + 1
vwport$AAPL.ret <- vwport$AAPL.ret + 1
vwport$TSLA.ret <- vwport$TSLA.ret + 1
vwport[c(1:3, nrow(vwport)), ]
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close AMZN.ret
## 1 2016-12-30 749.87 62.14 115.82 213.69 NA
## 2 2017-01-03 753.67 62.58 116.15 216.99 1.0050675
## 3 2017-01-04 757.18 62.30 116.02 226.99 1.0046572
## 126 2017-06-30 968.00 68.93 144.02 361.61 0.9918744
## MSFT.ret AAPL.ret TSLA.ret
## 1 NA NA NA
## 2 1.0070809 1.0028492 1.015443
## 3 0.9955257 0.9988807 1.046085
## 126 1.0064242 1.0023665 1.002384Step 3: Calculate the Market Capitalization of Each Security in the Portfolio
# Construct Series of Calendar Days
date <- seq(as.Date("2016-12-30"), as.Date("2017-12-31"), by = 1)
date <- data.frame(date)
date[c(1:3, nrow(date)), ]
## [1] "2016-12-30" "2016-12-31" "2017-01-01" "2017-12-31"
# Create Data Object with Daily Prices
PRICE.qtr <- vwport[, 1:5]
PRICE.qtr[c(1:3, nrow(PRICE.qtr)), ]
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close
## 1 2016-12-30 749.87 62.14 115.82 213.69
## 2 2017-01-03 753.67 62.58 116.15 216.99
## 3 2017-01-04 757.18 62.30 116.02 226.99
## 126 2017-06-30 968.00 68.93 144.02 361.61
# Filling in Last Available Price on Non-trading Days
PRICE.qtr <- na.locf(merge(x = date, y = PRICE.qtr, by = "date", all.x = TRUE))
#all.x=T tells R to keep all the data in the "by" varialble that is available in x=date
#na.locf tells R to copy over the last available data in y
#when there is a date in x that is not available in y
PRICE.qtr[c(1:3, nrow(PRICE.qtr)), ]
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close
## 1 2016-12-30 749.87 62.14 115.82 213.69
## 2 2016-12-31 749.87 62.14 115.82 213.69
## 3 2017-01-01 749.87 62.14 115.82 213.69
## 367 2017-12-31 968.00 68.93 144.02 361.61
# Keep Only Prices at the End of Each Calendar Quarter
PRICE.qtr <- subset(PRICE.qtr,
PRICE.qtr$date == as.Date("2016-12-30") |
PRICE.qtr$date == as.Date("2017-03-31"))
PRICE.qtr
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close
## 1 2016-12-30 749.87 62.14 115.82 213.69
## 92 2017-03-31 886.54 65.86 143.66 278.30
# Obtain Share Outstanding Data from SEC Filings
#http://www.sec.gov/edgar/searchedgar/companysearch.html
#Interactive Data / Financial Statements / Condensed Consolidated Balance Sheets
PRICE.qtr$AMZN.shout <- c(477000000, 478000000)
PRICE.qtr$MSFT.shout <- c(7730000000, 7723000000)
PRICE.qtr$AAPL.shout <- c(5255423 , 5205815)
PRICE.qtr$TSLA.shout <- c(161561000, 164164000)
PRICE.qtr
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close AMZN.shout
## 1 2016-12-30 749.87 62.14 115.82 213.69 4.77e+08
## 92 2017-03-31 886.54 65.86 143.66 278.30 4.78e+08
## MSFT.shout AAPL.shout TSLA.shout
## 1 7.730e+09 5255423 161561000
## 92 7.723e+09 5205815 164164000
# Calculate Market Capitalization of Each Security
str(PRICE.qtr)
## 'data.frame': 2 obs. of 9 variables:
## $ date : chr "2016-12-30" "2017-03-31"
## $ AMZN.Close: chr " 749.87" " 886.54"
## $ MSFT.Close: chr "62.14" "65.86"
## $ AAPL.Close: chr "115.82" "143.66"
## $ TSLA.Close: chr "213.69" "278.30"
## $ AMZN.shout: num 4.77e+08 4.78e+08
## $ MSFT.shout: num 7.73e+09 7.72e+09
## $ AAPL.shout: num 5255423 5205815
## $ TSLA.shout: num 1.62e+08 1.64e+08
PRICE.qtr$date <- as.Date(PRICE.qtr$date)
PRICE.qtr$AMZN.Close <- as.numeric(PRICE.qtr$AMZN.Close)
PRICE.qtr$MSFT.Close <- as.numeric(PRICE.qtr$MSFT.Close)
PRICE.qtr$AAPL.Close <- as.numeric(PRICE.qtr$AAPL.Close)
PRICE.qtr$TSLA.Close <- as.numeric(PRICE.qtr$TSLA.Close)
str(PRICE.qtr)
## 'data.frame': 2 obs. of 9 variables:
## $ date : Date, format: "2016-12-30" "2017-03-31"
## $ AMZN.Close: num 750 887
## $ MSFT.Close: num 62.1 65.9
## $ AAPL.Close: num 116 144
## $ TSLA.Close: num 214 278
## $ AMZN.shout: num 4.77e+08 4.78e+08
## $ MSFT.shout: num 7.73e+09 7.72e+09
## $ AAPL.shout: num 5255423 5205815
## $ TSLA.shout: num 1.62e+08 1.64e+08
weights <- PRICE.qtr
weights$AMZN.mcap <- weights$AMZN.Close * weights$AMZN.shout
weights$MSFT.mcap <- weights$MSFT.Close * weights$MSFT.shout
weights$AAPL.mcap <- weights$AAPL.Close * weights$AAPL.shout
weights$TSLA.mcap <- weights$TSLA.Close * weights$TSLA.shout
weights
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close AMZN.shout
## 1 2016-12-30 749.87 62.14 115.82 213.69 4.77e+08
## 92 2017-03-31 886.54 65.86 143.66 278.30 4.78e+08
## MSFT.shout AAPL.shout TSLA.shout AMZN.mcap MSFT.mcap AAPL.mcap
## 1 7.730e+09 5255423 161561000 357687990000 480342200000 608683092
## 92 7.723e+09 5205815 164164000 423766120000 508636780000 747867383
## TSLA.mcap
## 1 34523970090
## 92 45686841200
# Calculate Quarter-end Aggregate Market Capitalization
weights$tot.mcap <- rowSums(weights[,10:13])
weights
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close AMZN.shout
## 1 2016-12-30 749.87 62.14 115.82 213.69 4.77e+08
## 92 2017-03-31 886.54 65.86 143.66 278.30 4.78e+08
## MSFT.shout AAPL.shout TSLA.shout AMZN.mcap MSFT.mcap AAPL.mcap
## 1 7.730e+09 5255423 161561000 357687990000 480342200000 608683092
## 92 7.723e+09 5205815 164164000 423766120000 508636780000 747867383
## TSLA.mcap tot.mcap
## 1 34523970090 873162843182
## 92 45686841200 978837608583Step 4: Calculate Quarter-end Weights of Each Security in the Portfolio
weights$AMZN.wgt <- weights$AMZN.mcap / weights$tot.mcap
weights$MSFT.wgt <- weights$MSFT.mcap / weights$tot.mcap
weights$AAPL.wgt <- weights$AAPL.mcap / weights$tot.mcap
weights$TSLA.wgt <- weights$TSLA.mcap / weights$tot.mcap
weights
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close AMZN.shout
## 1 2016-12-30 749.87 62.14 115.82 213.69 4.77e+08
## 92 2017-03-31 886.54 65.86 143.66 278.30 4.78e+08
## MSFT.shout AAPL.shout TSLA.shout AMZN.mcap MSFT.mcap AAPL.mcap
## 1 7.730e+09 5255423 161561000 357687990000 480342200000 608683092
## 92 7.723e+09 5205815 164164000 423766120000 508636780000 747867383
## TSLA.mcap tot.mcap AMZN.wgt MSFT.wgt AAPL.wgt TSLA.wgt
## 1 34523970090 873162843182 0.4096464 0.5501175 0.0006971015 0.03953898
## 92 45686841200 978837608583 0.4329279 0.5196335 0.0007640362 0.04667459
weights <- weights[, c(1, 15:18)]
weights
## date AMZN.wgt MSFT.wgt AAPL.wgt TSLA.wgt
## 1 2016-12-30 0.4096464 0.5501175 0.0006971015 0.03953898
## 92 2017-03-31 0.4329279 0.5196335 0.0007640362 0.04667459
weights[1, "date"] <- weights[1, "date"]+2
weights[2, "date"] <- weights[2, "date"]+1 #since the weights are applicable at the start of the next Q
weights
## date AMZN.wgt MSFT.wgt AAPL.wgt TSLA.wgt
## 1 2017-01-01 0.4096464 0.5501175 0.0006971015 0.03953898
## 92 2017-04-01 0.4329279 0.5196335 0.0007640362 0.04667459Step 5: Calculate the Quarterly VW Portfolio Values
q1.vw.wgt <- subset(weights, date == "2017-01-01") #I'm not sure why author took the long way
q2.vw.wgt <- subset(weights, date == "2017-04-01")
q1.vw.wgt
## date AMZN.wgt MSFT.wgt AAPL.wgt TSLA.wgt
## 1 2017-01-01 0.4096464 0.5501175 0.0006971015 0.03953898
q2.vw.wgt
## date AMZN.wgt MSFT.wgt AAPL.wgt TSLA.wgt
## 92 2017-04-01 0.4329279 0.5196335 0.0007640362 0.04667459Step 7: Calculate VW Portfolio Values for 1Q 2013
vw.q1 <- subset(vwport,
vwport$date >= as.Date("2016-12-30") &
vwport$date <= as.Date("2017-03-31"))
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close AMZN.ret
## 1 2016-12-30 749.87 62.14 115.82 213.69 NA
## 2 2017-01-03 753.67 62.58 116.15 216.99 1.005068
## 3 2017-01-04 757.18 62.30 116.02 226.99 1.004657
## 63 2017-03-31 886.54 65.86 143.66 278.30 1.011639
## MSFT.ret AAPL.ret TSLA.ret
## 1 NA NA NA
## 2 1.0070809 1.0028492 1.015443
## 3 0.9955257 0.9988807 1.046085
## 63 1.0022828 0.9981242 1.001367
vw.q1 <- vw.q1[, c(1, 6:9)]
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN.ret MSFT.ret AAPL.ret TSLA.ret
## 1 2016-12-30 NA NA NA NA
## 2 2017-01-03 1.005068 1.0070809 1.0028492 1.015443
## 3 2017-01-04 1.004657 0.9955257 0.9988807 1.046085
## 63 2017-03-31 1.011639 1.0022828 0.9981242 1.001367
names(vw.q1)[2:5] <- paste(c("AMZN", "MSFT", "AAPL", "TSLA"))
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN MSFT AAPL TSLA
## 1 2016-12-30 NA NA NA NA
## 2 2017-01-03 1.005068 1.0070809 1.0028492 1.015443
## 3 2017-01-04 1.004657 0.9955257 0.9988807 1.046085
## 63 2017-03-31 1.011639 1.0022828 0.9981242 1.001367
vw.q1[1, 2:5] <- 1
vw.q1$AMZN <- cumprod(vw.q1$AMZN)
vw.q1$MSFT <- cumprod(vw.q1$MSFT)
vw.q1$AAPL <- cumprod(vw.q1$AAPL)
vw.q1$TSLA <- cumprod(vw.q1$TSLA)
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN MSFT AAPL TSLA
## 1 2016-12-30 1.000000 1.000000 1.000000 1.000000
## 2 2017-01-03 1.005068 1.007081 1.002849 1.015443
## 3 2017-01-04 1.009748 1.002575 1.001727 1.062240
## 63 2017-03-31 1.182258 1.066290 1.245751 1.302354
vw.q1$AMZN.idx <- vw.q1$AMZN * q1.vw.wgt$AMZN.wgt #Apply the Q-end weights; note
# that q1.vw.wgt$AMZN.wgt is a scalar
vw.q1$MSFT.idx <- vw.q1$MSFT * q1.vw.wgt$MSFT.wgt
vw.q1$AAPL.idx <- vw.q1$AAPL * q1.vw.wgt$AAPL.wgt
vw.q1$TSLA.idx <- vw.q1$TSLA * q1.vw.wgt$TSLA.wgt
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN MSFT AAPL TSLA AMZN.idx MSFT.idx
## 1 2016-12-30 1.000000 1.000000 1.000000 1.000000 0.4096464 0.5501175
## 2 2017-01-03 1.005068 1.007081 1.002849 1.015443 0.4117223 0.5540129
## 3 2017-01-04 1.009748 1.002575 1.001727 1.062240 0.4136397 0.5515340
## 63 2017-03-31 1.182258 1.066290 1.245751 1.302354 0.4843078 0.5865851
## AAPL.idx TSLA.idx
## 1 0.0006971015 0.03953898
## 2 0.0006990877 0.04014958
## 3 0.0006983052 0.04199988
## 63 0.0008684147 0.05149374
#idx = daily gross returns * quarterly weight
#weight is based on market capitalization
#So larger firms would have a greater influence on portfolio returns
q1.vw.val <- data.frame(rowSums(vw.q1[, 6:9])) #Calculate the daily portfolio values
q1.vw.val[c(1:3, nrow(q1.vw.val)), ]
## [1] 1.000000 1.006584 1.007872 1.123255
names(q1.vw.val) <- paste("port.val")
q1.vw.val$date <- vw.q1$date
q1.vw.val[c(1:3, nrow(q1.vw.val)), ]
## port.val date
## 1 1.000000 2016-12-30
## 2 1.006584 2017-01-03
## 3 1.007872 2017-01-04
## 63 1.123255 2017-03-31
q2.vw.inv <- q1.vw.val[nrow(q1.vw.val), 1]
q2.vw.inv
## [1] 1.123255Step 8: Calculate VW Portfolio Values for 2Q 2013
vw.q2 <- subset(vwport,
vwport$date >= "2017-04-01" &
vwport$date <= "2017-06-30")
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN.Close MSFT.Close AAPL.Close TSLA.Close AMZN.ret
## 64 2017-04-03 891.51 65.55 143.70 298.52 1.0056061
## 65 2017-04-04 906.83 65.73 144.77 303.70 1.0171843
## 66 2017-04-05 909.28 65.56 144.02 295.00 1.0027017
## 126 2017-06-30 968.00 68.93 144.02 361.61 0.9918744
## MSFT.ret AAPL.ret TSLA.ret
## 64 0.9952930 1.0002784 1.0726554
## 65 1.0027460 1.0074461 1.0173523
## 66 0.9974135 0.9948194 0.9713533
## 126 1.0064242 1.0023665 1.0023839
vw.q2 <- vw.q2[, c(1, 6:9)]
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN.ret MSFT.ret AAPL.ret TSLA.ret
## 64 2017-04-03 1.0056061 0.9952930 1.0002784 1.0726554
## 65 2017-04-04 1.0171843 1.0027460 1.0074461 1.0173523
## 66 2017-04-05 1.0027017 0.9974135 0.9948194 0.9713533
## 126 2017-06-30 0.9918744 1.0064242 1.0023665 1.0023839
names(vw.q2)[2:5] <- paste(c("AMZN", "MSFT", "AAPL", "TSLA"))
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN MSFT AAPL TSLA
## 64 2017-04-03 1.0056061 0.9952930 1.0002784 1.0726554
## 65 2017-04-04 1.0171843 1.0027460 1.0074461 1.0173523
## 66 2017-04-05 1.0027017 0.9974135 0.9948194 0.9713533
## 126 2017-06-30 0.9918744 1.0064242 1.0023665 1.0023839
#vw.q2[1, 2:5] <- 1 #difference from Q1
vw.q2$AMZN <- cumprod(vw.q2$AMZN)
vw.q2$MSFT <- cumprod(vw.q2$MSFT)
vw.q2$AAPL <- cumprod(vw.q2$AAPL)
vw.q2$TSLA <- cumprod(vw.q2$TSLA)
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN MSFT AAPL TSLA
## 64 2017-04-03 1.005606 0.9952930 1.000278 1.072655
## 65 2017-04-04 1.022887 0.9980261 1.007727 1.091269
## 66 2017-04-05 1.025650 0.9954448 1.002506 1.060007
## 126 2017-06-30 1.091885 1.0526131 1.006644 1.299353
vw.q2$AMZN.ind <- (q2.vw.inv * q2.vw.wgt$AMZN.wgt) * vw.q2$AMZN #Apply weight
vw.q2$MSFT.ind <- (q2.vw.inv * q2.vw.wgt$MSFT.wgt) * vw.q2$MSFT
vw.q2$AAPL.ind <- (q2.vw.inv * q2.vw.wgt$AAPL.wgt) * vw.q2$AAPL
vw.q2$TSLA.ind <- (q2.vw.inv * q2.vw.wgt$TSLA.wgt) * vw.q2$TSLA
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN MSFT AAPL TSLA AMZN.ind MSFT.ind
## 64 2017-04-03 1.005606 0.9952930 1.000278 1.072655 0.4890146 0.5809335
## 65 2017-04-04 1.022887 0.9980261 1.007727 1.091269 0.4974180 0.5825288
## 66 2017-04-05 1.025650 0.9954448 1.002506 1.060007 0.4987619 0.5810221
## 126 2017-06-30 1.091885 1.0526131 1.006644 1.299353 0.5309712 0.6143901
## AAPL.ind TSLA.ind
## 64 0.0008584465 0.05623660
## 65 0.0008648385 0.05721244
## 66 0.0008603581 0.05557349
## 126 0.0008639093 0.06812179
q2.vw.val <- data.frame(rowSums(vw.q2[, 6:9]))
q2.vw.val[c(1:3, nrow(q2.vw.val)), ]
## [1] 1.127043 1.138024 1.136218 1.214347
names(q2.vw.val) <- paste("port.val")
q2.vw.val[c(1:3, nrow(q2.vw.val)), ]
## [1] 1.127043 1.138024 1.136218 1.214347
q2.vw.val$date <- vw.q2$date
q2.vw.val[c(1:3, nrow(q2.vw.val)), ]
## port.val date
## 64 1.127043 2017-04-03
## 65 1.138024 2017-04-04
## 66 1.136218 2017-04-05
## 126 1.214347 2017-06-30
#$1.104 at the end of Q1 and $1.056 by the end of Q2
#it doesn't mean that the portfolio grew 5.6% during Q2 but 5.6% during Q1-Q2
#b/c it's a cumulative measureStep 11: Combine Quarterly VW Portfolio Values into One Data Object
vw.portval <- rbind(q1.vw.val, q2.vw.val)
vw.portval[c(1:3, nrow(vw.portval)), ]
## port.val date
## 1 1.000000 2016-12-30
## 2 1.006584 2017-01-03
## 3 1.007872 2017-01-04
## 126 1.214347 2017-06-303.3.3 Normalized EW and VW Portfolio Price Chart
Compare the performance of EW and VW portfolio from 12/30/2016 to 12/31/2017
Step 1: Combine the Data
port.val <- merge(vw.portval, ew.portval, by = "date")
port.val[c(1:3, nrow(port.val)), ]
## date port.val.x port.val.y
## 1 2016-12-30 1.000000 1.000000
## 2 2017-01-03 1.006584 1.007610
## 3 2017-01-04 1.007872 1.019072
## 126 2017-06-30 1.214347 1.334218Step 2: Rename the Variables
names(port.val)[2:3] <- paste(c("VW.cum", "EW.cum"))
port.val[c(1:3, nrow(port.val)), ]
## date VW.cum EW.cum
## 1 2016-12-30 1.000000 1.000000
## 2 2017-01-03 1.006584 1.007610
## 3 2017-01-04 1.007872 1.019072
## 126 2017-06-30 1.214347 1.334218Step 3: Plot the Data
par(mfrow = c(1, 1))
y.range <- range(port.val[, 2:3])
y.range
## [1] 1.000000 1.394362
plot(x = port.val$date,
y = port.val$EW.cum,
xlab = "Date",
ylab = "Value of Investment",
ylim = y.range,
type = "l",
lty = 1,
main = "Value of R1 Investment in Equal-Weighted and
Value-Weighted Portfolio of AMZN, MSFT, AAPL, and TSLA
December 30, 2016 - June 30, 2017")
lines(x = port.val$date,
y = port.val$VW.cum,
lty = 2)
abline(h = 1, lty = 1)
legend("topleft",
c("Equal-Weighted Portfolio", "Value-Weighted Portfolio"), #typo in the textbook
lty = c(1, 2))