How Do I Calculate Convexity in Excel?

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.

Key Takeaways

  • Convexity is the relationship between price and yield and is non-linear and U-shaped (convex).
  • Bonds with high convexity experience large moves when interest rates move. 
  • There is no bond convexity function in Excel, but it can be approximated via a multi-variable formula. 
  • It is considered to be a better measure of interest rate risk than duration.

Bond Duration vs. Convexity 

Bond duration is the change in a bond’s price relative to a change in interest rates. A higher duration means a bond’s price will move to a greater degree in the opposite direction that interest rates move. If the duration is low, the bond will show less movement.

Convexity measures the sensitivity of the duration of a bond as rates change. Convexity is considered a better measure of interest rate risk. Duration assumes the relationship between bond prices and interest rates is linear, while convexity incorporates other factors, producing a slope.

Negative convexity occurs when a bond’s duration increases as rates increase. This means the bond price will fall by a greater rate if rates rise than if they had fallen. A bond has positive convexity if its duration rises and rates fall. 

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.
  • 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 an effective convexity for the bond. A higher result means that the price is more sensitive to changes in interest rates. Increasing convexity means the systemic risk a portfolio is exposed to increases.

Article Sources
Investopedia requires writers to use primary sources to support their work. These include white papers, government data, original reporting, and interviews with industry experts. We also reference original research from other reputable publishers where appropriate. You can learn more about the standards we follow in producing accurate, unbiased content in our editorial policy.
  1. Milken Institute. "An Introduction to Duration."

  2. New York University. "Duration and Convexity."

Take the Next Step to Invest
×
The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.
Service
Name
Description