Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / Salwid-Fin, a financial planner, has hired you to develop a spreadsheet they will use with their clients
Salwid-Fin, a financial planner, has hired you to develop a spreadsheet they will use with their clients.
They’ve noticed that many of their clients ask about the consequences of withdrawing money from their super funds well before retirement. They want a spreadsheet that demonstrates these consequences to their clients.
Broadly, they’d like to show their clients a graph with seven curves on it: one, the client’s current super balance, two, the amount of super the client wants to withdraw, three, the fall in client’s annual retirement income stream (henceforth, RIS) in retirement, four, the target RIS the client is aiming for, five, the target capital the client requires at retirement (the lump
sum required to fund the RIS), six, the annual contributions required to hit the target RIS/capital if the super withdrawal goes ahead, and seven, the annual contribution to hit the target RIS/capital if no super is taken out now.
To achieve these objectives, you have been asked to develop the following.
Develop a spreadsheet with at least two sheets.
One sheet, titled ‘Inputs and Result’ consists of the following.
1. The Salwid-Fin logo
2. The client’s current age (an integer, from 18 to 64). Assume that it is the client’s birthday on the date he or she sees you.
3. The client’s gender (‘M’ or ‘F’)
4. The client’s current super balance (in dollars)
5. The client’s desired withdrawal (in dollars). Assume the withdrawal will be made today, on the client’s birthday.
6. Desired annual income between ages 65 to 85, inclusive, for males, assumed to be paid at the end of every year (so the first payment is at
age 66; the last payment is at age 85). This is the client’s desired, or target, RIS. For females, work with a payment period from age 65 to
87 (inclusive).
7. CY22, the Australian 10-year government bond yield for calendar year
2022 (i.e., the 10-year government bond yield on 31 December 2022, a
j2 rate).∗ We will adjust this rate, by adding 170 basis points to it, and assume this is the (constant) level of interest rates from the current time into the foreseeable future.
∗You will need to use FactSet to find this value.
1
ACST2001 Financial Modelling Group spreadsheet project S1 2023 [15 marks] Version 1.01
© 2023 Department of Actuarial Studies and Business Analytics, Macquarie University (All rights reserved)8. A horizontal bar graph, on a log scale, with seven components. These
are as follows.
(a) ‘Super balance now’
(b) ‘Super out now’
(c) ‘RIS fall at 65’
(d) ‘Target RIS at 65’
(e) ‘Target capital at 65’
(f) ‘Conts to target RIS’
(g) ‘Above if nothing out’
These seven components are detailed in the third paragraph of this document, above.
One sheet, titled ‘Model’, consists of the following: a cash flow diagram, drawn from the perspective of the client (choose either male or female),
and, following the principles developed in the lectures, models the super withdrawal, accumulation and decumulation (when the RIS is paid) phases.
Use the following notation on your diagram.
1. x, the current age of the client
2. B, the client’s current super balance, prior to any withdrawal
3. W, the client’s desired withdrawal (now)
4. c, the client’s required contributions prior to retirement to achieve
his/her desired RIS
5. R, the client’s desired RIS (annual)
6. i, the (constant) assumed rate of interest
In doing your calculations, note the following.
1. The client will retire at age 65.
2. The client will make contributions from the current age to age 65.
These contributions are made in arrears, so if the client’s current age
is x, then the first contribution will be made at age x + 1—the last contribution will made at age 65.
2
ACST2001 Financial Modelling Group spreadsheet project S1 2023 [15 marks] Version 1.01
© 2023 Department of Actuarial Studies and Business Analytics, Macquarie University (All rights reserved)3. The client’s first RIS payment will be at age 66, and will continue
annually up to and including age 85 for a male, and 87 for a female.
4. The rate of interest from now and into the foreseeable future will remain
constant at CY22 plus 170 basis points.
End of task
The submission deadline for the spreadsheet project is 9.00 a.m. on 2 May
2023. You will need to submit your solutions (in one functional .xlsx or
.xls file) to the link on iLearn prior to this time. Your spreadsheet should
be clearly labelled and easy to understand. Make sure you identify what the
“inputs” and “outputs” are. Include necessary information (e.g., title, axis
titles, etc.) in your plot. Document and describe the steps in the development
of each tab of your spreadsheet.
Please note that uploading a file can take up to 15 minutes. You need to
submit your file at least 20 minutes before the deadline to ensure a successful
submission.
3
ACST2001 Financial Modelling Group spreadsheet project S1 2023 [15 marks] Version 1.01
© 2023 Department of Actuarial Studies and Business Analytics, Macquarie University (All rights reserved)
