Assignment 1
Due: December 23, 2024 (Saturday), 11:59pm
MSBA7017 Financial Engineering, Fall 2023
❼ 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
2immediately 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
−
a
n)
1
−
a
.
(b). Current time is t. Given the forward rate curve f(t, t1, t2) (that is, t is fixed, t1 and t2
span all pairs of (t1, t2) that satisfy t ≤ t1 ≤ t2), recover the spot rate curve s(t, t0 ).
(c). Suppose there is a set of N zero-coupon bonds with maturities T1 < T2 < · · · < TN
(denote their prices now (t) by B0(t, Tn)). That is, the n-th bond makes only one
payment on its maturity date Tn (and pays nothing before Tn); suppose the payment
of n-th bond is Cn. Can you derive a discretely sampled spot rate curve (that is, obtain
the values of s(t, t0 ) for a set of specific values of t
0 ) such that the bond prices implied
by this (discretely sampled) curve matches exactly with the observed prices? If yes,
4derive this curve using the given zero-coupon bonds; in particular, specify which values
are used for t0 and the corresponding spot rates s(t, t0 ). If no, explain why.
(d). Your colleague fits a spot rate curve (from a certain class of bonds) that has the
following expression:
s(t, t0 ) = 0.06 + 0.006(t
0 − t) + 0.001(t
0 − t)
2 − 0.0005(t
0 − t)
3
, t0 ≥ t.
(As usual, for spot rates we use the continuous compounding.) t is the current time.
Some client request a fixed-income product that will pay ✩5000 on t + 0.25 and ✩10000
on t + 1.5, and you happen to be able to construct these two cash flows from a pool
of bonds that belong to the same class of bonds from which the spot rate curve is
fitted. Compute the price that you will charge the client (before any fees). Suppose
you compute the price and quote it to the client, he/she thinks it’s fair and would like
to request another fixed-income product. For this one, the client needs ✩10000 paid
on t + 1 and ✩50000 on t + 7. Do you have enough information to set a price for this
product? If yes, give the price; if no, explain why.
(e).** The discussion on bonds in this course is static, and changes in the underlying interest
rate structure are restricted to be local. Explain.
Problem 2
(a). Current time is t. Recall from (b) of the last problem: a zero-coupon bond makes a
one-shot payment which occurs on its maturity date. Show that both the Macaulay
duration and Fisher-Weil duration for a zero-coupon bond equal its time to maturity
(i.e. T − t). Based on this, argue that a long-term zero-coupon bond is riskier than a
short-term one.
(b). We have two bonds now (t). The first bond is a zero-coupon bond: it matures on T
and makes only one payment which occurs on T. The second bond also matures on
T; and it makes two payments: one on t1 (t < t1 < T), and the other on T. Show
that both the Macaulay duration and Fisher-Weil duration of the first bond are larger
than those for the second one. Based on this statement, argue that existence of cash
payments which occur before the maturity date tend to alleviate the bond risk.
5(c). We discussed an example of Forward Rate Agreement in class. Recall the setting: now
(t), two parties, call them “A” and “B”, agree to exchange cash flows on a future date
t2 > t. On t1 (t < t1 < t2), the spot rate prevailing from t1 to t2 will be observed,
denote it by s(t1, t2). On t2, A passes ✩e
s(t1,t2)(t2−t1)
to B, and B passes e
f(t2−t1)
to A.
The rate f is agreed now (t). We’ve shown that if A enters the FRA without cost
(i.e. the price charged on A to enter the FRA is 0), f should be set at the forward
rate f(t, t1, t2). Now show the counter part: if it costs “B” ✩0 to enter the FRA, the f
should still be set at f(t, t1, t2).
Now, suppose f > f(t, t1, t2), compute the price for A to enter the FRA.
Problem 3
(a). (This is a drill to compute mean and variance) Two dice are independently rolled and
the two resulting values are multiplied together to form the quantity Z. For example,
if first dice rolls 1 and the second dice rolls 6 then Z = 1 × 6 = 6. Compute E(Z) and
V ar(Z).
(b). There are two stocks, A and B. Let rA and rB denote their returns for the following year.
We know: µA := E(rA) = 10%, µB := E(rB) = 15%; σA := std(rA) = 15% and σB :=
std(rB) = 25%. (std(X) stands for the standard deviation of the random variable X.)
The correlation coefficient between rA and rB is 0.8. (Recall, correlation coefficient
between two random variables X and Y is defined as Cov(X, Y )/(std(X)std(Y )).) Do
the following:
(i) Formulate the Markowitz mean-variance optimization model without risk-free asset
in this context; (ii) Find the global minimum variance portfolio (GMV); (iii) compute
the expected return of the GMV computed in (ii).
(c).* Prove the Two Fund Theorem. (i.e. Theorem 1 in Notes 2).
63 Questions on FinTech (15%)
The questions in this section are open-ended, and we encourage any independent and creative
thinking.
Question 1
1. P2P lending platform provides access to financing by debts for small borrowers. On the
investor’s side, it provides new investment options by debts. And from the assigned readings,
we know they are considered to be a promising “new asset class”. Compare and contrast
the debts posted on P2P lending platforms with the bonds we discussed in this course.
2. Give some examples of crowdfunding platforms in your home country; if there is no such
platform in your home country, you can give examples from other countries that you are
familiar with. If you want to initiate a project which you believe to be very profitable and
will bring a 20% return on capital after two years, which platform from your examples will
you consider to fund the project? If you want to shoot a movie about your postgraduate
study experience and share it with people, which platform will you consider to raise the
fund?
Question 2
The FinTech book considers Singapore as one of the FinTech hubs in Asia. Describe how
FinTech is developing in your home country and the driving factors. Or, if it is not prospering
in your home country, provide an explanation.
7
