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 
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. 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
