ACTL30006 Intermediate Financial
Mathematics: Assignment 2
Due: Friday 6 October, 5pm
General information: you must read this
This is an individual assignment. You may discuss the assignment with other
students and compare results, but you must write your own spreadsheet.
Assignments are to be handed in through LMS in electronic form. LATE
ASSIGNMENTS WILL INCUR MARK DEDUCTIONS. This assignment
counts for 15% of the overall assessment in this subject.
Your task
Consider two types of utility functions:
• power function
U(W ) = bW
1
2
• exponential function
U(W ) = 1− exp−aW
Your task is to build a spreadsheet model that allows the user to com-
pare portfolios by inputting the returns with various probabilities, the initial
wealth, and the coefficients for power and exponential utility functions. The
probability distribution for each return should be discrete and take up to a
maximum of six possible values.
The spreadsheet will output
1
1. the indifference prices for power utility and exponential utility corre-
sponding to the inputted values,
2. the order of preferences if an investor is investing all their money to
maximize the long-term growth rate,
3. Assume the same amount of money is inputted for each pair of invest-
ments. For each of the following investors, rank the the portfolios:
(a) an investor who makes decisions according to stochastic domi-
nance theory and prefers more to less;
(b) an investor who makes decisions according to stochastic domi-
nance theory, prefers more to less and is risk averse;
(c) an investor who is a mean-variance investor.
When comparing two investments, the sheet should return one of “A”,
“B”, “C” or “no preference”.
Format
Download the model spreadsheet. You must not change anything on sheet
“Test” except the contents of the cells marked in blue. The model should
automatically update the contents of these cells when the contents of the
yellow cells are changed.
You can put whatever you want into the rest of the workbook. This
assignment can be done using only spreadsheet functions and the markers of
your assignment will use a computer with macro disabled. You may find the
spreadsheet functions, IF, MMULT, MINVERSE and OFFSET helpful.
File name
The assignment file name should be “StudentID LastName FirstName”.
Marking
• Marking will be carried out by changing the yellow cells in your submis-
sion and seeing if the computed numbers in the blue cells are correct.
2
• Failure to follow the instructions above will result in your assignment
losing marks.
• This assignment is intended to mimic the sort of work an analyst does:
you are given a format to produce a model in, and you are expected to
produce a working debugged model in that format for someone else to
use.
Common mistakes
It is crucial that your spreadsheet is live. Do not cut and paste data to other
sheets. Make sure that changing the inputs changes the outputs. You must
not change anything in sheet “Test” other than the blue cells.
3