< Return to Fixed Income

Bond Valuation: Coupon Pricing Adjustments

Education Hero Image
Textbook Relatable Excel User Guide

Guide: Pricing Bonds with Imminent Coupon Payments Using Microsoft Excel

Introduction:

Bonds play a crucial role in the financial world, providing a steady income stream through coupon payments. When the next coupon payment is due in less than six months, pricing these bonds requires specific adjustments. This guide will help you use Microsoft Excel to calculate bond prices with imminent coupon payments.

Step 1: Understanding the Formula:

The pricing formula for bonds with upcoming coupon payments is essential for accurate valuation. Let's break down the formula for easy Excel implementation:

Bond Price = (C / (1 + r)^t) + (C / (1 + r)^(t+1)) + ... + (C / (1 + r)^(t+n-1)) + (F / (1 + r)^(t+n-1))
 

Where:

  • C = Coupon payment
  • r = Required rate of return or discount rate
  • t = Time remaining until the next coupon payment
  • n = Number of remaining coupon payments
  • F = Face value or principal payment at maturity

Step 2: Excel Implementation - Example 1:

Let's take the first example:

  • F (Face value) is in cell A2: $1,000
  • C (Coupon payment) is in cell B2: $40
  • r (Required rate of return) is in cell C2: 6%
  • t (Time remaining until the next coupon payment) is in cell D2: 3/12
  • n (Number of remaining coupon payments) is in cell E2: 4

In Excel, you can use the following formula to calculate the bond price:

=($B$2 / (1 + $C$2) ^$D2) + ($B$2 / (1 + $C$2) ^($D2 + 1)) + ($B$2 / (1 + $C$2) ^($D2 + 2)) + ($B$2 / (1 + $C$2) ^($D2 + 3)) + ($A$2 / (1 + $C$2) ^($D2 + 3))

Step 3: Excel Implementation - Example 2:

Now, for the second example:

  • F (Face value) is in cell A3: $5,000
  • C (Coupon payment) is in cell B3: $250
  • r (Required rate of return) is in cell C3: 4.5%
  • t (Time remaining until the next coupon payment) is in cell D3: 2/12
  • n (Number of remaining coupon payments) is in cell E3: 6

Use the following formula in Excel (it is best to copy and paste!):

=($B$3 / (1 + $C$3) ^$D3) + ($B$3 / (1 + $C$3) ^($D3 + 1)) + ($B$3 / (1 + $C$3) ^($D3 + 2)) + ($B$3 / (1 + $C$3) ^($D3 + 3)) + ($B$3 / (1 + $C$3) ^($D3 + 4)) + ($B$3 / (1 + $C$3) ^($D3 + 5)) + ($A$3 / (1 + $C$3) ^($D3 + 5))

Step 4: Conclusion:

Pricing bonds with imminent coupon payments in Excel is simplified by understanding and implementing the formula using cell references. This Excel guide enables you to calculate bond prices efficiently, facilitating accurate valuation and investment decision-making in the financial world.

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