A:

In the bond market, convexity refers to the relationship between price and yield. When graphed, this relationship is non-linear and forms a long-sloping U-shaped curve. A bond with a high-degree of convexity will experience relatively dramatic fluctuations when interest rates move.

While there is no bond convexity function in Microsoft Excel, it can be approximated through a multi-variable formula.

Simplifying Convexity Formulas

The standard convexity formula involves a time series of cash flows and rather complicated calculus. This cannot be easily replicated in Excel, so a simpler formula is necessary:

Convexity = ((P+) + (P-) - (2Po)) / (2 x ((Po)(change in Y)²)))

Where (P+) is the bond price when the interest rate is decremented, (P-) is the bond price when the interest rate is incremented, (Po) is the current bond price and the "change in Y" is the change in interest rate represented in decimal form. The "change in Y" can also be described as the bond's effective duration.

This may not seem simple on the surface, but this is the easiest formula to use in Excel.

How to Calculate Convexity in Excel

To calculate convexity in Excel, begin by designating a different pair of cells for each of the variables identified in the formula. The first cell acts as the title (P+, P-, Po and Effective Duration), and the second carries the price, which is information you have to gather or calculate from another source.

Suppose that (Po) value is in cell C2, (P+) is in C3 and (P-) is in C4. The effective duration is in cell B5.

In a separate cell, enter the following formula: =(C3 + C4 - 2*C2) / (2*C2*(B5^2))

This should provide the effective convexity for the bond. A higher result means that the price is more sensitive to changes in interest rates.

RELATED FAQS
  1. How to calculate a bond's modified duration with Excel

    Understand more about the modified duration in fixed income securities and discover how to calculate a bond's modified Macaulay ... Read Answer >>
  2. What is the formula for calculating free cash flow in Excel?

    Find out more about free cash flow, the formula for calculating free cash flow and how to calculate a company's free cash ... Read Answer >>
  3. 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 >>
  4. How do I calculate yield in Excel?

    Learn about yield as it pertains to bonds and how to calculate this prospective valuation of an investment's total return ... Read Answer >>
Related Articles
  1. Investing

    Use duration and convexity to measure bond risk

    Find out how duration and convexity measures can help fixed-income investors manage risks such as interest rate sensitivity within their portfolios.
  2. Investing

    Treasuries Rout May Deepen on Mortgage Hedging

    Fed rate hikes may spur mortage bond owners to sell Treasuries to reduce their exposure to rising rates
  3. Trading

    Improve your investing with Excel

    Find out how to use Excel, a useful tool for assisting with investment organizations and evaluations.
  4. Investing

    Why Investors Should Use Duration to Compare Bonds

    Duration is a helpful metric that determines a bond's sensitivity to interest rates.
  5. Investing

    Guide To Excel For Finance

    Formulas, functions and features you need to know when using Excel for financial analysis.
  6. Small Business

    The Importance of Excel in Business

    Microsoft Excel is an indispensable tool for any business.
  7. Personal Finance

    Schedule Loan Repayments With Excel Formulas

    Learn how to calculate all the particulars of a loan using Excel, and find out how to set up a schedule of repayment for a mortgage or any other loan.
  8. Investing

    How Rising Interest Rates and Inflation Affect Bonds

    Understand bonds better with these four basic factors.
RELATED TERMS
  1. Immunization

    Immunization is a strategy that matches the duration of assets ...
  2. Regulation P

    Regulation P is one of the regulations which addresses standards ...
  3. Accumulated Earnings and Profits

    Accumulated earnings and profits (E & P) are a corporation's ...
  4. Formula Investing

    Formula investing is a method of investing that rigidly follows ...
  5. Bond Equivalent Yield - BEY

    The bond equivalent yield is a calculation for restating semi-annual, ...
  6. Empirical Duration

    Empirical Duration is the calculation of a bond's duration based ...
Trading Center