{"text_format":"3","workbook":"xlsx","n_sheets":2,"sheets":["ScreenCapture","ConvFact"],"not_rendered":{"macros":1},"read":"whole","date_system":"1904"}

{"sheet":"ScreenCapture","index":0,"n_rows":46,"n_cols":14,"rows_kept":46}
[null,"This is a Bloomberg screen that I exported to Excel."]
[null,"To get to the March 2014 10-year contract, I type TYH4 <CMDTY>"]
[null,"   and then I choose DLV (Cheapest to Deliver)"]
[{"$nulls":5},"<CMDTY> is the yellow F9 key on the Bloomberg keyboard."]
[]
[]
[null,"The screen shows all notes that are deliverable against the March 2014 10-year Treasury note futures contract."]
[null,"It presents them in order from the Cheapest-to-Deliver to the Dearest-to-Deliver."]
[]
[null,"Note that the note prices, gross basis, and net basis are reported in 32nds."]
[]
[]
["The US Treasury started issuing 7-year notes on a monthly basis in February 2009.  Unlike 10- and 30-year notes, these mature on the last day of the month."]
["--- Therefore in the table, any notes that mature on a 15th are 10-year notes, whereas those maturing on last days in the month are 7-year notes."]
[null,"TYH4"]
[null,"US 10-Yr Note  (CBT) Mar14"]
[]
[null,"Cash Security","Price","Source","Conven. Yield","Conver. Factor","Gro/Bas (32nds)","Implied Repo%","Actual Repo%","Net/Bas (32nds)"]
[null,"Adjust Value",{"$nulls":8},"delmoy","matmoy","cpn","Conversion Factor from the user-defined function"]
["1)","T 2.625 11/15/20","102-21","BGN",2.203,0.8205,16.128,-0.056,0.072,0.8,32014,112020,0.02625,{"$f":"ConvFctr(M20,K20,L20)","v":0.8205351287461624}]
["2)","T 3.625 02/15/21","108-31","BGN",2.245,0.8697,22.115,-0.068,0.072,0.929,32014,22021,0.03625,{"$f":"ConvFctr(M21,K21,L21)","v":0.8696885435932639}]
["3)","T 2 11/30/20","98-13.25","BGN",2.251,0.7873,12.647,-0.116,0.072,1.122,32014,112020,0.02,0.7873008933287851]
["4)","T 1.75 10/31/20","96-31.25","BGN",2.233,0.774,19.634,-1.549,0.072,9.54,32014,102020,0.0175,0.7740071991618341]
["5)","T 2 09/30/20","98-23.75","BGN",2.203,0.7873,23.147,-1.863,0.072,11.625,32014]
["6)","T 3.125 05/15/21","105-11+","BGN",2.323,0.8376,34.502,-2.454,0.072,16.18,32014]
["7)","T 2.375 12/31/20","100-23.25","BGN",2.261,0.8012,31.269,-2.787,0.072,17.434,32014]
["8)","T 2.125 08/15/21","98-01.25","BGN",2.41,0.7748,50.447,-6.354,0.072,38.176,32014]
["9)","T 2 11/15/21","96-21.25","BGN",2.472,0.7612,60.629,-8.287,0.072,49.031,32014]
["10)","T 2 02/15/22","96-03.25","BGN",2.537,0.7549,67.728,-9.58,0.072,56.197,32014]
["11)","T 1.75 05/15/22","93-22.75","BGN",2.596,0.7331,78.08,-11.886,0.072,67.971,32014]
["12)","T 1.625 08/15/22","92-06","BGN",2.65,0.7185,87.496,-13.932,0.072,78.188,32014]
["13)","T 1.625 11/15/22","91-20.25","BGN",2.698,0.712,95.642,-15.453,0.072,86.276,32014]
["14)","T 2 02/15/23","94-06.75","BGN",2.725,0.7307,103.641,-16.063,0.072,92.101,32014]
["15)","T 2.5 08/15/23","97-18.75","BGN",2.789,0.7542,118.017,-17.425,0.072,103.504,32014]
["16)","T 2.75 11/15/23","99-12.75","BGN",2.82,0.7672,124.225,-17.832,0.072,108.132,32014]
["17)","T 1.75 05/15/23","91-19.75","BGN",2.778,0.7077,112.273,-18.309,0.072,102.156,32014]
[]
[]
[null,"Settle:","2014-01-22"]
[null,"First del date:","2014-03-03"]
[null,"Last del date:","2014-03-31"]
[]
[]
["The T-Note is identified by the issuer (T), coupon rate, and maturity date."]
["The data source BGN refers to Bloomberg generic."]
["The conven. yield is the ``conventional yield'' (to maturity) of the note."]

