1. Click cell B5. A formula will be constructed using two YEAR functions to calculate the terms of the bond using the Issuance Date and Maturity Date.
2. Start the YEAR function by typing an equal sign =, the function name year, and an open parenthesis. Click cell B4 and type a closing parenthesis.
3. Type a minus sign - followed by the function name year and an open parenthesis.
4. Click cell B3 followed by a closing parenthesis. Tap the ENTER key on your keyboard to complete the formula. The output of the function should be 10, indicating the bond terms are ten years.
5. Click cell C9. Enter a cell reference that displays the value in cell B7. This exercise will determine three potential values for a bond. The bond value will be determined based on the relationship between the market rate and the coupon rate. In the first scenario the market rate is equal to the coupon rate.
6. Enter a formula in cell C10 that adds .01 to the coupon rate in cell C7. This scenario will show the bond value when the market rate is 1% higher than the coupon rate.
7. Enter a formula in cell C11 that subtracts .01 from the coupon rate in cell C7. This scenario will show the bond value when the market rate is 1% lower than the coupon rate.
8. Click cell B9 and begin the PV function by typing an equal sign =, the function name pv, and open parenthesis.
9. Click cell C9 and then type a front slash / for division.
10. Click cell B6 and then tap the F4 key on your keyboard to add an absolute reference to this cell. The market rate will be used to define the rate argument of the PV function. Since this bond makes two payments per year, the market rate is divided by the value in cell B6, which is 2.
11. Type a comma to advance to the nper argument.
12. Click cell B5 and tap the F4 key on your keyboard to add an absolute reference to this cell.
13. Type an asterisk * for multiplication and then click cell B6. Tap the F4 key to add an absolute reference to this cell. The nper argument is being defined with the total number of interest payments that will be sent to the investor. Since the bond makes two payments per year and the bond term is ten years, cell B5 is multiplied by B6.
14. Type a comma to advance to the pmt argument.
15. Type a minus sign -, an open parenthesis, and click cell B7. Tap the F4 key to add an absolute reference to this cell.
16. Type a front slash / for division and then click cell B6. Tap the F4 key to add an absolute reference to his cell.
17. Type a closing parenthesis followed by an asterisk * for multiplication.
18. Click cell B2 and then tap the F4 key to add an absolute reference. The pmt argument is defined with the amount of interest that will be paid to the investor. Since there are two payments made per year, the coupon rate in cell B7 is divided by the number of payments in cell B6. This result is then multiplied by the face value of the bond in cell B2.
19. Type a comma to advance to the fv argument.
20. Type a minus sign -, click cell B2, and tap the F4 key to add an absolute reference to this cell. The fv argument is being defined with the face value of the bond in cell B2. This is the amount of money that will be returned to the investor at the end of the bond term.
21. Complete the function by typing a closing parenthesis and tap the ENTER key. The output of the function is $10,000, which is equal to the face value of the bond. This is because the coupon rate is equal to the market rate. In this scenario, the investor is required to lend $10,000 to the company and will receive $10,000 back the end of the bond term. This is called issuing a bond at par value.
22. Copy cell B9 and paste it into cell B10. The output of the function is $9,201.81, which is less than the face value of the bond. This is because the coupon rate is lower than the market rate. Investors will get a better return if they choose another bond at the market rate. Therefore, to fairly compensate investors, this bond will require investors to lend the company $9,201.81 but will return $10,000 to the investor at the end of the bond term. This gives the investor an additional $798.19 to compensate for other bonds that would have paid the investor more interest. This is called issuing a bond at a discount since the investor has to lend less than what will be returned at the end of the bond term.
23. Copy cell B10 and paste it into cell B11. The output of the function is $10,879.97, which is higher than the face value of the bond. This is because the coupon rate is higher than the market rate. Investors are getting a better deal from this bond compared to other bonds at the lower market rate. Therefore, to fairly compensate the company for paying investors more interest during the term of the bond compared to similar bonds at the lower market rate, this bond will require investors to lend the company $10,879.97 but will return $10,000 to the investor at the end of the bond term. This gives the investor $879.97 less than what was lent at the beginning of the bond term, but the investor receives higher interest payments during the bond term compared to similar bonds at the market rate. This is called issuing a bond at a premium since the investor has to lend more than what will be returned at the end of the bond term.
24. Protect the worksheet but allow users to edit the cell locations shaded in yellow. Begin by highlighting the range B2:B4 and click the Allow Edit Ranges button in the Review tab of the Ribbon.
25. Click the New button on the Allow Users to Edit Ranges dialog box.
26. Type Bond Details to enter a title for this range in the New Range dialog box and then click the OK button. Then, click the OK button on the Allow Users to Edit Ranges dialog box.
27. Define the range B6:B7 as a second range of cells a user can edit. Make the title for this range Payment Details.
28. Complete the protection by clicking the Protect Sheet button on the Review tab of the Ribbon. Then click the OK button on the Protect Sheet dialog box.
29. Save and close your workbook.

Issuing BondsFace Value$ 10,000Issuance Date3/1/2021Maturity Date3/1/2031Bond TermInterest Payments per Year2Coupon Rate3.50%Market RateBond Price at ParBond Price at DiscountBond Price at Premium