Calculate the daily market return over the last five years

Question

Assignment

Question 1 Risk and Return

Calculate the following using the data from Yahoo Finance (https://au.finance.yahoo.com/) for the company you selected for Question 1 of Assignment .

1. Calculate the daily market return over the last five years from the daily prices, calculate the monthly returns from the daily returns, and calculate the yearly returns from the monthly returns.

2. Calculate the total risk (i.e. yearly standard deviation of the daily returns).

3. Calculate the yearly systematic / market risk using the daily returns of the stock and daily return of the market index.

4. Calculate the unsystematic risk / firm specific risk. Suggest whether this company is a good investment. Answer the following questions while making your suggestion.

a) What is the basis for selection of this stock if you suggest this as a good investment?

b) Would you invest all your money into this stock? If not, why not? How will you address this concern?

Question 2 Capital Budgeting

ABC Ltd. would like to set up a new expansion plant. Currently, ABC has an option to buy an existing building at a cost of AUD 24 000. Necessary equipment for the plant will cost AUD 16 000, including installation costs. The economic life of the equipment and building are 5
and 40 years, respectively. The project also requires an initial investment of AUD 12 000 in net working capital. The initial working capital investment will be made at the time of the purchase of the building and equipment.

The project's estimated economic life is four years. At the end of that time, the building is expected to have a market value of AUD 15 000 and a book value of AUD 21 600, whereas the equipment is expected to have a market value of AUD 4 000 and a book value of AUD 3
200.

Annual sales will be AUD 80 000. The production department has estimated that variable manufacturing costs will total 60% of sales and that fixed overhead costs, excluding depreciation, will be AUD 10 000 a year. Depreciation expense will be determined for the year using straight line depreciation method.

ABC's tax rate is 40%; its cost of capital is 12%; and, for capital budgeting purposes, the company's policy is to assume that operating cash flows occur at the end of each year. The plant will begin operations immediately after the investment is made, and the first operating cash flows will occur exactly one year later.

Requirements:

1. Compute the initial investment outlay, operating cash flow over the project's life, and the terminal-year cash flows for ABC's expansion project.

2. Determine whether the project should be accepted using NPV analysis.

3. Do the sensitivity analysis using different levels of change (e.g. 2%, 5% and 10% increase and decrease) of each of the key inputs (e.g., sales, variable costs and cost of capital)

4. Identify the most sensitive factor

5. Perform the scenario analysis

Return and Risk Calculation

1. Calculation of daily market returns for the company selected for Module 1 assignment for over 5 years What is daily market return?
A: Percentage change in prices over one day holding period.
How to calculate?

R_t=((P_t-P_t-1)/P_t-1)
Where,

R_t = Return today
P_t = Price today
P_t-1= Price yesterday

Alternatively:

R_t= ln(P_t/P_t-1)

Where,

ln = Natural logarithm

What is the source of market price data?
A: yahoo finance

2. Calculation of monthly market returns from the daily market returns.

How to calculate monthly returns from daily returns?

A: Demonstrated in the "return calculation.xlsx" file available under key resources.

Step 1: Add 1 to the daily returns calculated using either Equation (1) or Equation (2)

Step 2: Use the product function in Excel (i.e., = PRODUCT (select the daily returns in a month)

Step 3: Subtract 1 from the product (I have combined step 2 and step 3 in my calculations)

3. Calculation of yearly market returns from the monthly market returns.

How to calculate yearly market returns from the monthly market returns?

A: Please follow the steps below.

Step 1: Add 1 to the monthly returns

Step 2: Use the product function in Excel (i.e., = PRODUCT (select the 12 monthly returns in a year)

Step 3: Subtract 1 from the product (I have combined step 2 and step 3 in my calculations)

4. Should I do calculations for five years in one file?

A: For simplicity, it is better to do the calculations for each year separately (so five files for five years). I am, however, happy if you can do all calculations in one file.

5. Calculate yearly standard deviation (i.e. total risk) of the daily returns.

How to calculate standard deviation of the daily returns?

A: Please use the Excel formula (=STDEV.S (select the range of daily returns))

6. Calculate yearly market risk (or systematic risk).

A: It is a yearly calculation using the daily returns of the company and daily returns of the market

Steps in Excel:

i) Data>Analysis>Regression (if you do not find analysis tab under Data, please add the analysis tool pack from options)

ii) Select the range of stock return as Y inputs

iii) Select the range of market returns as X inputs

iv) Tick the label box

v)Select a cell for the output range

vi) Click OK

vii) The coefficient of the market return is the systematic risk (commonly known as beta). Please ignore the other statistics.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.2154
R Square	0.0464
Adjusted R Square	0.0426
Standard Error	0.0265
Observations	252

ANOVA
	df	SS	MS	F	Significance F
Regression	1	0.0085	0.0085	12.1683	0.0006
Residual	250	0.1753	0.0007
Total	251	0.1838
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-0.0015	0.0017	-0.8779	0.3808	-0.0048	0.0018
Market return	0.5428	0.1556	3.4883	0.0006	0.2364	0.8493

7. How to calculate the daily market returns?

Step 1: Please use the daily price of a market index (i.e., ASX All Ordinaries or ASX S&P200)

Step 2: Use either Equation (1) or Equation (2) to calculate the daily market retruns.

8. How to calculate the unsystematic risk (or firm specific risk)?

Step 1: Use the following model to calculate the daily fitted returns (or forecasted returns) of the stock of the company

E(R_i)= α +β R_m

Where,

α = Intercept
β = Coefficient of X Variable 1 (i.e., beta)
R_m = Daily market return

Step 2: Calculate the residuals (i.e., actual return minus expected return) for every day.
ε=R_i-E( R_i )

Step 3: Calculate the yearly standard deviation of the daily residuals. This is defined as the unsystematic risk.