ACTL2111/5102 2020 Term 1 Excel Assignment
Deadline: Friday 17 April 2020 at 5pm sharp
Jackson is a fresh actuarial graduate who just started his first full time job in a reputable insurance
company in Sydney on 1 January 2020. Upon the start of his job, Jackson also decided to make an
investment to accumulate a deposit to purchase his first home in 10 years time. As a junior actuary who
passed Part I and Part II exams, Jackson has a personal preference of passive investment strategies,
that is, investments consisting of fund tracking well-diversified portfolios and government bonds only.
To make such important investment decision, he consulted a bank regarding his investment strategy.
You, a junior analyst in the bank, have received the case from your supervisor, with the following
information.
a. Salary:
(i) Jackson's annual starting salary is $60,000 (after income tax) and it will increase by 5% every
year at the beginning of each year.
(ii) There are three actuarial exams Jackson will take. His salary will increase by 20% (i.e. 15%
extra compared to the regular increase described in paer (i) above) at the beginning of the
year after he passes an exam. Since he is working full time, he will take only one exam every
year and his chance of passing each exam is 30%, independent of his performance in previous
exams (if there is any).
(iii) He receives his salary monthly on the last day of each month (i.e. the first payment will be
on 31 January 2020).
b. Savings: Jackson is very keen on saving as much as possible for his deposit, so he has decided that
each month when he receives his salary, he will put 30% of it to his investment portfolio.
c. Investment options: After a meeting with Jackson, your supervisor has shortlisted the following
two investments for Jackson:
Option 1: 70% Vanguard Australian Shares Index Fund; 30% government (zero-coupon) bonds
maturing on 31 December 2029,
Option 2: 50% Vanguard Australian Shares Index Fund; 50% government (zero-coupon) bonds
maturing on 31 December 2029.
For example, in Option 2, Jackson will invest 15% of his salary to in government bonds and 15%
of his salary in the index fund every month. The index fund does not pay dividends.
d. Bond return: Your colleague in the interest rate modelling team has given you the current spot
yield curve using the dynamic Nelson-Siegel model, where the (annual effective) τ -year spot yield
at time t is it,τ% with it,τ given by
it,τ = β1,t + β2,t
(1− e−λτ
λτ
)
+ β3,t
(1− e−λτ
λτ
− e−λτ
)
, (1)
where λ = 0.7173 is a parameter. Here both t and τ are measured in years, where t is the amount of
time from 1 January 2020 and τ is the remaining time (counting from time t) until maturity. (Note
that it,τ will not be known before time t, and therefore from time 0's perspective it is modelled as
a random variable.) The starting values of the β's are given by
β1,0 = 3.5,
β2,0 = − 1.5,
β3,0 = − 2,
1
and then for subsequent values of t = 1/12, 2/12, ..., 10 they satisfy the recursive relationship
β1,t = 0.35 + 0.9 β1,t−1/12 + ε1,t.
β2,t = − 0.28 + 0.8 β2,t−1/12 − ε1,t,
β3,t = − 0.81 + 0.7 β3,t−1/12 + ε2,t,
with
ε1,t ∼ N(0, 0.11),
ε2,t ∼ N(0, 0.21).
Here all εj,t's are assumed to be independent (j = 1, 2 and t = 1/12, 2/12, ..., 10).
*Note that negative interest rates are possible but rare, due to the dynamics of the β's.
e. Return for the fund: the log return
1
for period [t, t+ 1/12] is rt% with rt given by
it,1 + 5
12
+ σ Zt (2)
with
σ = 3.6,
Zt ∼ N(0, 1),
where t is the beginning of a month and the random variables Zt's are independent.
f. Costs and fees and others: At this stage, your supervisor is going to present the two products
(described in c.) in preliminary form to Jackson so you do not need to consider the details such as
costs, fees, taxes on investment and reinvestment of the bond coupons.
You need to perform the following analyses for your supervisor to present to Jackson:
1. Project Jackson's salary over the next 10 years (from the first payment on 31 January 2020 to
the payment on 31 December 2029), under the following scenarios:
(i) He passes the three exams in 2022, 2024 and 2026 (average scenario);
(ii) He passes the three exams in 2020, 2021 and 2022 (best scenario);
(iii) He passes the three exams in 2024, 2028 and does not pass the last exam before 2030 (bad
scenario).
Also calculate the probability of each of the scenarios (i)-(iii).
2. Interpret the β terms in (1). Via simulation, plot the spot yield curves at 1 January 2025 and
1 January 2029 respectively, for 0 ≤ τ ≤ 10. (Hint: You only need to simulate one trajectory
when plotting the two yield curves, and the yield curves are dependent.) Explain the shape of
the curves. Also interpret the formula (2).
3. For each of the options in the average scenario, run 100 simulations on the terminal value of
Jackson's investments and calculate relevant summary statistics including the mean, standard
deviation, minimum, maximum, median. Plot the histograms for the terminal values of the two
options in the same graph.
*Hint: You may want to create a new worksheet and use VBA to copy your answer in each
simulation to the new worksheet.
1
The log return of the fund over the period [t1, t2] is defined to be log
F (t2)
F (t1)
, where F (t) is the fund value at time t.
2
4. Provide a brief recommendation to Jackson based on your analysis (max. 250 words). This
should be placed in a separate sheet within the same EXCEL file.
Assignment submission procedure
Your assignment must be uploaded as a unique EXCEL document. As long as the due date is still
future, you can resubmit your work; the previous version of your assignment will be replaced by the
new version.
Assignments must be submitted via the Turnitin submission box that is available on the course Moodle
website. Turnitin reports on any similarities between their own cohort's assignments, and also with
regard to other sources (such as the internet or all assignments submitted all around the world via
Turnitin). More information is available at: [click]. Please read this page, as we will assume that you
are familiar with its content.
Please note that the School of Risk and Actuarial Studies will apply the following policy on late
assignments. A penalty of 25% of the mark the student would otherwise have obtained, for each
full (or part) day of lateness (e.g., 0 day 1 minute = 25% penalty, 2 days 21 hours = 75% penalty).
Students who are late must submit their assessment item to the LIC via e-mail. The LIC will then
upload documents to the relevant submission boxes. The date and time of reception of the e-mail
determines the submission time for the purposes of calculating the penalty.
You need to check your document once it is submitted (check it on-screen). We will not mark
assignments that cannot be read on screen.
Students are reminded of the risk that technical issues may delay or even prevent their submission
(such as internet connection and/or computer breakdowns). Students should then consider either
submitting their assignment from the university computer rooms or allow enough time (at least
24 hours is recommended) between their submission and the due time. The Turnitin module
will not let you submit a late report. No paper copy will be either accepted or graded.
In case of a technical problem, the full document must be submitted to the LIC before the due time
by e-mail, with explanations about why the student was not able to submit on time. In principle,
this assignment will not be marked. It is only in exceptional circumstances where the assignment was
submitted before the due time by e-mail that it may be markedand this only if a valid reason is
established (and the LIC has the discretion in deciding whether a given reason is valid).
Plagiarism awareness
Students are reminded that the work they submit must be their own. While we have no problem with
students discussing assignment problems if they wish, the material students submit for assessment
must be their own. In particular, this means that any code you present are from your own computer,
which you yourself developed, without any reference to any other student's work.
While some small elements of code are likely to be similar, big patches of identical code (even with
different variable names, layout, or commentsTurnitin picks this up) will be considered as plagiarism.
The best strategy to avoid any problem is not to share bits and pieces of code with other student
outside your group.
Note however that you are allowed to use any EXCEL files that were made available during the course
(either from the lectures or developed in the lab tutorials). You don't need to reference them formally,
and this will not be considered as plagiarism.
Students should make sure they understand what plagiarism iscases of plagiarism have a very high
probability of being discovered. For issues of collective work, having different persons marking the
assignment does not decrease this probability. For more information on plagiarism, see [click].
3
Students may consult the Write well; Learn deeply" website [click] and consult the resources provided
there. In particular, all students should do the quiz about plagiarism to make sure they know how to
avoid any issue. For instance, did you know that sharing any part of your work with other students
(outside your group) before the deadline is already considered as plagiarism?
2
Assessment criteria
Please see the file Rubric".
2
Yes, that's right, just sending it, even if the third party promises not to copy, is already plagiarism in the UNSW
policy!
4