Posted: September 13th, 2017

Financial Modelling

Financial Modelling

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-
structure data.
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.
Submission Rules
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
93004321P2.xlsm.
Submission is via KLE.
1 Assignment
1.1 Summary
You are asked to set up an Excel
application that
implements an
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.
1.3
Marks
Project 2 carries a weight of
60% in the overall module mark.
2
2 Style Guidelines
2.1
Application Design
Your Excel application should
follow the general design
principles
that guided us in Project
1. Your XLSM files shoul
d be self-explanatory
and should include detailed annot
ation and documentation.
2.2 VBA
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
provides
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
application;
(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.
2.4
Core and Extensions
Every submission should at least realise the ‘‘core’’
component
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.
2.5 Plagiarism
Please do all the work entirely
on your own. If you have any
problems
with your work, do
not
ask your colleagues for help but speak to your
tutor instead.
2.6 Suggestions for the Report.
Your XLSM file shoul
d contain a “report”
worksheet that
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.
3
3 Specification
of Project 2 (Core)
3.1 Task
Implement the Black-Derman-Toy (BDT)
binomial lattice
in Excel, for
spot
rates of 1, 2, …, 12 years maturi
ty. The application should perform the
following tasks:
(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.
3.2 Inputs
Your application requires the
user to provide the following
inputs
:
(i) A single future liabilit
y L of 10000 pounds,
due at an arbitrary future date
h
. [Note: The user must be able to
change
the liability date
h
.]
(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
1,
r
2,
…, r
12
. [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
1
, b
2
, … , b
12
. [Initially,
you may fix these at b
t
= 0.2 for all maturity dates.]
3.3 Outputs
Outputs
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
coefficients a
1
, a
2
, … , a
12
and the resulting short rates s
1,0
, s
1,1
, s
2,0
, …
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
structure r.
(iii) The predicted values V
1,0
, V
1,1
, V
2,0
, V
2,1
, V
2,2
, … of bonds A and B and
liability L at the various nodes of the tree.
(iv) The amounts x
a
and x
b
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.
(v) The
actual
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
and Payoffs
below.]
(vi) An assessment of the deviation Z
2
= (V’
q
– V
L
)
2
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 🙂