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