Financial information Technology Out of Class Assignment: Excel #2: Portfolio Optimization Due 5/3/21 Students will be required to complete an analysis using the solver function within EXCEL. The model in this assignment deal with decision to invest in fixed income using optimization techniques. We will be using the solver function in Excel to create a model that will optimize results under various constraints. If your PC does not have the solver function you will need to add it into the Analysis function under the Data heading in tool bar. To Add-in Solver (if needed): 1. Click the Microsoft Office Button , and then click Excel Options. 2. Click Add-Ins, 3. In the Manage box select Excel Add-ins and click Go 4. In the Add-Ins available box, check the box to select the Solver Add-in. In addition you should also make sure the Analysis Tool Pack is selected. 5. Click OK. Tip: If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in. If you get prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it. After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab. Using Solver: In order to use the Solver function in the model you will need to enter three categories of data in defining an optimization model: the target cell, the changing cells, and the constraints. Target cell The target cell represents the objective or goal. We want to either minimize or maximize the target cell. As an example using a drug company's product mix, the plant manager could desire to maximize the profitability of the plant during each month. The cell that measures profitability would be the target cell. Changing cells Changing cells are the spreadsheet cells that we can change or adjust to optimize the target cell. In the drug company example, the plant manager can adjust the amount produced for each product during a month. The cells in which these amounts are recorded are the changing cells in this model. Constraints Constraints are restrictions you place on the changing cells. In our product mix example, the product mix can't use more of any available resource than the amount available of the resource. Also, we would not want to produce more of a product than people are willing to buy. In most Solver models there is an implicit constraint that all changing cells must be nonnegative although a solver model does not need not to have any constraints. Using the HELP function in EXCEL is a very good way to learn more about using the SOLVER function.

Optimization: Bond Durations Matching Bonds are generally considered less risky than stocks. Students should be familiar with the inverse relationship of interest rates and bond prices. The higher the credit quality of the bond issuer the stronger this relationship such that if a bond is of the highest credit quality, i.e., government treasuries its price change is almost entirely dependent upon interest rate changes. If interest rates go up, the price of the bond will go down. Conversely, if interest rates go down the bonds rise will rise. However, there can be an offset to the investor as most bonds pay interest in an annual (or semi-annual) coupon, and the interest can be reinvested at current interest rates. As an example, if interest rates go up, the price of the bond will go down but the coupon interest paid can be reinvested at the higher rates of interest. 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 a face value of $100 and an annual coupon payment of $10 and yield to maturity 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 ones own investment time horizon. A portfolio of bonds has a duration that is weighted average of the duration of the individual bonds. In the model 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. Bond Immunization Model An investor wants to put together a portfolio, consisting of portions of 6 different 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 varies for each bond. 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, she wants to make sure the average duration (Note: use DURATION formula to calculate duration of each bond.) of the bonds equals her investment time horizon. How should the investor choose her portfolio to optimize the combined yield of the bonds, while making sure the duration of the portfolio equals the investment time horizon? The bonds in question have various maturity dates as well and coupon payment frequencies. The annual payments, the yield and the face value of the bonds are all known. You will need to use SumProduct function a couple times in the Solver template. Solution The objective is to maximize the portfolio yield under an investment time horizon of 6 years. a. Target Cell: Maximize the target cell of the portfolio yield b. Changing Cells: These are the individual percentage holdings of each individual Bond. c. Constraints: Portfolio Duration must equal the investment time horizon. Each bond holding cannot be less than 0 Each bond holding cannot be more than 33% Sum of the percentage holding of bonds must be equal to 100%

Do any 6 bonds thru FactSet and place it in excel please and show each formula.

Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Maturity/Par 100 100 100 100 100 100 Maturity 6/1/2024 6/1/2025 6/1/2031 6/1/2023 6/1/2026 6/1/2027 Coupon 3.5% 3.0% 5.0% 2.0% 4.0% 4.5% Payment Frequency 21 2 4) 4 2 2 Settlement Date 3/31/2021 3/31/2021 3/31/2021 3/31/2021 3/31/2021 3/31/2021 Yield 3.30% 3.20% 4.00% 2.75% 3.15% 3.75% Duration Matching Optimization Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Face Value Settlement Date Maturity Date Coupon % Coupon $ Yield % Payment Frequency Duration Portfolio % Investment Time Horizon Portfolio Duration Portfolio Yield Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Maturity/Par 100 100 100 100 100 100 Maturity 6/1/2024 6/1/2025 6/1/2031 6/1/2023 6/1/2026 6/1/2027 Coupon 3.5% 3.0% 5.0% 2.0% 4.0% 4.5% Payment Frequency 21 2 4) 4 2 2 Settlement Date 3/31/2021 3/31/2021 3/31/2021 3/31/2021 3/31/2021 3/31/2021 Yield 3.30% 3.20% 4.00% 2.75% 3.15% 3.75% Duration Matching Optimization Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Face Value Settlement Date Maturity Date Coupon % Coupon $ Yield % Payment Frequency Duration Portfolio % Investment Time Horizon Portfolio Duration Portfolio Yield