FINS 3636 Computer Assignment
For this assignment, you may work in groups of up to five. (You can work on your own if
you like.)
The due date for the assignment is Monday, 21 November, 2022 by 11pm. Please submit
one copy of your assignment (i.e., a Word document or preferably a pdf file) per group to the
Assignment link on Moodle. I don't require that you use a UNSW cover page, but please be
sure that all your names and IDs are clearly visible. If necessary, try to indicate which is your
surname (by underlying or using all caps).
Ordinarily, your submitted assignment will consist of one Word document, in which you
briefly write out the answers to my questions. I also occasionally ask for a screenshot of your
inputs, which can help with the marking.
When reporting a number (e.g., a price, interest rate or duration) I think 4 decimals should
suffice.
Introduction
The purpose of this assignment is to have you solve a number of problems using a variety of
spreadsheets that will be provided to you. For the most part, the spreadsheets are designed so
that you need merely enter the input variables, which are indicated by the red font. You
should only have to change the variables with the red font. Some other colours are used as
well, for example, in some cases the solution is given in blue font.
NOTE: any input variables that happen to be in the spreadsheet when you receive them should
be considered to be irrelevant. You will provide all the relevant input variables.
For each spreadsheet, answer the questions provided. The questions will be indicated by
letters, (a), (b), etc. Generally, each question is worth 1 mark. Please do not hand in a
spreadsheet unless explicitly asked to do so. As I mention above, screen shots of the inputs of
the spreadsheet help me with the marking, though.
1. Bonds.xls. NOTE: before opening this spreadsheet, you need to open Excel, go to Tools,
Add-Ins, and make sure there’s a check mark beside Analysis ToolPak-VBA. [In Office 2007,
click on the File button (top left hand side of the page) then click on Options (bottom,
near the right of this window), then click on Add-ins, then Manage Excel Add-ins and
click on Go. Put ticks beside Analysis ToolPak-VBA and Solver. You’ll also need Solver for
some of the other questions in this assignment.] Many of the bond functions require this
add-in. In the past it has been difficult to send this file to students because of these bond
functions,but this doesn’t seem to be a problem now. Still, if there are any problems with
the files, (for example, some functions ended up being “linked” to some web site) please
let me know.
This spreadsheet contains a variety of commands that are useful for pricing bonds.
(a) What are the cash price, quoted price, and accrued interest of a 2.75% May, 2041
bond? Find the yield from the Termstru-2023-2-version 2 spreadsheet (which uses
semi-annual compounding). What are its duration and convexity? How are the
dates entered: mm/dd/yy or dd/mm/yy? Settlement of the trade usually takes
place two settlement business days after the transaction. The newspaper was
from Friday 21 October, so any trades would have been from the day before,
Thursday 20 October. So, for the settlement date, use Monday 24 October.
(b) Does the command =PRICE give the cash price or the quoted price?
(c) Does the command =DURATION give the exact duration, or the approximate duration
for a round number of coupons?
2
(d) I now have two ways of calculating convexity. The first uses a round number of
coupons (see cells H15 and I15 for convexity and the second derivative,
respectively.) The second approach uses the general approximation
( ) ( )df f x h f x
dx h , for small h. (See cells K15 and L15.) In our case, we need
the second derivative, which is the derivative of the first derivative, where the first
derivative is equal to P MD? ? . Does the calculation in cell K15 require the
assumption that the bond has a round number of coupons remaining? Which
calculation should be more accurate, cell H15 or cell K15?
(e) Copy a screenshot of the first page of the spreadsheet to your Word document, so
that I can check your inputs.
2. termstru.xls: In this spreadsheet there are four columns with a light blue background.
These cells use the bond functions that require Analysis ToolPak-VBA or Analysis ToolPak.
Please let me know if these cells don’t seem to work properly, especially if they “link”
with some strange thing.
Note that in this question, we find estimates for parameters that will be used in questions
3 and 6 below.
With this spreadsheet, we want to use a smoothing technique to estimate the current term
structure (see Ch. 8, slide 8-41, of my lecture notes). We’ll use the Nelson-Siegel
formula
y(t) = (a1 + a2t) tae 3? + a4.
(Here, y(t) will be a rate compounded semi-annually.) We first need initial estimates for
the parameters. From calculus, we can see that reasonable initial estimates are a4 ? y(?),
a1 ? y(0) ? a4, a2 ? dt
dy )0( + a1a3 (although a2 ? dt
dy )0( seems to work better) and a3 ?
max
1
t
, where tmax = the time, t, for which y(t) is a maximum (or if the term structure is
downward sloping, tmax = the time for which y(t) is a minimum). (The OFFSET and
MATCH commands give a tricky way of automatically finding tmax from the spreadsheet.)
Keep in mind that the initial estimates are somewhat arbitrary. You may get a better
result if you change the initial estimates in some way. Hopefully, these initial estimates
are helpful—they won’t necessarily be perfect.
Once we have some initial estimates, we can use Solver to find optimal estimates. What
the spreadsheet does is this: given the estimated parameters, a1, a2, a3, a4, it calculates
discount rates y(t) for all the coupon dates, and then calculates the prices of each coupon
bond (call them Pmodel, i). We then solve for the parameters that minimize the sum of the
squared errors: 2observed, model,( )i i
i
P P?? . Here’s what you have to do.
(i) First, note that the settlement date, and the coupon rates, maturities, and yields
on Government Bonds have all been entered into both the Termstru-2022-3-version 1
and Termstru-2022-3-version 2 spreadsheets. So, the data is already in the
spreadsheet—even the settlement date: 24 October, 2022.
NOTE 1: Please check that my inputs are correct.
3
NOTE 2: We will do most of our work with Termstru-2022-3-version 2. We only
look at Termstru-2022-3-version 1, briefly, to see what is wrong with the data in that
spreadsheet.
(ii) For both ot the Term Structure spreadsheets, set the “Final estimates” equal to the
“Initial values”. (Some useful commands are Copy (ctrl + c) and Edit, Paste Special,
Values. Alternatively, for the final estimates, just use “= [above cell]” if that makes
sense.)
(iii) Now, we use Solver. Under Tools, click Solver. [In Office 2007, click on Data, and
then look for Solver on the right-hand side of the toolbar.] Here’s what the window
should look like:
Set Target Cell: $L$3
Equal to: Min
By changing cells
$D$4:$G$4
Then click Solve. In fact, using these new estimates for a1, a2, a3, a4, use Solver again to
get your final estimates. Using Solver a second time may increase the accuracy; if there is
no change, that’s fine. You can use Solver a third time if you like. (If you’re curious, try
this procedure with different initial estimates; you will probably get a slightly different
answer, and you may even get a better answer.)
(a) First, let us look at Termstru-2022-3-version 1. If you are curious, look at the pdf file
included with the spreadsheets: Fin Review-Treasury Bonds. At the bottom of the
table are seven bonds with very low yields (one of them is even negative!). These
bonds prices seem to be “stale”, that is, they may be from some time ago, when
interest rates were lower than they are now. Graph the implied zero coupon yield
curve. Include the yields for the coupon bonds on the same graph. Can you see
where the yields of the 7 bonds appear? Does the curve for coupon bond yields look
like a mess? Include a copy of the graph with your submitted homework.
For the remaining questions, use Termstru-2022-3-version 2.
(b) After using Solver, what are your final estimates for a1, a2, a3, a4?
(c) Graph the implied zero coupon yield curve. Include the yields for the coupon bonds
on the same graph. (See, for example, the graph on slide 8-46 of the Ch 8 lecture
notes; but don’t include the arrows and numbers that I’ve added, of course.)
(d) If you’re given two different estimates for a1, a2, a3, a4, how do you decide which
estimate is “better”?
(e) Please submit a copy of the spreadsheet (and, of course, the graph), showing the
results from part (b) so that I can check your inputs.
(f) Now use Solver again, but with this added constraint: $J$12=$E$12. Does this
improve the fit?
(g) Use Solver again, without the constraint from part (e) above, but now minimize the
absolute error between observed and estimated prices: observed, model,| |i i
i
P P.
(i) Which cell are you minimizing?
(ii) Roughly speaking, are the graphs from parts (b) and parts (g) similar? Are the
estimates similar? I won’t give marks for this part of the question; I’d just like
you to see if the results seem similar. (Note that these estimates will never be
exactly the same, but they can vary slightly without affecting the overall
effectiveness of the solution, if that makes sense.)
3. duration.xls: This spreadsheet calculates the exact Fisher-Weil duration and convexity
using the smoothed yield curve with the parameters that we estimated using the
termstru.xls spreadsheet.
Again, this spreadsheet uses some bond functions that require Analysis ToolPak-VBA. You
must go to the cells with light blue background and type in the “equal” sign in front of
each of the formulas.
Copy the estimates for a1, a2, a3, a4 from the termstru.xls spreadsheet and paste them into
this spreadsheet. For this question we will use the bond from the Bonds.xls spreadsheet.
(a) First, we want to use Solver to make sure that our bond (i.e., the bond used in the
spreadsheet Bonds.xls, from question 1) is correctly priced. Here’s a quick way to do
this. Go to Data, Solver. Set Target Cell, H4, equal to max; By Changing Cells: E8 (i.e.,
a4), Subject to the Constraints: Add, H4 = L4. What is the new value of a4?
(b) What are the duration and convexity (use both calculations for convexity) for the
bond used in Question 1, in the bonds.xls file? Is there much difference between the
Fisher-Weil values and the Macaulay values? Which calculation of convexity in the
bonds.xls file is closest to the convexity given by the duration.xls file?
(c) Attach a screenshot of the first page of the spreadsheet, so that I can check your
inputs.
4. Blacks Approx.xlsx: This spreadsheet uses Black’s approximation to calculate European
option prices for options on bonds (see Options#1 starting at slide 82). Using various
Bond functions from EXCEL, we can calculate the present value of coupons paid and
then calculate the bond’s futures price. We then use Black’s approximation to find the
value of an American call option on a coupon-bearing bond.
Again, this spreadsheet uses some bond functions that require Analysis ToolPak-VBA.
Note that the cells with light blue background use these special bond functions. If these
cells aren’t working properly, please let me know.
For input values, let’s assume that the underlying bond matures on 21 September, 2031
the bond’s yield is y = 4.0%, the option’s settlement date is 20 October, 2022, and the
option’s maturity date is 23 April, 2024. We’ll assume that the term structure is flat with
y = the bond’s yield. Also, assume the (cash) exercise price is X = 100.
NOTE: To get a flat term structure, set a1 = a2 = a3 = 0, a4 = y. You will need this fact when
using some of the other spreadsheets below.
For the bond’s volatility, assume that y = 0.15 (see cell M5) and then use the formula we
learned in Ch. 8: the bond’s volatility, P, is given by P = yyMD. (The spreadsheet
calculates this automatically in cell G5; the modified duration, MD, is calculated in cell
L5. The bond’s cash price is also calculated automatically once you enter the bond’s
coupon rate and yield.)
(a) What is the price of the European call if we assume that the bond’s coupon rate is
3.5%? What is the price of the American call? Is there an early exercise premium
for the American call? If so, what is the early exercise premium? The European
call option corresponding to which coupon payment has the maximum price?
(b) What is the Delta of the above American call option (assuming you hedge using the
underlying bond)?
(c) Now repeat the above calculations (i.e., give me the price and early exercise
premium) for a bond with a coupon rate of 4.5%. Now which option has the
maximum price?
(d) What is the Delta of this American call option?
(e) Using the inputs from the part (c), suppose the (cash) exercise price is now X = 105.
Now which European option has the maximum price?
(f) Hand in screenshots of the first pages of both of the spreadsheets (with each of the
coupon rates given above), so that I can check your inputs.
5
(g) Let us now look at some properties of the callable bond (see columns Q and R). For
X = 100, and coupon rate 3.5%, what are the yield and effective duration for the
callable bond? The effective duration involves a calculation described on Slide 66
of Options #2.
Now find the yield and effective duration of the callable bond when the coupon rate
is 4.5%. Does the effective duration seem to relate to which European option gives
the approximate American option price?
5. Cap&Flr.xls: This spreadsheet uses Black’s model (with lognormal rates) to calculate
prices of caps and floors (and collars). Note once again that some cells have a blue
background, and so please let me know if these cells aren’t working properly.
As input variables, let ? = 0.15, assume the face value of the floating debt is $100
million, the settlement date is 12 October, 2022, the bond’s maturity is 15 February,
2028, and the number of payments per year is 2. Assume that the term structure is flat
with y(t) = 5% per year (compounded semi-annually) for all maturities. Choose a1,…,a4
to give this flat term structure. (See the NOTE in question 4 above.)
I would like to point out a few featues in the spreadsheet. For one thing, you have to
enter the “start of the cap/floor” (cell E5). This is meant to tell us the first date at which
you may receive a non-zero cash-flow. If you’re curious, you can find this date used in a
“0-1”-type IF statement in the “discount factor” column (column M). (Note that, the start
of the cap/floor is often taken to be the period before the first actual cash-flow, but
hopefully this won’t be too confusing.) In our case, we’ll assume that the cap/floor has
been in place for some time, so you can just set the “start of the cap/floor” equal to the
settlement date. (In general, you should also note that the “start of the cap/floor” date is
not very sensitive to leap years, so you may have to adjust this date slightly to make sure
you get the right cash-flows; adjusting this date only adds or deletes a cash-flow—it
doesn’t affect the discounting of any other cash-flows.)
Another change I’ve made is that you must now enter the “Last LIBOR rate” (cell N5).
This rate is only needed when the first cash-flow is already determined (i.e., if the first
cash-flow comes in less than one period’s time). These cash-flows, which are calculated
in row 12 (cells G12 to L12), are either equal to zero or the cap or floor payoff. For this
question, let’s assume that the last LIBOR rate is equal to the yield given by the flat term
structure. The “Last LIBOR rate” is also used to calculate the price of floating rate debt
in cell P8. We also calculate the price of paying floating rate debt and buying a collar in
cell Q8. Then, for comparison purposes, we find the price of a fixed rate bond, based on
the coupon rate that you enter in cell R5. The price is then given in cell R8.
One final change, is that I’ve copied all these calculations to the right of the spreadsheet
but increased the interest rate by an amount ?y, which you can enter in cell V4. This is
then used to calculate (approximate) modified durations for all the instruments that are
priced in the spreadsheet.
(a) For a cap rate of 6% use Solver to find the floor rate such that the value of the collar
is zero, i.e., set the collar value equal to 0 by changing the value of R(L). What is
R(L)?
(b) Round off R(L) to the nearest basis point. What is the value of the collar now?
(c) Use Solver to find the floor and cap rates such that the value of the collar is zero and
the spread between the cap and floor rates is 2.0%.
(d) Again, round off R(U) and R(L) to the nearest basis point and find the value of the
collar.
6
(e) Hand in a screenshot of the first page of the two spreadsheets above (i.e., the
spreadsheets that use the rounded off rates), so that I can check your inputs.
(f) Using the spreadsheet and the above inputs, find the value of a range note (see the
lecture notes for a definition) with R(L) = 4.0% and R(U) = 6.0%.
(Hint: the spreadsheet gives N(d1) and N(d2) for the cap, and N(?d1) and N(?d2) for
the floor. In column N you can easily do the probability calculations for the range
note. Please put the undiscounted probabilities in column N, so that I can check
your work. The discount factors (i.e., zero coupon bond prices, b(0,tk+1)) are in
column M. (For your convenience, I’ve used an IF statement that sets the discount
factor equal to zero after the cap/floor expire.) Then you’ll need to use either the
=SUMPRODUCT() command, or the =SUM() command, while multiplying by a few
other variables to get the final answer. Put the final answer in cell N8. Explain your
calculations.)
(g) Hand in a screenshot of the first page of the spreadsheet with the range note.
(h) Now let’s calculate some modified durations for the “pay floating, buy a collar”
combination. In each case, we’ll use ?y = 0.000001. (i) Find the modified duration
when R(U) = R(L) = y(t). How does it compare to the modified duration for a fixed-
rate bond? A floating rate bond? (ii) now find the modified duration when R(L) =
1% and R(U) = 50%. Again, how does it compare to the modified duration for a
fixed-rate bond? A floating rate bond? Do these results make sense?
6. Convertible Debt: First, note that this spreadsheet uses Analysis ToolPak-VBA. See Question 1
above for information about how to add this application.
This file calculates the price of convertible debt using the approach discussed in Ch 10,
starting from slide 52. Recall that we use a sort of trinomial tree, but one node is
essentially a constant, so it is effectively a binomial tree. One feature of the spreadsheet, is
that the binomial tree