A. Consider a bond that makes annual payments. For example, a 6% coupon bond with face value $1000 pays $60 per year in payments. Create a bond table that would provide a reference to a bond trader. The trader chooses a bond term in one cell of the spreadsheet. The table shows coupon rates (0%-10% in 1% increments) across the columns and discount rates (0-10% in 25 bp 32 increments) across the rows, and in each cell of the report, the bond price is quoted. Using your table, answer the following questions.

a. Graph the bond prices as a function of discount rates. How would you describe the relationship between discount rates and bond prices?

b. On your table, use conditional formatting to identify bonds that trade at a premium or discount to par value of $1000.

Your solution should look like this: : 1000 Face value Bond term (years) 10 Discount rates 951 841 820 Coupon rates 0.00% 0.00% 1,000 0.25% 975 0.50% 0.75% 928 1.00% 905 1.25% 883 1.50% 862 1.75% 2.00% 2.25% 801 2.50% 781 2.75% 762 3.00% 744 3.25% 726 3.50% 709 3.75% 692 4.00% 676 4.25% 660 4.50% 644 4.75% 629 5.00% 614 5.25% 599 5.50% 5.75% 572 6.00% 558 6.25% 545 6.50% 533 6.75% 520 7.00% 508 7.25% 7.50% 485 7.75% 474 8.00% 463 8.25% 453 8.50% 442 8.75% 432 9.00% 422 9.25% 413 9.50% 404 9.75% 394 10.00% 386 1.00% 1,100 1,074 1,049 1,024 1,000 977 954 932 910 889 869 849 829 810 792 774 757 740 723 707 691 676 661 646 632 618 605 591 579 566 554 542 530 519 508 497 487 476 466 457 447 2.00% 1,200 1,173 1,146 1,120 1,095 1,070 1,046 1,023 1,000 978 956 935 915 895 875 856 838 820 802 785 768 752 736 721 7 E 2,500 E 3.00% 1,300 1,271 1,243 1,216 1,189 1,164 1,138 1,114 1,090 1,066 1,044 1,022 1,000 979 958 938 919 900 881 863 846 828 812 795 4.00% 1,400 1,370 1,341 1,312 1,284 1,257 1,231 1,205 1,180 1,155 1,131 1,108 1,085 1,063 1,042 1,021 1,000 980 960 941 923 905 887 870 5.00% 1,500 1,469 1,438 1,408 1,379 1,350 1,323 1,296 1,269 1,244 1,219 1,194 1,171 1,147 1,125 1,103 1,081 1,060 1,040 1,020 1,000 981 962 944 6.00% 1,600 1,567 1,535 1,504 1,474 1,444 1,415 1,387 1,359 1,332 1,306 1,281 1,256 1,232 1,208 1,185 1,162 1,140 1,119 1,098 1,077 1,057 1,038 1,019 7.00% 1,700 1,666 1,632 1,600 1,568 1,537 1,507 1,478 1,449 1,421 1,394 1,367 1,341 1,316 1,291 1,267 1,243 1,220 1,198 1,176 1,154 1,134 1,113 1,093 8.00% 1,800 1,764 1,730 1,696 1,663 1,631 1,599 1,569 1,539 1,510 1,481 1,454 1,427 1,400 1,374 1,349 1,324 1,300 1,277 1,254 1,232 1,210 1,188 1,168 9.00% 1,900 1,863 1,827 1,792 1,758 1,724 1,692 1,660 1,629 1,598 1,569 1,540 1,512 1,484 1,457 1,431 1,406 1,381 1,356 1,332 1,309 10.00% 2,000 1,962 1,924 1,888 1,852 1,818 1,784 1,751 1,719 1,687 1,656 1,626 1,597 1,569 1,541 1,513 1,487 1,461 1,435 1,410 1,386 1,362 1,339 1,317 1,294 1,273 1,252 1,231 1,211 1,191 1,172 1,153 1,134 1,116 1,098 1,081 1,064 1,048 1,031 1,016 1,000 1,286 585 1,264 1,242 Bond Prices by Discount Rate, Coupons 0-10% 2,000 497 E 1,500 1,000 5 500 5 5 0 0.00% 0.50% 1.00% 9.00% 9.50% 10.00% 5 5 523 519 508 : Uuu TOU JUU 581 570 643 631 705 693 767 754 045 829 816 891 877 953 939 Your solution should look like this: : 1000 Face value Bond term (years) 10 Discount rates 951 841 820 Coupon rates 0.00% 0.00% 1,000 0.25% 975 0.50% 0.75% 928 1.00% 905 1.25% 883 1.50% 862 1.75% 2.00% 2.25% 801 2.50% 781 2.75% 762 3.00% 744 3.25% 726 3.50% 709 3.75% 692 4.00% 676 4.25% 660 4.50% 644 4.75% 629 5.00% 614 5.25% 599 5.50% 5.75% 572 6.00% 558 6.25% 545 6.50% 533 6.75% 520 7.00% 508 7.25% 7.50% 485 7.75% 474 8.00% 463 8.25% 453 8.50% 442 8.75% 432 9.00% 422 9.25% 413 9.50% 404 9.75% 394 10.00% 386 1.00% 1,100 1,074 1,049 1,024 1,000 977 954 932 910 889 869 849 829 810 792 774 757 740 723 707 691 676 661 646 632 618 605 591 579 566 554 542 530 519 508 497 487 476 466 457 447 2.00% 1,200 1,173 1,146 1,120 1,095 1,070 1,046 1,023 1,000 978 956 935 915 895 875 856 838 820 802 785 768 752 736 721 7 E 2,500 E 3.00% 1,300 1,271 1,243 1,216 1,189 1,164 1,138 1,114 1,090 1,066 1,044 1,022 1,000 979 958 938 919 900 881 863 846 828 812 795 4.00% 1,400 1,370 1,341 1,312 1,284 1,257 1,231 1,205 1,180 1,155 1,131 1,108 1,085 1,063 1,042 1,021 1,000 980 960 941 923 905 887 870 5.00% 1,500 1,469 1,438 1,408 1,379 1,350 1,323 1,296 1,269 1,244 1,219 1,194 1,171 1,147 1,125 1,103 1,081 1,060 1,040 1,020 1,000 981 962 944 6.00% 1,600 1,567 1,535 1,504 1,474 1,444 1,415 1,387 1,359 1,332 1,306 1,281 1,256 1,232 1,208 1,185 1,162 1,140 1,119 1,098 1,077 1,057 1,038 1,019 7.00% 1,700 1,666 1,632 1,600 1,568 1,537 1,507 1,478 1,449 1,421 1,394 1,367 1,341 1,316 1,291 1,267 1,243 1,220 1,198 1,176 1,154 1,134 1,113 1,093 8.00% 1,800 1,764 1,730 1,696 1,663 1,631 1,599 1,569 1,539 1,510 1,481 1,454 1,427 1,400 1,374 1,349 1,324 1,300 1,277 1,254 1,232 1,210 1,188 1,168 9.00% 1,900 1,863 1,827 1,792 1,758 1,724 1,692 1,660 1,629 1,598 1,569 1,540 1,512 1,484 1,457 1,431 1,406 1,381 1,356 1,332 1,309 10.00% 2,000 1,962 1,924 1,888 1,852 1,818 1,784 1,751 1,719 1,687 1,656 1,626 1,597 1,569 1,541 1,513 1,487 1,461 1,435 1,410 1,386 1,362 1,339 1,317 1,294 1,273 1,252 1,231 1,211 1,191 1,172 1,153 1,134 1,116 1,098 1,081 1,064 1,048 1,031 1,016 1,000 1,286 585 1,264 1,242 Bond Prices by Discount Rate, Coupons 0-10% 2,000 497 E 1,500 1,000 5 500 5 5 0 0.00% 0.50% 1.00% 9.00% 9.50% 10.00% 5 5 523 519 508 : Uuu TOU JUU 581 570 643 631 705 693 767 754 045 829 816 891 877 953 939