24小时全年无休客服
本公司旗下写手皆为公司直聘 网络写手勿扰
案例中心
FINS 3636 Computer Assignment
创建日期：2024-05-16 15:49:18
所属分类：金融Finance
Computer Assignment
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
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,
2
2
P
y
,
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.
4
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
is represented in the spreadsheets as
So, as prices move horizontally, they increase. So, for example, when the price of the
stock is given in row 21, if the price moves up, it stays in row 21, if the price drops, it
moves down to row 23 (in the next column). (In general, once the price is in row 21 it
can’t move back up to row 19, despite appearances; the best it can do is move
horizontally.) Also, the bond prices are given right below the stock prices. For example,
if the stock price is given in row 21, then the corresponding callable convertible bond price
appears in row 22 (in blue font), directly below the stock price (given in black bold). So,
in a sense, one “node” in this case, is two cells high and contains information about the
price of the stock, and the bond.
S
S
Su
Su
Sd
Sd
B Bu
Bd
7
Note that these spreadsheets were also designed so that they could (fairly) easily be made
larger. You should be able to use Copy and Paste in order to make these trees as big as you
like. Note, however that if you do make the tree larger, you need to change the number for
n (in cell Q5). The time to maturity, T, and the number of steps, n, are independent of each
other.
Acquaint yourself with the spreadsheet. At the top of each spreadsheet you’ll find all the
input variables: r = rate = risk-free rate per annum (with semi-annual compounding), q =
dividend yield per annum (with continuous compounding), S = current stock price, K =
bond’s exercise price, the stock’s volatility is = sigma, as well as the Settlement Date, the
bond’s Maturity Date, Coupon Rate, the Number of Coupons per year, the bond’s face
value (for the bond functions, it is easiest to use FV = 100, even though the correct face
value is 1000; so, we calculate the price as a percentage of the true face value).
In cell A8, I convert the rate in cell A5 to continuous compounding, which is used for
discounting in the binomial tree. The rate in cell A5 is used to calculate the price of the
non-callable, non-convertible, risk-free bond in cell F11. The Next Coupon Date is also
calculated, by adding COUPDAYSNC to the settlement date.
The other input variables are the Default intensity, = lambda, the conversion ratio, and
the Recovery rate.
The spreadsheet automatically calculates T = time to expiration (in years, assuming 365
days in a year). Most of the cells containing input variables are named. You can see the
name of a cell by looking at the box in the upper left-hand corner of the spreadsheet. The
values used in the Binomial tree, u, d, a, p, Smax, and Smin, are calculated from these input
variables.
Row 15 gives the coupon payments, row 16, the discount factor, and row 17 calculates the
present value of all remaining dividends. The counting numbers in row 18, and in column
B are just used to calculate the various prices in the tree.
To make sure that the dividend payment dates are precise, there are some bond functions
in cells V2 – BJ2. These bond functions require that you go to Tools, Add-Ins, and make
sure there’s a check mark beside Analysis ToolPak-VBA.
Look at the formula in cell C19 (the stock price). The IF statement is merely designed to
make it easier to expand the binomial tree by using Copy and Paste. If you copy this
equation to an area that is below the tree, it produces a period, “.”.
Now look at the formula in cell C20 (the bond price). This cell contains the same IF
statement as in the previous paragraph, but it also contains the MAX and MIN functions that
are used to price callable, convertible bonds (see slide 59 of Chapter 10).
Note that if the bond is not callable, you can find the bond price by setting the bond’s
strike price, K, equal to some large number, say ten times the bond’s face value. (Setting
K = 0 will not work.)
NOTE: In the following questions, when I ask for the bond price, I mean the price given in
cell H11, the bond with American-style features.
(a) For input variables, let r = 5%, q = 0, S = 40, K = 103, sigma = 0.3, settlement date =
26 October 2022, bond expiry = 21 June 2030, coupon rate = 5%, number of
coupons per year = 2, bond’s face value = 100, default intensity lambda = 2%,
8
conversion ratio = 2.1 (2.1 on a face value of 100 is equivalent to 21 on a face value
of 1000), recovery rate = 0.4. What is the price of this bond?
(b) Using the inputs in part (a), find the bond price if K = 0. How do you calculate this
price using the other input variables? Briefly state what would happen if the strike
price were this low.
In the following questions we’ll approximate first derivatives using the calculation
described on Slide 66 of Options #2. We’ll use the one-sided approximation.
For these calculations, a useful tool is to use Paste Special (Values): CTRL+ALT+V, and
then hit Enter.
(c) First, let us calculate the derivative, dB/d(sigma). Let 1B be the bond price using
the parameters in part (a). Then let 2B be the price when we replace sigma with
sigma + 0.0001. Then, calculate 2 1
0.0001
B B
. What is the value of dB/d(sigma)?
(d) Re-set the parameters to those given in part (a) and calculate dB/d(lambda). (This
time, replace lambda with lambda + 0.0001 to calculate 2B .) What is the value of
dB/dlambda.
(e) Using the same procedure, calculate dB/d(conversion ratio). What is the value of
this derivative?
(f) Using the same procedure, calculate dB/d(recovery rate). What is the value of this
derivative?
(g) Using the same procedure, calculate dB/dK. What is the value of this derivative?
(h) Which parameter has the biggest impact (positive or negative) on the bond price?
案例分类
- 经济Economics
- 金融Finance
- 会计Accounting
- 工商管理Business Management
- 计算机computer science
- 商业分析business analysis
- 法律Law
- 管理Management
- 市场营销Marketing
- 哲学Philosophy
- 心理学Psychology
- 物理Physical
- 统计学statistics
- 电气工程electric engineering
- 化学chemistry
- 生物biology
- 数学mathematics
- 语言学linguistics
- 土木工程civil engineering
- 工程力学engineering mechanics
- 艺术art
标签云
CONTACT US FOR
PROFESSIONAL HELP!
PROFESSIONAL HELP!
在线咨询
立即下单
Wechat：THEend8_ 
Email：24小时全年无休客服
版权所有：Copyright 2014 Higher Education Group 本网站创作的所有文案及辅导内容均仅供参考 网站建设：海坤软件