Introduction
You are working in an investment company as a pricing analyst and you are required to build a pricing and dividends analysis tool (VBA program) for index-linked bond assets purchased between January 1988 and January 2017.
The input parameters to the program are given as follows:
Date of purchase (month and year) and length of remaining term until maturity in whole years (fixed between 5 – 35 years). Optionally (for bonus marks), you can also consider:
o Optional redemption term (min 5 years interval)
o The date of first dividend payment, which would be treated as ex-dividend if up to
3 months after purchase and with-dividend otherwise
· Nominal amount (between £10,000 – £100,000)
· Purchase price (as a percentage of the nominal amount, between 80% – 115%)
· Income and capital gains tax rates (between 15% – 40%)
o Note: CGT would be applicable only when the purchase price is less than the indexed redemption amount (at maturity)
· Annual coupon rate (as a percentage of the nominal amount, between 3% – 6%)
· Frequency of payments (monthly, quarterly, half-yearly or yearly) – to be entered as
text or a recognisable fragment of text (e.g. “mon”, “year”, etc.)
· Redemption amount (as a percentage of the nominal amount, between 90% – 110%)
· Lag period for the indexation of coupons and redemption (4, 6 or 8 months) – to be
entered as an integer value
· Index table by years and months between Jan 1987 to Jan 2017 (e.g. RPI, as provided)
· Future average annual inflation rate (fixed between 2% – 4%)
o Required for all future coupon and redemption amounts payable after January 2017 (i.e. where no index is available)
The program should output the following:
Cash flow table (CFT)
o CF Dates, applicable index, base coupon/redemption amounts, indexed and taxed
coupon/redemption amounts (including purchase price) Estimated annual effective money yield based on the CFT
o Using linear interpolation of the NPV function by 0.25% intervals between 0%-20% o Any value outside of the above interval should be displayed as a value error
Note: the input and output of the program should be on separate worksheets.
Page 1 of 2
Coursework Tasks
Therefore, you need to build an Excel macro-enabled workbook that will aid the pricing and analysis of an appropriate ILB investment. Thus, you should design a VBA program that can be run interactively by a user. The program should be able to read the input parameters (detect or trap input errors), extract/find the applicable indexes, output the resulting CFT and run the yield estimation function.1
Finally, you need to provide a brief written description of your overall program structure in the Module ‘CW3_Set’ in the form of VBA comments. This should list the main features of the program and how they have been implemented (e.g. global/private variables, type of arguments that the UDFs/macros might require and what they represent, dependencies, etc.).
Furthermore, each UDF/macro should be annotated with brief comments at their own definition section regarding any special considerations and/or how the code works (e.g. above the declaration statements of the routines).
Other than that, you might describe here any additional features that you have managed to implement in the program (e.g. other than the minimum specifications, such as the price of the unindexed bond with the given estimated yield, etc.).
Submission
Submit on Moodle a macro enabled Excel WB with your VBA program and, optionally, a Word or pdf document with the description/instructions (alternatively, these can also be given in a separate worksheet of the workbook). You must rename the file before submitting as “AS1055_T2_2016-17 – CW3-GR-yy”, where yy is your group number!
There should be only a single submission by each group before the published deadline (see Moodle).
Please note that submissions after the deadline will carry 10% penalty per day.
1 Note, however, the pricing model should only run if ALL of the input parameters are entered and all the values are within the required specs!
Page 2 of 2
/docProps/thumbnail.jpeg