Any part with a single asterisk (*) is for extra credit; any part with a double asterisk
(**) will not be graded and only serves as food for thought.
For all questions involving coding, you are free to use existing codes for the numerical
examples in notes/slides; you can choose either VBA or Python.
For all questions involving coding, hand in the spreadsheet together with the VBA
codes or Python codes. You may print the spreadsheet and the VBA codes, or Python
codes, as hard copies and hand in it with solutions/answers for other problems. You
can also include the spreadsheet with VBA codes in a single .xlsm file (if you choose
VBA), or putting all Python codes in a zipped file (if you choose Python), and email
it to the Teaching Assistants (remember to include your name in the email).
Students are allowed to discuss and collaborate for the problems, but each student is
required to independently prepare the codes and the answers.
1 Coding: 20-Year Spot Rate Curve and Forward Rate
Curve (40%)
Download the file bondData.xlsx from Moodle. Below is a description of data:
The data contains U.S. treasury coupon bond prices with terms up to around 20 years.
“2/17/2023” in the A1 cell is the time stamp on which the all the prices are observed
In other words, it is the “t” in the bond price notation B(t, T).
There are five columns, “Maturity”, “TTM”, “Coupon”, “price” and “yield”. Each
row corresponds to one bond (observed on 2/17/2023).
1 Maturity contains the maturity dates; i.e., it is the T in B(t, T).
TTM stands for “Time to Maturity”, i.e. T t, the unit is one year.
Coupon stands for annual coupon rate. (“0.01” means 1%.) U.S. treasury coupon
bonds pay coupons semiannually. This means, for a face value of 100 and coupon rate
0.01, every half year the coupon of amount 0.5 is paid. The last coupon together with
the face value is paid on maturity. For this assignment, use 100 as face value. The
function getBondCashFlows (codes available on Moodle in both VBA and Python)
for numerical examples we went through in class can help you to extract the cash
payment schedule for each bond in this dataset.
price is the observed bond price, and yield is the observed yield (calculated from the
price).
Complete the following tasks.
(a). Build a spot rate curve from this dataset. To deliver the solution, you need to: (i)
specify the spot rate model you use and why you choose this model; (ii) assessment of
goodness of fit; you can do it in the same style as Slide 60 of Topic1a; (iii) plot the
price errors and the yield errors. Your choice of model/technique is not necessarily
limited to those discussed in this course. You may choose any other model/technique
you deem to be reasonable.
To complete this part, you need to compute the model-implied yields given the model
implied prices. The Excel spreadsheet built-in function YIELD can help you with
this. But to use this function, the “bond price” entered should be the clean price.
Specifically, clean price = B(t, T) – Accrued Interest, where B(t, T) is the bond price
computed based on the spot rate curve model, and Accrued Interest (AI) is computed
as the following:
AI = face value ×
coupon rate
frequency
×
t – previous coupon payment date
current coupon period length
.
For this assignment, “face value” = 100; “frequency=2” (standing for semiannually
paid coupons); “coupon rates” are contained in the column of “Coupon”; “t” is the
time stamp “2/17/2023”. “previous coupon payment date” is the coupon payment date
2immediat
ely before t, which can be retrieved by the spreadsheet function COUPPCD;
“current coupon period length” is the time between the coupon payment date imme
diately after t and the previous coupon payment date, and in particular the coupon
payment date immediately after t can be retrieved by the spreadsheet function COUP
NCD. For each bond, after AI is computed as described, for a model-implied bond
price B(t, T), call the function YIELD to retrieve the model-implied yield. When
calling this function, “settlement” is “2/17/2023”, “maturity” is the maturity date in
the column “Maturity”, “rate” is the coupon rate contained in column “Coupon”, “pr”
is the clean price which equals B(t, T) – AI described above, “redemption” is the face
value which equals 100; “frequency” equals 2; set “basis” at 1.
As an example for computing model-implied yield described above, look at the file
“bonds allExamples.xlsm”, the sheet “Ex1 spotCurve p4”. The time stamp is 2/17/2023
set in cell A1. For each bond, column F computes the previous coupon payment date
by calling COUPPCD, column G computes the next coupon payment date by calling
COUPNCD, column H computes AI according to the formula above. In column J, the
function YIELD is called to compute the model-implied yield; in particular, for the
argument “pr”, the difference between column I (containing model-implied bond price)
and the column H (containing AI) is entered.
Remark: if you choose Python, there is no readily available function to calculate yield,
so you need to implement on your own by solving the equation; the bisection method
is straightforward to implement.
(b). Write a function forwardRate to compute f(t, t1, t1 +τ ) with given t, t1 and τ , based
on the spot rate curve built in (a). A reminder is that you need the function to have
access to s(t, t1) and s(t, t1 + τ ).
(c). Use τ = 0.5 to build a forward rate curve and plot it with the spot rate curve on the
same graph. That is, for each t1 ≥ t on the x-axis, the y-axis value for spot rate curve
is s(t, t1) and y-axis value for forward rate curve is f(t, t1, t1 + τ ). Comment on the
graph.
(d). Using the spot rate curve you built in (a), develop an immunization strategy using
bonds in the given dataset for an obligation of amount 1 Million that will occur on
318 February 2026, based on Fisher-Weil Duration; you only need to give the number
of units to buy for each bond on the current time point (i.e. “2/17/2023”).
(e).* When doing (a), what difficulties did you encounter (e.g. long running time) And
what limitations of your chosen curve fitting techniques are suggested by these diffi-
culties
2 Computational Problems (45%)
You may need calculators to do numerical computations in some of the problems in this
section.
Problem 1
(a). We have a bond of face value 100 and an annual coupon rate of 2.5%. The coupon
payment is made every half year and the face value is paid with the last coupon on the
maturity date. That is, on each coupon payment date before the maturity date, the
bond pays 1.25, and on the maturity date, the payment is 101.25. The current time
is t, and the next coupon is known to happen on t + 0.25 (i.e. 3 months from now).
The maturity date of this bond is known to be T = t + 2.25. The annual yield now
is quoted at 1.88%. Use d = 0.5 (i.e. the length of the reference time interval length
is half a year). Compute the current bond price, B(t, T); round the answer to two
decimal places.
You might need the formula for geometric sum: a + a 2 + a 3 + · · · + a n = a(1