This project involves an implementati
on a binomial tree-based immunisation
strategy in an Excel application and test
ing the model against the empirical term-
The project comprises a core com
ponent, which is compulsory, and also
several optional extensions which woul
d complement the core component but
which are not strictly required.
Deadline: 13 May 2014 (Tuesday in Week 12), 3pm.
All your work should be contained in one single XLSM file.
Naming conventions: Name the file by your
own student ID, followed by “P2”. For
example, if your ID number is 9300432
1, then your file should be called
Submission is via KLE.
You are asked to set up an Excel
immunisation strategy based on a binomial
tree model of the term structure.
1.2 Form of the Coursework
The coursework consists of one single Excel
XLSM file. In
addition to the computational sheets,
the file should contain a text
worksheet called “report”, providing an info
rmative summary of your application.
Project 2 carries a weight of
60% in the overall module mark.
2 Style Guidelines
Your Excel application should
follow the general design
that guided us in Project
1. Your XLSM files shoul
d be self-explanatory
and should include detailed annot
ation and documentation.
Most of the computational work is
done in straight Ex
cel, without VBA.
However, in Topic A there is a need to
use the VBA code for the calibration of the
BDT model. You are encourag
ed to modify the ‘‘match
term’’ routine that was
discussed in class. Students who wish to
implement larger parts of their
application in VBA are welcome to do so.
2.3 Quality Criteria
Your submission should provi
de a workable application that
meaningful financial computations in an easy to use and easy to
maintain format, alone with a brief summary
of your test simulations. The main
quality aspects that will be consid
ered in the marking are:
(i) A solid and consistent overall st
ructure and design of your spreadsheet
(ii) A good layout and presentati
on of your worksheets and graphs;
(iii) An appropriate use of re
levant spreadsheet techniques;
(iv) The use of spreadsheet form
ulae that perform a correct and well-
documented processing of the inputs;
(v) A clear structure of
the computational work
sheets that makes the
application easy to maintain and modify;
(vi) An economically relevant explanat
ion of your model and relevant analysis
in the accompanying report.
Core and Extensions
Every submission should at least realise the ‘‘core’’
of the topic. Realisation of so
me of the optional components is
welcome but not strictly required. A relia
ble and elegant realisation of the core
without the extensions is much preferr
ed over a poor and unsuccessful attempt
at realising all the optional
components. Some of the
best submissions are likely
to provide correct realisations of some
but not all of the optional extensions.
Please do all the work entirely
on your own. If you have any
with your work, do
ask your colleagues for help but speak to your
2.6 Suggestions for the Report.
Your XLSM file shoul
d contain a “report”
describes your application and its
use. The report should give a
succinct summary of your work. The word
count should be in the region of 400 to
1200 words. There is no rigid list of r
equirements for your report; different
students may wish to focus on different aspec
ts of their work. However, here are
some aspects that your report may wish to address:
(i) An informal outline of the underlying financial models;
(ii) A description of t
he Excel implementation;
(iii) A discussion of the outcome
s of your Excel simulations.
of Project 2 (Core)
Implement the Black-Derman-Toy (BDT)
in Excel, for
rates of 1, 2, …, 12 years maturi
ty. The application should perform the
(i) Match the term structure for exogenous
ly given volatility coefficients;
(ii) Determine an immunis
ed bond portfolio for a liabi
lity with maturity h,
where date h may be at any date prior to year 12;
(iii) Test the performance of
the immunised portfolio over a one-year period.
Your application requires the
user to provide the following
(i) A single future liabilit
y L of 10000 pounds,
due at an arbitrary future date
. [Note: The user must be able to
the liability date
(ii) The 12 possible future payoffs of
two different bonds, A and B. Payoffs at
dates after a bond’s maturity are of c
ourse zero. [Note: You should specify
two example bonds, but the user should
be able to choose different bonds
with different payoffs and maturi
ties from your example bonds.]
(iii) 2 different term structures r and r’
, from 2 consecutive years. Each term
structure covers maturities up to date 10: r
. [Note: As the
developer of the applicati
on, you should provide sa
mple values from the
UK term structure, 1970–2014. Data
are on the module home page.]
(iv) The twelve volatility c
oefficients for the BDT tree: b
, … , b
you may fix these at b
= 0.2 for all maturity dates.]
provided by the application to the user:
(i) The full Black-Derman-To
y tree that matches the
given term structure at
the beginning of the year. In particular,
the application calibrates the drift
, … , a
and the resulting short rates s
that are consistent with the current
term structure r and the assumed
volatility coefficients b.
(ii) The prices of bonds A and B and
liability L under the original term
(iii) The predicted values V
, … of bonds A and B and
liability L at the various nodes of the tree.
(iv) The amounts x
of bonds A and B in an immunised portfolio Q
that has the same value as liability
L in both nodes of the tree at date 1,
regardless of an up
or down move.
values of portfolio Q and liability L one year ahead, under the
next year’s term structure r’. [Note:
Make sure to update the payoffs as
described in the
Notes on Dates
(vi) An assessment of the deviation Z
between your immunised
portfolio and your liabilities after one year.
(vii) Relevant supporting graphs.
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT ?
Our Service Charter
Excellent Quality / 100% Plagiarism-FreeWe employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers.
Free RevisionsWe keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed.
Confidentiality / 100% No DisclosureWe make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service.
Money Back GuaranteeIf the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit.
24/7 Customer SupportWe have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.