Franklin, a well-known economist wants to retire.

Franklin, a well-known economist wants to retire. He wants to have 1 million dollars in 10 years. Create

a portfolio of 10 bonds from different issuers that will give him 1 million in 10 years. The issues must be

investment grade in order to be included in the portfolio. or better. Create and hand in the portfolio in

Excel and answer the following questions. There is a submission box in exams for the portfolios.

Remember that the bonds need to be bonds that are currently trading, and the retirement isin 10

years. So, you are buying the bonds using the prices from late November or in December of the current

year. This is a great problem because this is exactly how smaller investment firms set up their

portfolios. Do your best, and it will be worth it. Excel tutorials are in the folder.

1. How much money must he put up, or what is his cost?

2. What is the YTM of his portfolio?

3. What is the Modified Duration of his portfolio?

4. Explain how much money Franklin will have in the end.

5. What are some of the Risks Franklin will face? In short, will be able to watch Football in peace?

Assumptions

Assume the settlement date is the day you created the portfolio for simplicity purposes.

The last trade would be the current price.

Since you know the maturity date and the normal situation should be semiannual, you can work backward and find the coupon payment dates.

Also, assume the issue date was the last coupon payment date before you bought the bonds. For example, if the maturity was January 15, 2034, this means the last coupon payment date before the bond was paid off was July 15th, 2033. It also means that if you bought the bond today in 2023, the last coupon payment was July 15th, 2023.

Also, assume Franklin has $800,000 to invest and use 8% as the reinvestment rate.