Consider the following two bonds:

Bond A

Term to maturity: 5 years from today

Face value: $100

Annual Coupon rate: 5%

Number of payments per year: 2

Bond B

Term to maturity: 20 years from today

Face value: $1,000

Annual Coupon rate: 7%

Number of payments per year: 4

• Compute the coupon amount and price for each bond. The current YTM for each bond is 6%. Then make a table comparing the bond prices when the YTM varies from 1%, 2% 17%.
• Compute duration and modified duration for each bond.
• Use (modified) duration to estimate the percentage change of price for each bond if the YTM decreases from 6% to 4%.

please use the excel sheet to answer the question and please provide the formula inside the excel thanks!!