Excel and compute finance
hw009
G3x.xlsxStock Stats
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |||||||
| Security Statistics: Monthly Returns 2015-2019 | Do not change any cell formats | enter tickers into | enter tickers into | enter tickers into | enter tickers into | enter tickers into | enter tickers into | enter tickers into | enter tickers into | enter tickers into | enter tickers into | |||||
| row as on the left | row as on the left | row as on the left | row as on the left | row as on the left | row as on the left | row as on the left | row as on the left | row as on the left | row as on the left | |||||||
| Mean | Standard | Portfolio 1 | Portfolio 2 | Portfolio 3 | Portfolio 4 | Portfolio 5 | Portfolio 6 | Portfolio 7 | Portfolio 8 | Portfolio 9 | Portfolio 10 | |||||
| Do not rearrange list | Rate of Return | Variance | Deviation | Std Dev Rank | Stocks | Stocks | Stocks | Stocks | Stocks | Stocks | Stocks | Stocks | Stocks | Stocks | ||
| CMG | 0.8% | |||||||||||||||
| TXRH | ||||||||||||||||
| ALGN | ||||||||||||||||
| FBHS | ||||||||||||||||
| FDS | ||||||||||||||||
| MDT | ||||||||||||||||
| MCD | ||||||||||||||||
| FL | ||||||||||||||||
| TWTR | ||||||||||||||||
| JJSF | ||||||||||||||||
| VWwDivs | ||||||||||||||||
| Portfolio 10 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | ||||||
| Avg Stock Std Dev | ||||||||||||||||
| Port Std Dev | ||||||||||||||||
| Port Std Dev/Avg Stock Std Dev | ||||||||||||||||
| Correlation Coefficients | Do not enter into "--" cells | Do not change any cell formats | Do not enter into "--" cells | |||||||||||||
| Do not rearrange list | CMG | TXRH | ALGN | FBHS | FDS | MDT | MCD | FL | TWTR | JJSF | VWwDivs | Portfolio 10 | Cov / (Std Dev x Std Dev) | |||
| CMG | ||||||||||||||||
| TXRH | -- | |||||||||||||||
| ALGN | -- | -- | ||||||||||||||
| FBHS | -- | -- | -- | |||||||||||||
| FDS | -- | -- | -- | -- | ||||||||||||
| MDT | -- | -- | -- | -- | -- | |||||||||||
| MCD | -- | -- | -- | -- | -- | -- | ||||||||||
| FL | -- | -- | -- | -- | -- | -- | -- | |||||||||
| TWTR | -- | -- | -- | -- | -- | -- | -- | -- | ||||||||
| JJSF | -- | -- | -- | -- | -- | -- | -- | -- | -- | |||||||
| VWwDivs | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | |||||
| Portfolio 10 | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | |||||
| Covariances (Stock versus Portfolio 10) | Covariances/Portfolio 10 Variance (Stock versus Portfolio 10) | Beta vs. Port10 | ||||||||||||||
| Do not rearrange list | enter Cov here | Do not rearrange list | enter relative Cov here | enter Beta here | ||||||||||||
| CMG | CMG | xxx | xxx | |||||||||||||
| TXRH | TXRH | xxx | xxx | |||||||||||||
| ALGN | ALGN | xxx | xxx | |||||||||||||
| FBHS | FBHS | xxx | xxx | |||||||||||||
| FDS | FDS | xxx | xxx | |||||||||||||
| MDT | MDT | xxx | xxx | |||||||||||||
| MCD | MCD | xxx | xxx | |||||||||||||
| FL | FL | xxx | xxx | |||||||||||||
| TWTR | TWTR | xxx | xxx | |||||||||||||
| JJSF | JJSF | xxx | xxx | |||||||||||||
| Average Covariance | Average Covariance/Portfolio Variance | |||||||||||||||
Portfolio Rets and Stats
| Portfolio Returns and Statistics | Do not change any cell formats | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Do not change any cell formats | Equal Wtd | Avg Stock In | Equal Wtd | Avg Stock In | Equal Wtd | Avg Stock In | Value Wtd | Equal Wtd | Avg Stock In | Equal Wtd | Avg Stock In | Equal Wtd | Avg Stock In | Equal Wtd | Avg Stock In | Equal Wtd | Avg Stock In | Equal Wtd | Avg Stock In | Equal Wtd | Avg Stock In | |||||||||||||||||||||||||||||||||||||||||||||||||
| Portfolio 1 | Portfolio 1 | Portfolio 2 | Portfolio 2 | Portfolio 3 | Portfolio 3 | Portfolio 4 | Portfolio 4 | Portfolio 4 | Portfolio 5 | Portfolio 5 | Portfolio 6 | Portfolio 6 | Portfolio 7 | Portfolio 7 | Portfolio 8 | Portfolio 8 | Portfolio 9 | Portfolio 9 | Portfolio 10 | Portfolio 10 | ||||||||||||||||||||||||||||||||||||||||||||||||||
| Mean of Monthly Retruns | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Variance of Monthly Returns | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Std Dev of Monthly Returns | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Equal Wtd | Equal Wtd | Equal Wtd | Value Wtd | Equal Wtd | Equal Wtd | Equal Wtd | Equal Wtd | Equal Wtd | Equal Wtd | Equal Wtd | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Month | Return % | Return % | Return % | Return % | Return % | Return % | Return % | Return % | Return % | Return % | Return % | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Statements
| Statement Regarding Relationship Between Correlation Coefficients and Covariances (Finish the sentence below or completely replace it.) | ||
| The correlation coefficient between stock returns and portfolio returns is … | ||
| Statement Regarding Relationship Between Portfolio Variance and Its Stocks' Covariances (Finish the sentence below or completely replace it.) | ||
| The variance of portfolio returns is … | ||
| Statement Regarding Relationship Between Stock Covariance/Portfolio Variance and the Stock's Beta (Finish the sentence below or completely replace it.) | ||
| The ratio of a stock's covariance of returns with the portfolio's returns to the portfolio's variance of returns is … | ||
| Statement Regarding Relationship Between Portfolio Risk and Average Stock Risk As Stocks Are Added (Finish the sentence below or completely replace it.) | ||
| As we move from the one stock Portolio 1 to the ten stock Portfolio 10, portfolio risk relative to average security risk … |
