The Pennsylvania State University Smeal College of Business
FIN 406 – Security Analysis and Portfolio Management
Week 09 Assignment
Due Friday, October 20
This is an individual assignment – you should not communicate about it in any way with anyone other than me. Feel free to ask me any questions. Please follow the instructions for all previous assignments (points deducted for spelling errors, extremely poor grammar, and things that are “non-professional” in appearance, etc.). If you have any questions, please ask me. Use the Assignment Checklist located on Canvas to make sure you do not lose points unnecessarily. 0. A cover sheet … 1. Update the financial and economic variables from your Week 02 Assignment. Gather the current
values for these variables (any day this week is fine). The format ($, %, number of decimals) of the variables should be the same as in the Week 02 Assignment. Use the Excel template from your Week 02 Assignment.
a. Create and print out a table like the one below (yours will have all the financial variables from the
Week 02 Assignment). Use the same format as the example template for the Week 02 Assignment – just add 2 columns. An example is below:
Week 02 Current
(Week 09)
Change World Equities: S&P 500 (level) 1,997 1,909 -4.41% …
For the “Change” column, report equity indexes and commodities as percent changes (i.e., HPR) from Week 02 to the current value to two decimal places (e.g., 7.45%). For the Euro/USD, inflation, and unemployment simply report the difference (“Current” minus Week 02). For the Treasury rates report the difference (“Current” minus Week 02, NOT HPR) in basis points. For the three variables of your choosing, use your judgment as to how to report the change. You will have to do this exercise again at least once for a future Assignment. Therefore, it may be useful to set up formulas in Excel to do your calculations and present them in the appropriate format (decimals, bps, etc.) for the change column. If you have questions, please ask me.
b. For the S&P 500 and any two other variables from the table write a brief paragraph for each variable
(3 or 4 sentences per paragraph) about what happened over the last 7 weeks. For example, you might start out with “X went down by YY percent from its Week 02 value. Several potential causes for this are …”. Do NOT just say that the variable went up or down – provide at least some plausible reasons for what has happened – if you can’t do this, choose a different variable to describe!
2. For the last 30 years, gather monthly data for:
• the rate on the 3-month T-bill (Secondary Market Rate) • the rate 10-year T-note (Constant Maturity)
A good source for this data is the St. Louis Fed’s FRED Economic Data site. Do NOT hand in the raw data – hand in the charts specified below. Examine the T-bill rate for the most recent month and compare it to the T-bill rate in your table in Question 4 of the Week 02 Assignment. Does it make remote sense?
a. Plot both data series on the same chart and explain some features you notice. Make your chart
somewhat presentable (e.g., do not use the Excel defaults of ‘Series 1’ and ‘Series 2’, put dates in the chart, …). More features = more points. (Your chart should look similar to Figure 10.15 in the BKM textbook, except you will not have the “difference” time series.) For your ‘features,’ bullet points are fine.
If you are having difficulty getting started, take finance completely off the table. Imagine one series is burritos sold per month at Chipotle and the other series is for burritos sold at Taco Bell. Describe the interesting things you notice. Now add in finance and the fact that these are interest rates.
b. For the last 15 years, gather monthly data for:
• the rate on 3-Month AA Nonfinancial Commercial Paper • the rate on 3-month AA Financial Commercial Paper
In Excel, calculate the spread (aka the difference) for the two rates (do NOT hand in this data): • Spread 1: 3-Month AA Nonfinancial Commercial Paper minus 3-Month T-bill • Spread 2: 3-Month AA Financial Commercial Paper minus 3-Month T-bill
Use the 3-month T-bill data from part a. – make sure the months match-up between the different series. Plot a chart of the two spreads to include in your Assignment. What and when was the largest spread for each series? What is the average spread for each of the two series? What might explain why the spreads are not constant? Express your numeric answers to the nearest basis point (1% = 100 basis points (aka “bps”)).
c. Do you notice anything odd about the AA Nonfinancial Commercial Paper Rate raw data (look at the
numbers in that column of your Excel data and you should see something unusual)? Explain (just tell me what you see, NOT why that is the case).
d. Given that the average spreads calculated in part b. are not exactly 0, does this imply one investment
is generally superior to the other? Explain why this may or may not be the case.
3. While the U.S. stock market is open, go to the Bats website: (http://www.bats.com/us/equities/) and look at the “Book Viewer” (it is preferable to do this between 10 a.m. and 3 p.m. on a weekday). When you enter the ticker symbols in the questions below, you should take a screenshot of the page or figure out some way to save the data as the page refreshes with new data approximately every 10 seconds (you do NOT need to include this with your answers to the questions below). Use the two columns under “TOP OF BOOK, not “LAST 10 TRADES.”
a. For the ticker MSFT: What is the highest bid price? For what quantity of shares does this apply? What
is the lowest ask price? For what quantity of shares does this apply? Note that you would not have to buy or sell all the shares listed for these prices if there are more than 100 shares – they are good for ‘lots’ of 100 shares as well.
b. Imagine a transaction where you buy 300 shares at the best (lowest) ask price and then immediately
sell the shares at the best (highest) bid price. Using your initial investment (in $) and the price you receive when selling the shares, calculate the HPR of this transaction. Report this in basis points (1%=100 bps) to two decimals (e.g., 1.23 basis points). Your HPR should be negative and very, very small. Remember that in the Order Book, “size” means you can buy up to that amount of shares at that price – you do not have to buy the entire amount you could simply buy a ‘lot’ of 100 shares.
c. Repeat parts a. and b. for OTTR. It is likely to be the case that the order book will only show ‘lots’ of
100 shares or fewer so you will need to do some aggregation to calculate your cost to buy and sell (e.g. 100 shares @ $28.24 + 100 shares @ 28.32 + …). In other words it is unlikely that you can buy or sell the entire 300 shares at just one price. Always start each part of the transaction with the “best” price and work your way down or up until you reach a total of 300 shares. Report the HPR as in part b. This number should be “larger” (more negative) than that for part b.
d. What do you notice about the relation to the parts b. and c.? 300 shares is not a large amount.
Hypothetically, what do you think would happen if the amount was 3,000 shares in parts b. and c.? Will each HPR get larger or smaller (in absolute value)? Will the difference in HPRs between MSFT and OTTR get larger or smaller as the quantity of shares goes from 300 to 3,000?
e. Do you think the Order Book for the stock you selected from the list for the Week 02 Assignment will
look more like that of MSFT or OTTR? Look at the “Summary” (first page of Yahoo!Finance when you type in a ticker) and see whether your firm “looks” more like a Microsoft or an Otter Tail. Briefly explain your reasoning.
f. Repeat parts a. and b. for your stock. Was your conclusion in part e. correct – did the HPR for your
stock look more like that of MSFT or OTTR? If you picked a stock for one of your two financial variables and are interested in finance, repeat parts e. and f. for the stock you picked.
g. It is fairly unlikely that you will buy an asset and immediately sell it. Briefly explain what you think the
whole point of this exercise was.
