Formulas

Question 1

Calculate Mean with Weights in a Column and Means in a Column

Answer 1

=SUMPRODUCT(Weights,Means)

Question 2

Calculate Mean with Weights in a Column and Means in a Row

Answer 2

=MMULT(Row,Column)

Question 3

Calculate Portfolio Standard Deviation

Answer 3

=SQRT(MMULT(TRANSPOSE(Weights),MMULT(VCov,Weights)))

Question 4

Delta Normal VaR

Answer 4

=AmountxQuantilexScalexSTD

Where Quantile =NORMSINV(Confidence) and Scale =SQRT(1/n)

Question 5

GMVP

Answer 5

=MMULT(MINVERSE(Array),Ones)/MMULT(TRANSPOSE(Ones),MMULT(MINVERSE(Array),Ones))
OR
=MMULT(MINVERSE(Array),Ones)/SUM(MMULT(MINVERSE(Array),Ones))

Question 6

Efficient Portfolio

Answer 6

=MMULT(MINVERSE(Array),Er-c)/SUM(MMULT(MINVERSE(Array),Er-c))

Question 7

Variance

Answer 7

=W1^2Variance1+W2^2Variance2+2W1W2*Cov

Question 8

Covariance Efficient with GMVP

Answer 8

=MMULT(TRANSPOSE(GMVP Weights),MMULT(VCov,Efficient Weights))