Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

(Can you please help me create a spreadsheet that satisfies following requirements? Thank you.) Subject: Financial Modeling You have been hired by Yasuki, a financial

(Can you please help me create a spreadsheet that satisfies following requirements? Thank you.) Subject: Financial Modeling

You have been hired by Yasuki, a financial planner, to create a spreadsheet that they would use with their clients.

Many of their clients have been observed to enquire about the consequences of withdrawing funds from their super funds before retirement. They need a spreadsheet that shows these effects to their customers.

Broadly, they'd like to show their clients a bar graph with seven bars 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.

In general, they want to show their clients a graph with seven curves on it: the first represents the client's current super balance; the second represents the amount of super the client wants to withdraw; the third represents the decline in the client's annual retirement income stream (hereafter, RIS) in retirement; the fourth represents the target RIS the client is aiming for; the fifth represents the target capital the client needs at retirement (the lump sum required to fund the RIS); and the sixth represents the annual contributions needed to reach the target RIS/capital if the super withdrawal proceeds. Seven: the annual contribution needed to reach the target RIS/capital if no super is taken out at this time.

To achieve these objectives, you have been asked to develop the following. Develop a spreadsheet with a title 'Inputs and Result' consists of the following.

1. The Yasuki logo

2. The client's current age 50

3. The client's gender: Male

4. The client's current super balance (in dollars): $200,000

5. The client's desired withdrawal (in dollars): $80,000

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.

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.

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.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Finance questions