32. Write a spreadsheet program to calculate the expected return and beta for a portfolio of 10 stocks given the expected returns and betas of the stocks in the portfolio and their dollar values.

The calculation involves taking a weighted average of the individual stocks’

expected returns and betas where the weights are based on the dollar values invested in each stock.

Set up your spreadsheet like this:

The computational procedure is as follows.
1. Input the names of the stocks, their dollar values, their betas, and their ke’s.
2. Sum the value column.
3. Calculate the weight column by dividing each row’s value cell by the cell carrying the sum of the values.
4. Calculate the beta and ke factors by multiplying the individual beta and ke cells by the cells in the weight column on the same row.
5. Sum the two factor columns for the results indicated.
Is your program general in that it will handle a portfolio of up to 10 stocks, or will it only work for exactly 10? If it is general, what do you have to be careful about with respect to inputs?
Extra: Assume you have $1 million to invest in stocks. Look up several stocks’
betas in Value Line and estimate ke for each. Look up the current price of each stock in The Wall Street Journal, and form a hypothetical portfolio by allocating your money among the stocks. Find your portfolio’s expected return and beta using your program.

Stock Value Weight Beta Factor ke Factor 1. ABC $ 5,530 .0645 .93 .0600 8.0% .5160 2. EFG 2,745 .0320 1.25 .0400 12.2% .3904 B 10. XYZ 9,046 .1055 1.12 .1182 11.5% 1.2133 $85,715 1.0000 XXX XXX Sum columns for: Portfolio beta Portfolio ko