MAA719 Superannuation and Retirement Planning

Published : 21-Sep,2021 | Views : 10

Question:

Kerry and Vishan would like a report which models and displays the following information:
A. Prepare a cash flow statement for the year ended 30 June 2017 based on the couple’s current financial position showing the cash surplus or deficit.
B. Based on the existing situation, calculate the total amount the couple are likely to accumulate in their superannuation accounts by the time Vishan retires at age 60. You are required to submit your excel spreadsheet and display superannuation projections with each account displayed on a separate A4 spreadsheet page.
C. Calculate the combined retirement income stream, expressed both in dollar terms and as a percentage of Vishan’s preretirement income, that the couple can expect to receive in retirement based on the accumulated amount you have determined in part B above. This should be based on Kerry’s life expectancy at the date of retirement.

Discuss any problems that the couple are likely to face with the adequacy of their income in retirement, particularly the ability of their accumulated super to generate a retirement income of 65% of their preretirement income.
D. Explain fully what strategies you would recommend to assist the couple increase long term wealth within their superannuation accounts.

Answer:

Working section
Year	Age	Salary	Opening superannuation balance	Add employer superannuation contributions (10%)	Salary sacrifice	Less contributions tax (15%)	Add earnings	Earnings tax	Closing super balance
2018	30	1,26,115	$88,450	12,611	2,000	12,911	3,892	584	93,458
2019	31	1,26,733	93,458	12,673	2,000	12,973	4,112	617	98,653
2020	32	1,27,354	98,653	12,735	2,000	13,035	4,341	651	1,04,043
2021	33	1,27,978	1,04,043	12,798	2,000	13,098	4,578	687	1,09,634
2022	34	1,28,605	1,09,634	12,861	2,000	13,161	4,824	724	1,15,435
2023	35	1,29,235	1,15,435	12,924	2,000	13,224	5,079	762	1,21,452
2024	36	1,29,868	1,21,452	12,987	2,000	13,287	5,344	802	1,27,694
2025	37	1,30,505	1,27,694	13,050	2,000	13,350	5,619	843	1,34,170
2026	38	1,31,144	1,34,170	13,114	2,000	13,414	5,903	886	1,40,888
2027	39	1,31,787	1,40,888	13,179	2,000	13,479	6,199	930	1,47,857
2028	40	1,32,433	1,47,857	13,243	2,000	13,543	6,506	976	1,55,087
2029	41	1,33,082	1,55,087	13,308	2,000	13,608	6,824	1024	1,62,587
2030	42	1,33,734	1,62,587	13,373	2,000	13,673	7,154	1073	1,70,368
2031	43	1,34,389	1,70,368	13,439	2,000	13,739	7,496	1124	1,78,440
2032	44	1,35,047	1,78,440	13,505	2,000	13,805	7,851	1178	1,86,813
2033	45	1,35,709	1,86,813	13,571	2,000	13,871	8,220	1233	1,95,500
2034	46	1,36,374	1,95,500	13,637	2,000	13,937	8,602	1290	2,04,512
2035	47	1,37,042	2,04,512	13,704	2,000	14,004	8,999	1350	2,13,860
2036	48	1,37,714	2,13,860	13,771	2,000	14,071	9,410	1411	2,23,559
2037	49	1,38,389	2,23,559	13,839	2,000	14,139	9,837	1475	2,33,620
2038	50	1,39,067	2,33,620	13,907	2,000	14,207	10,279	1542	2,44,057
2039	51	1,39,748	2,44,057	13,975	2,000	14,275	10,739	1611	2,54,885
2040	52	1,40,433	2,54,885	14,043	2,000	14,343	11,215	1682	2,66,118
2041	53	1,41,121	2,66,118	14,112	2,000	14,412	11,709	1756	2,77,771
2042	54	1,41,813	2,77,771	14,181	2,000	14,481	12,222	1833	2,89,859
2043	55	1,42,508	2,89,859	14,251	2,000	14,551	12,754	1913	3,02,400
2044	56	1,43,206	3,02,400	14,321	2,000	14,621	13,306	1996	3,15,410
2045	57	1,43,907	3,15,410	14,391	2,000	14,691	13,878	2082	3,28,906
2046	58	1,44,613	3,28,906	14,461	2,000	14,761	14,472	2171	3,42,907
2047	59	1,45,321	3,42,907	14,532	2,000	14,832	15,088	2263	3,57,432
2048	60	1,46,033	3,57,432	14,603	2,000	14,903	15,727	2359	3,72,500