Sob Doc

...

Q3. Given the following Excel for the covariance matrix of 3 correlated stocks’ returns, please select the correct choice ((a) to (e) for each) of Excel codes for the empty cells (1) to (5):

[pic 1]

(1) To compute the product of Cholesky decomposition and its transpose:

(a) =MMULT(B18:D20, B18:D20)

(b) =MMULT(B18:D20, B13:D15)

(c) =MMULT(B13:D15, B13:D15)

(d) =MMULT(B13:D15, B18:D20)

(e) All (b), (c), and (d) are wrong

(2) To generate a random standard normal error:

(a) RAND()

(b) =NORMSINV(RAND())

(c) =NORMSDIST(RAND())

(d) =NORMSINV()

(e) All (b), (c), and (d) are wrong

(3) To calculate the 1-by-3 vector of correlated errors:

(a) =TRANSPOSE(G9:I9)

(b) =MMULT(TRANSPOSE(G9:I9), $B$13:$D$15)

(c) =TRANSPOSE(MMULT(TRANSPOSE(G9:I9), $B$13:$D$15))

(d) =TRANSPOSE($B$13:$D$15)

(e) All (b), (c), and (d) are wrong

(4) To calculate the stock prices based on simulated stock returns (using the formula: ln(S(t+δt)/St) ~ N((r – (σ^2)/2)δt, σ√δt) and S(t+δt) = St exp[ln(S(t+δt)/St)] ):

(a) =P8*EXP((F3*F4+C3*K9*SQRT(F4))

(b) =P8*EXP((F3-(C3^2)/2)*F4)

(c) =P8*EXP((F3-(C3^2)/2)*F4+C3*K9*SQRT(F4))

(d) =P8

(e) All (b), (c), and (d) are wrong

Q4. You are given the following Excel Worksheet for the time series return data of Stock A, Stock B, and Market Portfolio, and the betas of Stocks A and B (Let the risk free rate be 0.05% per day).

[pic 2]

Now, please select the right Excel codes for the following 4 sub-questions:

(1) To calculate the CAPM residuals of stock A’s excess return on day 1;

(a) = G3-($C$9+$C$10*H3)

(b) = F3-($B$9+$B$10*H3)

(c) = B3-($B$9+$B$10*D3)

(d) = C3-($C$9+$C$10*D3)

(e) All (b), (c), and (d) are wrong

(2) To calculate the idiosyncratic volatility of stock B in the sample period (day 1 to day 5);

(a) =STDEV(J3:J7)

(b) =STDEV(F3:F7)

(c) =STDEV(G3:G7)

(d) =STDEV(K3:K7)

(e) All (b), (c), and (d) are wrong

(3) To calculate the Sharpe ratio (average of excess return /volatility of excess return) of Stock A in the sample period:

(a) =AVERAGE(B3:B7)/STDEV(B3:B7)

(b) = STDEV(B3:B7)/AVERAGE(B3:B7)

(c) =AVERAGE(F3:F7)/STDEV(F3:F7)

(d) = STDEV(F3:F7)/AVERAGE(F3:F7)

(e) All (b), (c), and (d) are wrong

Q5. Given the following Excel worksheet

[pic 3]

Now, please select the correct Excel codes for the following 4 sub-questions:

(1) To calculate the probability-weighted expected value of ROE (return on equity) in cell (1) (cell I19):

(a) =C14*H14+C15*H15+C16*H16+C17*H17+C18*H18

(b) =C14*I14+C15*I15+C16*I16+C17*I17+C18*I18

(c) =AVERAGE(I14, I15, I16, I17, I18)

(d) =AVERAGE(H14, H15, H16, H17, H18)

(e) All (a), (b), (c), and (d) are wrong

(2) To calculate the probability-weighted standard deviation of ROE in cell (2) (cell I20):

(a) = C14*(I14-I19)+C15*(I15-I19)+C16*(I16-I19)+C17*(I17-I19)+C18*(I18-I19)

(b) = C14*(I14-I19)^2+C15*(I15-I19)^2+C16*(I16-I19)^2+C17*(I17-I19)^2+C18*(I18-I19)^2

(c) = SQR(C14*(I14-I19)+C15*(I15-I19)+C16*(I16-I19)+C17*(I17-I19)+C18*(I18-I19))

(d) = SQR(C14*(I14-I19)^2+C15*(I15-I19)^2+C16*(I16-I19)^2+C17*(I17-I19)^2+C18*(I18-I19)^2)

(e) All (a), (b), (c), and (d) are wrong

(3) If we want to let expected ROE in cell (1) (cell I19) be 100% by changing unit price, we should use:

(a) “Data table” function

(b) “Scenario manager” function

(c) “Regression” function

(d) “Solver” function

(e) “Goal seek” function

Q6. Please fill in blanks (1) to (3) from choices (a) to (o) for the VBA code that will output a row vector of Delta, Gamma, Vega, and Rho for American options.

Note: the basic idea is that all Greeks in the format of ∂Function (input) / ∂input can be approximated by [Function (input + ∆input) – Function (input) ] / ∆input when ∆input is very small.

[pic 4][pic 5][pic 6][pic 7]

================================================================

Function greek_amer(ByVal Style As String, ByVal C_P As Double, _

ByVal spot As Double, ByVal strike