Need this in excel, Just need how to go through each steps, you can make numbers and bond yourself with more than 4.5 YTM. Just need all the steps being incorporated .

You are Phil Hartmanss portfolio manager. The year is 2019 and she is retiring now at age 60. Phil;s life expectancy is 85 years. His investment fund is $4.5 million. He has asked you to develop a bond portfolio that will generate $330,000 starting in one year (2020). To maintain the purchasing power (keep up with inflation) of her retirement income you anticipate 1% growth per annum in his synthetic pension. Constraints on the bond portfolio are:

1. Assume the market price of all bonds is par value of $1000.

2. 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 (point 4).

4. Bond coupon and par value (at maturity) cash flows will be reinvested at 2% per year unless used for payment to Phil.

5. Assume coupon cash flows and maturity value are on Phil's birthday. In other words, a coupon or maturity value received in 2020 is on her birthday regardless of actual date in 2020, etc.

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. As mentioned in the Organization section, your files should make it clear that you are fulfilling all of the above 10 points.