Finance Assignment - Financial modeling

1

Keele University Fin-40003 Financial Modelling Spring 2014

Coursework Assignment

Project 1 The overall coursework comprises two projects, Project 1 and Project 2. The

present coursework sheet gives details on Project 1. Submission Rules Deadline: Wednesday 26 March (Week 9), 3pm. All your work should be contained in one single XLSX file. Naming conventions: Name the file by your own student ID, followed by "P1". For

example, if your ID number is 93004321, then your file should be called 93004321P1.xlsx.

Submission is via ‘‘Assessment’’ on the module’s webpage on KLE.

1 Assignment 1.1 Summary You are asked to develop a working Excel application that produces

relevant pricing and investment information about bonds, based on user-supplied

inputs. The applications should provide accurate and relevant output and should

offer a consistent and effective user interface.

1.2 Form of the Coursework The coursework consists of one single Excel XLSX file.

The file should contain a text worksheet explaining how to use the application.

1.3 Marks Project 1 carries a weight of 40% in the overall module mark.

1.4 Quality Criteria Your submission should provide a workable application that

provides meaningful financial computations in an easy to use and easy to maintain

format, along with relevant user instructions.

The main quality aspects that will be considered in the marking are:

A solid and consistent overall structure and design of your spreadsheet

applications;

A helpful and consistent user-interface that makes it easy for the user to

understand and utilise your applications without outside help;

Good layout and presentation in your worksheets and graphs;

Appropriate use of relevant spreadsheet techniques;

Spreadsheet formulas that perform a correct and well documented processing of

the user-supplied inputs; Clear structure of the computational worksheets, that makes the application easy to

maintain and modify.

 Relevant and well supported analysis and explanations where required.

1.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

2 Project 1 2.1 Part 1

Develop an application that computes an immunised bond portfolio Q for a

given future liability L, based on user-supplied inputs. The overall time horizon

is 12 years. Inputs Your application requires the user to provide the following inputs: A single future liability L, due at a given date H between date 2 and date 12. You

may offer the user 11 consecutive cells, labelled 2, 3, …, 12 in which to type in

the amount of his liability at the due date. All other cells remain zero.

The future payoffs of two different bonds, A and B. Payoffs at dates after a

bond’s maturity are of course zero. The current term structure, spot rates r1, r2 , …, r12. An alternative term structure, spot rates r’1, r’2, …r’12, thought of as the term

structure of the ‘‘next day’’. As the developer of the application, you should

provide sample values for all required inputs. UK term structure data are

available on the module home site. Outputs Outputs provided by the application to the user: The bond prices pa and pb under the given original term structure r.

The present value VL of liability L under the original term structure r. The bond prices p’a and p’b and liability value V’L under the alternative term

structure r’. The durations and modified durations (Fisher-Weil) of bond A, bond B and liability L for the original term structure only. The amounts xa and xb of a portfolio Q that covers L and is immunised against

parallel shifts. A measure of the value gap Z between the value V’Q of your portfolio Q and the

value V’L of liability L under the alternative term structure.

Relevant supporting graphs.

2.2 Part 2

Use your application to produce a data table and a graph(s) that demonstrate the

relationship between the duration and the coupon rate. Provide a relevant

explanation of this relationship in no more than 400 words.

Part 2 of the project should be implemented on a separate (single) worksheet of

your Excel file.