The excel file contains more than 9,000 bonds with different interest rates and maturity dates. How to pick the perfect bond, and how the data should be filtered?
The excel file contains more than 9,000 bonds with different interest rates and maturity dates. How to pick the perfect bond, and how the data should be filtered? 
Constraints on the bond portifilio arei 1. Al bonds are selected from the provided NYsE bond market fle.. 2. Assume the market price of all bonds is par value of $1000. Given this simplification, you cannot choose any bond wath a coupon rate greater than 4.5 W. 3. Once a bend is mut iate the imestment portfolio you only recehe the coupon, maturity value (di it maturesh and reievest at the stated rate below 4. Boend coupen and par vahje (at matweity) cash flows will be reimested at 2 W per year unless vied for payment te Ms. topeu. 6. You carnot bontew hands. 7. You must meet the requied annual income payment. 8. Inyetiment grade crly boedi are to be kalasted in the investment portifolio. 9. The minimum matket value weighted bond rating ol the bond perticles is A gyade. 10. You cansot inest more thas 10% of yout capital in any one bond or iswuef. Constraints on the bond portifolic are: 1. All bonds are selected froen the provided Nrst bond market file. 2. Assume the market price of all bonds is pat walue of 51000 . Given this sievplication, yoe cannot choose any bond with a coopon tate greater than 4.5%. 3. Once a bond ho put into the investment portfollo you only receive the coepen, maturity value fif it maturesl, and reinvest at the stated rate below. 6. You cannet boerew hunds. 7. You must meet the required ancual inceene bapment. 8. Wrestment grade ondy bonds are to be induded in the tirestirent portiolia. 9. The minikean market value weighted bond rating of the bond porticlio is A grade. 10. Yeu cannot imest inve than 10N of your cavital it amy one loent or isuar; Juanita Lopez is the client, and you are her portfolio manager. The year is 2022 and she is retiring now at age 65. Juanita's life expectancy is age 85 . Ms. Lopez's investment fund is $3.5 million. She has asked you to develop a bond portfolio that will generate $230,000 starting in one year (2023). To maintain the purchasing power (keep up with inflation) of her retirement income you anticipate 1% growth per annum in her synthetic pension. Constraints on the bond portfolio are: 1. All bonds are selected from the provided NYSE bond market file. 2. Assume the market price of all bonds is par value of $1000. Given this simplification, you cannot choose any bond with a coupon rate greater than 4.5%. 3. Once a bond is put into the investment portfolio you only receive the coupon, maturity value (if it matures), and reinvest at the stated rate below. 4. Bond coupon and par value (at maturity) cash flows will be reinvested at 2% per year unless used for payment to Ms. Lopez. 5. Assume coupon cash flows and maturity value are on the birthday of Juanita. That is, a coupon or maturity value received in 2023 is on the birthday of Juanita regardless of actual date in 2023 and so on. 6. You cannot borrow funds. 7. You must meet the required annual income payment. 8. Investment grade only bonds are to be included in the investment portfolio. 9. The minimum market value weighted bond rating of the bond portfolio is A grade. 10. You cannot invest more than 10% of your capital in any one bond or issuer. Show your constructed bond portfolio (for each bond the total value and bond rating), cash flows generated for each year from coupon and maturity value and reinvested funds, and client income needs. Constraints on the bond portifilio arei 1. Al bonds are selected from the provided NYsE bond market fle.. 2. Assume the market price of all bonds is par value of $1000. Given this simplification, you cannot choose any bond wath a coupon rate greater than 4.5 W. 3. Once a bend is mut iate the imestment portfolio you only recehe the coupon, maturity value (di it maturesh and reievest at the stated rate below 4. Boend coupen and par vahje (at matweity) cash flows will be reimested at 2 W per year unless vied for payment te Ms. topeu. 6. You carnot bontew hands. 7. You must meet the requied annual income payment. 8. Inyetiment grade crly boedi are to be kalasted in the investment portifolio. 9. The minimum matket value weighted bond rating ol the bond perticles is A gyade. 10. You cansot inest more thas 10% of yout capital in any one bond or iswuef. Constraints on the bond portifolic are: 1. All bonds are selected froen the provided Nrst bond market file. 2. Assume the market price of all bonds is pat walue of 51000 . Given this sievplication, yoe cannot choose any bond with a coopon tate greater than 4.5%. 3. Once a bond ho put into the investment portfollo you only receive the coepen, maturity value fif it maturesl, and reinvest at the stated rate below. 6. You cannet boerew hunds. 7. You must meet the required ancual inceene bapment. 8. Wrestment grade ondy bonds are to be induded in the tirestirent portiolia. 9. The minikean market value weighted bond rating of the bond porticlio is A grade. 10. Yeu cannot imest inve than 10N of your cavital it amy one loent or isuar; Juanita Lopez is the client, and you are her portfolio manager. The year is 2022 and she is retiring now at age 65. Juanita's life expectancy is age 85 . Ms. Lopez's investment fund is $3.5 million. She has asked you to develop a bond portfolio that will generate $230,000 starting in one year (2023). To maintain the purchasing power (keep up with inflation) of her retirement income you anticipate 1% growth per annum in her synthetic pension. Constraints on the bond portfolio are: 1. All bonds are selected from the provided NYSE bond market file. 2. Assume the market price of all bonds is par value of $1000. Given this simplification, you cannot choose any bond with a coupon rate greater than 4.5%. 3. Once a bond is put into the investment portfolio you only receive the coupon, maturity value (if it matures), and reinvest at the stated rate below. 4. Bond coupon and par value (at maturity) cash flows will be reinvested at 2% per year unless used for payment to Ms. Lopez. 5. Assume coupon cash flows and maturity value are on the birthday of Juanita. That is, a coupon or maturity value received in 2023 is on the birthday of Juanita regardless of actual date in 2023 and so on. 6. You cannot borrow funds. 7. You must meet the required annual income payment. 8. Investment grade only bonds are to be included in the investment portfolio. 9. The minimum market value weighted bond rating of the bond portfolio is A grade. 10. You cannot invest more than 10% of your capital in any one bond or issuer. Show your constructed bond portfolio (for each bond the total value and bond rating), cash flows generated for each year from coupon and maturity value and reinvested funds, and client income needs
