A:

You can create a yield curve in Microsoft Excel if you are given the time to maturities of bonds and their respective yields to maturity. The yield curve depicts the term structures of interest rates for bonds and the term structures could be normal, inverted or flat.

The shape of a yield curve indicates where future interest rates are headed. The x-axis of the graph of a yield curve is reserved for the time to maturity, while the yield to maturities are located on the y-axis.

Assume you want to plot the yield curve for the two-, five-, 10-, 20- and 30-year U.S. Treasury bonds (T-bonds). The respective yield to maturities of the U.S.T-bonds are 2.5%, 2.9%, 3.3%, 3.60% and 3.9%.

Now Follow These Five Steps:

  1. Using Microsoft Excel, enter "U.S. Treasury Bonds' Times to Maturity" in cell A1 and "U.S. Treasury Bond's Yields to Maturity" in cell B1.
  2. Next, enter "2" into cell A2, "5" into cell A3, "10" into cell A4, "20" into cell A5, and "30" into cell A6.
  3. Then, enter "2.5%" into cell B2, "2.9%" into cell B3, "3.3%" into cell A4, "3.6%" into cell A5, and "3.9%" into cell A6.
  4. Select cells A2 through A6 and B2 through B6 together and click on Insert. Under the Charts tab, select Scatter and click on Scatter with Smooth Lines and Markers.
  5. Next, click on the chart, select Chart Elements and click on Axis Titles. For the horizontal axis, enter "Time to Maturity (In Years)" and "Yields" into the vertical axis title. Enter "U.S. Treasury Bonds Yield Curve" into the Chart Title.

The resulting yield curve for these U.S. T-bonds is considered normal because it is concave down (increasing), and the rates are increasing as the times to maturity are further out.

RELATED FAQS
  1. How do you calculate return on equity (ROE) in Excel?

    Find out more about return on equity, the formula to calculate ROE and how to calculate this measure of a company's profitability ... Read Answer >>
  2. How can I calculate a bond's coupon rate in Excel?

    Find out how to use Microsoft Excel to calculate the coupon rate of a bond using its par value and the amount and frequency ... Read Answer >>
  3. Treasury Bond vs Treasury Note vs Treasury Bill

    Understand the types of securities the government issues. Learn the difference between Treasury notes (T-notes), Treasury ... Read Answer >>
  4. How do you calculate a payout ratio using Excel?

    Learn what the payout ratio is, what dividends per share and earnings per share are, and how the payout ratio is calculated ... Read Answer >>
  5. How is the interest rate on a treasury bond determined?

    Explore the difference between interest rates and bond coupons, what determines current yield on debt instruments, and why ... Read Answer >>
Related Articles
  1. Investing

    Bond yield curve holds predictive powers

    This measure can shed light on future economic activity, inflation levels and interest rates.
  2. Insights

    U.S. Recession Without a Yield Curve Warning?

    The inverted yield curve has correctly predicted past recessions in the U.S. economy. However, that prediction model may fail in the current scenario.
  3. Investing

    One Thing The Yield Curve Says About Stocks

    Contrary to media hype, a flattening yield curve does not mean a recession is coming soon.
  4. Financial Advisor

    Top 4 Treasurys ETFs (SHY, IEI)

    Learn about the specifics of the top four U.S. Treasury ETFs and how investors can buy ETFs that invest in bonds along the yield curve.
  5. Insights

    Is a Recession Coming? Watch the Yield Curve

    The yield curve can be an indicator of which way the economy is heading.
  6. Investing

    Understanding the Different Types of Bond Yields

    Any investor, private or institutional, should be aware of the diverse types and calculations of bond yields before an actual investment.
  7. Investing

    TLT: Why Low Interest Rates Are Dangerous for Investors

    Explore how the rally in long-dated Treasury bonds has escalated the risks in ETFs such as the iShares iBoxx $ Investment Grade Corporate Bond ETF.
  8. Investing

    Find the Right Bond at the Right Time

    Learn about the types of bonds you should consider investing in, when you should be buying them and how to compare yields against their time to maturity.
  9. Trading

    Improve your investing with Excel

    Find out how to use Excel, a useful tool for assisting with investment organizations and evaluations.
RELATED TERMS
  1. Term Structure Of Interest Rates

    The term structure of interest rates is the relationship between ...
  2. Curve Steepener Trade

    Curve steepener trade is a strategy that uses derivatives to ...
  3. Inverted Yield Curve

    An inverted yield curve is the interest rate environment in which ...
  4. Humped Yield Curve

    A humped yield curve is a relatively rare type of yield curve ...
  5. Flat Yield Curve

    The flat yield curve is a yield curve in which there is little ...
  6. Treasury Note

    A treasury note is a marketable U.S. government debt security ...
Trading Center