Please make explain each method and formula used, and please break down the steps and formula in Excel to answer each question below.  Sam Strother and Shawna Tibbs are vice-presidents of Mutual of Seattle Insurance Company and co-directors of the company's pension fund management division.  A major new client, the Northwestern Municipal Alliance, has requested that Mutual of Seattle present an investment seminar to the mayors of the represented cities, and Strother and Tibbs, who will make the actual presentation, have asked you to help them by answering the following questions. 1. How is the value of a bond determined? What is the value of a 10-year, $1,000 par value bond with a 10 percent annual coupon if its required rate of return is 10 percent? Does the bond sell at par? 2. What would be the value of the bond described in Part a. if, just after it had been issued, the expected inflation rate rose by 1 percentage point, causing investors to require an 11 percent return? Would we now have a discount or a premium bond? 3. What would be the value of the bond described in Part a. if, just after it had been issued, the expected inflation rate drop by 1 percentage point, causing investors to require a 9 percent return? Would we now have a discount or a premium bond?

Please make explain each method and formula used, and please break down the steps and formula in Excel to answer each question below. 

Sam Strother and Shawna Tibbs are vice-presidents of Mutual of Seattle Insurance Company and co-directors of the company's pension fund management division.  A major new client, the Northwestern Municipal Alliance, has requested that Mutual of Seattle present an investment seminar to the mayors of the represented cities, and Strother and Tibbs, who will make the actual presentation, have asked you to help them by answering the following questions.

1. How is the value of a bond determined? What is the value of a 10-year, $1,000 par value bond with a 10 percent annual coupon if its required rate of return is 10 percent? Does the bond sell at par?

2. What would be the value of the bond described in Part a. if, just after it had been issued, the expected inflation rate rose by 1 percentage point, causing investors to require an 11 percent return? Would we now have a discount or a premium bond?

3. What would be the value of the bond described in Part a. if, just after it had been issued, the expected inflation rate drop by 1 percentage point, causing investors to require a 9 percent return? Would we now have a discount or a premium bond?

4. What would happen to the value of the 10-year bond over time if the required rate of return remained at 11 percent, or if it remained at 9 percent? Would we now have a premium or a discount bond in either situation?

5. What is the yield to maturity (YTM) on a 10-year, 9 percent annual coupon, $1,000 par value bond that sells for $887.00? How about it sells for $1,134.20? What does the fact that a bond sells at a discount or at a premium tell you about the relationship between investors’ YTM and the bond's coupon rate?

6. How does the calculation for valuing a bond change if semiannual payments are made? Find the value of a 10-year, semiannual payment, a 10 percent coupon bond if investor’s required rate of return is 13%.

7. Suppose a 10-year, 10 percent, semiannual coupon bond with a par value of $1,000 is currently selling for $1,135.90, producing a yield to maturity (YTM) of 8 percent. However, the bond can be called after 5 years for a price of $1,050.

a. What is the bond's yield to call (YTC)?

b. If you bought this bond, do you think you would be more likely to earn the YTM or the YTC? Why?