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.