Lecture Financial modeling - Topic 3: Computing portfolio risk and return

Financial Modeling Topic #3: Computing Portfolio Risk and Return 1 References • Financial Modeling 3rd Edition by Simon Benninga – Modeling Support – Ch. 8: Portfolio Models – Ch. 36: User Defined Functions with VBA – Ch. 31: Matrices 2 Learning Objectives • Compute correlation and covariance matrices • Compute the standard deviation of a portfolio of risky assets • Use matrix algebra to compute portfolio return and risk • Use VBAcomments and application. object 3 Asset ExpRet Std Dev Weights SPY 6.50% 16.00% 50.00% EFA 6.75% 18.00% 40.00% EEM 7.75% 25.00% 10.00% Portfolio Mmults Returns Returns Returns SPY EFA EEM 6.69% 8.30% 7.27% 0.00% -4.82% -2.91% 3.81% 3.81% 3.02% 8.96% 9.97% 11.74% -4.51% -3.78% -3.24% 6.84% 11.61% 10.94% -5.18% -1.89% -1.38% -7.95% -11.18% -9.40% 1.55% -2.81% -0.18% 6.09% 6.39% 8.12% 3.13% 0.27% 1.76% -3.64% -5.07% -7.75% 1.91% 0.70% 3.28% 6.16% 3.93% 7.84% -1.92% -2.53% -3.44% 3.54% 3.81% 10.20% 3.69% 4.51% -1.31% 7.47% 10.04% 11.02% -0.07% -1.41% -2.26% 5.85% 13.17% 15.96% 9.93% 11.53% 15.55% 8.34% 8.39% 16.86% -10.75% -10.38% -6.29% -8.22% -13.73% -9.27% 0.98% 8.84% 10.32% -6.96% -6.35% -9.74% -16.52% -20.85% -25.57% -9.42% -11.43% -14.70% 1.55% -4.25% -6.30% -0.90% -3.32% -5.50% -8.35% -10.48% -10.30% 1.51% 1.18% 3.16% 4.77% 5.44% 9.15% -0.90% 0.43% -3.76% -2.58% -1.03% 2.00% -6.05% -7.84% -8.92% -1.12% -2.99% -1.41% -3.87% -3.62% -7.66% 1.36% 4.24% 11.89% 3.87% 5.32% 11.56% 1.28% -0.63% 1.05% -3.13% -2.30% 0.70% -1.47% -0.32% 3.69% 3.39% 2.36% 5.05% 4.43% 3.74% 3.74% 1.16% 2.90% 5.31% -1.96% -0.11% -3.24% 1.50% 1.38% 0.12% 1.34% 3.20% 5.46% 1.99% 3.07% 5.98% 3.15% 3.76% 7.06% 2.70% 0.21% -0.86% 2.19% 2.55% 1.57% 0.44% 0.83% 2.35% 0.26% -0.07% 0.21% -3.01% -3.81% -11.15% 1.26% 4.79% 6.53% 1.66% 4.00% 2.14% 0.56% -0.71% -3.82% 2.41% 5.78% 14.19% -0.20% 5.18% 6.08% 4.39% 2.30% 5.79% -2.36% -3.18% -6.34% 0.80% 3.69% 8.67% -0.94% 3.85% 1.32% 3.83% 2.99% 7.71% 0.15% 1.44% 3.93% 3.22% -0.87% 3.20% -1.88% -1.62% -1.25% -1.82% -2.63% -7.93% 2.09% 3.78% 9.72% -2.25% -1.90% -0.55% 3.01% 4.80% 4.74% 4.45% 6.08% 9.97% 1.29% 3.50% 2.80% 1.01% 2.07% 7.02% 0.24% 1.20% 3.50% -3.22% -4.27% -3.63% 1.85% 3.36% 0.96% 1.71% 1.14% 1.17% -1.89% -3.39% -9.84% -1.33% 0.08% 0.59% 1.36% 2.29% 3.92% 1.98% 1.12% 2.45% 5.03% 8.73% 8.95% 1.09% 2.55% 1.88% 5.35% 6.50% 9.20% -1.09% 3.00% -0.68% 2.07% 3.30% 7.99% 1.79% 1.50% 5.69% 1.07% 1.96% 4.22% 5.48% 6.44% 9.12% 8.46% 10.19% -57.41% 0.22% -2.32% -2.32% -1.35% -2.12% -2.12% -2.46% -4.32% -4.32% -5.66% -3.32% -3.32% 6.17% 5.62% 5.62% 8.23% 3.81% 3.81% -10.49% -10.34% -10.34% 0.68% -0.97% -0.97% -7.88% -9.53% -9.53% -7.38% -3.30% -3.30% -0.59% 0.54% 0.54% -5.82% 1.51% 1.51% 3.32% 5.59% 5.59% -1.79% 1.20% 1.20% -0.98% -5.78% -5.78% 0.56% 0.62% 0.62% 7.80% 3.33% 3.33% 1.30% 1.85% 1.85% -8.15% -9.62% -9.62% CovarianceMatrix: Cov1,2 =Corr1,2*SD *SD SPY EFA EEM SPY EFA EEM Cov=Vols*Transpose(Vols)*Correl, Ctrl-Shift-E Correlation Matrix SPY EFA EEM SPY EFA EEM Var CalcMatrix: =Cov1,2*W *W2 SPY EFA EEM SPY EFA EEM Cov=W*Transpose(W)*CovMat, Ctrl-Shift-Ente Monthly Return Copy and save ata as macro enabled workbook 4 Expected Return for a portfolio with N-assets • Expected rate of e.g., rp return of the portfolio: N wri i 1 N=3 i=1 to N wiri 1 w1r1 2 w2r2 3 w3r3 Sum= w1r1+w2r2+w3r3 5 ... - tailieumienphi.vn