FNCE90045 Financial Spreadsheeting, Assignment 2
Complete each of the following tasks:
Part A :: function procedures – module Part_A (8 marks)
1.. An analyst has developed code to emulate the WS DAYS360 function
The VBA code has syntax:
Source: https://excelatfinance.com/xlf20/xlf-emulate-days360-nasd.php
Required:
In your Part_A module, create a new function named xlfDAYS360NASD_v2 that also
allows for the case where start_date > end_date. Also consider whether the return
value equivalent to -DAYS360 (negative DAYS360)? If so, is it always the case?
[You might need to think about this issue]
2.. Required: Create another UDF to emulate the WS function DAYS360 European
method. Name this procedure xlfDAYS360EURO
3.. Required: Create TestMacro procedures for parts 1 and 2. Use relevant dates from
the FNCE90045 materials to demonstrate code operation.
FNCE90045 Financial Spreadsheeting, Assignment 2
Semester 2, 2020.
5
Part B Macro procedures (13 marks)
Required: Insert a Code Module and name it Part_B.
Write a Macro procedure named Pricer, to emulate the WS PRICE function.
The WS function has syntax:
PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
See: WS PRICE function for details
Only basis 0 and 4 are required in this section
An important feature is inclusion of an incomplete period between settlement and first
coupon. Described as DSC (days to first coupon) in resource #2,
Use your custom function from Part A to handle the time component. These can be called
from this module. More information about the count basis can be found at 1998-ISDAmemo-EMU-and-Market-Conventions-Recent-Developments.pdf
and Coupon Interest and
Yield for eTBs
Be aware that the price formula includes summation operators, so apply your knowledge
of For…Next loops to this part of the procedure.
The main macro, named Main will display an InputBox with comma delimited
parameters. Clicking OK will pass the parameters to the Pricer macro, this means that
Pricer has arguments. On completion, a MessageBox will display the result details. The
MessageBox activation technique is your choice.
Demonstrate relevant combinations of the input parameters including the values from
example 1 in resource #2. These can be the default parameters to the InputBox
Unsure that the code is readable!
Resources
1. Bond pricing - nominal-treasury-bond-calculation-coupon.pdf
2. WS implementation of example 1 from reference 2 xlf-bond-dsc-ass2s1y20.xlsx
[14 KB]
FNCE90045 Financial Spreadsheeting, Assignment 2
Semester 2, 2020.
6
Part C User interface + (10 marks)
The Correlation, and Covariance, Analysis Tools available in the Analysis ToolPak Add-in,
suffer from a number of operational deficiencies.
Your task is the develop a UserForm based tool, that solves these operational deficiencies, and
combines both Correlation and Covariance into one interface.
The test data set for the part, is four companies from the assignment 1. Your Part C WS will
have a Date column, followed by four columns of returns data with header row, as a
contiguous range. Time period is FY20.
Required:
1.. Add a UserForm module named Part_C, then create the following UserForm
interface.
Include all controls, and color scheme as shown.
2.. Colors include
RGB(0, 0, 255)
RGB(255, 204, 102)
RGB(255, 255, 153)
RGB(255, 255, 204)
3.. Event :: Initial display of interface
Input Range (default)
The Address of the CurrentRegion (ReSized to exclude the Date
column)
FNCE90045 Financial Spreadsheeting, Assignment 2
Semester 2, 2020.
7
Subject to, the CurrentRegion – Column A being valid dates, and other
Return columns being valid values (such as Numeric and no Errors)
Labels in First Row (User selected)
4.. Comovement type
User selected (includes combined choice). Covariance is the default setting.
5.. Output Options
User Choice
6.. Click for Help
Displays User Help feature. (Message Box or UserForm based)
7.. Submit your completed work with full output sent to the top region of the Part C
WS.
Resources:
Part D The open project (15 marks)
In this section you have the opportunity to develop a VBA based solution to a challenging
BUSINESS task in the Excel environment.
Whilst the project is open and can be based on any discipline from the Faculty of Business
and Economics, your idea must be in the spirit of Financial Spreadsheeting and does require
development of a Project Proposal (Specification).
Proposal ideas may include a calculation engine, input interface, and output interface. You are
encouraged to include data analysis and graphical (chart) output.
FNCE90045 Financial Spreadsheeting, Assignment 2
Semester 2, 2020.
8
The proposal: Describe the project’s function, with details of the finance, Excel, and VBA
components. The financial spreadsheeting proposal, including a title, a list of VBA
components and associated finance features, should not exceed the equivalent of 1 page of A4
paper.
The proposal is for use by the group members and does not require prior approval.
The proposal is your main avenue available to “air your ideas” as to the projects content and
validity to all group members. You are required to submit a copy of the original proposal,
which now forms the plan for the project, and the completed project, within the assignment
workbook. By this stage it should also include an outline of the plan to completion. Include it
as text in a scrolling UserForm window.
Be mindful of the project’s presentation. When opened, will the project’s purpose, features,
and operation be obvious.
General (4 marks)
Your workbook must include, but may not be limited to, the following sheets, in order:
Name: Introduction, including a range named WorkArea.
Name: Part A (even if not used)
Name: Part B (even if not used)
Name: Part C (even if not used)
Name: Part D (even if not used)
The Introduction should include:
• Your group name and logo (image)
• A list of all group members – student numbers only
• A series of navigation buttons – that provides access to your answers on the WS,
including, if needed, the WorkArea
• A list of references in proper academic style
Assignment question protocol
Any assignment question must be discussed by group members in the first instance. If
necessary, the group leader is then able to discuss the matter at the scheduled ZOOM
consultation sessions in weeks 10 to 12. Reminder, this work is part of your
assessment.