{"text_format":"3","workbook":"xlsx","n_sheets":1,"sheets":["AI Primer"],"not_rendered":{"defined_names":26},"read":"whole","date_system":"1900"}

{"sheet":"AI Primer","index":0,"n_rows":53,"n_cols":19,"rows_kept":53}
["Excel is an very useful tool for bond analysis.  One reason is that computing the difference between two dates is simple."]
[]
["Consider the 7-year US Treasury note issued on July 31, 2020.  It matures on July 31,2027, and has a coupon rate of 0.375%.",{"$nulls":9},0.00375,"  Be careful with interest rates below 1%.  We read this as 37.5 basis points."]
[]
["Suppose that today is Friday, August 28, 2020, and we seek to buy roughly $10,000 worth of this note.",{"$nulls":7},10000]
["We see the bid-ask spread on Bloomberg is 98-21 - 98-21+."]
["First, Treasury notes and bonds are quoted in 32nds, so 98-21 means:",{"$nulls":4},{"$f":"98+21/32","v":98.65625}," and second prices for all fixed income securities are expresssed as percentage of par value."]
[{"$nulls":6},"Par value and face value are the same things, and they are the way that we express the amount of bonds for the purpose of receiving coupon payments and final maturity value."]
[{"$nulls":4},"98-21+ means:",{"$f":"98+21.5/32","v":98.671875}]
[{"$nulls":4},"In this case we see that the bid-ask spread is one-half of a tick.  (A tick is 1/32 of a percent)."]
[]
["So we tell our broker to buy $10,000 par value of this note at the market."]
["We immediately buy from a dealer who posted the ask quote at the ask quote of 98-21+."]
["The trade will settle on Monday, August 31.  This is when you will receive the electronic ownership of the note, and the funds will be taken from your account (also electronically)."]
["Trades in the US Treasury bill, note, and bond markets settle on a T+1 basis -- that is the next business day."]
["In the US, quotes for all notes and bonds are on a ``clean'' basis.  This is a convention that lacks economic meaning, so we just have to memorize this."]
["Your cash price will include accrued interest in addition to the clean price."]
["  We compute accrued interest by taking the percentage of time between the last coupon and the next coupon and multiplying this by the dollar amount of the coupon."]
["       Note that we express prices as percentage of par value, so we do the same with the accrued interest."]
["First we need the semi-annual coupon payment:",{"$nulls":3},{"$f":"0.5 * 100 * K3","v":0.1875},"By convention, the coupon payment is made on the last day of the maturity month and the last day of the month that is 6 months away from the maturity month."]
["  So for every $100 f par value that we own, we receive",{"$nulls":4},"In this case, the ``last coupon'' date is:",null,null,"2020-07-31","  of course this was the issue date, and no coupon payment was made."]
["       $0.1875 on each coupon payment date.",{"$nulls":4},"  and the next coupon payment date is:",null,null,"2021-01-31","  even if this is a wekend or holidday date, it is used for the purpose of computing accrued interest."]
[{"$nulls":5},"Of course our settlement date is:",null,null,"2020-08-31"]
[]
[{"$nulls":5},"Now comes the facility of excel.  The number of days between the last coupon payment and the next coupon payment is:",{"$nulls":9},{"$f":"I22-I21","v":184}]
[{"$nulls":7},"                     and the number of days between the last coupon payment and the settlement date is:",{"$nulls":7},{"$f":"I23-I21","v":31}]
[{"$nulls":10},"so the percentage of the coupon period that has elapsed is:",{"$nulls":4},{"$f":"P26/P25","v":0.16847826086956522}]
[{"$nulls":12},"        And the accrued interest is:",null,null,{"$f":"P27*E20","v":0.03158967391304348}]
[]
[{"$nulls":12},"And the cash price -- or dirty price -- is:",null,null,{"$f":"P28+F9","v":98.70346467391305}]
[{"$nulls":8},"Therefore on August 31, the amount of cash that will be ``wired'' out of our account is:",{"$nulls":6},{"$f":"I5*P30/100","v":9870.346467391304}]
[{"$nulls":8},"We will own ",{"$f":"I5","v":10000}," par value of the note."]
["This accrued interest treatment is simply a convention.  As financial analysts and economists, we know that the only thing that matters in valuation is cash flow, so we know that the value of this note is its cash value determined by discounting its future cash flows."]
[null,"Computing the yield to maturity:"]
[]
["Date:","2020-08-31","2021-02-01","2021-08-02","2022-01-31","2022-08-01","2023-01-31","2023-07-31","2024-01-31","2024-07-31","2025-01-31","2025-07-31","2026-02-02","2026-07-31","2027-02-01","2027-08-02"]
["Days ahead:",0,{"$f":"C36-$B$36","v":154},{"$f":"D36-$B$36","v":336},518,700,883,1064,1248,1430,1614,1795,1981,2160,2345,2527]
["T: (Years ahead):",{"$f":"B37/365.25","v":{"v":0,"d":"0.000000"}},{"$f":"C37/365.25","v":{"v":0.4216290212183436,"d":"0.421629"}},{"v":0.919917864476386,"d":"0.919918"},{"v":1.4182067077344285,"d":"1.418207"},{"v":1.9164955509924708,"d":"1.916496"},{"v":2.4175222450376452,"d":"2.417522"},{"v":2.9130732375085557,"d":"2.913073"},{"v":3.4168377823408624,"d":"3.416838"},{"v":3.915126625598905,"d":"3.915127"},{"v":4.418891170431212,"d":"4.418891"},{"v":4.914442162902122,"d":"4.914442"},{"v":5.423682409308693,"d":"5.423682"},{"v":5.9137577002053385,"d":"5.913758"},{"v":6.420260095824777,"d":"6.420260"},{"v":6.91854893908282,"d":"6.918549"}]
["Cash Flow:",{"$f":"-P30","v":-98.70346467391305},{"$f":"$E$20","v":0.1875},{"$f":"$E$20","v":0.1875},0.1875,0.1875,0.1875,0.1875,0.1875,0.1875,0.1875,0.1875,0.1875,0.1875,0.1875,{"$f":"100+E20","v":100.1875}]
["YTM /Candidate:",0.0044]
["PV:",{"$f":"B39*EXP(-$B$40*B38)","v":-98.70346467391305},{"$f":"C39*EXP(-$B$40*C38)","v":0.18715247851248357},0.18674260163330242,0.18633362241285073,0.18592553888519386,0.18551611424730807,0.18511205106604373,0.184702192764577,0.18429768218074727,0.18388962698094885,0.18348910636859714,0.1830784304662938,0.18268407805002512,0.1822773997201873,97.18358465342716,null,"sum:",{"$f":"SUM(B41:P41)","v":0.8813209028026705}]
[]
["We use Solver to find the note's ytm.  We want the present value of the net cash flows to be 0, so we set up a criterion cell -- such as the net PV squared, which we ask solver to minimize by moving the ytm candidate around.",{"$nulls":16},"Criterion:",{"$f":"S41^2","v":0.7767265337169141}]
["  Before we do that we note several things:"]
[{"$nulls":3},"1. We know before we start that the note's ytm will be greater than 0.375%.  That is because the note is selling at a discount (its dirty price is below par)."]
[{"$nulls":3},"2. When I try 44 basis points as the candidate, the Net present value of the cash flows is positive, so the ytm is greater than this guess."]
[{"$nulls":3},"  In fact when we run Solver we find that the note's ytm on August 28, 2020 is",{"$nulls":5},57.01," basis points."]
[]
["Note that the dates I have emboldened are the result of ``bad days.''  The statutory payment date of the next coupon (and the date we used to compute the accrued interest) is January 31, 2021."]
[null,"However, a glance at the calendar shows that that date is a Sunday.  So the cash flow will not occur until the next business day -- Monday, February 1, 2021."]
[]
["The yield to maturity is another way of characterizing the note's price."]
["   It is the result of the term structure of interest rates.  The market value is determined by professional investors discounting all of the future cash flows at the spot rates corresponding to those dates."]