Bond coupon rate calculation

Most bonds issued pay periodic interest payments, those of the bonds that are issued in the U.S. make two interest payments per year whereas most bonds issued in Europe make only a single annual interest payment. When bonds are issued at par, the coupon rate and the YTM - yield to maturity is the same. If the coupon rate is lower than the interest rates the bond is issued at a discount, when the interest rates are lower than the coupon rate the bond is issued at a premium. Finding the coupon rate is possible by rearranging the bond price equations which happens to be the sum of discounted interest payments and the discounted par value. This coupon rate calculator accepts the par value, market price of the bond, yield to maturity and the remaining years to maturity to find the coupon rate. It returns a detailed solution showing you all the steps that were taken to find the coupon rate however if you are only interested in knowing only the coupon rate then have a look at some of the tools that are listed below.

Using tadXL function such as tadBCouponRate finding coupon rate on bonds in Excel 2007, 2010 and 2013 becomes really easy. Let us now briefly look at the financial functions in tadXL to find coupon rate using Excel:

=bondCouponRate ( ytm, years_to_maturity, bond_price, par_value, compounding )

Data input

Data output

Compounding = semi annually
Par Value = 1000
Market Rate = 9%
Market Price = 1100
N = 20

Coupon Rate Formula

Coupon Rate = 2[ (Market Price - Par Value x PVIF(ytm%, n) ) / ( Par Value x PVIFA(ytm%, n) ) ]

PVIFA(0.045, 20) = 13.007936451454
PVIF(0.045, 20) = 0.41464285968458

Coupon Rate Calculation

Coupon Rate = 2[ (1100 - 1000 x 0.41464285968458 ) / ( 1000 x 13.007936451454 ) ]
Coupon Rate = 2[ 1100 - 414.64285968458 ] / 13007.936451454
Coupon Rate = 2[685.35714031542 / 13007.936451454]
Coupon Rate = 2[0.052687614432405]
Coupon Rate = 10.54%