In Lecture 4 (CH11 and CH12), you were introduced to the concept of systematic risk, as measured by beta. Beta is an indicator of an asset’s exposure (or vulnerability) to systematic risk and evaluates the degree to which an asset’s return is correlated with the market return. Beta is a key parameter in the Capital Asset Pricing Model (CAPM) which is most commonly used asset pricing model for the estimation of required rate of returns on a share (i.e. cost of equity=rE). This assignment consists of two parts. Part I directs you to work with real financial data in Excel and aims to take you through the basic exercise of estimating a company beta and computing statistics which can be evaluated as part of investment decision-making process. Part II requires you to write a report based on the outputs obtained from the first part. In this manual, you will also find out how to download historical share price and market index data from the web portal.
? Details of Questions
PART 1 [20 marks]
To successfully complete PART 1, please read each section carefully. I also encourage you to read the entire manual before you start the assignment. For Part I, all computations are required to be produced in excel spreadsheets.
1. Download historical prices for your companies into Excel.
[4.5 marks]
Instructions!
a. To learn about two companies you have been allocated to, please go to http://www.asx.com.au/asx/research/listedCompanies.do
b. To download the data, go to http://au.finance.yahoo.com
c. Type the name of your first company in the search box. Then click “Search”.
d. Click the tab titled “Historical Data”.
e. Set Start date as 01/08/2016 and End date as 01/08/2021 (sample period). Please note that date format should be ‘dd/mm/yyyy’. Set frequency at ‘Monthly’. Click “Apply”.
f. Click “Download”.
g. You will see seven columns in the saved spreadsheet with data ranging from 01/08/2016 to 01/08/2021. All we need is Date and Adj. close price [P]. Delete other columns.
h. Replicate steps b to g to download data for your second company.
i. The data for the market index [m], S&P/ASX200, is provided on Moodle site.
j. Display the following on the same spreadsheet; i) date, ii) Adj. close price for your first stock [P1], iii) Adj. close price for your second stock [P2], iv) Adj. close price for the market index [m]. Then save the file using the file extension .xlsx or .xls format. Do not use .csv or .xlsm. At this point, you should have four columns and 62 rows.
2. Calculate the monthly realised returns for your stocks and market
index. [3 marks]
Instructions!
a. We learnt in Lecture 4 that realised return at t can be calculated as
????????-????????-1 + ????????????????
???????? = ????????-1
b. As you are using prices adjusted for dividends, you do not need to consider a dividend component in return calculation.
c. In the spreadsheet produced above, three additional columns should be created for computing monthly realised returns for your two stocks and the market index. Monthly returns should be available from 01/09/2016 as you don’t have price data on 01/07/2016 for the calculation of returns for 01/08/2016.
d. In the end, 60 individual monthly returns should be generated for your stocks and for the market index.
3. Compute the following for each of your stocks and the market index. [4.5 marks]
3.1. Monthly average returns
3.2. Variance of monthly returns
3.3. Standard Deviation of monthly returns
Instructions!
a. To compute the monthly average returns, use Excel function, =AVERAGE (data range)
b. To compute the variance, use Excel function, =VAR.S (data range)
c. To compute the standard deviation, use Excel function, =STDEV.S(data range)
Note: Your data range here is 60 monthly returns for your stocks and for your market index.
4. Compute the following for the returns of your stocks and the market
index. [3 marks]
4.1. Covariance
4.2. Correlation Coefficient
Instructions!
a. To compute Covariance between your first stock and the market index, use the following Excel function.
=COVARIANCE.S (array1, array2)
(Array 1 will be 60 monthly returns for your first stock while Array 2 will be 60 monthly returns for your market index, or vice versa).
b. To compute Correlation Coefficient between your first stock and the market index, use the following Excel function.
=CORREL (array1, array2)
(Array 1 will be 60 monthly returns for your first stock while Array 2 will be 60 monthly returns for your market index, or vice versa).
c. Repeat a and b for your second stock.
d. Using the same excel functions above, compute Covariance and
Correlation Coefficient between your first stock and the second stock.
5. Compute a beta for your stocks using the following three methods. [ 5 marks]
5.1. Use the following Excel function. =SLOPE(known_ys, known_xs) (known_ys must be 60 monthly returns for your stock while known_xs
must be 60 monthly returns for your market index.)
The resulting value is your beta. Compute the beta for both stocks.
5.2. Compute the beta using the following formula. The resulting value is your beta. Compute the beta for both stocks.
????????????????????????????????????????????,????
???????? = ????????????????????????????????????
5.3. Compute the beta using a regression analysis.
PART II
[50 marks]
1. For your Company 1, conduct some market research to find out what has been happening to your stock for the last 5 years. Based on what we learnt, there must be two primary sources which cause the prices of your stock to plunge (i.e. decline) at certain times; Unsystematic risk and Systematic risk. Highlight two specific down periods where one
is caused by unsystematic risk and the other by systematic risk. [11 marks]
• For each, discuss the real-event in detail and how it affected the company performance on the day (or over the days) by referring to price changes (You can have a look at daily price changes in Yahoo by following 1.e. in Part I and changing the option to ‘Daily’). Clearly discuss through what channels company performance would have been affected.
• For each, discuss how S&P/ASX200 responded on the same day (or over the days) by referring to value changes.
• Discuss your findings with reference to the impact of unsystematic and systematic risks.
• If your answer contains plagiarised materials, no marks will be given.
2. Suppose that you have an option of investing in one single asset; the first stock or the second stock. Which stock would expose you to greater total risk? [2 marks]
3. Which asset, your first stock or your second stock, would require a higher risk premium
and why? [3 marks]
4. Suppose that you consider forming a four-asset portfolio by investing 20% of your wealth in your first stock, 20% in your second stock, 40% in S&P/ASX200 and 20% in the 10 year
Australian government bonds. Compute the beta of your portfolio and interpret the value. [4 marks]
5. Suppose that you consider forming a two-asset portfolio by investing 40% of your wealth in your first stock and 60% in your second stock.
5.1. Compute the beta of your portfolio. [2 marks]
5.2. Compute the standard deviation of your portfolio. [5 marks] 5.3. What would be the standard deviation if two stocks are perfectly negatively correlated, keeping other variables constant? Compare the answer to the standard deviation in 5.2 and comment. [3 marks]
5.4 Compute the required return of your portfolio. Use 6.5% as a market risk premium and the 10-year government bond rate on 3 August 2021 as a risk-free rate. The 10-year government bond rates can be obtained from http://www.rba.gov.au/statistics/tables/index.html#interest-rates. See “Capital market yields – Government bonds-daily” under Interest Rates. The units of rates shown are per cent (%) per annum. [2 marks]
5.5 Compute the expected return of your portfolio using the annual average return. (Note: Calculate the annual average return of an individual stock using the formula
given in Q2, Part I. Each interval is to be August in Year t to August in Year t+1.)
[6 marks]
5.6 Is your portfolio overpriced or underpriced and why? Consequently, which
recommendation would you make, “Buy/Hold” or “Sell/don’t buy”? [9 marks]
• Please show all your relevant calculations and justify your decision using the Security Market Line. Label X-axis, Y-axis and intercept. Clearly present values on X- and Yaxis when you locate your portfolio in the graph.
6. Looking at the average monthly return and standard deviation of your first stock, can you be 95% confident that it will not lose more than 10% of its value in September 2021?
[3 marks]
? Format/Submission Requirements
[10 marks]
• Format Requirements
PART I
1. You should aim to fit Q1- Q5.2 into one spreadsheet and Q5.3 into one or two spreadsheets. This would be optimal.
2. For betas, keep 8 decimal points. For others, keep 4 decimal points.
3. Label your final answers properly using your own notations or a question number.
4. Whenever formula is used, formula should be shown in a formula bar.
PART II
1. You must produce a signed coversheet and add the coversheet file (PDF or picture file) to the first page of your Part II word document. To do this,
• Place the cursor in the Word document where you want to insert the coversheet file as an object.
• Select the Insert tab.
• Select Object in the Text group.
• Select the Create from File tab, select Browse, then locate your PDF or picture file.
The link to an assignment coversheet is here.
https://documents.uow.edu.au/content/groups/public/@web/@gsb/documents/doc/uow077733.pdf
2. Your written report can be typed in any preferred font style.
3. The report should be typed in size 12 with 1.5 line spacing. There is no margin requirement.
4. When answers are discussed, question numbers (i.e. 1,2,3…) are to be clearly presented.
5. Use your own words. Reference wherever necessary using the Harvard referencing style.
Please be guided by the UOW Style Guides from http://uow.libguides.com/refcite.
6. Hand-written workings or hand-drawn SML will be penalised.
7. Reference (or bibliography) should be provided at the back of your report.
8. Ensure that your document is professionally presented with spell and grammatical errors checked and eliminated.
9. The use of appendix is allowed.
• Submission Requirements
1. The assignment is due by 3pm on Thursday, 23 September 2021 in Week 9.
2. Do not wait until the last minute to submit your work. Server problems, network outrages, computer breakdowns, USB failures, and the like will not be considered for late submissions.
3. Assignment handed in late will be penalised by a deduction of 10% for each day late (i.e. 2 marks deduction (out of 20) per day late).
4. FIN222 students are required to submit a soft-copy of the completed assignment via Moodle.
For the submission of the soft-copy,
o go to ‘Turnitin’ link on FIN222 Moodle site. o Submit a single excel file (in .xls or xlsx) under the tab -Part I Excel Spreadsheet-. o Submit a single word file (coversheet included) under the tab -Part II Written Report-. o Under each tab, click -Submit to Turnitin- button. o Under Submission Title, please use the format
Your First Name_Student ID (eg. Hoang_7654321) o Failure to submit a soft-copy of each part will result in a zero mark for the part unsubmitted.
5. You are not allowed to collaborate with anyone on this assignment. Sign the declaration stating that the submitted work is your own on a coversheet.
6. If you fail to meet format/submission requirements, you will be penalised.
7. The marked assignment will be returned in Week 12 via Moodle.
