Bond Valuation: Pricing Coupon Bonds

Coupon Bond Pricing for Microsoft Excel Users

Step 1: Open Microsoft Excel

Open Microsoft Excel on your computer.

Step 2: Create a New Worksheet

Set up the following columns in the worksheet:

• Column A: Period
• Column B: Cash Flow
• Column C: Discount Factor
• Column D: Present Value

Step 3: Input Bond Characteristics

Enter the following details:

• Cell A1: Period 0
• Cell A2: Period 1
• Cell B1: Face Value (F)
• Cell B2: Coupon Rate (C)
• Cell B3: Required Rate of Return (r)
• Cell B4: Maturity (n)
• Cell B5: Coupon Frequency (e.g., 2 for semi-annual)

In Row 2, input the specific values for your coupon bond:

• Cell C1: 1000 (assuming a face value of $1000)
• Cell C2: =B1(B2/B5)* (calculating the semi-annual coupon payment)
• Cell C3: 0.08 (assuming an 8% annual required rate of return)
• Cell C4: 5 (assuming a 5-year maturity)
• Cell C5: 2 (assuming semi-annual coupon payments)

Step 4: Perform Calculations

• Cell A3 (Period 0): =0
• Cell A4 (Period 1): =A3+1
• Cell B4: =IF(A4<=$B$4*B$5, $C$2, $C$1)
• Cell C4: =1/(1+$C$3/B$5)^A4
• Cell D4: =B4*C4

Drag these formulas down for subsequent periods to complete the table.

Step 5: Summation

In Cell D5, sum the present values:

=SUM(D4:D$X)

Replace X with the total number of periods.

Step 6: Calculate Bond Price

In a new cell (e.g., E5), calculate the bond price:

=D5

Now you have a practical example to calculate the price of a coupon bond using Microsoft Excel, considering a specific bond's characteristics. Feel free to change the values in B1, B2, B3, B4, and B5 to explore different scenarios and understand the impact on bond pricing.

This article takes inspiration from a lesson found in FIN 4243 at the University of Florida.