QUESTION 1
1. This problem set is designed to give you an opportunity to exercise with excel program to compute returns and risks. Read carefully the instructions and practice step by step.
You purchased one share of the stock in Microsoft Corporation,fiveyears ago and sold the stock at thebeginning of this month. You want to figure out the monthly returns andthevolatility of the returns using an Excel spreadsheet.
Click onthe 'Historical Prices'tab on the left menu.
Go to the Set Data Range area. Set the 'Start Date' as on 'January1, 2010' and the 'End Date' as on 'January 9, 2015'. Monthly data must be retrieved.Click onthe'Monthly'circlein the right column.
Then, click the Get Prices tab.
Now scroll down thewebpage and click the 'Download to Spreadsheet' tab.
You may find an excel spreadsheet, named ‘table', in the download area of your computer.
Open the spreadsheetthat contains monthly prices of theMicrosoftstock.
Sort the whole data by the‘Date'in an ascending order, that is, oldest to newest.
After set the range so all the cells are selected, click the Data tab in the top menu bar, click the Sort tab, then check on ‘My data has headers'.
Select the ‘Date' by clicking the right scroll menu of the ‘Sort by' tab.
Select the ‘Values' from the ‘Sort on' menu.
Select the ‘Oldest to Newest' from the ‘Order' menu.
Createa column named'HPR' to the right of 'Adj Close'by typing ‘HPR' in the cell H1 if the'Adj Close' is in the row 'G'.
Typea function in the cell H3 such that '=G3/G2-1'.
Copythis functionand paste itto the next rows and repeat this to the endinthe‘HPR'column.
You may use the ctrl + C key to copy a cell and ctrl + V keys to paste.
In thiswayyoucan compute the monthly returns. You shouldhave.0219for the first monthly returnin the H3 cell, and .0218 for the second monthly return in the H4 cell.
Total 60 monthly returns should be identified.Thenanswer the following questions.
(Hint: You may want to refer to an excel reference for Apple stock posted on Blackboard)
(1) To compute the arithmeticaverage returnof the monthly returns, add every monthly return in the HPR column. Use a function by typing ‘=SUM(H3:H62)' in the H63 cell. What is the sum of the monthly returns?
(2) Find the number of returns by typing a function ‘=count(H3:H62)' in the H64 cell. How many monthly returns exist during the holding period?
(3) Divide the sum computed in the part (1) by the number of monthly returns found in the part (2). Type ‘=H63/H64' in the H65 cell. What is the arithmetic average return?
(4) Create a column named ‘Deviation' to the right of the HPR column by typing ‘Deviation' in the ‘I1' cell. Use a function by typing ‘=H3-$H$65' in the I3 cell. Copy the function in the I3 cell and paste it to the cells I4 through I62. Compute the sum of every deviation from mean in the ‘Deviation' column by typing ‘=SUM(I3:I62)' in the I63 cell. What is the sum of the deviations from mean?
(5) Create a column named ‘Squared Deviation' to the right of the ‘Deviation' column by typing ‘Squared Deviation' in the ‘J1' cell. Use a function by typing ‘=I3^2' in the J3 cell. Copy the function in the J3 cell and paste it to the cells J4 through J62. Compute the sum of every squared deviations in the ‘Squared Deviation' column by typing ‘=SUM(J3:J62)' in the J63 cell. What is the sum of the squared deviations?
(6) To compute the variance of the monthly returns, type a function ‘=J63/(H64-1)' in the J64 cell. What is the variance of the returns?
(7) To compute the volatility of the monthly returns, type a function ‘=SQRT(J64)' in the J65 cell. What is the standard deviation of the returns?
(8) In order to confirm your answers, type a function '=AVERAGE(H3:H62)' in the H66 cell. Is this average return the same to the arithmetic return computed in the part (3)?
Additionally, type a function ‘=STDEV(H3:H62)' in the J66 cell. Is this standard deviation the same to the standard deviation of the returns computed in the part (7)?
(9) To compute the geometric return, type a function‘=(G62/G2)^(1/H64) - 1' in the cell G67. What is the geometric return?
QUESTION 2
1. You purchased a share of stock for $25. One year later you received $1.25 as dividend and sold the share for $30.
(1) Your capital gains yield (CGY) was _____.
(2) Your dividend yield (DY) was _____.
(3) Your holding-period return (HPR) was _____.
QUESTION 3
A stock had year-end prices of $42, $37, $44, and $46 over the past four years, respectively. Ignore the dividends.
(1) What are annual holding period returns for each year?
(2) What is the arithmetic average rate of return?
(3) What is the variance of the annual returns?
(4) What is the volatility of the annual returns (measured by the standard deviation)?
(5) What is the geometric average rate of return?
QUESTION 4
An investment earns 12.5% the first year, earns 20% the second year, and loses 15% the third year.
(1) Suppose that you invest $1 three years ago. What will be the amount you earn in three years? Then, the total compound return over the 3 years is _____.
(2) What is the geometric average return over the 3 years?
QUESTION 5
Your investment has a 20% chance of earning a 35% rate of return at a boom economy, a 50% chance of earning a 10% rate of return at a normal economy, and a 30% chance of losing 15% at a recessionary economy. What is your expected return on this investment?