In SPT3 we extend your modelling from SPT1 to income streams. Specifically, we generate the retirement income stream lost by withdrawing a lump sum (now) from your super fund. In addition, given an individual’s desired income stream at retirement, we generate the (lump sum) investment needed now to realise that goal.

Your modelling needs knowledge of interest rates applicable from today onwards. For simplicity, assume this rate is the Australian 10-year government bond yield as at the end of 2019 (31.xii.19), plus 100 basis points (i.e., a j2 rate). You can use FactSet to find this value.

A user of your spreadsheet will need to input the following.

  1. Age (an integer)
  2. Gender (‘M’ or ‘F’)
  3. Desired superannuation withdrawal amount (in dollars)
  4. Desired annual income for ages 65 to 85, inclusive (males), or 65 to 87, inclusive (females). Assume this annual amount is paid on each birthday, starting at age 65, and ending at 85 for males and 87 for females. This stream of annual payments is what we refer to below as the user’s retirement income stream.

The spreadsheet will then generate the following outputs.

  1. The reduction in the user’s retirement income stream by making the above withdrawal from the user’s superannuation fund. For a male user, this retirement income stream refers to constant annual payments made at ages 65 to 85, inclusive. For a female user, this retirement income stream refers to constant annual payments made from ages 65 to 87, inclusive.
  2. The lump sum required (today) at the user’s current (integer) age to generate the desired annual retirement income stream inputted.
  3. A bar chart consisting of one bar representing the reduction in the user’s annual retirement income stream (generated by the user’s current superannuation withdrawal) and another bar representing the user’s desired annual retirement income stream.

Test your spreadsheet model with a variety of different inputs and check its outputs by manual calculations to make sure your model works correctly.

$20.00 – Buy Solution

Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).

After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.

If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.