Assignment
In this assignment you will work through the trades necessary for rebalancing a simple portfolio and investigate the effect of transaction costs on portfolio performance.
Portfolio Rebalancing
You manage a portfolio of two stocks, FedEx Corp. (ticker: FDX) and McDonald's Corp. (ticker: MCD), that begins trading on 1/3/1995 with $20,000 invested. To exploit the benefits of diversification, the investment is split equally between both stocks, i.e. $10,000 are initially allocated to each.1 After setting up the portfolio, stock prices change and portfolio weights start to deviate from the initial weights of 50% in each stock. Your task is to determine the trades necessary for rebalancing the portfolio.
On Blackboard, you will find a spreadsheet with daily prices for the two stocks from 1/3/1995 to 12/31/2014. The sheet also contains information on historical dividends and stock splits.2 To simplify the analysis, assume that the dividend dates in the spreadsheet are both the ex-dividend date and the payment date.3
The columns "Bid" and "Ask" are computed by adding and subtracting a percentage of the daily closing price from that price itself - an input cell at the top of the spreadsheet controls the magnitude of the bid-ask spread. By assuming a spread instead of relying on the actual bid and ask prices that prevailed over the sample period, we will be able to investigate the effect of various hypothetical spreads on portfolio performance. For the baseline scenario, we will assume a spread of 10 basis points (0.1%), which is realistic for a large company with a liquidly traded stock.
A second source of transaction costs arises from brokerage fees. For retail investors, brokers typically charge on a per-trade basis. A second input cell allows you to control the brokerage fee. For the baseline scenario, we will assume a cost of $5 per trade.
In addition to the data table, I have set up three tables in the spreadsheet that contain the portfolio positions before trading, the trades themselves, and portfolio positions after trading. Your task is to work towards completing the missing columns of the "Trades" table.
1As we will see later in the course, efficient diversification requires substantially more than two stocks.
However, the example in this assignment illustrates the fact that frequent trading is necessary to maintain diversification over time. This is true regardless of the number of stocks in a portfolio, so we'll stick with two stocks to keep things as simple as possible.
2The variable "split" equals 1 when no split occurred, 2 when a 2:1 split occurred etc.
3In reality, the payment date occurs after the ex-dividend date. Investors who own the stock just prior to the ex-dividend date are entitled to the dividend, but will typically receive their actual payment at a later time.
QUESTIONS:
Please complete all questions except for G, H, and I. We will work on those parts in class after you have handed in the assignment.
A. Explain how the tables in the spreadsheet adjust the number of shares held in the portfolio to account for stock splits.
B. In the spreadsheet, what happens to dividends after they are paid out?
C. Suppose that you never rebalance after making the initial investment of $10,000 in each stock. In this case, what is the total $-amount of dividends that the portfolio earns over the 20 year period?
D. Suppose that you never rebalance after making the initial investment of $10,000 in each stock. In this case, you will observe that portfolio weights drift away from the initial 50/50 mix over time. Which weight increase more over the 20 years (FDX or MCD)? Why? Your answer should address changes in the prices of both stocks as well as any stock splits. Assume that the interest on cash is 1% per year.
E. What is the portfolio value after rebalancing the portfolio to 50/50 on a given day? Denote this value by V ∗ and write down a separate formula for V ∗ for each of the following cases:
1. Buy X1 shares of FDX, sell X2 shares of MCD
2. Sell X1 shares of FDX, buy X2 shares of MCD
3. Buy X1 shares of FDX, buy X2 shares of MCD
(this case can occur when you have a large cash balance)
Here, X1 and X2 represent the unknown number of shares that need to be bought or sold in order to rebalance the portfolio (we will solve for these later). Express your formula for V ∗ in terms of the following notation (you may not need all symbols):
• S1: Number of FDX shares before trading (S2 for MCD)
• B1: Bid price of FDX (B2 for MCD)
• A1: Ask price of FDX (A2 for MCD)
• P1: Price of FDX, given by P1 = ( A1+B1) / 2 (P2 for MCD)
• C: Balance of cash account before trading
• V : Portfolio value before trading, given by V = C + S1P1 + S2P2
• X1: Number of FDX shares bought or sold (sold: X1 negative) (X2 for MCD)
• F : Brokerage fee per trade (trading in both stocks costs 2F )
Hints: (i) The correct answer combines the portfolio value before trading with two costs, one related to the brokerage fee and one related to the bid-ask spread. For example, if you buy one FDX share for A1 and its' value is P1 = (A1+B1) / 2 , then you loose P1 -A1 = -(A1-B1) / 2 (the portfolio value decreases by one half of the bid-ask spread). (ii) The correct answer depends on both X1 and X2, i.e. it is a function of the number of shares you buy/sell of each stock.
F. Use the same notation as in part E to write down an expression for X1 (the number of FDX shares to buy/sell in order to rebalance the portfolio to 50/50) for each of the three cases. The answer should reflect the fact that half of the portfolio value (after trading costs) should be allocated to FDX. Note that you have found the portfolio value after trading in the previous question build on this answer here. Hint: The correct expression for X1 is a linear function of X2, i.e. it takes the form X1 = a + bX2 or X1 = a - bX2, where a and b are expressions that depend on a combination of the symbols defined in E.
G. Write down a similar expression for the number of MCD shares to buy/sell in order to rebalance the portfolio to 50/50, i.e. an expression for X2.
H. Solve the two equations for X1 and X2 for the two unknowns (separately for each of the three cases) and implement your solution in the "Trades" table of the spreadsheet. Assume that arbitrary fractions of shares can be bought or sold. For example, it is possible to buy 0.5682 of a share.
The easiest way to implement these solutions in Excel is to first calculate all of the a's and b's in separate columns, and then use them to compute X1 and X2 for each case. Lastly, one needs to decide which case is applicable. For example, if X1 is negative for the case "buy FDX/sell MCD" it cannot be the correct solution because buying FDX would imply that X1 is positive. You should find that on every day only one of the three solutions is consistent with the case it corresponds to. An easy way to "pick out" the correct case in Excel is via IF statements.
I. Compute the portfolio value on 12/31/2014 for the following scenarios
|
Brokerage Fee
|
Bid/Ask spread
|
Acceptable deviation
|
|
0%
|
1%
|
5%
|
|
$5
|
10 bps
|
|
|
|
|
$5
|
1000 bps
|
|
|
|
|
$0
|
10 bps
|
|
|
|
|
$0
|
1000 bps
|
|
|
|
J. The results in part I indicate that frequent rebalancing is much easier when you face a low brokerage fee - as is the case for most institutional investors. However, in the real world, institutional investors face an additional (implicit) transaction cost that we ignored in this assignment. What is this cost and why is it more relevant for institutional investors than for retail investors? Hint: You do not need to solve part I in order to answer this question!
https://www.dropbox.com/s/vwvngn24n6t0w07/Assignment_Data.rar?dl=0