The coupon rate on bonds is simply the interest that it pays each year, expressed as a percentage of the nominal value of the bonds. Nominal value the nominal value of the bonds or the amount the organization-the Issuer must pay the holder of bonds after the maturity. All bonds have a clearly stated coupon rate in percent. However, the calculation of the coupon rate using Excel-it’s easy if you have the coupon the payment amount and the nominal value of the bonds.
The formula for the coupon rate is the total annual coupon payment divided by par value. Some bonds pay interest every six months or quarterly, so it is important to know how many coupon payments per year your bond creates.
In Excel, enter coupon payment in cell A1. In cell A2, enter the number of coupon payments you receive each year. If the bond pays interest once a year, enter 1. If you get payments in six months, enter 2. Enter 4 for a bond that pays quarterly. In cell A3, enter the formula =A1x A2 to yield the total annual coupon payment.
Moving down the table, enter the nominal value of your bonds in cell B1. Most bonds with a nominal value of $100 or $1000, although some municipal bonds Pars $ 5,000. In cell B2, enter the formula “=A3/B1” to receive an annual coupon rate of your bond in decimal form.
Finally, select cell B2 and press Ctrl+shift+% apply percentage formatting.
For example, if a bond has a par value of $1,000, and creates each year two of $30 coupon payments coupon rate ($30 x 2) ÷ $ 1,000 range, or 0.06. After the format of the cell is adjusted, the formula provides a return of 6%.