Question

ASSIGNMENT 1 B8F306/05 PORTFOLIO MANAGEMENT Answer ALL questions QUESTION 14 MARKS AD Young worth w esteck ALAM MARTINOURCES (ALAM) and DATAREP HOLDINGS BERHAD DATAP The following return for the stocks of ALAM DATAPRP DATN BBBBBBBBBB 23.75 2635 a) Use Excel to create a spreadsheet that calculates anual portfolio returns for an equally weighted portfolio of ALAM and DATAPRO.Calculate the average annual return for both stocks and the portfolio 17 marks b) Use the table of annual returns for ALAM and DATAPRP to create an Excel spreadsheet that calculates the standard deviation of annual returns for ALAM and DATAPRP using the below information Companies Alocation of Fund DATAPRP RM50,000 Total Allocation RM100,000 RM50,000 1 1 c) Use the table of annual returns in (a) for ALAM and DATAPRP to create an Excel spread the calculates the correlation coefficient for ALAM and DATAPRP (10 marks ASSIGNMENTS 30/OS PORTFOLIO MANAGEMENT the of f or ALAM and DATAPRP terance 11.00, 0.001 10.75, 0.251 10.50 0.50 10.30, 0.70 (1 mark! Calculate the portfolio Mandard deviation avocated with each portfolio composition You will need to use the standard deviations found previously for ALAM and DATAPRP and the correlation coefficient 15 marks Create an ecel spreadsheet the graph the portfolio return and standard deviation combinations found ind for ALAM and DATAPRP's. TOTAL MARKS: 45 MARKS

Answer 1

a]

average annual return is calculated using AVERAGE function in Excel

Return of two-asset portfolio R_p = w₁R₁ + w₂R_2,

where R_p = expected return

w₁ = weight of Asset 1.  

R₁ = expected return of Asset 1

w₂ = weight of Asset 2

R₂ = expected return of Asset 2

The weight of each asset is 0.50, as the portfolio is equally weighted

b]

standard deviation is calculated using STDEV.S function in Excel

c]

Correlation is calculated using CORREL function in Excel

d]

Return of two-asset portfolio R_p = w₁R₁ + w₂R_2,

where R_p = expected return

w₁ = weight of Asset 1.  

R₁ = expected return of Asset 1

w₂ = weight of Asset 2

R₂ = expected return of Asset 2

e]

standard deviation for a two-asset portfolio σ_p = $\sqrt{}$ w₁²σ₁² + w₂²σ₂² + 2w₁w₂Cov_1,2

where σ_p = standard deviation of the portfolio

w₁ = weight of Asset 1

w₂ = weight of Asset 2

σ₁ = standard deviation of Asset 1

σ₂ = standard deviation of Asset 2

Cov_1,2 = covariance of returns between Asset 1 and Asset 2

Cov_1,2 = ρ_1,2 * σ₁ * σ_2, where ρ_1,2 = correlation of returns between Asset 1 and Asset 2

E A B C D 1 Year ALAM DATAPRP Portfolio 2011 17.50% 27.50% 22.50% 2012 17.50% 25.30% 21.40% 2013 20.00% 22.00% 21.00% 2014 21.25% 19.80% 20.53% 2015 21.25% 18.70% 19.98% 2016 23.75% 17.60% 20.68% 2017 26.25% 15.40% 20.83% 2018 27.50% 13.20% 20.35% 2019 27.50% 11.00% 19.25% 11 2020 30.00% 8.80% 19.40% 12 Average 23.25% 17.93% 20.59% 13 Std. Dev. 4.42% 6.03% 0.95% 14 Correlation (0.9803) 15 Weight - Weight - 16 Combination ALAM DATAPRP Return 1.00 0.00 23.25% 2 0.75 0.25 21.92% 3 0.50 0.50 20.59% 4 0.30 0.70 19.53% 5 0.00 1.00 17.93% Std. Dev. 4.42% 1.86% 0.95% 2.93% 6.03%

A 1 Year 2 2011 3 2012 4 2013 5 2014 6 2015 7 2016 8 2017 9 2018 10 2019 11 2020 12 Average 13 Std. Dev. 14 Correlation ALAM 0.175 0.175 0.2 0.2125 0.2125 0.2375 0.2625 0.275 0.275 0.3 =AVERAGE(B2:B11) =STDEV.S(B2:B11) =CORREL(B2:B11,C2:C11) DATAPRP Portfolio 0.275 =(0.5*82)+(0.5*C2) 0.253 =(0.5*83)+(0.5*C3) 0.22 =(0.5*34)+(0.5*C4) 0.198 =(0.5*85)+(0.5*C5) 0.187 =(0.5*16)+(0.5*C6) 0.176 =(0.5*87)+(0.5*07) 0.154 =(0.5*88)+(0.5*08) 0.132 =(0.5*89)+(0.5*09) 0.11 =(0.5*310)+(0.5*010) 0.088 =(0.5*311)+(0.5*C11) =AVERAGE(C2:C11) =AVERAGE(D2:011) |-STDEV.S(C2:C11) =STDEV.S(D2:011) 15 0 16 Combination Weight - ALAM 17 1 18 2 0.75 19 3 20 4 0.3 21 Weight - DATAPRP Return Std. Dev. =(B17*$B$12)+(C17*$C$12) =SQRT (B1712)*($B$13^2)+(C1712)*($C$13^2)+(2*B17*C17*$B$14*$B$13*$C$13)) 0.25 =(B18*$B$12)+(C18*$C$12) =SQRT (B1842)*($B$13^2)+(C1812)*($C$13^2)+(2*118*C18*$B$14*$B$13*$C$13)) =(B19*$B$12)+(C19*$C$12) =SQRT (B1912)*($B$13^2)+(C1942)*($C$13^2)+(2*B19*C19*$B$14*$B$13*$C$13)) =(B20*$B$12)+(C20*$C$12) =SQRT (B20^2)*($B$13^2)+(C2012)*($C$13^2)+(2*320*С20*$B$14*$B$13*$C$13)) =(B21*$B$12)+(C21"$C$12) =SQRT (B21^2)*($B$13^2)+(C21^2)*($C$13^2)+(2*821"C21"$B$14*$B$13*$C$13)) 0.5 0.7

f]

25.00% 20.00% 15.00% 10.00% 5.00% 0.0096 3 4 5 Retum Std. Dev.