Matthew and Anna are setting up a retirement pay-out account for the next twenty years. They have decided to buy government bonds that have various coupon rates. The following table gives the yield to maturity and the coupon rate on each bond for each maturity date.
Maturity YTM Coupon
1 3.2500% 5.50%
2 3.5000% 6.25%
3 3.7500% 4.75%
4 4.0000% 7.00%
5 4.1250% 6.50%
6 4.2500% 8.25%
7 4.3750% 8.00%
8 4.5000% 7.25%
9 4.6250% 6.50%
10 4.7500% 5.50%
11 4.8750% 5.25%
12 5.0000% 4.75%
13 5.0625% 4.00%
14 5.1250% 4.50%
15 5.1875% 5.25%
16 5.2500% 6.00%
17 5.3125% 6.50%
18 5.3750% 6.75%
19 5.4375% 7.50%
20 5.5000% 8.00%
They decided to buy ten bonds of each maturity (Par value of $1,000). Using a spreadsheet, determine the cost of this bond portfolio and the payment of the bond portfolio each year and at the six-month intervals. (Assume that all bonds have June and December coupon dates)