US Treasury Notes and Bonds are more complicated than futures when simulating historical trades. This is because of several distinct features:
- They pay a coupon every 6 months. Therefore, it’s necessary to consider Accrued Interests and coupon payments into carry calculations.
- They can be used as collateral in the REPO market, which usually reduces the cost of financing a position. Depending on the credit worthiness of the borrower (and other factors) the banks might require a haircut.
- They are issued by the government regularly, and most of the liquidity gets transferred to the most recent issues (the “on-the-runs”). Therefore, a strategy that relies on liquidity will usually roll its positions forward whenever there is a new auction.
SAMOA can take care of all these calculations for you automatically. Let’s look at an example.
Say you make the following trades:
| Trade ID | Date | Time | Face Value | Note | Price (32nds) |
| 1 | 7/13/1995 Thu | 11:00:00 AM | $100,000,000 | T 7.875 08/15/2001 | 109.304 |
| 2 | 9/8/1995 Fri | 1:00:00 PM | -$100,000,000 | T 7.875 08/15/2001 | 108.180 |
Using the Excel API, we can calculate the following table
| Trade ID | Settlement | Price (Dec) | Accrued Interest | Dirty Price |
| 1 | 7/14/1995 Fri | 109.953125 | 3.24 | 113.194492403315 |
| 2 | 9/11/1995 | 108.5625 | 0.58 | 109.140285326087 |
| API calls ==> | ssPLInfo_GetSettlement | ssDisplayToPrice | ssTsyAccrued |
Manual Calculations
- Let’s get the generic code for that note on 7/14:
select dbo.ssCodeToGeneric('T 7.875 08/15/2001', '7/13/1995', 'Auction+1', '10Y') as 'Generic'
We can see that the “T 7.875 08/15/2001” is the 11th old 10Y on 7/14.
- Also note that there are 59 days between settlements (7/14 to 9/11), and a coupon is paid on 8/15.
select dbo.ssPLInfo_CalculateTsyCoupons('T 7.875 08/15/2001', '7/14/1995', '9/11/1995') as 'Coupon'
- The P&L resulting from the change in dirty price only is (109.140285326087-113.194492403315)/100 *100000000 = -4,054,207.08
- On 7/14 we’ll have to pay the invoice amount of 113.194492403315*100000000/100 = $113,194,492. We’ll finance this in the repo market.
- The offer side of the repo curve on 7/13 is (for now we’ll use the General Collateral curve):
select * from dbo.ssUSGCCurve_Get_rates('7/13/1995', 1) --note: second parameter 0=bid, 1=offer
- The rate from 7/14 to 9/11 is:
select dbo.ssUSGCCurve_MMkt_DS('7/13/1995',1,'7/14/1995','9/11/1995',100,NULL,1,7,2,7)* 100 AS 'Rate'
- Therefore the cost of borrowing the invoice amount will be 5.70%/360 *113194492.403315*59 = $1,057,663.92
- The coupon payment on 8/15 is 3.9375/100 * 100000000 = $3,937,500
- Therefore, the financing cost will amount to: 3937500-1057663.91814718 = $2,879,836.08
- And the final P&L is: = 4054207.07722796+2879836.08185282 = - $1,174,371.00
But that’s all manual. How can we simulate all this automatically with SAMOA.
Automatic Calculations
- Let’s calculate the book first, and store it in the variable @Notes_SimpleLongWithCouponDrop_TradingBook_Xml (for more information on the ssBookXml function, please read this):
declare @Notes_SimpleLongWithCouponDrop_XmlCalls_Inline table (
Seq bigint IDENTITY(1,1), TimeS datetime, XmlCall nvarchar(max)
);
insert into @Notes_SimpleLongWithCouponDrop_XmlCalls_Inline (TimeS, XmlCall) VALUES
('7/13/1995 11:00:00', '<Trade TimeS="7/13/1995 11:00:00" Instrument="T 7.875 08/15/2001" Q="100000000" P="109.953125" Comment="Open(Wed)" />' ),
('7/13/1995', '<Mark DateS="7/13/1995" Instrument="T 7.875 08/15/2001" P="109.953125" />' ),
('9/8/1995 1:00:00 PM', '<Trade TimeS="9/8/1995 1:00:00 PM" Instrument="T 7.875 08/15/2001" Q="-100000000" P="108.5625" Comment="Close (Thu)" />' ),
('9/8/1996', '<Mark DateS="9/8/1996" Instrument="T 7.875 08/15/2001" P="108.5625" />' ),
('9/11/1996', '<Mark DateS="9/11/1996" Instrument="T 7.875 08/15/2001" P="104.828125" />' );
declare @Notes_SimpleLongWithCouponDrop_XmlCalls table ([Seq] bigint,[XmlCall] nvarchar(1024));
insert into @Notes_SimpleLongWithCouponDrop_XmlCalls
select * from dbo.ssXmlCalls(
(select TS.* from @Notes_SimpleLongWithCouponDrop_XmlCalls_Inline TS FOR XML AUTO, xmlschema('type'), root ('ROOT'), TYPE),
NULL,NULL,NULL,NULL
);
declare @Notes_SimpleLongWithCouponDrop_TradingBook_Xml xml;
select @Notes_SimpleLongWithCouponDrop_TradingBook_Xml = dbo.ssBookXml(
(select TS.* from @Notes_SimpleLongWithCouponDrop_XmlCalls TS FOR XML AUTO, xmlschema('type'), root ('ROOT'), TYPE)
);
select @Notes_SimpleLongWithCouponDrop_TradingBook_Xml
- Now let’s extract a daily P&L report from @Notes_SimpleLongWithCouponDrop_TradingBook_Xml:
select
MarkDay.value('@DateS', 'datetime') as DateS,
MarkDay.value('@Realized', 'decimal(38,10)') as Realized,
MarkDay.value('@AccRealized', 'decimal(38,10)') as AccRealized,
MarkDay.value('@AccUnrealized', 'decimal(38,10)') as AccUnrealized,
MarkDay.value('@Income', 'decimal(38,10)') as Income,
MarkDay.value('@AccIncome', 'decimal(38,10)') as AccIncome,
MarkDay.value('@AccPL', 'decimal(38,10)') as AccPL,
MarkDay.value('@DeltaPL', 'decimal(38,10)') as DeltaPL
from @Notes_SimpleLongWithCouponDrop_TradingBook_Xml.nodes('/Book/MarkMonth/MarkDay') MarkDays(MarkDay);
- As you can see we arrive to the same result, but SAMOA took care of everything. We only had to input the trades.
[...] Treasury Carry Calculations [...]