Stock Examples - Background

Every stock in the market has a certain return. We assume that this return has a normal  
distribution. This means that we can completely describe this distribution by two terms. The
first term is the mean (expected return) and the second is the variance of returns. We can also
compute a covariance of returns between any pair of stocks -- those that move together will
have positive covariances, those that move in opposite directions will have negative  
covariances. If we know the expected return and variance of several stocks, we can put
together a portfolio of these stocks that has a desired variance (risk) with a certain expected
return. The Solver is used to pick the portfolio with the smallest variance for a certain  
expected return. (Or it could be used to find the highest expected return for a certain  
How do we calculate the expected return and variance of a portfolio? Harry Markowitz  
developed a method that computes the portfolio variance as the sum of the individual stock
variances and covariances between pairs of stocks in the portfolio, weighted by the relative
proportion of each stock in the portfolio. From a mathematical point of view this is the right
thing to do. However, since all covariance terms between all stocks must be known, this
requires many calculations. A portfolio with 100 different stocks would require more than
5000 covariance terms, for instance.          
William Sharpe devised another method of determining the expected return and variance of
a portfolio. This method assumes that the return of each stock is composed of two parts:
One part (denoted by beta) is dependent on the overall market's performance, and the other
(denoted by alpha) is independent of the market. So we can write the expected return as
    Return = Beta * Market + Alpha + Residual      
In this equation, alpha and beta are constants that are different for each stock; the residual
term is a random variable with an expected value of zero. When this formula is used, a  
portfolio with 100 stocks would only require 302 terms to fully describe its distribution (100
alphas, 100 betas, 100 residual variances, plus the market return and the market variance).
Compared to the more than 5000 terms of the Markowitz method, this is a big improvement.
However, although the Sharpe method requires less work, it is not as accurate as the  
Markowitz method.              
Bond Examples - Background
Bonds are generally considered less risky than stocks. If a bond is of high credit quality, its
price changes will depend almost entirely on interest rate changes. If interest rates go up, the
price of a bond goes down, while if interest rates go down, the price of a bond goes up.
However, most bonds pay interest in an annual (or semi-annual) coupon, and the interest can
be reinvested. If interest rates go up, the price of the bond does go down, but the coupon could
be reinvested at the higher interest rate. These two effects cancel each other out at the end of
the duration of a bond. The duration of a bond is the average time in which a bond is repaid.
For example, if a bond has a maturity of 3 years with face value $1,000 and an annual coupon
payment of $100 and a yield of 10%, the duration is 2.735 years. Thus, one way of protecting
against fluctuation of interest rates is to have a portfolio whose duration is equal to one's own
investment time horizon. A portfolio of bonds has a duration that is the weighted average of
the duration of the individual bonds.          
In model Bond1 we assume we know the duration of the bonds available and want to maximize
the yield of the portfolio while keeping the duration equal to a given investment time horizon.
This technique is known as bond portfolio 'immunization.' Bond2 is another immunization
model, but this time the duration for each bond is calculated from the face value, maturity,
annual coupon and yield to maturity of each bond.        
In model Bond3 we look at another common way to protect against interest rate fluctuations.
When we acquire a portfolio of bonds, we can exactly calculate the cash flow that arrives from
this portfolio. This consists of the coupons plus the face values of the bonds that mature.
This also works the other way around. If we know we need to have certain amounts of cash
available at certain periods, we can put together a portfolio that does exactly this. The Solver
chooses that portfolio that covers the required amounts in each year and costs the least.
Bond4 is another example of this technique called 'Exact Matching.' This time we allow
excess funds in a certain period to be reinvested to meet requirements in a future period.