Here are the answers to each question and the corresponding formulas to solve in Excel without specifying cell blocks.

Given Information:

• Face Value $F = 1000$
• Coupon Rate $r = 10\%$ (annual)
• Yield to Maturity $YTM = 8\%$ (annual)
• Number of Years $N = 10$
• Semiannual Payments

To calculate values accurately, note that the coupon payments are semiannual, meaning we’ll adjust the rates and periods accordingly.

Solution

a. Annual Coupon Payment

Since the bond has a coupon rate of 10%, the annual coupon payment will be: $\text{Annual Coupon Payment} = F \times r = 1000 \times 0.10 = 100$

Thus: $\text{Annual Coupon Payment} = 100$

b. Price of the Bond

For the price, we’ll use the Present Value of Annuity formula (for coupon payments) and the Present Value formula (for the face value).

Since the bond pays semiannual coupons:

• Coupon per period = $\frac{100}{2} = 50$
• Semiannual yield = $\frac{YTM}{2} = \frac{8\%}{2} = 4\%$
• Total periods = $2 \times N = 2 \times 10 = 20$

The bond price $P$ is given by: $P = \left( C \times \frac{1 - (1 + y)^{-n}}{y} \right) + \frac{F}{(1 + y)^n}$ Where:

• $C$ is the semiannual coupon payment ($50$)
• $y$ is the semiannual yield (0.04)
• $n$ is the total number of periods (20)

Plugging in the values: $P = \left( 50 \times \frac{1 - (1 + 0.04)^{-20}}{0.04} \right) + \frac{1000}{(1 + 0.04)^{20}}$

In Excel (Formula):

excelCopy code
= (50 * (1 - (1 + 0.04) ^ -20) / 0.04) + (1000 / (1 + 0.04) ^ 20)

This formula calculates the bond price by summing the present value of the coupon payments and the present value of the face value.

c. Is the bond selling at a discount, par, or premium?

Since the yield to maturity (8%) is less than the coupon rate (10%), the bond price will be higher than its face value. Therefore, this bond is selling at a premium.

Would you like more detailed steps on any part, or have questions?

Related Questions:

1. How would the bond price change if the yield to maturity increased to 12%?
2. What happens to the bond price if there are only 5 years left to maturity?
3. How does compounding frequency affect the bond price calculation?
4. What is the difference between the coupon rate and yield to maturity?
5. How can one calculate the bond's duration in Excel?

Tip:

When calculating bond prices in Excel, remember that higher yields result in lower bond prices, and lower yields lead to higher bond prices due to the inverse relationship between bond prices and yields.