Question

You have a job offer ($40,000 per annum, plus 9.5% super. Your salary is expected to grow with the CPI each year, CPI is 3% per year.)

Your super fund is returning an expected 5% per year.

You are 26 years old and plan to retire at age 65, and assume you require $40,000 per year in present value $ from super fund when you retire.

Super earnings should be calculated based on the opening superannuation account balance.

1. Provide an excel spreadsheet of accumulated super for each year through to retirement.

Use the following headings in spreadsheet (year, age, salary, opening super balance, add employer super contributions (9.5%), less contributions tax (15%), add earnings (net of tax), closing super balance).

2. Provide an excel spreadsheet illustrating the withdrawal of the accumulated superannuation commencing retirement, detailing how long your superannuation is likely to last.

use the following headings for spreadsheet (year, age , opening pension balance, less pension withdrawal (real income required), add net earnings, closing pension balance)

Thanks, just looking to double check my numbers are correct!!

Answer 1

Please find the answer below, for year 20 after retirement only $20,000 can be afforded due to non-availability of balance in a superannuation fund.

For Question No 2
Year	Age	Opening Super Annuation	Pension Withdrawl	Earnings	Closing
1	66	$522,726	($40,000)	$22,215.84	$504,942
2	67	$504,942	($40,000)	$21,460.02	$486,402
3	68	$486,402	($40,000)	$20,672.07	$467,074
4	69	$467,074	($40,000)	$19,850.63	$446,924
5	70	$446,924	($40,000)	$18,994.28	$425,919
6	71	$425,919	($40,000)	$18,101.54	$404,020
7	72	$404,020	($40,000)	$17,170.86	$381,191
8	73	$381,191	($40,000)	$16,200.62	$357,392
9	74	$357,392	($40,000)	$15,189.14	$332,581
10	75	$332,581	($40,000)	$14,134.68	$306,715
11	76	$306,715	($40,000)	$13,035.41	$279,751
12	77	$279,751	($40,000)	$11,889.41	$251,640
13	78	$251,640	($40,000)	$10,694.71	$222,335
14	79	$222,335	($40,000)	$9,449.24	$191,784
15	80	$191,784	($40,000)	$8,150.83	$159,935
16	81	$159,935	($40,000)	$6,797.24	$126,732
17	82	$126,732	($40,000)	$5,386.12	$92,118
18	83	$92,118	($40,000)	$3,915.03	$56,033
19	84	$56,033	($40,000)	$2,381.42	$18,415
20	85	$18,415	($19,197)	$782.63	$0