1. Choose 3 domestic indexes from the Data tab on the template. Compute the following for each index and the 3-month US TBill Index: a. mean, wealth index, annualized return (4 each) b. variance, standard deviation, Sharpe ratio, semi-deviation (4 each) (not for the 3-mo Bill always assume variance of TBill = 0) c. correlation matrix (12) d. Graph the Growth of $1 for all four indices from 2000-2019. Label axes, legend, last point (ending wealth per $1). You must include proper labelling for full credit. (8) 2. Choose 2 of your 3 indexes. Enter the expected return, standard deviation, Sharpe, correlation, and covariance for them in the space provided. Compute the portfolio expected return, standard deviation, and reward-to-risk ratio for the listed weights on the template. (14) a. Graph the efficient frontier of the combinations from Part 2. Label axes and data points. You must include proper labelling for full credit. (8) 3. For a portfolio containing your two indexes, calculate: a. Weights, expected return, and standard deviation for the Minimum Variance Portfolio (6) b. Weights, expected return, standard deviation, and Reward-to-Risk ratio for the Optimal Risky Portfolio (6) c. Standard deviation for a portfolio with a target expected return equal to the expected return of the 50/50 weighted portfolio (assuming the risk-free asset is not available) (6) d. Weights and standard deviation for a Complete Portfolio with the same target expected return as in the Part 3c 50/50 portfolio (assume the risk-free asset is available for lending and borrowing) (8) You can either use a formula to get the weights for 3a, b, and d, or use the Excel Add-in Solver to do optimization.

I need of formulas for everything, please to pull in up on my Excel!

Thank you in advance!

