Financial Modeling
Ch3 Portfolio Returns
Daniel Lee
Chapter 3: Portfolio Returns - Five-Stock Case
Construct an equal-weighted (EW) and value-weighted (VW) portfolio, consisting of AMZN, AABA, and IBM.
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 = "2010-12-31", to = "2014-01-01", auto.assign = FALSE)
data.AABA <- getSymbols("AABA", from = "2010-12-31", to = "2014-01-01", auto.assign = FALSE)
data.IBM <- getSymbols("IBM", from = "2010-12-31", to = "2014-01-01", auto.assign = FALSE)
data.MSFT <- getSymbols("MSFT", from = "2010-12-31", to = "2014-01-01", auto.assign = FALSE)
data.FB <- getSymbols("FB", from = "2010-12-31", to = "2014-01-01", auto.assign = FALSE)
#to = "2013-12-31" in the book was replaced by to = "2014-01-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
## 2010-12-31 181.96 182.30 179.51 180.00 3451900
## 2011-01-03 181.37 186.00 181.21 184.22 5331400
## 2011-01-04 186.15 187.70 183.78 185.01 5031800
## 2013-12-31 394.58 398.83 393.80 398.79 1996500
## AMZN.Adjusted
## 2010-12-31 180.00
## 2011-01-03 184.22
## 2011-01-04 185.01
## 2013-12-31 398.79
data.AABA[c(1:3, nrow(data.AABA)), ]
## AABA.Open AABA.High AABA.Low AABA.Close AABA.Volume
## 2010-12-31 16.74 16.76 16.47 16.63 7754500
## 2011-01-03 16.81 16.94 16.67 16.75 17684000
## 2011-01-04 16.71 16.83 16.57 16.59 11092800
## 2013-12-31 40.17 40.50 40.00 40.44 8291400
## AABA.Adjusted
## 2010-12-31 16.63
## 2011-01-03 16.75
## 2011-01-04 16.59
## 2013-12-31 40.44
data.IBM[c(1:3, nrow(data.IBM)), ]
## IBM.Open IBM.High IBM.Low IBM.Close IBM.Volume IBM.Adjusted
## 2010-12-31 176.417 176.826 175.492 146.76 2969800 122.0634
## 2011-01-03 176.994 178.185 176.910 147.48 4603800 122.6622
## 2011-01-04 177.415 178.209 176.309 147.64 5060100 122.7953
## 2013-12-31 212.715 214.197 212.498 187.57 3619700 164.4453
data.MSFT[c(1:3, nrow(data.MSFT)), ]
## MSFT.Open MSFT.High MSFT.Low MSFT.Close MSFT.Volume
## 2010-12-31 33.515 33.659 33.310 27.91 24752000
## 2011-01-03 33.816 33.973 33.659 27.98 53443800
## 2011-01-04 33.683 33.961 33.575 28.09 54405600
## 2013-12-31 41.459 41.658 41.259 37.41 17503500
## MSFT.Adjusted
## 2010-12-31 23.15101
## 2011-01-03 23.20908
## 2011-01-04 23.30032
## 2013-12-31 33.74764
data.FB[c(1:3, nrow(data.FB)), ]
## FB.Open FB.High FB.Low FB.Close FB.Volume FB.Adjusted
## 2012-05-18 42.05 45.00 38.00 38.23 573576400 38.23
## 2012-05-21 36.53 36.66 33.00 34.03 168192700 34.03
## 2012-05-22 32.61 33.59 30.94 31.00 101786600 31.00
## 2013-12-31 54.12 54.86 53.91 54.65 43076200 54.65Step 2: Create Object with Only the Relevant Data
port <- data.AMZN[, c(4, 6)]
port <- merge(port, data.AABA[, c(4, 6)])
port <- merge(port, data.IBM[, c(4, 6)])
port <- merge(port, data.MSFT[, c(4, 6)])
port <- merge(port, data.FB[, c(4, 6)])
#port <- cbind(port, data.AABA[, c(4, 6)], data.IBM[, c(4, 6)])
port[c(1:3, nrow(port)), ]
## AMZN.Close AMZN.Adjusted AABA.Close AABA.Adjusted IBM.Close
## 2010-12-31 180.00 180.00 16.63 16.63 146.76
## 2011-01-03 184.22 184.22 16.75 16.75 147.48
## 2011-01-04 185.01 185.01 16.59 16.59 147.64
## 2013-12-31 398.79 398.79 40.44 40.44 187.57
## IBM.Adjusted MSFT.Close MSFT.Adjusted FB.Close FB.Adjusted
## 2010-12-31 122.0634 27.91 23.15101 NA NA
## 2011-01-03 122.6622 27.98 23.20908 NA NA
## 2011-01-04 122.7953 28.09 23.30032 NA NA
## 2013-12-31 164.4453 37.41 33.74764 54.65 54.65Step 3: Calculate Returns of Each Security
port$AMZN.ret <- Delt(port$AMZN.Adjusted)
port$AABA.ret <- Delt(port$AABA.Adjusted)
port$IBM.ret <- Delt(port$IBM.Adjusted)
port$MSFT.ret <- Delt(port$MSFT.Adjusted)
port$FB.ret <- Delt(port$FB.Adjusted)
port[c(1:3, nrow(port)), ]
## AMZN.Close AMZN.Adjusted AABA.Close AABA.Adjusted IBM.Close
## 2010-12-31 180.00 180.00 16.63 16.63 146.76
## 2011-01-03 184.22 184.22 16.75 16.75 147.48
## 2011-01-04 185.01 185.01 16.59 16.59 147.64
## 2013-12-31 398.79 398.79 40.44 40.44 187.57
## IBM.Adjusted MSFT.Close MSFT.Adjusted FB.Close FB.Adjusted
## 2010-12-31 122.0634 27.91 23.15101 NA NA
## 2011-01-03 122.6622 27.98 23.20908 NA NA
## 2011-01-04 122.7953 28.09 23.30032 NA NA
## 2013-12-31 164.4453 37.41 33.74764 54.65 54.65
## AMZN.ret AABA.ret IBM.ret MSFT.ret FB.ret
## 2010-12-31 NA NA NA NA NA
## 2011-01-03 0.023444450 0.007215935 0.004906025 0.002508141 NA
## 2011-01-04 0.004288318 -0.009552239 0.001085053 0.003931436 NA
## 2013-12-31 0.013778412 0.005970099 0.006222998 0.003218311 0.01750145Step 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 AABA.Close AABA.Adjusted
## 2010-12-31 2010-12-31 180.00 180.00 16.63 16.63
## 2011-01-03 2011-01-03 184.22 184.22 16.75 16.75
## 2011-01-04 2011-01-04 185.01 185.01 16.59 16.59
## 2013-12-31 2013-12-31 398.79 398.79 40.44 40.44
## IBM.Close IBM.Adjusted MSFT.Close MSFT.Adjusted FB.Close
## 2010-12-31 146.76 122.0634 27.91 23.15101 NA
## 2011-01-03 147.48 122.6622 27.98 23.20908 NA
## 2011-01-04 147.64 122.7953 28.09 23.30032 NA
## 2013-12-31 187.57 164.4453 37.41 33.74764 54.65
## FB.Adjusted AMZN.ret AABA.ret IBM.ret MSFT.ret
## 2010-12-31 NA NA NA NA NA
## 2011-01-03 NA 0.023444450 0.007215935 0.004906025 0.002508141
## 2011-01-04 NA 0.004288318 -0.009552239 0.001085053 0.003931436
## 2013-12-31 54.65 0.013778412 0.005970099 0.006222998 0.003218311
## FB.ret
## 2010-12-31 NA
## 2011-01-03 NA
## 2011-01-04 NA
## 2013-12-31 0.01750145
port <- subset(port,
port$date >= "2012-12-31" &
port$date <= "2013-12-31")
port[c(1:3, nrow(port)), ]
## date AMZN.Close AMZN.Adjusted AABA.Close AABA.Adjusted
## 2012-12-31 2012-12-31 250.87 250.87 19.90 19.90
## 2013-01-02 2013-01-02 257.31 257.31 20.08 20.08
## 2013-01-03 2013-01-03 258.48 258.48 19.78 19.78
## 2013-12-31 2013-12-31 398.79 398.79 40.44 40.44
## IBM.Close IBM.Adjusted MSFT.Close MSFT.Adjusted FB.Close
## 2012-12-31 191.55 164.7379 26.71 23.38747 26.62
## 2013-01-02 196.35 168.8661 27.62 24.18427 28.00
## 2013-01-03 195.27 167.9372 27.25 23.86029 27.77
## 2013-12-31 187.57 164.4453 37.41 33.74764 54.65
## FB.Adjusted AMZN.ret AABA.ret IBM.ret MSFT.ret
## 2012-12-31 26.62 0.023207448 0.020512821 0.009060670 0.006026316
## 2013-01-02 28.00 0.025670679 0.009045226 0.025058852 0.034069574
## 2013-01-03 27.77 0.004547095 -0.014940189 -0.005500590 -0.013396147
## 2013-12-31 54.65 0.013778412 0.005970099 0.006222998 0.003218311
## FB.ret
## 2012-12-31 0.027402586
## 2013-01-02 0.051840682
## 2013-01-03 -0.008214286
## 2013-12-31 0.0175014533.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, 12:16)]
ewport[c(1:3, nrow(ewport)), ]
## date AMZN.ret AABA.ret IBM.ret MSFT.ret
## 2012-12-31 2012-12-31 0.023207448 0.020512821 0.009060670 0.006026316
## 2013-01-02 2013-01-02 0.025670679 0.009045226 0.025058852 0.034069574
## 2013-01-03 2013-01-03 0.004547095 -0.014940189 -0.005500590 -0.013396147
## 2013-12-31 2013-12-31 0.013778412 0.005970099 0.006222998 0.003218311
## FB.ret
## 2012-12-31 0.027402586
## 2013-01-02 0.051840682
## 2013-01-03 -0.008214286
## 2013-12-31 0.017501453
names(ewport)[2:6] <- c("AMZN", "AABA", "IBM", "MSFT", "FB")
rownames(ewport) <- seq(1, nrow(ewport), 1)
ewport[c(1:3, nrow(ewport)), ]
## date AMZN AABA IBM MSFT
## 1 2012-12-31 0.023207448 0.020512821 0.009060670 0.006026316
## 2 2013-01-02 0.025670679 0.009045226 0.025058852 0.034069574
## 3 2013-01-03 0.004547095 -0.014940189 -0.005500590 -0.013396147
## 253 2013-12-31 0.013778412 0.005970099 0.006222998 0.003218311
## FB
## 1 0.027402586
## 2 0.051840682
## 3 -0.008214286
## 253 0.017501453Step 2: Convert Net Returns to Gross Returns
ewport[2:6] <- ewport[2:6] + 1
ewport[c(1:3, nrow(ewport)), ]
## date AMZN AABA IBM MSFT FB
## 1 2012-12-31 1.023207 1.0205128 1.0090607 1.0060263 1.0274026
## 2 2013-01-02 1.025671 1.0090452 1.0250589 1.0340696 1.0518407
## 3 2013-01-03 1.004547 0.9850598 0.9944994 0.9866039 0.9917857
## 253 2013-12-31 1.013778 1.0059701 1.0062230 1.0032183 1.0175015Step 3: Calculate EW Portfolio Values for 1Q 2013
# Subset ewport by data from December 31, 2012 to March 31, 2013
ewq1 <- subset(ewport,
ewport$date >= "2012-12-31" &
ewport$date <= "2013-03-31")
ewq1[c(1:3, nrow(ewq1)), ]
## date AMZN AABA IBM MSFT FB
## 1 2012-12-31 1.023207 1.0205128 1.0090607 1.0060263 1.0274026
## 2 2013-01-02 1.025671 1.0090452 1.0250589 1.0340696 1.0518407
## 3 2013-01-03 1.004547 0.9850598 0.9944994 0.9866039 0.9917857
## 61 2013-03-28 1.004485 0.9974566 1.0114279 1.0084597 0.9804523
# Calculate the cumulative gross returns fro each security for Q1
ewq1[1, 2:6] <- 1
ewq1[2:6] <- cumprod(ewq1[2:6])
ewq1[c(1:3, nrow(ewq1)), ]
## date AMZN AABA IBM MSFT FB
## 1 2012-12-31 1.000000 1.0000000 1.000000 1.000000 1.0000000
## 2 2013-01-02 1.025671 1.0090452 1.025059 1.034070 1.0518407
## 3 2013-01-03 1.030335 0.9939699 1.019420 1.020217 1.0432006
## 61 2013-03-28 1.062263 1.1824121 1.118235 1.080003 0.9609316
# Calculate the index value for each security for Q1
num.sec <- 5
ewq1$AMZN.ind <- ewq1$AMZN / num.sec
ewq1$AABA.ind <- ewq1$AABA / num.sec
ewq1$IBM.ind <- ewq1$IBM / num.sec
ewq1$MSFT.ind <- ewq1$MSFT / num.sec
ewq1$FB.ind <- ewq1$FB / num.sec
ewq1[c(1:3, nrow(ewq1)), ]
## date AMZN AABA IBM MSFT FB AMZN.ind
## 1 2012-12-31 1.000000 1.0000000 1.000000 1.000000 1.0000000 0.2000000
## 2 2013-01-02 1.025671 1.0090452 1.025059 1.034070 1.0518407 0.2051341
## 3 2013-01-03 1.030335 0.9939699 1.019420 1.020217 1.0432006 0.2060669
## 61 2013-03-28 1.062263 1.1824121 1.118235 1.080003 0.9609316 0.2124527
## AABA.ind IBM.ind MSFT.ind FB.ind
## 1 0.2000000 0.2000000 0.2000000 0.2000000
## 2 0.2018090 0.2050118 0.2068139 0.2103681
## 3 0.1987940 0.2038841 0.2040434 0.2086401
## 61 0.2364824 0.2236469 0.2160005 0.1921863
# Calculate the agrregate portfolio value on each day
q1.val <- data.frame(rowSums(ewq1[, 7:11]))
q1.val[c(1:3, nrow(q1.val)), ]
## [1] 1.000000 1.029137 1.021428 1.080769
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 2012-12-31
## 2 1.029137 2013-01-02
## 3 1.021428 2013-01-03
## 61 1.080769 2013-03-28
# Pass the aggregate portfolio value at the end of Q1 to Q2
q2.inv <- q1.val[nrow(q1.val), 1]
q2.inv
## [1] 1.080769Step 4: Calculate EW Portfolio Values for 2Q 2013
# Subset ewport by data from April 1, 2013 to June 30, 2013
ewq2 <- subset(ewport,
ewport$date >= "2013-04-01" &
ewport$date <= "2013-06-30")
ewq2[c(1:3, nrow(ewq2)), ]
## date AMZN AABA IBM MSFT FB
## 62 2013-04-01 0.9816878 0.9987250 0.9956867 1.0000000 0.9980454
## 63 2013-04-02 1.0065365 1.0119149 1.0093230 1.0066412 0.9956913
## 64 2013-04-03 0.9837080 0.9831791 0.9920694 0.9916666 1.0326515
## 125 2013-06-28 1.0005045 0.9866510 0.9767954 0.9976890 1.0089213
# Calculate the cumulative gross returns fro each security for Q2
#ewq2[1, 2:4] <- 1 This is the difference from Q1
ewq2[2:6] <- cumprod(ewq2[2:6])
ewq2[c(1:3, nrow(ewq2)), ]
## date AMZN AABA IBM MSFT FB
## 62 2013-04-01 0.9816878 0.9987250 0.9956867 1.0000000 0.9980454
## 63 2013-04-02 0.9881047 1.0106247 1.0049695 1.0066412 0.9937451
## 64 2013-04-03 0.9720065 0.9936251 0.9969995 0.9982524 1.0261923
## 125 2013-06-28 1.0420279 1.0679982 0.9001679 1.2157358 0.9726348
# Calculate the index value for each security for Q2
ewq2$AMZN.ind <- (q2.inv / num.sec) * ewq2$AMZN #a difference from Q1
ewq2$AABA.ind <- (q2.inv / num.sec) * ewq2$AABA
ewq2$IBM.ind <- (q2.inv / num.sec) * ewq2$IBM
ewq2$MSFT.ind <- (q2.inv / num.sec) * ewq2$MSFT
ewq2$FB.ind <- (q2.inv / num.sec) * ewq2$FB
ewq2[c(1:3, nrow(ewq2)), ]
## date AMZN AABA IBM MSFT FB AMZN.ind
## 62 2013-04-01 0.9816878 0.9987250 0.9956867 1.0000000 0.9980454 0.2121955
## 63 2013-04-02 0.9881047 1.0106247 1.0049695 1.0066412 0.9937451 0.2135825
## 64 2013-04-03 0.9720065 0.9936251 0.9969995 0.9982524 1.0261923 0.2101029
## 125 2013-06-28 1.0420279 1.0679982 0.9001679 1.2157358 0.9726348 0.2252383
## AABA.ind IBM.ind MSFT.ind FB.ind
## 62 0.2158782 0.2152214 0.2161538 0.2157313
## 63 0.2184503 0.2172279 0.2175893 0.2148017
## 64 0.2147758 0.2155052 0.2157760 0.2218153
## 125 0.2308518 0.1945747 0.2627859 0.2102387
# Calculate the agrregate portfolio value on each day
q2.val <- data.frame(rowSums(ewq2[, 7:11]))
q2.val[c(1:3, nrow(q2.val)), ]
## [1] 1.075180 1.081652 1.077975 1.123689
names(q2.val) <- paste("port.val")
q2.val$date <- ewq2$date
q2.val[c(1:3, nrow(q2.val)), ]
## port.val date
## 62 1.075180 2013-04-01
## 63 1.081652 2013-04-02
## 64 1.077975 2013-04-03
## 125 1.123689 2013-06-28
#$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 2012-12-31
## 2 1.029137 2013-01-02
## 3 1.021428 2013-01-03
## 125 1.123689 2013-06-283.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, 12:16)] #port is from 3.3 Constructing Benchmark Portfolio Returns
vwport[c(1:3, nrow(vwport)), ]
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 2012-12-31 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 2013-01-02 2013-01-02 257.31 20.08 196.35 27.62 28.00
## 2013-01-03 2013-01-03 258.48 19.78 195.27 27.25 27.77
## 2013-12-31 2013-12-31 398.79 40.44 187.57 37.41 54.65
## AMZN.ret AABA.ret IBM.ret MSFT.ret FB.ret
## 2012-12-31 0.023207448 0.020512821 0.009060670 0.006026316 0.027402586
## 2013-01-02 0.025670679 0.009045226 0.025058852 0.034069574 0.051840682
## 2013-01-03 0.004547095 -0.014940189 -0.005500590 -0.013396147 -0.008214286
## 2013-12-31 0.013778412 0.005970099 0.006222998 0.003218311 0.017501453
rownames(vwport) <- seq(1:nrow(vwport))
vwport[c(1:3, nrow(vwport)), ]
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 2 2013-01-02 257.31 20.08 196.35 27.62 28.00
## 3 2013-01-03 258.48 19.78 195.27 27.25 27.77
## 253 2013-12-31 398.79 40.44 187.57 37.41 54.65
## AMZN.ret AABA.ret IBM.ret MSFT.ret FB.ret
## 1 0.023207448 0.020512821 0.009060670 0.006026316 0.027402586
## 2 0.025670679 0.009045226 0.025058852 0.034069574 0.051840682
## 3 0.004547095 -0.014940189 -0.005500590 -0.013396147 -0.008214286
## 253 0.013778412 0.005970099 0.006222998 0.003218311 0.017501453Step 2: Convert Net Returns to Gross Returns
vwport$AMZN.ret <- vwport$AMZN.ret + 1
vwport$AABA.ret <- vwport$AABA.ret + 1
vwport$IBM.ret <- vwport$IBM.ret + 1
vwport$MSFT.ret <- vwport$MSFT.ret + 1
vwport$FB.ret <- vwport$FB.ret + 1
vwport[c(1:3, nrow(vwport)), ]
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 2 2013-01-02 257.31 20.08 196.35 27.62 28.00
## 3 2013-01-03 258.48 19.78 195.27 27.25 27.77
## 253 2013-12-31 398.79 40.44 187.57 37.41 54.65
## AMZN.ret AABA.ret IBM.ret MSFT.ret FB.ret
## 1 1.023207 1.0205128 1.0090607 1.0060263 1.0274026
## 2 1.025671 1.0090452 1.0250589 1.0340696 1.0518407
## 3 1.004547 0.9850598 0.9944994 0.9866039 0.9917857
## 253 1.013778 1.0059701 1.0062230 1.0032183 1.0175015Step 3: Calculate the Market Capitalization of Each Security in the Portfolio
# Construct Series of Calendar Days
date <- seq(as.Date("2012-12-31"), as.Date("2013-12-31"), by = 1)
date <- data.frame(date)
date[c(1:3, nrow(date)), ]
## [1] "2012-12-31" "2013-01-01" "2013-01-02" "2013-12-31"
# Create Data Object with Daily Prices
PRICE.qtr <- vwport[, 1:6]
PRICE.qtr[c(1:3, nrow(PRICE.qtr)), ]
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 2 2013-01-02 257.31 20.08 196.35 27.62 28.00
## 3 2013-01-03 258.48 19.78 195.27 27.25 27.77
## 253 2013-12-31 398.79 40.44 187.57 37.41 54.65
# 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 AABA.Close IBM.Close MSFT.Close FB.Close
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 2 2013-01-01 250.87 19.90 191.55 26.71 26.62
## 3 2013-01-02 257.31 20.08 196.35 27.62 28.00
## 366 2013-12-31 398.79 40.44 187.57 37.41 54.65
# Keep Only Prices at the End of Each Calendar Quarter
PRICE.qtr <- subset(PRICE.qtr,
PRICE.qtr$date == as.Date("2012-12-31") |
PRICE.qtr$date == as.Date("2013-03-31") )
PRICE.qtr
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 91 2013-03-31 266.49 23.53 213.30 28.61 25.58
# 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(454000000, 455000000)
PRICE.qtr$AABA.shout <- c(1115233000, 1084766000)
PRICE.qtr$IBM.shout <- c(1117367676, 1108794396)
PRICE.qtr$MSFT.shout <- c(8374000000, 8349000000)
PRICE.qtr$FB.shout <- c(1671000000+701000000, 1741000000+670000000)
PRICE.qtr
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 91 2013-03-31 266.49 23.53 213.30 28.61 25.58
## AMZN.shout AABA.shout IBM.shout MSFT.shout FB.shout
## 1 4.54e+08 1115233000 1117367676 8.374e+09 2.372e+09
## 91 4.55e+08 1084766000 1108794396 8.349e+09 2.411e+09
# Calculate Market Capitalization of Each Security
str(PRICE.qtr)
## 'data.frame': 2 obs. of 11 variables:
## $ date : chr "2012-12-31" "2013-03-31"
## $ AMZN.Close: chr "250.87" "266.49"
## $ AABA.Close: chr "19.90" "23.53"
## $ IBM.Close : chr "191.55" "213.30"
## $ MSFT.Close: chr "26.71" "28.61"
## $ FB.Close : chr "26.62" "25.58"
## $ AMZN.shout: num 4.54e+08 4.55e+08
## $ AABA.shout: num 1.12e+09 1.08e+09
## $ IBM.shout : num 1.12e+09 1.11e+09
## $ MSFT.shout: num 8.37e+09 8.35e+09
## $ FB.shout : num 2.37e+09 2.41e+09
PRICE.qtr$date <- as.Date(PRICE.qtr$date)
PRICE.qtr$AMZN.Close <- as.numeric(PRICE.qtr$AMZN.Close)
PRICE.qtr$AABA.Close <- as.numeric(PRICE.qtr$AABA.Close)
PRICE.qtr$IBM.Close <- as.numeric(PRICE.qtr$IBM.Close)
PRICE.qtr$MSFT.Close <- as.numeric(PRICE.qtr$MSFT.Close)
PRICE.qtr$FB.Close <- as.numeric(PRICE.qtr$FB.Close)
str(PRICE.qtr)
## 'data.frame': 2 obs. of 11 variables:
## $ date : Date, format: "2012-12-31" "2013-03-31"
## $ AMZN.Close: num 251 266
## $ AABA.Close: num 19.9 23.5
## $ IBM.Close : num 192 213
## $ MSFT.Close: num 26.7 28.6
## $ FB.Close : num 26.6 25.6
## $ AMZN.shout: num 4.54e+08 4.55e+08
## $ AABA.shout: num 1.12e+09 1.08e+09
## $ IBM.shout : num 1.12e+09 1.11e+09
## $ MSFT.shout: num 8.37e+09 8.35e+09
## $ FB.shout : num 2.37e+09 2.41e+09
weights <- PRICE.qtr
weights$AMZN.mcap <- weights$AMZN.Close * weights$AMZN.shout
weights$AABA.mcap <- weights$AABA.Close * weights$AABA.shout
weights$IBM.mcap <- weights$IBM.Close * weights$IBM.shout
weights$MSFT.mcap <- weights$MSFT.Close * weights$MSFT.shout
weights$FB.mcap <- weights$FB.Close * weights$FB.shout
weights
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 91 2013-03-31 266.49 23.53 213.30 28.61 25.58
## AMZN.shout AABA.shout IBM.shout MSFT.shout FB.shout AMZN.mcap
## 1 4.54e+08 1115233000 1117367676 8.374e+09 2.372e+09 1.13895e+11
## 91 4.55e+08 1084766000 1108794396 8.349e+09 2.411e+09 1.21253e+11
## AABA.mcap IBM.mcap MSFT.mcap FB.mcap
## 1 22193136700 214031778338 223669540000 63142640000
## 91 25524543980 236505844667 238864890000 61673380000
# Calculate Quarter-end Aggregate Market Capitalization
weights$tot.mcap <- rowSums(weights[, 12:16])
weights
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 91 2013-03-31 266.49 23.53 213.30 28.61 25.58
## AMZN.shout AABA.shout IBM.shout MSFT.shout FB.shout AMZN.mcap
## 1 4.54e+08 1115233000 1117367676 8.374e+09 2.372e+09 1.13895e+11
## 91 4.55e+08 1084766000 1108794396 8.349e+09 2.411e+09 1.21253e+11
## AABA.mcap IBM.mcap MSFT.mcap FB.mcap tot.mcap
## 1 22193136700 214031778338 223669540000 63142640000 636932075038
## 91 25524543980 236505844667 238864890000 61673380000 683821608647Step 4: Calculate Quarter-end Weights of Each Security in the Portfolio
weights$AMZN.wgt <- weights$AMZN.mcap / weights$tot.mcap
weights$AABA.wgt <- weights$AABA.mcap / weights$tot.mcap
weights$IBM.wgt <- weights$IBM.mcap / weights$tot.mcap
weights$MSFT.wgt <- weights$MSFT.mcap / weights$tot.mcap
weights$FB.wgt <- weights$FB.mcap / weights$tot.mcap
weights
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62
## 91 2013-03-31 266.49 23.53 213.30 28.61 25.58
## AMZN.shout AABA.shout IBM.shout MSFT.shout FB.shout AMZN.mcap
## 1 4.54e+08 1115233000 1117367676 8.374e+09 2.372e+09 1.13895e+11
## 91 4.55e+08 1084766000 1108794396 8.349e+09 2.411e+09 1.21253e+11
## AABA.mcap IBM.mcap MSFT.mcap FB.mcap tot.mcap
## 1 22193136700 214031778338 223669540000 63142640000 636932075038
## 91 25524543980 236505844667 238864890000 61673380000 683821608647
## AMZN.wgt AABA.wgt IBM.wgt MSFT.wgt FB.wgt
## 1 0.1788181 0.03484380 0.3360355 0.3511670 0.09913559
## 91 0.1773166 0.03732632 0.3458590 0.3493088 0.09018928
weights <- weights[, c(1, 18:22)]
weights
## date AMZN.wgt AABA.wgt IBM.wgt MSFT.wgt FB.wgt
## 1 2012-12-31 0.1788181 0.03484380 0.3360355 0.3511670 0.09913559
## 91 2013-03-31 0.1773166 0.03732632 0.3458590 0.3493088 0.09018928
weights$date <- weights$date + 1 #since the weights are applicable at the start of the next Q
weights
## date AMZN.wgt AABA.wgt IBM.wgt MSFT.wgt FB.wgt
## 1 2013-01-01 0.1788181 0.03484380 0.3360355 0.3511670 0.09913559
## 91 2013-04-01 0.1773166 0.03732632 0.3458590 0.3493088 0.09018928Step 5: Calculate the Quarterly VW Portfolio Values
q1.vw.wgt <- subset(weights, date == "2013-01-01")
q2.vw.wgt <- subset(weights, date == "2013-04-01")
q1.vw.wgt
## date AMZN.wgt AABA.wgt IBM.wgt MSFT.wgt FB.wgt
## 1 2013-01-01 0.1788181 0.0348438 0.3360355 0.351167 0.09913559
q2.vw.wgt
## date AMZN.wgt AABA.wgt IBM.wgt MSFT.wgt FB.wgt
## 91 2013-04-01 0.1773166 0.03732632 0.345859 0.3493088 0.09018928Step 7: Calculate VW Portfolio Values for 1Q 2013
vw.q1 <- subset(vwport,
vwport$date >= as.Date("2012-12-31") &
vwport$date <= as.Date("2013-03-31"))
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close AMZN.ret
## 1 2012-12-31 250.87 19.90 191.55 26.71 26.62 1.023207
## 2 2013-01-02 257.31 20.08 196.35 27.62 28.00 1.025671
## 3 2013-01-03 258.48 19.78 195.27 27.25 27.77 1.004547
## 61 2013-03-28 266.49 23.53 213.30 28.61 25.58 1.004485
## AABA.ret IBM.ret MSFT.ret FB.ret
## 1 1.0205128 1.0090607 1.0060263 1.0274026
## 2 1.0090452 1.0250589 1.0340696 1.0518407
## 3 0.9850598 0.9944994 0.9866039 0.9917857
## 61 0.9974566 1.0114279 1.0084597 0.9804523
vw.q1 <- vw.q1[, c(1, 7:11)]
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN.ret AABA.ret IBM.ret MSFT.ret FB.ret
## 1 2012-12-31 1.023207 1.0205128 1.0090607 1.0060263 1.0274026
## 2 2013-01-02 1.025671 1.0090452 1.0250589 1.0340696 1.0518407
## 3 2013-01-03 1.004547 0.9850598 0.9944994 0.9866039 0.9917857
## 61 2013-03-28 1.004485 0.9974566 1.0114279 1.0084597 0.9804523
names(vw.q1)[2:6] <- paste(c("AMZN", "AABA", "IBM", "MSFT", "FB"))
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN AABA IBM MSFT FB
## 1 2012-12-31 1.023207 1.0205128 1.0090607 1.0060263 1.0274026
## 2 2013-01-02 1.025671 1.0090452 1.0250589 1.0340696 1.0518407
## 3 2013-01-03 1.004547 0.9850598 0.9944994 0.9866039 0.9917857
## 61 2013-03-28 1.004485 0.9974566 1.0114279 1.0084597 0.9804523
vw.q1[1, 2:6] <- 1
vw.q1$AMZN <- cumprod(vw.q1$AMZN)
vw.q1$AABA <- cumprod(vw.q1$AABA)
vw.q1$IBM <- cumprod(vw.q1$IBM)
vw.q1$MSFT <- cumprod(vw.q1$MSFT)
vw.q1$FB <- cumprod(vw.q1$FB)
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN AABA IBM MSFT FB
## 1 2012-12-31 1.000000 1.0000000 1.000000 1.000000 1.0000000
## 2 2013-01-02 1.025671 1.0090452 1.025059 1.034070 1.0518407
## 3 2013-01-03 1.030335 0.9939699 1.019420 1.020217 1.0432006
## 61 2013-03-28 1.062263 1.1824121 1.118235 1.080003 0.9609316
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$AABA.idx <- vw.q1$AABA * q1.vw.wgt$AABA.wgt
vw.q1$IBM.idx <- vw.q1$IBM * q1.vw.wgt$IBM.wgt
vw.q1$MSFT.idx <- vw.q1$MSFT * q1.vw.wgt$MSFT.wgt
vw.q1$FB.idx <- vw.q1$FB * q1.vw.wgt$FB.wgt
vw.q1[c(1:3, nrow(vw.q1)), ]
## date AMZN AABA IBM MSFT FB AMZN.idx
## 1 2012-12-31 1.000000 1.0000000 1.000000 1.000000 1.0000000 0.1788181
## 2 2013-01-02 1.025671 1.0090452 1.025059 1.034070 1.0518407 0.1834085
## 3 2013-01-03 1.030335 0.9939699 1.019420 1.020217 1.0432006 0.1842425
## 61 2013-03-28 1.062263 1.1824121 1.118235 1.080003 0.9609316 0.1899519
## AABA.idx IBM.idx MSFT.idx FB.idx
## 1 0.03484380 0.3360355 0.3511670 0.09913559
## 2 0.03515897 0.3444561 0.3631311 0.10427485
## 3 0.03463369 0.3425614 0.3582666 0.10341831
## 61 0.04119974 0.3757665 0.3792613 0.09526252
#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[, 7:11])) #Calculate the daily portfolio values
q1.vw.val[c(1:3, nrow(q1.vw.val)), ]
## [1] 1.000000 1.030430 1.023122 1.081442
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 2012-12-31
## 2 1.030430 2013-01-02
## 3 1.023122 2013-01-03
## 61 1.081442 2013-03-28
q2.vw.inv <- q1.vw.val[nrow(q1.vw.val), 1]
q2.vw.inv
## [1] 1.081442Step 8: Calculate VW Portfolio Values for 2Q 2013
vw.q2 <- subset(vwport,
vwport$date >= "2013-04-01" &
vwport$date <= "2013-06-30")
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN.Close AABA.Close IBM.Close MSFT.Close FB.Close
## 62 2013-04-01 261.61 23.50 212.38 28.61 25.53
## 63 2013-04-02 263.32 23.78 214.36 28.80 25.42
## 64 2013-04-03 259.03 23.38 212.66 28.56 26.25
## 125 2013-06-28 277.69 25.13 191.11 34.54 24.88
## AMZN.ret AABA.ret IBM.ret MSFT.ret FB.ret
## 62 0.9816878 0.9987250 0.9956867 1.0000000 0.9980454
## 63 1.0065365 1.0119149 1.0093230 1.0066412 0.9956913
## 64 0.9837080 0.9831791 0.9920694 0.9916666 1.0326515
## 125 1.0005045 0.9866510 0.9767954 0.9976890 1.0089213
vw.q2 <- vw.q2[, c(1, 7:11)]
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN.ret AABA.ret IBM.ret MSFT.ret FB.ret
## 62 2013-04-01 0.9816878 0.9987250 0.9956867 1.0000000 0.9980454
## 63 2013-04-02 1.0065365 1.0119149 1.0093230 1.0066412 0.9956913
## 64 2013-04-03 0.9837080 0.9831791 0.9920694 0.9916666 1.0326515
## 125 2013-06-28 1.0005045 0.9866510 0.9767954 0.9976890 1.0089213
names(vw.q2)[2:6] <- paste(c("AMZN", "AABA", "IBM", "MSFT", "FB"))
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN AABA IBM MSFT FB
## 62 2013-04-01 0.9816878 0.9987250 0.9956867 1.0000000 0.9980454
## 63 2013-04-02 1.0065365 1.0119149 1.0093230 1.0066412 0.9956913
## 64 2013-04-03 0.9837080 0.9831791 0.9920694 0.9916666 1.0326515
## 125 2013-06-28 1.0005045 0.9866510 0.9767954 0.9976890 1.0089213
#vw.q2[1, 2:4] <- 1 #difference from Q1
vw.q2$AMZN <- cumprod(vw.q2$AMZN)
vw.q2$AABA <- cumprod(vw.q2$AABA)
vw.q2$IBM <- cumprod(vw.q2$IBM)
vw.q2$MSFT <- cumprod(vw.q2$MSFT)
vw.q2$FB <- cumprod(vw.q2$FB)
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN AABA IBM MSFT FB
## 62 2013-04-01 0.9816878 0.9987250 0.9956867 1.0000000 0.9980454
## 63 2013-04-02 0.9881047 1.0106247 1.0049695 1.0066412 0.9937451
## 64 2013-04-03 0.9720065 0.9936251 0.9969995 0.9982524 1.0261923
## 125 2013-06-28 1.0420279 1.0679982 0.9001679 1.2157358 0.9726348
vw.q2$AMZN.ind <- (q2.vw.inv * q2.vw.wgt$AMZN.wgt) * vw.q2$AMZN #Apply weight
vw.q2$AABA.ind <- (q2.vw.inv * q2.vw.wgt$AABA.wgt) * vw.q2$AABA
vw.q2$IBM.ind <- (q2.vw.inv * q2.vw.wgt$IBM.wgt) * vw.q2$IBM
vw.q2$MSFT.ind <- (q2.vw.inv * q2.vw.wgt$MSFT.wgt) * vw.q2$MSFT
vw.q2$FB.ind <- (q2.vw.inv * q2.vw.wgt$FB.wgt) * vw.q2$FB
vw.q2[c(1:3, nrow(vw.q2)), ]
## date AMZN AABA IBM MSFT FB AMZN.ind
## 62 2013-04-01 0.9816878 0.9987250 0.9956867 1.0000000 0.9980454 0.1882462
## 63 2013-04-02 0.9881047 1.0106247 1.0049695 1.0066412 0.9937451 0.1894766
## 64 2013-04-03 0.9720065 0.9936251 0.9969995 0.9982524 1.0261923 0.1863897
## 125 2013-06-28 1.0420279 1.0679982 0.9001679 1.2157358 0.9726348 0.1998168
## AABA.ind IBM.ind MSFT.ind FB.ind
## 62 0.04031478 0.3724131 0.3777572 0.09734383
## 63 0.04079513 0.3758851 0.3802659 0.09692440
## 64 0.04010892 0.3729041 0.3770970 0.10008913
## 125 0.04311108 0.3366865 0.4592529 0.09486542
q2.vw.val <- data.frame(rowSums(vw.q2[, 7:11]))
q2.vw.val[c(1:3, nrow(q2.vw.val)), ]
## [1] 1.076075 1.083347 1.076589 1.133733
names(q2.vw.val) <- paste("port.val")
q2.vw.val[c(1:3, nrow(q2.vw.val)), ]
## [1] 1.076075 1.083347 1.076589 1.133733
q2.vw.val$date <- vw.q2$date
q2.vw.val[c(1:3, nrow(q2.vw.val)), ]
## port.val date
## 62 1.076075 2013-04-01
## 63 1.083347 2013-04-02
## 64 1.076589 2013-04-03
## 125 1.133733 2013-06-28
#$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 2012-12-31
## 2 1.030430 2013-01-02
## 3 1.023122 2013-01-03
## 125 1.133733 2013-06-283.3.3 Normalized EW and VW Portfolio Price Chart
Compare the performance of EW and VW portfolio from 12/31/2012 to 12/31/2013
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 2012-12-31 1.000000 1.000000
## 2 2013-01-02 1.030430 1.029137
## 3 2013-01-03 1.023122 1.021428
## 125 2013-06-28 1.133733 1.123689Step 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 2012-12-31 1.000000 1.000000
## 2 2013-01-02 1.030430 1.029137
## 3 2013-01-03 1.023122 1.021428
## 125 2013-06-28 1.133733 1.123689Step 3: Plot the Data
par(mfrow = c(1, 1))
y.range <- range(port.val[, 2:3])
y.range
## [1] 1.000000 1.173701
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, AABA, IBM, MSFT and FB
December 31, 2012 - June 30, 2013")
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))