Categories: Asset Liability Management Convexity & Duration Editors Choice EXCEL Examples Fixed Income Portfolio Risk

4 mins read# Convexity & Duration calculator for US Treasury Bills, Notes and Bonds.

## Bond Duration Calculation for US T-Bills

### Macaulay Duration

### Modified Duration

### Effective Bond Duration

### Convexity

### % Change is Price

## Bond Duration, convexity calculation for Treasury Notes Bonds

### Macaulay Duration

To demonstrate how Duration and Convexity are calculated for specific US Treasuries we select instruments from recent US Treasury bill, note and bond auctions. Please note that we are determining these metrics (Convexity & Duration) at issue. We will calculate Macaulay, Modified and Effective Duration as well as Convexity for the selected Treasury issues.

You can use the approach to build your own Excel calculator.

You may first want to review our course on **Duration & Convexity** to become familiar with the formulas & mechanics of the calculations before proceeding.

Prices & Yields

According to the Federal Reserve Bank of New York, T-Bills “*are quoted at a discount from face value, with the discount expressed as an annual rate based on a 360-day year*“. The price (per $100) at issue date therefore is calculated as 100*(1-T/360*0.14%) where T is the number of days between the maturity date and the issue date, in this case 364 days.

So we have 100/(1-364/360*0.14%) = 99.858444

As you can see this tallies with the price mentioned in the auction results.

The investment rate is the coupon equivalent yield. This yield is the annualized percentage return that the purchaser will receive if the note is purchased on the day of the quotation at the ask price and held until maturity. Given the redemption value of the bill is $100, and the ask price is 99.858444 the coupon equivalent yield (i%) is calculated as:

99.858444*(1+i%*364/365) = 100

**Therefore i% = (100/99.858444-1)*365/364 = 0.142%**

Macaulay Duration is the weighted average time to maturity where the weights are the present values of future cash flows. In the case of a T-Bill there is only one cash flow that is due on the maturity date, the present value of this cash flow on the issue date therefore is equal to the price of the T-Bill. Hence the Macaulay Duration for a T-Bill will be equal to the time to maturity (expressed in years), in this instance 364/365 =0.9973.

The same result for Macaulay Duration may be obtained using the EXCEL’s DURATION function with settlement date = issue date = 10-Jan-2013, maturity date = 9-Jan-2014, Rate = 0% (as bills are zero coupon instruments), Yield = 0.142%, frequency = 1 and basis (day count convention) =3 (i.e. Actual/365).

Modified Duration may be obtained by making an adjustment to Macaulay Duration. In particular Modified Duration = Macaulay Duration/(1+yield %/frequency) = 0.9973/(1+0.142%/1) = 0.99584.

Again, the result may be obtained directly in EXCEL using the function MDURATION with settlement date = issue date = 10-Jan-2013, maturity date = 9-Jan-2014, Rate = 0% (as bills are zero coupon instruments), Yield = 0.142%, frequency = 1 and basis (day count convention) =3 (i.e. Actual/365).

Effective Duration is a measure of the interest rate sensitivity of the price of the instrument to small changes in the interests. It is given by:

(P_{–} – P_{+})/(2*P_{0}*(absolute change in yield%))

where P_{– }is the price of the bill if the yield rate were to decline by a small amount (in our example 0.10%) and P_{+} is the price of the bill if the yield rate were to increase by the same small amount. P_{0} is the price of the bill at the current yield%. The current yield as mentioned earlier is 0.142% so given the % change in yields, the increased yield is 0.242% and the decreased yield is 0.042%.

The prices have been calculated using the coupon equivalent yields as follows:

100/(1+i%*364/365).

With effective duration calculated as given in the image below:

The effective duration therefore works out to 0.9958.

Duration ignores the curvature of the price-yield function. It may therefore over- or under-estimate the price change when interest rates change because it presumes that prices will be impacted by the same extent whether interest rates increase or decrease. The convexity measure aims to correct for this deficiency in the duration metric. Effective convexity is calculated as follows:

(P_{–} + P_{+}-2*P_{0})/(2*P_{0}*(absolute change in yield%^2)) , where prices are defined as mentioned earlier for effective duration.

The effective convexity therefore works out to 0.9917.

What is the % change in price if interest rates increase by 0.10%? The answer is given by the following formula:

-Duration * change in yield% + Convexity *(change in yield%^2)

Note in this instance we are taking the actual change in yield and not its absolute value.

An increase of 0.10% in the yield rate would cause prices to decline by 0.09949%. While a 0.10% decline in yields would result in a price increase of 0.09968%.

The price for the 5-year Note determined above may be calculated in EXCEL using the PRICE function with settlement date = issue date = 31-Jan-2013, maturity date = 31-Jan-2018, Rate = 0.875%, Yield =0.889%, Frequency =2 (for semiannual coupon payments) and day count convention =1 (Actual/Actual).

The price may also be determined using first principles, by stripping each future coupon and principal cash flow and discounting each of these to the issue date using the yield rate.

The same methodology mentioned for notes in this section may be used to determine the price, duration, convexity & sensitivity for the 30-year Treasury Bond.

According to the Federal Reserve Bank of New York, “*note and bond prices are quoted in dollars and fractions of a dollar … [where the] fraction used for Treasury security prices is 1/32*“.

To convert these prices into the quoting convention we may use the following approach:

Where, 99.298138 in quoting convention is equivalent to $99.931681 (=99+29.8138/32).

Notes and Bonds have periodic coupon payments. To calculate the Macaulay duration first strip each payment and calculate its present value to the settlement date (in this case the issue date). Multiply the each present value with its corresponding time to maturity in years. Sum the derived products and divide this sum by the total of the present values (i.e. the price). The resulting figure is the Macaulay Duration – the weighted average time to maturity (in years) with weights as these present values.

Macaulay Duration using EXCEL’s DURATION function will also give the same answer without requiring the user to calculate the present values of each cash flow [with settlement date = issue date = 31-Jan-2013, maturity date = 31-01-2018, Rate = 0.875%, Yield =0.889%, Frequency =2 and day count convention =1 (Actual/Actual)].

The methodologies for Modified Duration, Effective Duration and % change in price (sensitivity) are similar to what was done for bills. For Modified Duration calculated using EXCEL MDURATION function use settlement date = issue date = 31-Jan-2013, maturity date = 31-01-2018, Rate = 0.875%, Yield =0.889%, Frequency =2 and day count convention =1 (Actual/Actual).

Download the Duration and Convexity for US Treasury Bill, Note and Bond EXCEL file for free!

*Check out our new book – Portfolio Optimization Models in Excel, Revised Edition – 227 pages, Excel templates and dataset included.*

Published by

February 28, 2013 11:54 am

What is the best way of evaluating portfolio performance allocation strategies? Should we just compare risk, return or risk adjusted…

February 5, 2019 12:51 pm

6 mins read Introducing Project Plain speak. Currently a work in progress Plain speak focuses on bringing intelligent financial reporting…

January 22, 2019 6:08 am

9 mins read What can we learn about oil markets from the last ten years? The next decade. The final…

January 19, 2019 3:47 pm

5 mins read What does the data say about future direction of crude oil markets. We look at OPEC spare…

January 19, 2019 12:44 pm

5 mins read What factors would impact crude oil supply side equation in 2019? Russian non compliance, lower breakeven for…

January 19, 2019 5:14 am

5 mins read How would you model demand for crude oil? What are the key components? What is the long…

January 18, 2019 4:46 pm

This website uses cookies.