Hi there,

I am working on this homework. Could you show me the steps in a spreadsheet (excel)?

Part 1

1. Datemine the market price using a sing column set up

2. Use all the same data above and change the purchase data below and recalculate the market price

3. Determine tge market price

4. Describe and compare the parttern of what happens to the market price as the bond approaches the maturity date in each case

Part 2

1. Determine the market rate

2. Use the data above and change the market price and recalculate the market rate

3. Describe the relationship between market prices and market rates and the coupon rate

Bond Valuation Case PART 1 This case will build on the single column approach to bond valuation. Begin by setting up an Excel template to determine the market price in a single column format for the following bond Face Value 1000 Coupon Rate 4.51 Maturity Date 9/10/36 Purchase Date 3/10/20 Market Rate 3.1% Holding all the other inputs constant change the purchase date using each date below) and recalculate the market price using a separate column for each purchase date: 3/10/23 3/10/26 3/1029 3/10/32 3/10/35 Copy and paste the entire block of Excel cells from above and change the market rate as indicated below Face Value 1000 Coupon Rate 4.5% Maturity Date 9/10/36 Purchase Date 3/10/20 Market Rate 6.1% Describe and compare the pattern of what happens to the market price as the bond approaches the maturity date in each case PART 2 For this part begin by setting up an Excel template to determine the market rate in a single column format for the following bond Face Value 1000 Coupon Rate 4.5% Maturity Date 9/10/36 Purchase Date 3/10/20 Market Price 80.000 Holding all the other inputs constant change the market price (using each price below) and recalculate the market rate using a separate column for each market price: 90.000 100.000 110.000 120.000 Describe the relationship between market prices and market rates and the coupon rate