{"text_format":"3","workbook":"xls","n_sheets":11,"sheets":["Summary","Theory","Markowitz","Full Markowitz","Efficient Frontier","Sharpe","Bond1","Bond2","Bond3","Bond4","Module1"],"not_rendered":{"charts":1,"macros":1,"defined_names":452},"unopened":[{"sheet":"Module1","index":10,"reason":"not_a_worksheet"}],"read":"whole","date_system":"1900","formulas":"cached_only"}

{"sheet":"Summary","index":0,"n_rows":34,"n_cols":1,"rows_kept":34,"flags":["formulas_cached_only"]}
["Investment Examples"]
[]
["On each example worksheet, read the comments at the bottom of the sheet, then"]
["click Tools Solver... to examine the decision variables, constraints, and objective."]
["To find the optimal solution, click the Solve button."]
[]
["The models in this workbook deal with decisions to invest in stocks and bonds. To help you"]
["understand the models in this workbook, we briefly explain the ideas behind the stock and bond"]
["models in the Theory worksheet."]
[]
["The Markowitz model uses the Solver to determine the percent of our available funds to invest "]
["in each of five stocks, so as to incur the least risk for a target rate of return.  The resulting "]
["portfolio of stocks is called an efficient portfolio.  In this model, we assume that the variances "]
["and covariances of stocks are known. In the Full Markowitz model, we calculate the variances "]
["and covariances of stocks from a history of stock prices."]
[]
["The Efficient Frontier worksheet contains a Markowitz model and an embedded chart.  A "]
["short VBA program, also included in the workbook, uses the Solver to optimize the "]
["Markowitz model for several different target rates of return.  The VBA code is run and the "]
["resulting portfolio returns and variances are plotted on the chart when you press the button on "]
["the worksheet."]
[]
["The Sharpe model computes an efficient portfolio using alphas and betas which relate the "]
["return on each stock to the return on the market and the risk-free rate.  The alphas, betas and "]
["residual variances are computed via linear regression from a history of stock prices."]
[]
["The four Bond worksheets illustrate how a portfolio of bonds may be 'immunized' against "]
["changes in interest rates.  The model Bond1 assumes that bond durations are known.  In model "]
["Bond2, the durations are calculated using Excel’s DURATION function."]
[]
["Models Bond3 and Bond4 illustrate 'exact matching' in fixed-income portfolios.  Here we "]
["assume that the investor has a series of liabilities (for example, pension payments) to be met in "]
["future periods, and the principal and interest on the bonds must cover the liabilities in each "]
["period."]

{"sheet":"Theory","index":1,"n_rows":63,"n_cols":3,"rows_kept":63,"flags":["formulas_cached_only"]}
["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 "]
["variance.)"]
[]
["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 "]
[null,null,"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."]