{"sheet":"ConvFact","index":1,"n_rows":122,"n_cols":11,"rows_kept":122}
["This worksheet shows the computation of the various cells on the Bloomberg DLV screen."]
[]
[]
[]
[0.02625,null,"2014-03-01","Contract",201403,3,2014,32014]
["2020-11-15",6,"n","number of whole years from first day of delivery month to note's maturity date."]
[202011,6,"z","number of whole months between n and note's maturity date rounded down to nearest quarter."]
[11,null,"v","= z","if z < 7",{"$nulls":5},{"$f":"RIGHT(A10,4)","v":"2020"}]
[2020,6,null,"   3","if z >= 7",{"$nulls":5},{"$f":"A10-K8","v":110000}]
[112020,null,"a",{"$f":"1/1.03^(B9/6)","v":0.970873786407767},{"$nulls":6},{"$f":"K9/10000","v":11}]
[{"$f":"ConvFctr(A5,H5,A10)","v":0.8205351287461624},null,"b",{"$f":"(A5/2)*(6-B9)/6","v":0}]
[null,0.8205351287461624,"c",{"$f":"1/1.03^(2*B6)","v":0.7013798801929733}]
[null,null,"d",{"$f":"(A5/0.06)*(1-D12)","v":0.1306463024155742},null,"2014-01-22"]
[]
["Conversion Factor:",{"$f":"D10*(0.5*A5+D12+D13)-D11","v":0.8205351287461626}," (Computed directly from the spreadsheet)."]
[null,0.820535128746162," (Using VBA Macro-- Subroutine CF()   )"]
[]
["Since there are many notes that are eligible to be delivered against the futures contract, the exchange uses a conversion factor to"]
["   put these notes on a more equal footing.  The idea behind the conversion factor is that we want to know what the note's price "]
["    would be on the first delivery date, if its yield to maturity were 6%.  Obviously, 6% is much higher than this note's ytm, so the"]
["    conversion factor is less than 1."]
[]
[]
["Note Price:",102.21,"  (Remember note prices are usually quoted in 32nds)"]
["$ Price",{"$f":"+DOLLARDE(B24,32)","v":102.65624999999999},"Note yield:",{"$f":"YIELD(F13,A6,A5,B25,100,2,1)","v":0.02202679259932594},"  This is the note's yield-to-maturity."]
[]
[]
["Futures Price:",124.16,"  --in 32nds"]
[null,{"$f":"+DOLLARDE(B28,32)","v":124.49999999999999},"  -- in $"]
["  Times CF = ",{"$f":"B15*B29","v":102.15662352889724},"  the futures contract is settled by delivering a specific note."]
[null,null,"----the buyer pays the seller the futures price times the conversion factor on that note."]
[]
["Gross Basis:",{"$f":"B25-B30","v":0.4996264711027436},"  The gross basis is simply the note's clean price minus the futures price-adjusted by the conversion factor for the note."]
["---- in 32nds:",{"$f":"B33*32","v":15.988047075287795}]
[]
["Basis Trade:"]
["If we buy the note now and sell it using the futures contract:"]
["AI:",{"$f":"D40*D41","v":0.4930939226519337},"Last Int Pmt:","2013-11-15",null,"Settle:","2014-01-22"]
[null,null,"Next Int Pmt:","2014-05-15"]
[null,null,"$ amt:",{"$f":"A5/2*100","v":1.3125}]
[null,null,"Prop due:",{"$f":"(G38-D38)/(D39-D38)","v":0.3756906077348066}]
[]
["Cash Outlay (and amt we have to finance):",{"$nulls":3},{"$f":"B25+B38","v":103.14934392265192},"   This is the note's dirty (or cash) price."]
[]
[]
["Last Delivery Date:",null,"2014-03-31",{"$nulls":3},{"$f":"C46-G38","v":68}," day holding period"]
["Clean Selling price:",null,{"$f":"B30","v":102.15662352889724}]
["AI:",null,{"$f":"(C46-D38)/(D39-D38)*D40","v":0.9861878453038674}]
["Total Cash Inflow:",null,{"$f":"C47+C48","v":103.14281137420112}]
[]
["Implied repo rate:",null,{"$f":"C49/E43","v":0.9999366690256828}]
[null,null,{"$f":"C51^(365/(C46-G38))","v":0.9996601086933491}]
[null,null,{"$f":"C52-1","v":-3.3989130665090794E-4},"  The implied repo rate is that rate which we would earn on our repo position to make the two sides of the"]
[{"$nulls":3},"         transaction exactly equal."]
["Actual Repo Rate:",null,0.00072]
[]
["Net Basis:"]
[null,"Sell Short:",{"$f":"E43","v":103.14934392265192}]
[null,"Repo:",{"$f":"C55*(G46/360)*C58","v":0.014028310773480662}]
[]
[null,"Purchase:",{"$f":"C49","v":103.14281137420112}]
[]
[null,"Net Basis:",{"$f":"C58+C59-C61","v":0.02056085922428963},"   The net basis is the cash flow consequence of selling the note short, and buying it back on the last delivery date as a long trader"]
[null,"-- in 32nds:",{"$f":"C63*32","v":0.6579474951772681},"  (Ticks)","    in the futures market."]
[]
[{"$nulls":3},"If the net basis is positive, then the implied repo rate will be negative."]
[]
[{"$nulls":3},"The cheapest to deliver is that note that has the lowest net basis."]
[{"$nulls":3},"The cost of delivering the second CTD is simply the difference between that note's"]
[{"$nulls":3},"       net basis, and the CTD's net basis."]
[{"$nulls":3},"In this instance,  there is virtually no cost difference to the short position between delivering"]
[{"$nulls":3},"   any of the first three notes on the screen."]
[]
[]
["Situation on Feb 2, 2017:"]
[null,"Futures Price ","124-11"]
[null,"In % of par terms:",null,{"$f":"124+11/32","v":124.34375}]
[]
[null,"CTD:",{"v":0.0275,"d":"2.75%"},"2023-11-15","103-06 3/4",{"$f":"103+6.75/32","v":103.2109375}]
[]
[null,"Conversion Factor:",null,0.8272]
[]
[null,"Forward analysis:"]
[null,"Settlement:","2017-02-03"]
[null,"Last Cpn","2016-11-15"]
[null,"Next Coupon","2017-05-15"]
[null,"Forward Date:","2017-03-31"]
[null,"Last Del Dt:","2017-03-31"]
[null,"AI:",{"$f":"C79*50*(C84-C85)/(C86-C85)","v":0.6077348066298343}]
[null,"Note Dirty Price:",null,{"$f":"F79+C89","v":103.81867230662984}]
[null,"AI on forward date:",null,{"$f":"C79*50*(C87-C85)/(C86-C85)","v":1.0331491712707181}]
[]
[null,"Repo to 3/31/17:",null,{"v":0.00615,"d":"0.62%"}]
[]
[null,"Forward Price:",null,{"$f":"D90*(1+D93*(C87-C84)/360)-D91","v":102.88484299853246}]
[]
[null,"Implied Futures Price:",null,{"$f":"D95/D81","v":124.37722799629165},null,"Implied Repo rate:",null,{"$f":"((D77*D81+D91)/D90-1)*360/(C87-C84)","v":0.00443521772450122}]
[]
[]
[]
[]
[]
[null,"Example using the on-the-run 10-year  note:"]
[null,null,{"v":0.02,"d":"2%"},"2026-11-15","95-30 1/4",{"$f":"95+30.25/32","v":95.9453125}]
[]
[null,"Conversion Factor:",null,0.7135]
[]
[null,"Forward analysis:"]
[null,"Settlement:","2017-02-03"]
[null,"Last Cpn","2016-11-15"]
[null,"Next Coupon","2017-05-15"]
[null,"Forward Date:","2017-03-31"]
[null,"Last Del Dt:","2017-03-31"]
[null,"AI:",{"$f":"C104*50*(C109-C110)/(C111-C110)","v":0.4419889502762431}]
[null,"Note Dirty Price:",null,{"$f":"F104+C114","v":96.38730145027624}]
[null,"AI on forward date:",null,{"$f":"C104*50*(C112-C110)/(C111-C110)","v":0.7513812154696132}]
[]
[null,"Repo to 3/31/17:",null,{"v":0.00615,"d":"0.62%"}]
[]
[null,"Forward Price:",null,{"$f":"D115*(1+D118*(C112-C109)/360)-D116","v":95.72813075319407}]
[]
[null,"Implied Futures Price:",null,{"$f":"D120/D106","v":134.16696671786133},null,"Implied Repo rate:",null,{"$f":"((D77*D106+D116)/D115-1)*360/(C112-C109)","v":-0.4613077400951792}]