{"text_format":"1","workbook":"xlsx","n_sheets":10,"sheets":["Summary","Theory","Markowitz","Full Markowitz","Efficient Frontier","Sharpe","Bond1","Bond2","Bond3","Bond4"],"not_rendered":{"charts":1,"images":0,"macros":1,"external_links":0,"defined_names":450,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Summary","index":0,"n_rows":34,"n_cols":1,"rows_kept":34,"cut":false,"hidden":false,"merged":[]}
["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,"cut":false,"hidden":false,"merged":[]}
["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,"cut":false,"hidden":false,"merged":[]}
["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 %",0.2,0.2,0.2,0.2,0.2,null,{"$f":"SUM(B6:F6)","v":1}]
["Expected Return",7.0000000000000007E-2,0.08,9.5000000000000001E-2,6.5000000000000002E-2,0.14000000000000001]
["Linear QP Terms",0,0,0,0,0]
[]
["Variance/Covariance Matrix"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5"]
["Stock 1",2.5000000000000001E-2,1E-3,0.01,-5.0000000000000001E-3,1.6E-2]
["Stock 2",{"$f":"C12","v":1E-3},1.0999999999999999E-2,-1E-3,1.2E-2,-8.5000000000000006E-3]
["Stock 3",{"$f":"D12","v":0.01},{"$f":"D13","v":-1E-3},1.2E-2,6.4999999999999997E-3,7.4999999999999997E-3,null,"Variance",{"$f":"_xll.QUADPRODUCT(Allocations,B8:F8,Stock_Covariances)","v":6.7600000000000004E-3}]
["Stock 4",{"$f":"E12","v":-5.0000000000000001E-3},{"$f":"E13","v":1.2E-2},{"$f":"E14","v":6.4999999999999997E-3},4.0000000000000001E-3,0.01,null,"Std. Dev.",{"$f":"SQRT(I14)","v":8.2219219164377869E-2}]
["Stock 5",{"$f":"F12","v":1.6E-2},{"$f":"F13","v":-8.5000000000000006E-3},{"$f":"F14","v":7.4999999999999997E-3},{"$f":"F15","v":0.01},0.02,null,"Return",{"$f":"SUMPRODUCT(Allocations,Stock_Returns)","v":0.09}]
[]
[]
["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,"cut":false,"hidden":false,"merged":[]}
["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 %",0.2,0.2,0.2,0.2,0.2,null,{"$f":"SUM(B6:F6)","v":1}]
["Expected Return",{"$f":"AVERAGE(B23:B32)","v":8.6999999999999994E-2},{"$f":"AVERAGE(C23:C32)","v":7.0999999999999994E-2},{"$f":"AVERAGE(D23:D32)","v":9.4E-2},{"$f":"AVERAGE(E23:E32)","v":0.10699999999999998},{"$f":"AVERAGE(F23:F32)","v":6.8999999999999992E-2}]
[]
["Variance/Covariance Matrix"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5"]
["Stock 1",{"$f":"VARP(B23:B32)","v":3.2100000000000352E-4},{"$f":"COVAR(B23:B32,C23:C32)","v":1.2430000000000002E-3},{"$f":"COVAR(B23:B32,D23:D32)","v":2.7200000000000005E-4},{"$f":"COVAR(B23:B32,E23:E32)","v":5.7099999999999978E-4},{"$f":"COVAR(B23:B32,F23:F32)","v":1.17E-4}]
["Stock 2",{"$f":"C11","v":1.2430000000000002E-3},{"$f":"VARP(C23:C32)","v":1.2329000000000001E-2},{"$f":"COVAR(C23:C32,D23:D32)","v":1.9960000000000004E-3},{"$f":"COVAR(C23:C32,E23:E32)","v":1.6329999999999997E-3},{"$f":"COVAR(C23:C32,F23:F32)","v":-5.3899999999999977E-4}]
["Stock 3",{"$f":"D11","v":2.7200000000000005E-4},{"$f":"D12","v":1.9960000000000004E-3},{"$f":"VARP(D23:D32)","v":4.2400000000000217E-4},{"$f":"COVAR(D23:D32,E23:E32)","v":3.8200000000000012E-4},{"$f":"COVAR(D23:D32,F23:F32)","v":-6.5999999999999937E-5}]
["Stock 4",{"$f":"E11","v":5.7099999999999978E-4},{"$f":"E12","v":1.6329999999999997E-3},{"$f":"E13","v":3.8200000000000012E-4},{"$f":"VARP(E23:E32)","v":5.1410000000000067E-3},{"$f":"COVAR(E23:E32,F23:F32)","v":2.3699999999999996E-4}]
["Stock 5",{"$f":"F11","v":1.17E-4},{"$f":"F12","v":-5.3899999999999977E-4},{"$f":"F13","v":-6.5999999999999937E-5},{"$f":"F14","v":2.3699999999999996E-4},{"$f":"VARP(F23:F32)","v":5.0900000000000218E-4}]
[]
["Variance Terms",{"$f":"B6*SUMPRODUCT($B$6:$F$6,B11:F11)","v":1.0096000000000014E-4},{"$f":"C6*SUMPRODUCT($B$6:$F$6,B12:F12)","v":6.6648000000000011E-4},{"$f":"D6*SUMPRODUCT($B$6:$F$6,B13:F13)","v":1.2032000000000011E-4},{"$f":"E6*SUMPRODUCT($B$6:$F$6,B14:F14)","v":3.1856000000000028E-4},{"$f":"F6*SUMPRODUCT($B$6:$F$6,B15:F15)","v":1.0320000000000096E-5},null,"Variance",{"$f":"SUM(B17:F17)","v":1.2166400000000008E-3}]
[null,null,null,null,null,null,null,"Std. Dev.",{"$f":"SQRT(I17)","v":3.4880366970546639E-2}]
["Return Terms",{"$f":"B6*B7","v":1.7399999999999999E-2},{"$f":"C6*C7","v":1.4199999999999999E-2},{"$f":"D6*D7","v":1.8800000000000001E-2},{"$f":"E6*E7","v":2.1399999999999999E-2},{"$f":"F6*F7","v":1.38E-2},null,"Return",{"$f":"SUM(B19:F19)","v":8.5600000000000009E-2}]
[]
["Historical data (Returns) on stocks"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5"]
["Period 1",0.1,0.15,0.12,0.18,0.05]
["Period 2",0.12,0.17,0.13,0.16,0.08]
["Period 3",0.08,0.04,0.09,0.03,0.1]
["Period 4",7.0000000000000007E-2,-0.08,7.0000000000000007E-2,0.04,0.09]
["Period 5",0.09,0.15,0.09,0.08,0.05]
["Period 6",7.0000000000000007E-2,0.22,0.11,0.1,0.04]
["Period 7",0.08,0.03,0.09,-0.03,0.04]
["Period 8",0.06,-0.14000000000000001,0.06,0.15,0.06]
["Period 9",0.09,0.02,0.08,0.2,0.08]
["Period 10",0.11,0.15,0.1,0.16,0.1]
[]
[]
["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,"cut":false,"hidden":false,"merged":[]}
["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 %",0,3.5714285714288627E-2,0,0,0.96428571428571275,null,{"$f":"SUM(B6:F6)","v":1.0000000000000013}]
["Expected Return",7.0000000000000007E-2,0.08,9.5000000000000001E-2,6.5000000000000002E-2,0.15]
[]
["Variance/Covariance Matrix"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5"]
["Stock 1",2.5000000000000001E-2,1E-3,0.01,-5.0000000000000001E-3,1.6E-2]
["Stock 2",{"$f":"C11","v":1E-3},1.0999999999999999E-2,-1E-3,1.2E-2,-8.5000000000000006E-3]
["Stock 3",{"$f":"D11","v":0.01},{"$f":"D12","v":-1E-3},1.2E-2,6.4999999999999997E-3,7.4999999999999997E-3]
["Stock 4",{"$f":"E11","v":-5.0000000000000001E-3},{"$f":"E12","v":1.2E-2},{"$f":"E13","v":6.4999999999999997E-3},4.0000000000000001E-3,0.01]
["Stock 5",{"$f":"F11","v":1.6E-2},{"$f":"F12","v":-8.5000000000000006E-3},{"$f":"F13","v":7.4999999999999997E-3},{"$f":"F14","v":0.01},0.02]
[null,null,null,null,null,null,null,"Variance",{"$f":"SUM(B17:F17)","v":1.8025510204081531E-2}]
["Variance Terms",{"$f":"B6*SUMPRODUCT($B$6:$F$6,B11:F11)","v":0},{"$f":"C6*SUMPRODUCT($B$6:$F$6,B12:F12)","v":-2.786989795918579E-4},{"$f":"D6*SUMPRODUCT($B$6:$F$6,B13:F13)","v":0},{"$f":"E6*SUMPRODUCT($B$6:$F$6,B14:F14)","v":0},{"$f":"F6*SUMPRODUCT($B$6:$F$6,B15:F15)","v":1.8304209183673387E-2},null,"Std. Dev.",{"$f":"SQRT(I16)","v":0.13425911590682224}]
[null,null,null,null,null,null,null,"Des. Ret",0.14749999999999999]
["Return Terms",{"$f":"B6*B7","v":0},{"$f":"C6*C7","v":2.85714285714309E-3},{"$f":"D6*D7","v":0},{"$f":"E6*E7","v":0},{"$f":"F6*F7","v":0.14464285714285691},null,"Return",{"$f":"SUM(B19:F19)","v":0.14749999999999999}]
[]
[]
["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,"cut":false,"hidden":false,"merged":[]}
["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 %",0.2,0.2,0.2,0.2,0.2,null,{"$f":"SUM(B6:F6)","v":1}]
["Beta-Coefficient",{"$f":"INDEX(LINEST(B18:B27,$H$18:$H$27),1)","v":0.57738095238095211},{"$f":"INDEX(LINEST(C18:C27,$H$18:$H$27),1)","v":1.6726190476190472},{"$f":"INDEX(LINEST(D18:D27,$H$18:$H$27),1)","v":0.64880952380952372},{"$f":"INDEX(LINEST(E18:E27,$H$18:$H$27),1)","v":0.93452380952380942},{"$f":"INDEX(LINEST(F18:F27,$H$18:$H$27),1)","v":0.76785714285714279}]
["Alpha-Coefficient",{"$f":"INDEX(LINEST(B18:B27,$H$18:$H$27),2)","v":4.7738095238095246E-2},{"$f":"INDEX(LINEST(C18:C27,$H$18:$H$27),2)","v":-4.2738095238095228E-2},{"$f":"INDEX(LINEST(D18:D27,$H$18:$H$27),2)","v":4.9880952380952387E-2},{"$f":"INDEX(LINEST(E18:E27,$H$18:$H$27),2)","v":4.3452380952380937E-2},{"$f":"INDEX(LINEST(F18:F27,$H$18:$H$27),2)","v":1.6785714285714279E-2}]
["Residual Variance",{"$f":"(INDEX(LINEST(B18:B27,$H$18:$H$27,1,1),3,2))^2","v":2.61235119047619E-4},{"$f":"(INDEX(LINEST(C18:C27,$H$18:$H$27,1,1),3,2))^2","v":1.4236235119047622E-2},{"$f":"(INDEX(LINEST(D18:D27,$H$18:$H$27,1,1),3,2))^2","v":3.5319940476190466E-4},{"$f":"(INDEX(LINEST(E18:E27,$H$18:$H$27,1,1),3,2))^2","v":6.059449404761906E-3},{"$f":"(INDEX(LINEST(F18:F27,$H$18:$H$27,1,1),3,2))^2","v":3.8861607142857155E-4}]
["Weighted Variance",{"$f":"B6^2*B9","v":1.0449404761904762E-5},{"$f":"C6^2*C9","v":5.6944940476190496E-4},{"$f":"D6^2*D9","v":1.4127976190476189E-5},{"$f":"E6^2*E9","v":2.423779761904763E-4},{"$f":"F6^2*F9","v":1.5544642857142863E-5}]
[]
[null,null,null,null,"Portfolio Variance",null,null,{"$f":"SUMPRODUCT(B7:F7,B6:F6)^2*VAR(H18:H27)+SUM(B10:F10)","v":1.1681023148148153E-3}]
[]
[null,null,null,null,"Portfolio Return",null,null,{"$f":"SUMPRODUCT(B8:F8,B6:F6)+SUMPRODUCT(B7:F7,B6:F6)*AVERAGE(H18:H27)","v":8.5599999999999996E-2}]
[]
["Historical data (Returns) on stocks"]
[null,"Stock 1","Stock 2","Stock 3","Stock 4","Stock 5",null,"Market"]
["Period 1",0.1,0.15,0.12,0.18,0.05,null,0.08]
["Period 2",0.12,0.17,0.13,0.16,0.08,null,0.1]
["Period 3",0.08,0.04,0.09,0.03,0.1,null,0.09]
["Period 4",7.0000000000000007E-2,-0.08,7.0000000000000007E-2,0.04,0.09,null,7.0000000000000007E-2]
["Period 5",0.09,0.15,0.09,0.08,0.05,null,0.04]
["Period 6",7.0000000000000007E-2,0.22,0.11,0.1,0.04,null,0.06]
["Period 7",0.08,0.03,0.09,-0.03,0.04,null,0.05]
["Period 8",0.06,-0.14000000000000001,0.06,0.15,0.06,null,0.05]
["Period 9",0.09,0.02,0.08,0.2,0.08,null,0.06]
["Period 10",0.11,0.15,0.1,0.16,0.1,null,0.08]
[]
[]
["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,"cut":false,"hidden":false,"merged":[]}
["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 %",0.2,0.2,0.2,0.2,0.1,0.1,null,{"$f":"SUM(B6:G6)","v":1}]
["Duration (Years)",2.8,3.1,3.7,3.5,3.8,4]
["Yield to Maturity",0.08,0.06,0.1,0.09,0.08,0.05]
[]
[null,null,null,null,null,"Investment Time Horizon",3.8]
[null,null,null,null,null,"Portfolio Duration",{"$f":"SUMPRODUCT(B6:G6,B7:G7)","v":3.4000000000000004}]
[null,null,null,null,null,"Portfolio Yield",{"$f":"SUMPRODUCT(B6:G6,B8:G8)","v":7.9000000000000015E-2}]
[]
[]
["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,"cut":false,"hidden":false,"merged":[]}
["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 %",0.2,0.2,0.2,0.2,0.2,0.2,null,{"$f":"SUM(B7:G7)","v":1.2}]
["Yield to Maturity",0.08,0.06,0.09,0.1,7.0000000000000007E-2,0.09]
["Coupon Payment",150,100,120,175,0,125]
["Face Value",1000,1000,1000,1000,1000,1000]
[]
["Duration (Years)",{"$f":"DURATION(DATE(93,1,1),DATE(97,1,1),B9/B10,B8,1,1)","v":3.3562983556224268},3.5213612662766085,3.430418126196058,3.274476802737043,4,3.4153176190270536]
[]
[null,null,null,null,null,"Investment Time Horizon",3.5]
[null,null,null,null,null,"Portfolio Duration",{"$f":"SUMPRODUCT(B7:G7,B12:G12)","v":4.1995744339718382}]
[null,null,null,null,null,"Portfolio Yield",{"$f":"SUMPRODUCT(B7:G7,B8:G8)","v":9.8000000000000004E-2}]
[]
[]
["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,"cut":false,"hidden":false,"merged":[]}
["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",7.0000000000000007E-2]
[]
["Characteristics of bonds"]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5"]
["Face Value",1000,1000,1000,1000,1000]
["Coupon Payment",100,125,150,200,75]
["Years to Maturity",3,5,6,4,6]
["Price",{"$f":"PRICE(DATE(93,1,1),DATE(93+B10,1,1),B9/B8,0.07,100,1,0)*B8/100","v":1078.729481332492},{"$f":"PRICE(DATE(93,1,1),DATE(93+C10,1,1),C9/C8,0.07,100,1,0)*C8/100","v":1225.5108589771176},{"$f":"PRICE(DATE(93,1,1),DATE(93+D10,1,1),D9/D8,0.07,100,1,0)*D8/100","v":1381.3231727811283},{"$f":"PRICE(DATE(93,1,1),DATE(93+E10,1,1),E9/E8,0.07,100,1,0)*E8/100","v":1440.3374633403105},{"$f":"PRICE(DATE(93,1,1),DATE(93+F10,1,1),F9/F8,0.07,100,1,0)*F8/100","v":1023.8326982988204}]
[]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5",null,"Cost"]
["Number Purchased",10,10,10,10,10,null,{"$f":"SUMPRODUCT(B14:F14,B11:F11)","v":61497.336747298687}]
[]
["Cash Flow","Bond 1","Bond 2","Bond 3","Bond 4","Bond 5",null,"Total",null,"Liability"]
["Year 1",{"$f":"B9*B14","v":1000},{"$f":"C9*C14","v":1250},{"$f":"D9*D14","v":1500},{"$f":"E9*E14","v":2000},{"$f":"F9*F14","v":750},null,{"$f":"SUM(B17:F17)","v":6500},null,32000]
["Year 2",{"$f":"B9*B14","v":1000},{"$f":"C9*C14","v":1250},{"$f":"D9*D14","v":1500},{"$f":"E9*E14","v":2000},{"$f":"F9*F14","v":750},null,6500,null,25000]
["Year 3",{"$f":"B9*B14","v":1000},{"$f":"C9*C14","v":1250},{"$f":"D9*D14","v":1500},{"$f":"E9*E14","v":2000},{"$f":"F9*F14","v":750},null,6500,null,22000]
["Year 4",null,{"$f":"C9*C14","v":1250},{"$f":"D9*D14","v":1500},{"$f":"E9*E14","v":2000},{"$f":"F9*F14","v":750},null,5500,null,28000]
["Year 5",null,{"$f":"C9*C14","v":1250},{"$f":"D9*D14","v":1500},null,{"$f":"F9*F14","v":750},null,3500,null,25000]
["Year 6",null,null,{"$f":"D9*D14","v":1500},null,{"$f":"F9*F14","v":750},null,2250,null,20000]
[]
[]
["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,"cut":false,"hidden":false,"merged":[]}
["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",7.0000000000000007E-2]
[]
["Characteristics of bonds"]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5"]
["Face Value",1000,1000,1000,1000,1000]
["Coupon Payment",100,125,150,175,75]
["Years to Maturity",3,5,6,4,6]
["Price",{"$f":"PRICE(DATE(93,1,1),DATE(93+B10,1,1),B9/B8,0.07,100,1,0)*B8/100","v":1078.729481332492},{"$f":"PRICE(DATE(93,1,1),DATE(93+C10,1,1),C9/C8,0.07,100,1,0)*C8/100","v":1225.5108589771176},{"$f":"PRICE(DATE(93,1,1),DATE(93+D10,1,1),D9/D8,0.07,100,1,0)*D8/100","v":1381.3231727811283},{"$f":"PRICE(DATE(93,1,1),DATE(93+E10,1,1),E9/E8,0.07,100,1,0)*E8/100","v":1355.6571819287119},{"$f":"PRICE(DATE(93,1,1),DATE(93+F10,1,1),F9/F8,0.07,100,1,0)*F8/100","v":1023.8326982988204}]
[]
[null,"Bond 1","Bond 2","Bond 3","Bond 4","Bond 5",null,"Cost"]
["Number Purchased",50,50,50,50,50,null,{"$f":"SUMPRODUCT(B14:F14,B11:F11)","v":303252.66966591351}]
[]
["Cash Flow","Bond 1","Bond 2","Bond 3","Bond 4","Bond 5",null,"Total w/Int",null,"Liability"]
["Year 1",{"$f":"B9*B14","v":5000},{"$f":"C9*C14","v":6250},{"$f":"D9*D14","v":7500},{"$f":"E9*E14","v":8750},{"$f":"F9*F14","v":3750},null,{"$f":"SUM(B17:F17)","v":31250},null,32000]
["Year 2",{"$f":"B9*B14","v":5000},{"$f":"C9*C14","v":6250},{"$f":"D9*D14","v":7500},{"$f":"E9*E14","v":8750},{"$f":"F9*F14","v":3750},null,{"$f":"SUM(B18:F18)+(H17-J17)*(1+$B$4)","v":30447.5},null,25000]
["Year 3",{"$f":"B9*B14","v":5000},{"$f":"C9*C14","v":6250},{"$f":"D9*D14","v":7500},{"$f":"E9*E14","v":8750},{"$f":"F9*F14","v":3750},null,{"$f":"SUM(B19:F19)+(H18-J18)*(1+$B$4)","v":37078.824999999997},null,22000]
["Year 4",null,{"$f":"C9*C14","v":6250},{"$f":"D9*D14","v":7500},{"$f":"E9*E14","v":8750},{"$f":"F9*F14","v":3750},null,{"$f":"SUM(B20:F20)+(H19-J19)*(1+$B$4)","v":42384.342749999996},null,28000]
["Year 5",null,{"$f":"C9*C14","v":6250},{"$f":"D9*D14","v":7500},null,{"$f":"F9*F14","v":3750},null,{"$f":"SUM(B21:F21)+(H20-J20)*(1+$B$4)","v":32891.246742499992},null,25000]
["Year 6",null,null,{"$f":"D9*D14","v":7500},null,{"$f":"F9*F14","v":3750},null,{"$f":"SUM(B22:F22)+(H21-J21)*(1+$B$4)","v":19693.634014474992},null,20000]
[]
[]
["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."]