{"sheet":"Markowitz","index":2,"n_rows":43,"n_cols":9,"rows_kept":43,"flags":["formulas_cached_only"]}
["Portfolio Optimization - Markowitz Method"]
["This Solver model uses the QUADPRODUCT function at cell I14 to compute the portfolio variance."]
["It can be solved for the minimum variance using either the GRG nonlinear solver or the Quadratic Solver."]
[]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5",null,"Total"]
["Portfolio %",{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},null,{"v":1,"d":"100.00%"}]
["Expected Return",{"v":0.07,"d":"7.00%"},{"v":0.08,"d":"8.00%"},{"v":0.095,"d":"9.50%"},{"v":0.065,"d":"6.50%"},{"v":0.14,"d":"14.00%"}]
["Linear QP Terms",0,0,0,0,0]
[]
["Variance/Covariance Matrix"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5"]
["Stock 1",{"v":0.025,"d":"2.50%"},{"v":0.001,"d":"0.10%"},{"v":0.01,"d":"1.00%"},{"v":-0.005,"d":"-0.50%"},{"v":0.016,"d":"1.60%"}]
["Stock 2",{"v":0.001,"d":"0.10%"},{"v":0.011,"d":"1.10%"},{"v":-0.001,"d":"-0.10%"},{"v":0.012,"d":"1.20%"},{"v":-0.0085,"d":"-0.85%"}]
["Stock 3",{"v":0.01,"d":"1.00%"},{"v":-0.001,"d":"-0.10%"},{"v":0.012,"d":"1.20%"},{"v":0.0065,"d":"0.65%"},{"v":0.0075,"d":"0.75%"},null,"Variance",{"$error":"#NAME?"}]
["Stock 4",{"v":-0.005,"d":"-0.50%"},{"v":0.012,"d":"1.20%"},{"v":0.0065,"d":"0.65%"},{"v":0.004,"d":"0.40%"},{"v":0.01,"d":"1.00%"},null,"Std. Dev.",{"$error":"#NAME?"}]
["Stock 5",{"v":0.016,"d":"1.60%"},{"v":-0.0085,"d":"-0.85%"},{"v":0.0075,"d":"0.75%"},{"v":0.01,"d":"1.00%"},{"v":0.02,"d":"2.00%"},null,"Return",{"v":0.09,"d":"9.00%"}]
[]
[]
["Problem"]
["An investor wants to put together a portfolio consisting of up to 5 stocks. Using the Markowitz method, what is the"]
["best combination of stocks to minimize risk for a given return? The variances are known for each stock, as are the"]
["covariances between all stocks. The returns for all stocks are also known."]
[" "]
["Solution"]
["1) The variables are the percentage allocations of our funds to invest in each stock. In this worksheet, the variables"]
["are given the name Allocations.  The sum of the allocations (which must be 100%) is computed in the cell named"]
["Total_Portfolio."]
["2) The constraints are very simple. First there are the logical constraints:"]
[null,"Allocations >= 0  via the Assume Non-Negative option"]
[null,"Total_Portfolio = 1"]
["Then there is a constraint that the portfolio return should be at least a certain target value (9%  in this example).  This"]
["return is calculated in the cell named Portfolio_return:"]
[null,"Portfolio_Return >= 0.09"]
["3) The objective is to minimize portfolio variance, which is calculated according to the Markowitz method in the cell "]
["named Portfolio_Variance."]
[]
["Remarks"]
["In this worksheet, we use the QUADPRODUCT function to compute the portfolio variance.  If you see #NAME? on"]
["this worksheet, you need to open the add-in (DOTPRD32.XLL or DOTPROD.XLL) that provides QUADPRODUCT."]
["In the Full Markowitz worksheet, we calculate the portfolio variance 'manually' without using QUADPRODUCT."]
["The Markowitz method can only be used if all the variances of individual stocks, and the covariances between each "]
["pair of stocks are known. In this model we assumed that all the terms are given. In the Full Markowitz worksheet we "]
["actually calculate the variances and covariances from a history of stock prices."]

{"sheet":"Full Markowitz","index":3,"n_rows":57,"n_cols":9,"rows_kept":57,"flags":["formulas_cached_only"]}
["Portfolio Optimization - Markowitz Method"]
["An investor wants to put together a portfolio, drawing from a set of 5 candidate stocks."]
["What is the best combination of stocks to achieve a given rate of return with the least risk?"]
[]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5",null,"Total"]
["Portfolio %",{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},null,{"v":1,"d":"100.00%"}]
["Expected Return",{"v":0.087,"d":"8.70%"},{"v":0.071,"d":"7.10%"},{"v":0.094,"d":"9.40%"},{"v":0.10699999999999998,"d":"10.70%"},{"v":0.06899999999999999,"d":"6.90%"}]
[]
["Variance/Covariance Matrix"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5"]
["Stock 1",{"v":0.0003210000000000035,"d":"0.03%"},{"v":0.0012430000000000002,"d":"0.12%"},{"v":0.00027200000000000005,"d":"0.03%"},{"v":0.0005709999999999998,"d":"0.06%"},{"v":0.000117,"d":"0.01%"}]
["Stock 2",{"v":0.0012430000000000002,"d":"0.12%"},{"v":0.012329000000000001,"d":"1.23%"},{"v":0.0019960000000000004,"d":"0.20%"},{"v":0.0016329999999999997,"d":"0.16%"},{"v":-0.0005389999999999998,"d":"-0.05%"}]
["Stock 3",{"v":0.00027200000000000005,"d":"0.03%"},{"v":0.0019960000000000004,"d":"0.20%"},{"v":0.0004240000000000022,"d":"0.04%"},{"v":0.0003820000000000001,"d":"0.04%"},{"v":-6.599999999999994e-05,"d":"-0.01%"}]
["Stock 4",{"v":0.0005709999999999998,"d":"0.06%"},{"v":0.0016329999999999997,"d":"0.16%"},{"v":0.0003820000000000001,"d":"0.04%"},{"v":0.005141000000000007,"d":"0.51%"},{"v":0.00023699999999999996,"d":"0.02%"}]
["Stock 5",{"v":0.000117,"d":"0.01%"},{"v":-0.0005389999999999998,"d":"-0.05%"},{"v":-6.599999999999994e-05,"d":"-0.01%"},{"v":0.00023699999999999996,"d":"0.02%"},{"v":0.0005090000000000022,"d":"0.05%"}]
[]
["Variance Terms",{"v":0.00010096000000000014,"d":"0.01%"},{"v":0.0006664800000000001,"d":"0.07%"},{"v":0.00012032000000000011,"d":"0.01%"},{"v":0.0003185600000000003,"d":"0.03%"},{"v":1.0320000000000096e-05,"d":"0.00%"},null,"Variance",{"v":0.0012166400000000008,"d":"0.12%"}]
[{"$nulls":7},"Std. Dev.",{"v":0.03488036697054664,"d":"3.49%"}]
["Return Terms",{"v":0.0174,"d":"1.74%"},{"v":0.014199999999999999,"d":"1.42%"},{"v":0.0188,"d":"1.88%"},{"v":0.0214,"d":"2.14%"},{"v":0.0138,"d":"1.38%"},null,"Return",{"v":0.08560000000000001,"d":"8.56%"}]
[]
["Historical data (Returns) on stocks"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5"]
["Period 1",{"v":0.1,"d":"10.00%"},{"v":0.15,"d":"15.00%"},{"v":0.12,"d":"12.00%"},{"v":0.18,"d":"18.00%"},{"v":0.05,"d":"5.00%"}]
["Period 2",{"v":0.12,"d":"12.00%"},{"v":0.17,"d":"17.00%"},{"v":0.13,"d":"13.00%"},{"v":0.16,"d":"16.00%"},{"v":0.08,"d":"8.00%"}]
["Period 3",{"v":0.08,"d":"8.00%"},{"v":0.04,"d":"4.00%"},{"v":0.09,"d":"9.00%"},{"v":0.03,"d":"3.00%"},{"v":0.1,"d":"10.00%"}]
["Period 4",{"v":0.07,"d":"7.00%"},{"v":-0.08,"d":"-8.00%"},{"v":0.07,"d":"7.00%"},{"v":0.04,"d":"4.00%"},{"v":0.09,"d":"9.00%"}]
["Period 5",{"v":0.09,"d":"9.00%"},{"v":0.15,"d":"15.00%"},{"v":0.09,"d":"9.00%"},{"v":0.08,"d":"8.00%"},{"v":0.05,"d":"5.00%"}]
["Period 6",{"v":0.07,"d":"7.00%"},{"v":0.22,"d":"22.00%"},{"v":0.11,"d":"11.00%"},{"v":0.1,"d":"10.00%"},{"v":0.04,"d":"4.00%"}]
["Period 7",{"v":0.08,"d":"8.00%"},{"v":0.03,"d":"3.00%"},{"v":0.09,"d":"9.00%"},{"v":-0.03,"d":"-3.00%"},{"v":0.04,"d":"4.00%"}]
["Period 8",{"v":0.06,"d":"6.00%"},{"v":-0.14,"d":"-14.00%"},{"v":0.06,"d":"6.00%"},{"v":0.15,"d":"15.00%"},{"v":0.06,"d":"6.00%"}]
["Period 9",{"v":0.09,"d":"9.00%"},{"v":0.02,"d":"2.00%"},{"v":0.08,"d":"8.00%"},{"v":0.2,"d":"20.00%"},{"v":0.08,"d":"8.00%"}]
["Period 10",{"v":0.11,"d":"11.00%"},{"v":0.15,"d":"15.00%"},{"v":0.1,"d":"10.00%"},{"v":0.16,"d":"16.00%"},{"v":0.1,"d":"10.00%"}]
[]
[]
["Problem"]
["An investor wants to put together a portfolio consisting of up to 5 stocks. Using the Markowitz method, what is the"]
["best combination of stocks to minimize risk for a given return? In this model, we calculate stock returns, the variance"]
["of each stock, and the covariances between stocks, using the Excel functions AVERAGE, VARP and COVAR."]
[" "]
["Solution"]
["1) The variables are the percentage allocations of our funds to invest in each stock. In this worksheet, the variables"]
["are cells B6 to F6 (they are not given a name).  The sum of the percentage allocations (which must be 100%) is "]
["computed in cell H6."]
["2) The constraints are very simple. First there are the logical constraints:"]
[null,"B6:F6 >= 0  via the Assume Non-Negative option"]
[null,"H6 = 1"]
["Then there is a constraint that the portfolio return should be at least a certain target value (9%  in this example).  This"]
["return is calculated in cell I19, as the sum of the weighted stock returns:"]
[null,"I19 >= 0.09"]
["3) The objective is to minimize portfolio variance, which is calculated from the weighted individual stock variances"]
["and covariances according to the Markowitz method in cell I17."]
[]
["Remarks"]
["The stock variances and covariances are calculated in cells B11:F15 from the historical price data in cells B23:F32."]
["Using historical price data to compute estimates of stock returns, variances and covariances is only a first step in"]
["investment planning.  Stock returns, as well as variances and covariances, vary over time.  Investors often rely on"]
["security analysts to provide better estimates of these quantities for the future."]

{"sheet":"Efficient Frontier","index":4,"n_rows":31,"n_cols":9,"rows_kept":31,"flags":["formulas_cached_only"]}
["Portfolio Optimization - Markowitz Method"]
["An investor wants to put together a portfolio, drawing from a set of 5 candidate stocks."]
["'What is the best combination of stocks to achieve a given rate of return with the least risk?"]
[]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5",null,"Total"]
["Portfolio %",{"v":0,"d":"0.00%"},{"v":0.03571428571428863,"d":"3.57%"},{"v":0,"d":"0.00%"},{"v":0,"d":"0.00%"},{"v":0.9642857142857127,"d":"96.43%"},null,{"v":1.0000000000000013,"d":"100.00%"}]
["Expected Return",{"v":0.07,"d":"7.00%"},{"v":0.08,"d":"8.00%"},{"v":0.095,"d":"9.50%"},{"v":0.065,"d":"6.50%"},{"v":0.15,"d":"15.00%"}]
[]
["Variance/Covariance Matrix"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5"]
["Stock 1",{"v":0.025,"d":"2.50%"},{"v":0.001,"d":"0.10%"},{"v":0.01,"d":"1.00%"},{"v":-0.005,"d":"-0.50%"},{"v":0.016,"d":"1.60%"}]
["Stock 2",{"v":0.001,"d":"0.10%"},{"v":0.011,"d":"1.10%"},{"v":-0.001,"d":"-0.10%"},{"v":0.012,"d":"1.20%"},{"v":-0.0085,"d":"-0.85%"}]
["Stock 3",{"v":0.01,"d":"1.00%"},{"v":-0.001,"d":"-0.10%"},{"v":0.012,"d":"1.20%"},{"v":0.0065,"d":"0.65%"},{"v":0.0075,"d":"0.75%"}]
["Stock 4",{"v":-0.005,"d":"-0.50%"},{"v":0.012,"d":"1.20%"},{"v":0.0065,"d":"0.65%"},{"v":0.004,"d":"0.40%"},{"v":0.01,"d":"1.00%"}]
["Stock 5",{"v":0.016,"d":"1.60%"},{"v":-0.0085,"d":"-0.85%"},{"v":0.0075,"d":"0.75%"},{"v":0.01,"d":"1.00%"},{"v":0.02,"d":"2.00%"}]
[{"$nulls":7},"Variance",{"v":0.01802551020408153,"d":"0.0180255"}]
["Variance Terms",{"v":0,"d":"0.00%"},{"v":-0.0002786989795918579,"d":"-0.03%"},{"v":0,"d":"0.00%"},{"v":0,"d":"0.00%"},{"v":0.018304209183673387,"d":"1.83%"},null,"Std. Dev.",{"v":0.13425911590682224,"d":"13.43%"}]
[{"$nulls":7},"Des. Ret",{"v":0.1475,"d":"14.75%"}]
["Return Terms",{"v":0,"d":"0.00%"},{"v":0.00285714285714309,"d":"0.29%"},{"v":0,"d":"0.00%"},{"v":0,"d":"0.00%"},{"v":0.1446428571428569,"d":"14.46%"},null,"Return",{"v":0.1475,"d":"14.75%"}]
[]
[]
["This worksheet includes a Markowitz portfolio model that can be optimized by the Solver to find the minimum variance"]
["portfolio for a given target rate of return.  We then use VBA (Visual Basic Application Edition) code to set the target"]
["rate of return to different values (from 10% to almost 15%) and run the Solver to optimize the model for each target return."]
["The VBA code stores the target returns and resulting portfolio variances in cells J21 through K40, which are linked to"]
["the X-Y plot shown to the right.  When you press the button labeled 'Create Frontier', the VBA code is run and the"]
["resulting efficient frontier is drawn on the embedded chart."]
["To see the VBA code controlling the Solver, select Tools Macro... Visual Basic Editor, or press Alt+F11.  In the VBA"]
["window, in the left-hand Project list window double-click on Modules, then double-click on Module1.  To successully"]
["run the code and create the chart, you may need to choose Tools References... in the VBA Editor and click to set a"]
["check mark next to 'Solver'."]

{"sheet":"Sharpe","index":5,"n_rows":52,"n_cols":8,"rows_kept":52,"flags":["formulas_cached_only"]}
["Portfolio Optimization - Single-Index Method"]
["An investor wants to put together a portfolio, drawing from a set of 5 candidate stocks."]
["What is the best combination of stocks to get the maximum return with a given variance (risk) ?"]
[]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5",null,"Total"]
["Portfolio %",{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},null,{"v":1,"d":"100.00%"}]
["Beta-Coefficient",0.5773809523809562,1.6726190476190432,0.6488095238095184,0.9345238095238123,0.7678571428571426]
["Alpha-Coefficient",0.04773809523809496,-0.04273809523809495,0.04988095238095274,0.04345238095238074,0.016785714285714293]
["Residual Variance",0.000261235119047619,0.014236235119047622,0.00035319940476190466,0.006059449404761906,0.00038861607142857155]
["Weighted Variance",{"v":1.0449404761904762e-05,"d":"0.00001"},{"v":0.000569449404761905,"d":"0.00057"},{"v":1.4127976190476189e-05,"d":"0.00001"},{"v":0.0002423779761904763,"d":"0.00024"},{"v":1.5544642857142863e-05,"d":"0.00002"}]
[]
[{"$nulls":4},"Portfolio Variance",null,null,{"v":0.001168102314814815,"d":"0.12%"}]
[]
[{"$nulls":4},"Portfolio Return",null,null,{"v":0.0856,"d":"8.56%"}]
[]
["Historical data (Returns) on stocks"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5",null,"Market"]
["Period 1",{"v":0.1,"d":"10.00%"},{"v":0.15,"d":"15.00%"},{"v":0.12,"d":"12.00%"},{"v":0.18,"d":"18.00%"},{"v":0.05,"d":"5.00%"},null,{"v":0.08,"d":"8.00%"}]
["Period 2",{"v":0.12,"d":"12.00%"},{"v":0.17,"d":"17.00%"},{"v":0.13,"d":"13.00%"},{"v":0.16,"d":"16.00%"},{"v":0.08,"d":"8.00%"},null,{"v":0.1,"d":"10.00%"}]
["Period 3",{"v":0.08,"d":"8.00%"},{"v":0.04,"d":"4.00%"},{"v":0.09,"d":"9.00%"},{"v":0.03,"d":"3.00%"},{"v":0.1,"d":"10.00%"},null,{"v":0.09,"d":"9.00%"}]
["Period 4",{"v":0.07,"d":"7.00%"},{"v":-0.08,"d":"-8.00%"},{"v":0.07,"d":"7.00%"},{"v":0.04,"d":"4.00%"},{"v":0.09,"d":"9.00%"},null,{"v":0.07,"d":"7.00%"}]
["Period 5",{"v":0.09,"d":"9.00%"},{"v":0.15,"d":"15.00%"},{"v":0.09,"d":"9.00%"},{"v":0.08,"d":"8.00%"},{"v":0.05,"d":"5.00%"},null,{"v":0.04,"d":"4.00%"}]
["Period 6",{"v":0.07,"d":"7.00%"},{"v":0.22,"d":"22.00%"},{"v":0.11,"d":"11.00%"},{"v":0.1,"d":"10.00%"},{"v":0.04,"d":"4.00%"},null,{"v":0.06,"d":"6.00%"}]
["Period 7",{"v":0.08,"d":"8.00%"},{"v":0.03,"d":"3.00%"},{"v":0.09,"d":"9.00%"},{"v":-0.03,"d":"-3.00%"},{"v":0.04,"d":"4.00%"},null,{"v":0.05,"d":"5.00%"}]
["Period 8",{"v":0.06,"d":"6.00%"},{"v":-0.14,"d":"-14.00%"},{"v":0.06,"d":"6.00%"},{"v":0.15,"d":"15.00%"},{"v":0.06,"d":"6.00%"},null,{"v":0.05,"d":"5.00%"}]
["Period 9",{"v":0.09,"d":"9.00%"},{"v":0.02,"d":"2.00%"},{"v":0.08,"d":"8.00%"},{"v":0.2,"d":"20.00%"},{"v":0.08,"d":"8.00%"},null,{"v":0.06,"d":"6.00%"}]
["Period 10",{"v":0.11,"d":"11.00%"},{"v":0.15,"d":"15.00%"},{"v":0.1,"d":"10.00%"},{"v":0.16,"d":"16.00%"},{"v":0.1,"d":"10.00%"},null,{"v":0.08,"d":"8.00%"}]
[]
[]
["Problem"]
["An investor wants to put together a portfolio consisting of up to 5 stocks. Using the Sharpe Single-Index method,"]
["what is the best combination of stocks to maximize return for a given level of risk (variance)?"]
[" "]
["Solution"]
["1) The variables are the percentage or fractional allocations of our funds to invest in each stock. In this worksheet,"]
["the variables are given the name Portfolio_fractions.  The sum of the allocations (which must be 100%) is computed"]
["in the cell named Portfolio_Total."]
["2) The constraints are very simple. First there are the logical constraints:"]
[null,"Portfolio_Fractions >= 0  via the Assume Non-Negative option"]
[null,"Portfolio_Total = 1"]
["Then there is a constraint that the portfolio variance should be no more than 0.0003 (in this example).  The portfolio"]
["variance is calculated in the cell named Variance:"]
[null,"Variance <= 0.0003"]
["3) The objective is to maximize portfolio return which is calculated according to the Sharpe method in the cell "]
["named Return."]
[]
["Remarks"]
["This model uses historical data on the stocks and market to calculate the returns and variance of the stocks and "]
["the portfolio. It also uses Excel's regression tools to estimate the alphas and betas, as well as the residual error, "]
["necessary to use the Sharpe Single Index method."]
["A different approach to this problem would be to minimize risk for a certain return.  It is easy to adjust the Solver to"]
["do this. Simply change the set cell to be the variance and adjust the constraint to hold the return at a given level."]

{"sheet":"Bond1","index":6,"n_rows":35,"n_cols":9,"rows_kept":35,"flags":["formulas_cached_only"]}
["Bond Immunization Model"]
["An investor wants to put together a portfolio, drawing from a set of 5 candidate bonds."]
["What is the best combination of bonds to get the optimum yield with a given investment time horizon ?"]
[]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5","Bond 6",null,"Total"]
["Portfolio %",{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.1,"d":"10.00%"},{"v":0.1,"d":"10.00%"},null,{"v":1,"d":"100.00%"}]
["Duration (Years)",2.8,3.1,3.7,3.5,3.8,4]
["Yield to Maturity",{"v":0.08,"d":"8.00%"},{"v":0.06,"d":"6.00%"},{"v":0.1,"d":"10.00%"},{"v":0.09,"d":"9.00%"},{"v":0.08,"d":"8.00%"},{"v":0.05,"d":"5.00%"}]
[]
[{"$nulls":5},"Investment Time Horizon",3.8]
[{"$nulls":5},"Portfolio Duration",3.4000000000000004]
[{"$nulls":5},"Portfolio Yield",{"v":0.07900000000000001,"d":"7.90%"}]
[]
[]
["Problem"]
["An investor wants to put together a portfolio consisting of up to 6 different bonds. To minimize risk of "]
["loss of principal value due to interest rate fluctuations and to assure enough cash-flow at a certain point"]
["in the future, he wants to make sure that the average duration of the bonds equals his investment time "]
["horizon.  How should the investor choose his portfolio to optimize the combined yield of the bonds, "]
["while making sure that the duration of the portfolio equals the investment time horizon?  The duration "]
["and the yield to maturity are known for each bond."]
[]
["Solution"]
["1) The variables are the percentages or fractions of our available funds to invest in each bond. In "]
["worksheet BOND1 these are given the name Portfolio_fractions."]
["2) The constraints are very simple. First we have the logical constraints:"]
[null,"Portfolio_fractions >= 0  via the Assume Non-Negative option"]
[null,"Portfolio_total = 1"]
["Then there is the constraint to make sure that the portfolio duration equals the investment time horizon:"]
[null,"Portfolio_duration = Time_horizon"]
["3) The objective is to maximize the portfolio yield. This is given the name Portfolio_yield"]
[]
["Remarks"]
["In this model we assume that the duration of the bond is known. In worksheet BOND2 we will see how"]
["to use EXCEL's build-in functions to calculate the duration of each bond."]

{"sheet":"Bond2","index":7,"n_rows":44,"n_cols":9,"rows_kept":44,"flags":["formulas_cached_only"]}
["Bond Immunization Model"]
["An investor wants to put together a portfolio, consisting out of a maximum of 6 bonds."]
["What is the best combination of bonds to get the optimum yield with a given investment time horizon ?"]
["The period from settlement to maturity is 4 years for each bond."]
[]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5","Bond 6",null,"Total"]
["Portfolio %",{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},{"v":0.2,"d":"20.00%"},null,{"v":1.2,"d":"120.00%"}]
["Yield to Maturity",{"v":0.08,"d":"8.00%"},{"v":0.06,"d":"6.00%"},{"v":0.09,"d":"9.00%"},{"v":0.1,"d":"10.00%"},{"v":0.07,"d":"7.00%"},{"v":0.09,"d":"9.00%"}]
["Coupon Payment",{"v":150,"d":"$150"},{"v":100,"d":"$100"},{"v":120,"d":"$120"},{"v":175,"d":"$175"},{"v":0,"d":"$0"},{"v":125,"d":"$125"}]
["Face Value",{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"}]
[]
["Duration (Years)",3.356298355622427,3.5213612662766085,3.4304181261960576,3.274476802737043,4,3.415317619027054]
[]
[{"$nulls":5},"Investment Time Horizon",3.5]
[{"$nulls":5},"Portfolio Duration",4.199574433971838]
[{"$nulls":5},"Portfolio Yield",{"v":0.098,"d":"9.80%"}]
[]
[]
["Problem"]
["An investor wants to put together a portfolio consisting of up to 6 different bonds. To minimize risk of "]
["loss of principal value due to interest rate fluctuations and to assure enough cash-flow at a certain point"]
["in the future, he wants to make sure that the average duration of the bonds equals his investment time "]
["horizon.  How should the investor choose his portfolio to optimize the combined yield of the bonds, "]
["while making sure that the duration of the portfolio equals the investment time horizon?  All bonds"]
["mature in 4 years and have one annual interest payment. The annual payments, the yield and the"]
["face values of the bonds are all known "]
[]
["Solution"]
["1) The variables are the percentages or fractions of our available funds to invest in each bond. In "]
["worksheet BOND1 these are given the name Portfolio_fractions."]
["2) The constraints are very simple. First we have the logical constraints:"]
[null,"Portfolio_fractions >= 0  via the Assume Non-Negative option"]
[null,"Portfolio_total = 1"]
["Then there is the constraint to make sure that the portfolio duration equals the investment time horizon:"]
[null,"Portfolio_duration = Time_horizon"]
["3) The objective is to maximize the portfolio yield. This is given the name Portfolio_yield"]
[]
["Remarks"]
["The solution is the same as in worksheet BOND1. The difference is that instead of the duration of "]
["each bond being given, it is now calculated from the yield, annual coupon and face value."]
["The Excel function DURATION is only available if you have installed the Analysis ToolPak add-in. If "]
["you see #NAME? on the worksheet, choose Tools Add-Ins..., click to put a check mark next to "]
["Analysis ToolPak, then click OK. If Analysis ToolPak is missing from the list of available add-ins, you "]
["may need to install it from the Excel or Office CD."]

{"sheet":"Bond3","index":8,"n_rows":46,"n_cols":10,"rows_kept":46,"flags":["formulas_cached_only"]}
["Bond Model - Exact Matching"]
["What is the minimum cost portfolio, consisting of up to 6 bonds, that provides enough "]
["cash flow to cover liabilities in each period?"]
["Interest Rate",{"v":0.07,"d":"7%"}]
[]
["Characteristics of bonds"]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5"]
["Face Value",{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"}]
["Coupon Payment",{"v":100,"d":"$100"},{"v":125,"d":"$125"},{"v":150,"d":"$150"},{"v":200,"d":"$200"},{"v":75,"d":"$75"}]
["Years to Maturity",3,5,6,4,6]
["Price",{"v":1078.7294813324918,"d":"$1,078.73"},{"v":1225.5108589771176,"d":"$1,225.51"},{"v":1381.3231727811278,"d":"$1,381.32"},{"v":1440.3374633403103,"d":"$1,440.34"},{"v":1023.8326982988202,"d":"$1,023.83"}]
[]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5",null,"Cost"]
["Number Purchased",10,10,10,10,10,null,{"v":61497.33674729867,"d":"$61,497"}]
[]
["Cash Flow","Bond 1","Bond 2","Bond 3","Bond 4","Bond 5",null,"Total",null,"Liability"]
["Year 1",{"v":1000,"d":"$1,000"},{"v":1250,"d":"$1,250"},{"v":1500,"d":"$1,500"},{"v":2000,"d":"$2,000"},{"v":750,"d":"$750"},null,{"v":6500,"d":"$6,500"},null,{"v":32000,"d":"$32,000"}]
["Year 2",{"v":1000,"d":"$1,000"},{"v":1250,"d":"$1,250"},{"v":1500,"d":"$1,500"},{"v":2000,"d":"$2,000"},{"v":750,"d":"$750"},null,{"v":6500,"d":"$6,500"},null,{"v":25000,"d":"$25,000"}]
["Year 3",{"v":1000,"d":"$1,000"},{"v":1250,"d":"$1,250"},{"v":1500,"d":"$1,500"},{"v":2000,"d":"$2,000"},{"v":750,"d":"$750"},null,{"v":6500,"d":"$6,500"},null,{"v":22000,"d":"$22,000"}]
["Year 4",null,{"v":1250,"d":"$1,250"},{"v":1500,"d":"$1,500"},{"v":2000,"d":"$2,000"},{"v":750,"d":"$750"},null,{"v":5500,"d":"$5,500"},null,{"v":28000,"d":"$28,000"}]
["Year 5",null,{"v":1250,"d":"$1,250"},{"v":1500,"d":"$1,500"},null,{"v":750,"d":"$750"},null,{"v":3500,"d":"$3,500"},null,{"v":25000,"d":"$25,000"}]
["Year 6",null,null,{"v":1500,"d":"$1,500"},null,{"v":750,"d":"$750"},null,{"v":2250,"d":"$2,250"},null,{"v":20000,"d":"$20,000"}]
[]
[]
["Problem"]
["In models BOND1 and BOND2 we saw a way for an investor to protect against interest rate fluctuations. Here, we'll look "]
["at another method.  An investor wants to put together a portfolio consisting of up to 6 different bonds. He has certain cash-"]
["flow requirements in the future that the coupons of the bonds should cover.  (For example, a pension fund must meet "]
["requirements for future pension payments.)  These payments are independent of interest rate changes.  How should the "]
["investor choose his portfolio to minimize the cost of the bonds, while making sure that the payments cover his future cash-"]
["flow requirements?"]
[]
["Solution"]
["1) The variables are the number of each bond to include in the portfolio. In worksheet BOND3 these are given the name "]
["Purchased_bonds."]
["2) The constraints are very simple. First we have the logical constraints:"]
[null,"Purchased_bonds >= 0  via the Assume Non-Negative option"]
[null,"Purchased_bonds = integer  (We can not buy fractions of a bond)"]
["Then there is the constraint to make sure that the cash-flow requirements are met:"]
[null,"Cash_flow >= Liabilities"]
["3) The objective is to minimize the portfolio cost. This is given the name Total_cost."]
[]
["Remarks"]
["In this model we assume that money coming in from maturing bonds can not be used to cover the cash-flow requirements."]
["Also, we do not account for excess money in one period that may be transferred to the next period. In model BOND4 we "]
["will account for this."]

{"sheet":"Bond4","index":9,"n_rows":47,"n_cols":10,"rows_kept":47,"flags":["formulas_cached_only"]}
["Bond Model - Exact Matching with Cash Carryforward"]
["What is the minimum cost portfolio, consisting of up to 6 bonds, that provides enough "]
["cash flow to cover liabilities in each period?"]
["Interest Rate",{"v":0.07,"d":"7%"}]
[]
["Characteristics of bonds"]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5"]
["Face Value",{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"},{"v":1000,"d":"$1,000"}]
["Coupon Payment",{"v":100,"d":"$100"},{"v":125,"d":"$125"},{"v":150,"d":"$150"},{"v":175,"d":"$175"},{"v":75,"d":"$75"}]
["Years to Maturity",3,5,6,4,6]
["Price",{"v":1078.7294813324918,"d":"$1,078.73"},{"v":1225.5108589771176,"d":"$1,225.51"},{"v":1381.3231727811278,"d":"$1,381.32"},{"v":1355.657181928712,"d":"$1,355.66"},{"v":1023.8326982988202,"d":"$1,023.83"}]
[]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5",null,"Cost"]
["Number Purchased",50,50,50,50,50,null,{"v":303252.66966591345,"d":"$303,253"}]
[]
["Cash Flow","Bond 1","Bond 2","Bond 3","Bond 4","Bond 5",null,"Total w/Int",null,"Liability"]
["Year 1",{"v":5000,"d":"$5,000"},{"v":6250,"d":"$6,250"},{"v":7500,"d":"$7,500"},{"v":8750,"d":"$8,750"},{"v":3750,"d":"$3,750"},null,{"v":31250,"d":"$31,250"},null,{"v":32000,"d":"$32,000"}]
["Year 2",{"v":5000,"d":"$5,000"},{"v":6250,"d":"$6,250"},{"v":7500,"d":"$7,500"},{"v":8750,"d":"$8,750"},{"v":3750,"d":"$3,750"},null,{"v":30447.5,"d":"$30,448"},null,{"v":25000,"d":"$25,000"}]
["Year 3",{"v":5000,"d":"$5,000"},{"v":6250,"d":"$6,250"},{"v":7500,"d":"$7,500"},{"v":8750,"d":"$8,750"},{"v":3750,"d":"$3,750"},null,{"v":37078.825,"d":"$37,079"},null,{"v":22000,"d":"$22,000"}]
["Year 4",null,{"v":6250,"d":"$6,250"},{"v":7500,"d":"$7,500"},{"v":8750,"d":"$8,750"},{"v":3750,"d":"$3,750"},null,{"v":42384.342749999996,"d":"$42,384"},null,{"v":28000,"d":"$28,000"}]
["Year 5",null,{"v":6250,"d":"$6,250"},{"v":7500,"d":"$7,500"},null,{"v":3750,"d":"$3,750"},null,{"v":32891.24674249999,"d":"$32,891"},null,{"v":25000,"d":"$25,000"}]
["Year 6",null,null,{"v":7500,"d":"$7,500"},null,{"v":3750,"d":"$3,750"},null,{"v":19693.634014474992,"d":"$19,694"},null,{"v":20000,"d":"$20,000"}]
[]
[]
["Problem"]
["An investor wants to put together a portfolio consisting of up to 6 different bonds. He has certain cash-flow requirements in the future"]
["that the coupons of the bonds should cover.  (For example, a pension fund must meet requirements for future pension payments.)"]
["These payments are independent of interest rate changes.  Excess payments in a period can be reinvested, to be available in the  "]
["next period, at a certain interest rate.  How should the investor choose his portfolio to minimize the cost of the bonds, while making "]
["sure that the payments cover his future cash-flow requirements?"]
[]
["Solution"]
["1) The variables are the number of each bond to include in the portfolio. In worksheet BOND3 these are given the name "]
["Purchased_bonds."]
["2) The constraints are very simple. First we have the logical constraints:"]
[null,"Purchased_bonds >= 0  via the Assume Non-Negative option"]
[null,"Purchased_bonds = integer  (We can not buy fractions of a bond)"]
["Then there is the constraint to make sure that the cash-flow requirements are met:"]
[null,"Cash_flow >= Liabilities"]
["3) The objective is to minimize the portfolio cost. This is given the name Total_cost."]
[]
["Remarks"]
["The solution is similar to the one in BOND3. The difference is that the cash-flow takes into account the reinvestment of excess"]
["funds in one period for the next period. Remember that the original idea behind exact matching was to minimize the influence"]
["of interest rate changes.  In this model, however, we are again more dependent on the interest rate, since a shift in the future"]
["rate would affect the solution to the model.  Thus, the market value of the portfolio may fluctuate to a greater extent than if we "]
["ignored reinvestment opportunities."]