Please help me analyze this bond portfolio. All calculations and tables must be shown in Excel. Thank you
Bond Portfolio The bond portfolio has seven bonds: . . . A $20,000 par value bond from 3M Co. with a coupon of 3.00 %. The bond is a senior secured bond that matures in 2025 and has a Standard and Poors rating of AA-. The bond is currently selling for $20,232. Mr. Fong purchased the bond when it was issued in 2005 for 95.0% of par. A $30,000 par value bond from General Motors Financial, rated BB, with a 4.25% coupon, maturing in 2023. This bond is selling for 95.00% of par. A Starwood bond with a par of $35,000, rated BBB and maturing in 2021. It has a coupon of 7.15% and is selling for 117.90% of par. An Amgen $60,000 par value bond, rated BBB with a 6.40% coupon and maturing in 2039. This bond is selling for $72,120. The bond is callable in 2024 for a 3.20% call premium. A Kellogg Co. bond rated A with a $50,000 par and a 2.750% coupon. The bond matures in 2023. The bond has a yield to maturity of 2.86% A Safeway bond with a CCC rating. The bond has a par value of $40,000 and a coupon of 7.450%. It matures in 2027 and is yielding 7.758% (YTM). A US Treasury bond with a par of $25,000 and a 3.125% coupon. It matures in 2041 and is yielding 2.332% (YTM). UST bonds are rated AAA. . . . 1. Build a table of the bonds that shows all their information including a. market price, b. current yield and C. yield to maturity. Bond Portfolio The bond portfolio has seven bonds: . . . A $20,000 par value bond from 3M Co. with a coupon of 3.00 %. The bond is a senior secured bond that matures in 2025 and has a Standard and Poors rating of AA-. The bond is currently selling for $20,232. Mr. Fong purchased the bond when it was issued in 2005 for 95.0% of par. A $30,000 par value bond from General Motors Financial, rated BB, with a 4.25% coupon, maturing in 2023. This bond is selling for 95.00% of par. A Starwood bond with a par of $35,000, rated BBB and maturing in 2021. It has a coupon of 7.15% and is selling for 117.90% of par. An Amgen $60,000 par value bond, rated BBB with a 6.40% coupon and maturing in 2039. This bond is selling for $72,120. The bond is callable in 2024 for a 3.20% call premium. A Kellogg Co. bond rated A with a $50,000 par and a 2.750% coupon. The bond matures in 2023. The bond has a yield to maturity of 2.86% A Safeway bond with a CCC rating. The bond has a par value of $40,000 and a coupon of 7.450%. It matures in 2027 and is yielding 7.758% (YTM). A US Treasury bond with a par of $25,000 and a 3.125% coupon. It matures in 2041 and is yielding 2.332% (YTM). UST bonds are rated AAA. . . . 1. Build a table of the bonds that shows all their information including a. market price, b. current yield and C. yield to maturity
