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 realize that goal. Your modeling needs knowledge of interest rates applicable from today onwards. For simplicity, assume this rate is the Australian 10-year government bond yield as of 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.

a. Age (an integer)

b. Gender (‘M’ or ‘F’)

c. Desired superannuation withdrawal amount (in dollars)

d. 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.

a. 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.

b. The lump sum required (today) at the user’s current (integer) age to generate the desired annual retirement income stream inputted.

c. 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.