A bond's coupon rate is simply the rate of interest it pays each year, expressed as a percentage of the bond's par value. The par value is the bond's face value, or the amount the issuing entity must pay the bondholder once the bond matures. Most bonds have a clearly stated coupon rate percentage. However, calculating the coupon rate using Microsoft Excel is simple if all you have is the coupon payment amount and the par value of the bond.
The formula for the coupon rate is the total annual coupon payment divided by the par value. Some bonds pay interest semiannually or quarterly, so it is important to know how many coupon payments per year your bond generates.
In Excel, enter the 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 receive payments semiannually, enter 2. Enter 4 for a bond that pays quarterly. In cell A3, enter the formula =A1*A2 to yield the total annual coupon payment.
Moving down the spreadsheet, enter the par value of your bond in cell B1. Most bonds have par values of $100 or $1,000, though some municipal bonds have pars of $5,000. In cell B2, enter the formula "=A3/B1" to yield the annual coupon rate of your bond in decimal form.
Finally, select cell B2 and hit CTRL+SHIFT+% to apply percentage formatting.
For example, if a bond has a par value of $1,000 and generates two $30 coupon payments each year, the coupon rate is ($30 * 2) / $1,000, or 0.06. Once the cell format is adjusted, the formula yields a return rate of 6%.

How do debit spreads impact the trading of options?
Find out what it means when a bond has a coupon rate of zero and how a bond's coupon rate and par value affect its selling ... Read Answer >> 
How does the money from the interest on my bond get to me?
When you buy a regular coupon bond, you are entitled to a coupon, which is typically paid at regular intervals, and the face ... Read Answer >> 
How do I use the holding period return yield to evaluate my bond portfolio?
Find out how to use the holding period return yield formula to evaluate the performance of bonds in your portfolio, and view ... Read Answer >>

Investing
Comparing Yield To Maturity And The Coupon Rate
Investors base investing decisions and strategies on yield to maturity more so than coupon rates. 
Financial Advisor
Calculate PV of different bond type with Excel
To determine the value of a bond today — for a fixed principal (par value) to be repaid in the future — we can use an Excel spreadsheet. 
Investing
The Pros & Cons Of Using Coupons For Your Business
Coupons can drive business to your store – you just need to make sure it's profitable business. Here are strategies that work. 
Personal Finance
6 Tricks To Make Coupons Work For You
Use these strategies to counteract the stores' and manufacturers' coupon tactics and come out ahead. 
Investing
Simple Math for FixedCoupon Corporate Bonds
A guide to help to understand the simple math behind fixedcoupon corporate bonds. 
Managing Wealth
How Bond Prices and Yields Work
Understanding bond prices and yields can help any investor in any market. 
Personal Finance
Couponing 101: Dos and Don'ts
Coupon clipping isn't new, but it has gone digital. Find out how to use coupons to your advantage today. 
Investing
4 basic things to know about bonds
Learn the basic lingo of bonds to unveil familiar market dynamics and open to the door to becoming a competent bond investor.

Current Coupon
A current coupon refers to a security that is trading closest ... 
Bond Valuation
Bond valuation is a technique for determining the theoretical ... 
Bond Discount
Bond discount is the amount by which the market price of a bond ... 
Coupon Equivalent Yield (CEY)
The coupon equivalent yield (CEY) is used to calculate the yield ... 
ZeroCoupon Bond
A zerocoupon bond is a debt security that doesn't pay interest ... 
Effective Yield
The effective yield is the yield of a bond which has its coupons ...