My Work

Computer Excel Assignment:

Using the information in P17-5B complete parts 1 and 2 via an Excel spreadsheet. (1) Calculate the issue price by using the PV feature and building a table such as the one below. (2) Then take the information and fill in the Bond Amortization Table provided.

Example of the PV feature in Excel:

Using the PV function in Excel, you can quickly calculate the issue price of a bond. Below is an example assuming a $1,000 8% bond with a 3-year maturity paying interest twice a year. Assuming that your required return for the bond is 9.5% per year, what is the most that you would be willing to pay for this bond?

We can calculate the present value of the cash flows using the PV() function, but we first need to set up our worksheet. Open a new workbook, and then duplicate the worksheet presented below:

Note that I have set up the data using annual values for the coupon rate, required return, and term to maturity. I have also included a cell (B6) that provides a place to specify the number of payments per year. This way, we can set up the formula without making assumptions regarding the payment frequency, which adds some flexibility since not all bonds pay semiannually. To calculate the value of the bond, in B8, we use the PV function:

=-PV(B4/B6,B5*B6,B3/B6*B2,B2)

Take notice of the "-" in front of the function. If I didn't put that there, then the function would have returned a negative value. Technically, that would be correct because you would have to pay (a cash outflow) that amount. However, we tend to think in terms of positive dollars, not negative. Also, note that the required return and annual payment are converted in the function to semiannual values by dividing by the payment frequency. Similarly, the number of years to maturity is converted to the number of semiannual periods by multiplying by the payment frequency. The value of the bond is $961.63.