Financial modeling - Topic 3: Computing portfolio risk and return

Financial ModelingTopic #3: Computing Portfolio Risk and ReturnL. Gattis12ReferencesFinancial Modeling 3rd Edition by Simon BenningaModeling SupportCh. 8: Portfolio ModelsCh. 36: User Defined Functions with VBACh. 31: MatricesLearning ObjectivesCompute correlation and covariance matricesCompute the standard deviation of a portfolio of risky assetsUse matrix algebra to compute portfolio return and riskUse VBA comments and application. object3Monthly Return Data4Copy and save as macro enabled workbookExpected Return for a portfolio with N-assetsExpected rate of return of the portfolio:i=1 to Nwiri1w1r12w2r23w3r3Sum=w1r1+w2r2+w3r3e.g., N=35Calculating Portfolio Return in ExcelThe sumproduct function computes the sum of the product of two row or column vectors=sumproduct(wts,ret)You could also use matrix multiplication to compute portfolio return67Matrices(Chapter 27)A matrix is a rectangular array of numbersA matrix with only one row is a row vectorA matrix with only one column is a column vector A single letter is often used to denote a matrix (e.g., Matrix A)Aij refers to the value in row i and column j of Matrix A8Matrix MultiplicationMultiplication of MatricesMatrix A can be multiplied by Matrix B (written AxB or AB), if the number of columns of A equals the number of rows in Matrix B. (inside dimensions (rC*Rc)The resulting matrix has the same number of rows as the first matrix and the number of columns as the second matrix (outside dimensions Rc*rC)If the outer dimensions are 1, the result is a single numberEach element of the new matrix AB (ABij) is the sum “sumproducts” of the ith row of A times the jth column of B. (Element ABij=ΣAiBj)Excel uses MMULT(Matrix1,Matrix2) to perform matrix multiplicationYou must use “Ctrl, Shift, Enter” to enter an array in excel9MultiplicationTo copy formula: highlight area, press f2, press ctrl-shift-enterMMULT Portfolio Return=MMULT(Transpose(Weights Column Vector), Returns Column Vector) = WTRThe Weights vector is transposed (turning the column into a row) to allow matrix multiplication (=inner dimensions)Weights Transpose is size (1,3), Returns is a size (3,1)The resulting matrix is size (1,1), a single element“Ctrl,Shift,Enter” to enter arrays in excel10Tip: Cannot Use R or C as range names (Column/Row). Upper/Lower Case does not matterPortfolio Risk and Return11Portfolio standard deviation is much more complicated. The portfolio σ is less than the weighted average σ because of diversification benefits of combining assets that are not perfectly correlatedThe variance of the portfolio rate of return isWhere σij is the covariance between assets i and j, which is a function of the correlation between the two and their volatiliesThe ƩƩ is mathematical short hand to compute the summation of all “pairwise” combinations12Correlation and covariance are measures of how asset return deviations move togetherCovariance is the average of the product of deviations. (Actual – Expected or mean)Where i is the period, and there are n period returnsIf both securities returns tend to be above or below their expected return at the same time, the covariance will be a positive value (-,- or +,+)Low covariances are an indicator of diversification, but covariances are hard to interpret and compare because covariances also depend on the relative size of each assets standard deviationCorrelation normalizes covariances and are bounded by 1 and -1Covariance and Correlation Example13Double click to verify formulas14Variance of a 3-asset Portfolioi=1 to nj =1 to nwiwjσij11w1w1σ11= w12σ1212w1w2σ1213w1w3σ1321w2w1σ2122w2w2σ22= w22σ2223w2w3σ2331w3w1σ3132w3w2σ3233w3w3σ33= w32σ32Sum=w12σ12 +.+ w32σ3215The Covariance Matrix shows all the covariance terms between asset pairsThe covariance matrix shows the covariances ( ij) between the returns on any pair of securities. The diagonal simply shows all the variances.Note: σ11= σ12 and σ12= σ2116The Covariance Matrix makes it easier to compute portfolio varianceTo find the variance of this portfolio, we need the portfolio weights and the variances and covariances:17The Correlation Matrix shows all the correlations among assetsExcel’s data analysis/correlation tool will create a correlation matrix from columns of returnsCopy returns only , paste into excel, and create correlation matrixCreate Covariance Matrix from Correlation Matrix and Volatility VectorIt’s OK to combine annual σ and monthly ρ to compute covariance. The resulting covariance has the term of the σCorrelations are not time dependent if returns are assumed to be independent. You can compute correlations from weekly, monthly, or annual data and use them for any frequencyWe could have created a covariance matrix directly from the monthly data, but It would have to be scaled to annual and It would not incorporate our estimates of future volatility. The “super-secret covariance matrix short-cut” Vols*Transpose(Vols)*Correl, Ctrl-Shift-Enter18Complete covariance matrix using trick belowMmult vs. Sumproduct vs. Array Multiplication19Portfolio Volatility2021Portfolio VarianceThe variance of a two asset portfolio isVariance(rp) = ∑i∑j wiwjσijUsing matrices to calculate varianceVariance(rp)=WTSW where S is the covariance matrix and W is a column vector of weights σ11 σ12 σ1N w1w1σ11 ++ w1wNσ1N σ21 σ22 σ2N w2w1σ21 ++ w2wNσ2N +++ σN1 σN2 σNN wNw1σN1++ wNwNσNNw1w2w3 wnw1,w2, w3, , wn= {WT (1xN) S (NxN)} W (Nx1) 1x1 (1xN) 22Matrix FormulasMean Return = mmult(WT,R)WhereW= Column Vector of WeightsWT= Transpose of Column Vector of Weights = Row Vector or WeightsR= Column Vector of ReturnsStd Dev. = mmult(mmult(transpose(W),S),W)^.5WhereS= Covariance MatrixPress Control-Shift-Enter to enter this formulaPortfolio Volatility23Use named ranges S, WConstructing a Covariance MatrixYou could use Excel’s COVAR tool to compute the covariance matrix directly from historical returns. If you doThe covariance terms use both historical correlations and historical standard deviationsThe covariance terms have the same frequency as data and must be scaled for use for another frequencyIt is common the compute correlations from historical returns (weekly or monthly) and combine them with annual volatility estimates from other models (EWMA, Implied Volatility)The resulting covariance matrix is the same term as the volatilities (Annual in this case). 24Portfolio Risk and Return: Risk-Free + Risky Asset RiskIf you combine a risky and risk-free asset, the Portfolio µ = WriskyRrisky+ Wrisk-freeRrisk-freePortfolio σ = WriskyσriskyE.g., Assume Stock A (µ=10%, σ=40%) and Rf=5%and 60% is invested in Stock A and 40% in Rf assetPortfolio µ=60%*10%+40%*5%=8%Portfolio σ=60%*40%=24%25Portfolio Mean VBA FunctionFunction port_mean(w as range, r as range)'w is a column cell range of weights'r is a column cell range or returns'port_mean is the weighted average portfolio meanport_mean = Application.MMult(Application.Transpose(w), r)End Function26Use a single quote to make comments. VBA ignores all to the right of a single quoteApplication. ObjectApplication. Is a VBA object that allows program to access excel functionsWarning: Not all Excel functions are supported in VBA, and some Excel functions do not require application.. Try both ways if program is not working“As” is an VBA object that specifies data type, “range” tells user that data is a range of cells. 27JudgmentLearning ObjectivesCompute the standard deviation of a portfolio of risky assetsCompute correlation and covariance matricesUse matrix algebra to compute portfolio return and riskUse VBA comments and application. objectData (Returns and Risk based on calculations and judgment28