A:

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 semi-annually 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 semi-annually, 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%.

RELATED FAQS
  1. What is the most common solvency ratios used in fundamental analysis?

    Learn about the difference between a bond's coupon rate and its yield rate, how the coupon rate influences market price and ... Read Answer >>
  2. 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 >>
  3. When is a bond's coupon rate and yield to maturity the same?

    Find out when a bond's yield to maturity is equal to its coupon rate, and learn about the components of bonds and how they ... Read Answer >>
  4. If I buy a $1,000 bond with a coupon of 10% and a maturity in 10 years, will I receive ...

    Yes, fixed-income investors can expect semi-annual payments – if the bond or debt instrument is held until maturity, and ... Read Answer >>
  5. What is the relationship between current yield and yield to maturity (YTM)?

    Learn about the relationship between a bond's current yield and its yield to maturity, including how the market price of ... Read Answer >>
Related Articles
  1. Personal Finance

    Coupon Shopping: Clip Your Way To Savings

    Use coupons strategically to score big savings on everyday purchases.
  2. 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.
  3. 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.
  4. Personal Finance

    6 Sneaky Ways Coupons Make You Spend More

    If you're hoping to save money by using coupons, watch out for sellers' strategies.
  5. Investing

    Why Do Companies Print Coupons?

    Coupons save customers money, but there's also a lot in it for the company.
  6. Managing Wealth

    How Bond Prices and Yields Work

    Understanding bond prices and yields can help any investor in any market.
RELATED TERMS
  1. Bond Valuation

    Bond valuation is a technique for determining the theoretical ...
  2. Coupon

    A coupon is the annual interest rate paid on a bond, expressed ...
  3. Par Value

    Par value is the face value of a bond, or for a share, the stock ...
  4. Zero-Coupon Bond

    A zero-coupon bond is a debt security that doesn't pay interest ...
  5. Ex Coupon

    A bond or preferred stock that does not include the interest ...
  6. Effective Yield

    The effective yield is the yield of a bond which has its coupons ...
Hot Definitions
  1. Donchian Channels

    A moving average indicator developed by Richard Donchian. It plots the highest high and lowest low over the last period time ...
  2. Consumer Price Index - CPI

    A measure that examines the weighted average of prices of a basket of consumer goods and services, such as transportation, ...
  3. Moving Average - MA

    A moving average (MA) is a widely used indicator in technical analysis that helps smooth out price action by filtering out ...
  4. Stop Order

    A stop order is an order to buy or sell a security when its price increases past a particular point in order to limit losses ...
  5. Inflation

    The rate at which the general level of prices for goods and services is rising and, consequently, the purchasing power of ...
  6. Candlestick

    A chart that displays the high, low, opening and closing prices for a security for a single day. The wide part of the candlestick ...
Trading Center