Ex1 S20 Template Q- Search Sheet Insert Draw Data View Home F Page Layout Formulas 2 Bu Pictures Shapes Icons Review Get Add-ins B L K E Slicer **Timeline A A + Share TI equation 2 Symbol Pivot Table Recommended Table Pivot Tables My Add-ins Recommended Charts IN Maps PivotChart Sparklines Link New Comment Text Box Header & Footer | 11 x v fx H G I J K L M N O P Q R S T U V W X Y Z AA AB AC AD Bloomberg Barclays US Inv Bloomberg Barclays Capital US Bloomberg Barclays US Inv Grade Corp 9.08% 10.31% 10.12% 8.24% 5.39% 1.68% 4.30% 4.56% 4.94% 18.68% 9.00% 8.15% 9.82% -1.53% 7.46% -0.68% 6.11% 6.42% -2.51% 14.54% Bloomberg Barclays Capital US Aggregate 11.63% 8.44% 10.25% 4.10% 4.34% 2.43% 4.33% 6.97% 5.24% 5.93% 6.54% 7.84% 4.21% -2.02% 5.97% 0.55% 2.65% 3.54% 0.01% 8.72% Template S20 Sheet1 Domestic Indexes + Ready + 100% Ex1 S20 Template Q Search Sheet Home + Share AutoSum Insert Cut Copy Format x Draw Page Layout Calibri (Body) 11 B I U S fx B C Formulas As A A Data Review View = = = . = = = = *3 E Wrap Text Merge & Center General $ . % F 12 Conditional Format - Insert -* * Delete Format ) Celle Clear U Paste E5 A Sort & Filter Find & Select D E F G H I J K L O 0.00E+00 0.00E+00 0.00E+00 3TB Possible Earned 4 1 1a. Exp Return Wealth Index Annualized Return 1b. Variance St Dev Sharpe (Reward-to-Risk) Ratio Semi-Dev (Assumed for T-Bill) 0.0000 0.0% 0.00 0.0% 4 4 17 1c. Exp Return Wealth Index Annualized Return Variance St Dev Sharpe (Reward-to-Risk) Ratio Semi-Dev Corr Matrix Growth of $1 Graph Portf Wtd Std, E(r) Eff Frontier Graph MVP ORP Target Portf (no TB] Target Complete Portf 8 12 1c. Correlation Matrix 0.00E+00 0.00E+00 0.00E+00 1271 2004 12/31/2005 12/31/2006 12/31/2007 12/31/2008 12/31/2009 12/31/2010 12/31/2011 12/31/2012 12/31/2013 12/31/2014 12/31/2015 12/31/2016 12/31/2017 12/31/2018 12/31/2019 1a. Exp Return Wealth Index Annualized Return 1b. Variance St Dev Sharpe (Reward-to-Risk) Ratio Semi-Devl 14 0.00E+00 0.00E+00 0.00E+00 Selected Indexes Index Names E(R) St Dev Var Correlation Covariance 100 S&P IBM bill (Assume St Dev = 0% for TB) 2. Efficient Frontier St Dev Exp Return Rew-to-Risk Ratio Eff Front S&P Weight 100% 80% 60% 40% 20% 10% 0% IBM Weight 0% 20% 40% 60% 80% 90% 100% Template S20 Domestic Indexes + Ready @ J - - - + 132% Ex1 S20 Template Q Search Sheet Home View View + Share o O D. HLAutoSum. A Ep Wrap Text 5 Wrap Text Merge & Center - General $ % - 12 2 . - -*, 2 AutoSum Clear 4 9 Q Paste ) 0 Format .0 Insert Delete Format Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select E5 M S T U V W X Y Z AA AB AC AD Wealth Index Semi-Deviation Insert Draw Page Layout Formulas Data Review Cut Calibri (Body) , 11 An A = = = 2 Copy BIVA X fx N O P Q R Your Data - Copy Paste Special Values here: Index Name => 12/31/2000 12/31/2001 12/31/2002 12/31/2003 12/31/2004 12/31/2005 12/31/2006 12/31/2007 12/31/2008 12/31/2009 12/31/2010 12/31/2011 12/31/2012 12/31/2013 12/31/2014 12/31/2015 12/31/2016 12/31/2017 12/31/2018 12/31/2019 1a. Exp Return Wealth Index Annualized Return 1b. Variance St Dev Sharpe (Reward-to-Risk) Ratio Semi-Dev 12 - Bill) 3mo Tbill 6.2% 4.5% 1.8% 1.1% 1.3% 3.1% 4.9% 5.1% 2.2% 0.2% 0.2% 0.1% 0.1% 0.1% 0.1% 0.1% 0.4% 0.9% 1.9% 2.3% (Assumed for T-Bill) 0.0000 0.0% 0.00 0.0% Eff Frontier Graph Growth of $1 Graph Template S20 Domestic Indexes + Ready 0 0 - - - + 132% Ex1 S20 Template Q Search Sheet Home View + Share . Insert Cut Copy Data Review = = = 2 E 12 AutoSum ' A Ep Wrap Text Merge & Center - , 3, . Q General $ % U FIN . Draw Page Layout Formulas Calibri (Body) | 11 | Aa A BIU fx B C D Paste ) Format 0 .0 Insert Delete Format Conditional Format Formatting as Table Cell Styles Clear Sort & Filter Find & Select E5 x E J K L M N O P I 2. Efficient Frontier St Dev Exp Return Rew-to-Risk Ratio Eff Fronti G S&P Weight 100% 80% 60% 40% 20% 10% 0% H IBM Weight 0% 20% 40% 60% 80% 90% 100% 3a. Minimum Variance S&P MV Portfolio Reward-to- R isk Ratio IBM Total E(R) St Dev 3b. Optimal Risky Portfolio S&P OR Portfolio IBM Total Reward-to- Risk Ratio E(R) St Dev 3c. Risk-Free Asset is not Available Target Expected Return = S&P IBM Portfolio 50% 50% 0.00% Total Reward-to- R isk Ratio E(R) St Dev 3d. Risk-Free Asset is Available Target Expected Return = ORP Wt Portfolio 0.00% Total Reward-to- Risk Ratio Tbill Wt E(R) St Dev Template S20 Domestic Indexes + Ready O 0 - - + 132% Ex1 S20 Template Q Search Sheet + Share AutoSum General $ % E 12 Conditional Format , 3, Insert Delete ) Cell Format Clear Sort & Find & Home Insert Draw Page Layout Formulas Data Review View . Cut Calibri (Body) | 11 | Aa A = = = 2 Ep Wrap Text U Copy Paste Format B 1 U 2 A = = = = * Merge & Center E5A x V foe No e o Rs Tu 1b. Variance | 0.0000 (Assumed for T-Bill) St Dev 0.0% Reward-to-Risk) Ratio 0.00 Semi-Dev 0.0% V W X Y Z AA AB AC AD AE Eff Frontier Graph Growth of $1 Graph Template S20 Domestic Indexes + Ready O - - - + 132% Ex1 S20 Template Q- Search Sheet Insert Draw Data View Home F Page Layout Formulas 2 Bu Pictures Shapes Icons Review Get Add-ins B L K E Slicer **Timeline A A + Share TI equation 2 Symbol Pivot Table Recommended Table Pivot Tables My Add-ins Recommended Charts IN Maps PivotChart Sparklines Link New Comment Text Box Header & Footer | 11 x v fx H G I J K L M N O P Q R S T U V W X Y Z AA AB AC AD Bloomberg Barclays US Inv Bloomberg Barclays Capital US Bloomberg Barclays US Inv Grade Corp 9.08% 10.31% 10.12% 8.24% 5.39% 1.68% 4.30% 4.56% 4.94% 18.68% 9.00% 8.15% 9.82% -1.53% 7.46% -0.68% 6.11% 6.42% -2.51% 14.54% Bloomberg Barclays Capital US Aggregate 11.63% 8.44% 10.25% 4.10% 4.34% 2.43% 4.33% 6.97% 5.24% 5.93% 6.54% 7.84% 4.21% -2.02% 5.97% 0.55% 2.65% 3.54% 0.01% 8.72% Template S20 Sheet1 Domestic Indexes + Ready + 100% Ex1 S20 Template Q Search Sheet Home + Share AutoSum Insert Cut Copy Format x Draw Page Layout Calibri (Body) 11 B I U S fx B C Formulas As A A Data Review View = = = . = = = = *3 E Wrap Text Merge & Center General $ . % F 12 Conditional Format - Insert -* * Delete Format ) Celle Clear U Paste E5 A Sort & Filter Find & Select D E F G H I J K L O 0.00E+00 0.00E+00 0.00E+00 3TB Possible Earned 4 1 1a. Exp Return Wealth Index Annualized Return 1b. Variance St Dev Sharpe (Reward-to-Risk) Ratio Semi-Dev (Assumed for T-Bill) 0.0000 0.0% 0.00 0.0% 4 4 17 1c. Exp Return Wealth Index Annualized Return Variance St Dev Sharpe (Reward-to-Risk) Ratio Semi-Dev Corr Matrix Growth of $1 Graph Portf Wtd Std, E(r) Eff Frontier Graph MVP ORP Target Portf (no TB] Target Complete Portf 8 12 1c. Correlation Matrix 0.00E+00 0.00E+00 0.00E+00 1271 2004 12/31/2005 12/31/2006 12/31/2007 12/31/2008 12/31/2009 12/31/2010 12/31/2011 12/31/2012 12/31/2013 12/31/2014 12/31/2015 12/31/2016 12/31/2017 12/31/2018 12/31/2019 1a. Exp Return Wealth Index Annualized Return 1b. Variance St Dev Sharpe (Reward-to-Risk) Ratio Semi-Devl 14 0.00E+00 0.00E+00 0.00E+00 Selected Indexes Index Names E(R) St Dev Var Correlation Covariance 100 S&P IBM bill (Assume St Dev = 0% for TB) 2. Efficient Frontier St Dev Exp Return Rew-to-Risk Ratio Eff Front S&P Weight 100% 80% 60% 40% 20% 10% 0% IBM Weight 0% 20% 40% 60% 80% 90% 100% Template S20 Domestic Indexes + Ready @ J - - - + 132% Ex1 S20 Template Q Search Sheet Home View View + Share o O D. HLAutoSum. A Ep Wrap Text 5 Wrap Text Merge & Center - General $ % - 12 2 . - -*, 2 AutoSum Clear 4 9 Q Paste ) 0 Format .0 Insert Delete Format Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select E5 M S T U V W X Y Z AA AB AC AD Wealth Index Semi-Deviation Insert Draw Page Layout Formulas Data Review Cut Calibri (Body) , 11 An A = = = 2 Copy BIVA X fx N O P Q R Your Data - Copy Paste Special Values here: Index Name => 12/31/2000 12/31/2001 12/31/2002 12/31/2003 12/31/2004 12/31/2005 12/31/2006 12/31/2007 12/31/2008 12/31/2009 12/31/2010 12/31/2011 12/31/2012 12/31/2013 12/31/2014 12/31/2015 12/31/2016 12/31/2017 12/31/2018 12/31/2019 1a. Exp Return Wealth Index Annualized Return 1b. Variance St Dev Sharpe (Reward-to-Risk) Ratio Semi-Dev 12 - Bill) 3mo Tbill 6.2% 4.5% 1.8% 1.1% 1.3% 3.1% 4.9% 5.1% 2.2% 0.2% 0.2% 0.1% 0.1% 0.1% 0.1% 0.1% 0.4% 0.9% 1.9% 2.3% (Assumed for T-Bill) 0.0000 0.0% 0.00 0.0% Eff Frontier Graph Growth of $1 Graph Template S20 Domestic Indexes + Ready 0 0 - - - + 132% Ex1 S20 Template Q Search Sheet Home View + Share . Insert Cut Copy Data Review = = = 2 E 12 AutoSum ' A Ep Wrap Text Merge & Center - , 3, . Q General $ % U FIN . Draw Page Layout Formulas Calibri (Body) | 11 | Aa A BIU fx B C D Paste ) Format 0 .0 Insert Delete Format Conditional Format Formatting as Table Cell Styles Clear Sort & Filter Find & Select E5 x E J K L M N O P I 2. Efficient Frontier St Dev Exp Return Rew-to-Risk Ratio Eff Fronti G S&P Weight 100% 80% 60% 40% 20% 10% 0% H IBM Weight 0% 20% 40% 60% 80% 90% 100% 3a. Minimum Variance S&P MV Portfolio Reward-to- R isk Ratio IBM Total E(R) St Dev 3b. Optimal Risky Portfolio S&P OR Portfolio IBM Total Reward-to- Risk Ratio E(R) St Dev 3c. Risk-Free Asset is not Available Target Expected Return = S&P IBM Portfolio 50% 50% 0.00% Total Reward-to- R isk Ratio E(R) St Dev 3d. Risk-Free Asset is Available Target Expected Return = ORP Wt Portfolio 0.00% Total Reward-to- Risk Ratio Tbill Wt E(R) St Dev Template S20 Domestic Indexes + Ready O 0 - - + 132% Ex1 S20 Template Q Search Sheet + Share AutoSum General $ % E 12 Conditional Format , 3, Insert Delete ) Cell Format Clear Sort & Find & Home Insert Draw Page Layout Formulas Data Review View . Cut Calibri (Body) | 11 | Aa A = = = 2 Ep Wrap Text U Copy Paste Format B 1 U 2 A = = = = * Merge & Center E5A x V foe No e o Rs Tu 1b. Variance | 0.0000 (Assumed for T-Bill) St Dev 0.0% Reward-to-Risk) Ratio 0.00 Semi-Dev 0.0% V W X Y Z AA AB AC AD AE Eff Frontier Graph Growth of $1 Graph Template S20 Domestic Indexes + Ready O - - - + 132%