Question

You have just started a new job and your employer has enrolled you in KiwiSaver.

This is the first time you have been enrolled in KiwiSaver and you decide not to “opt out”.

You are interested in estimating how much your KiwiSaver fund could be worth when you retire.

You make the following assumptions:

• You have just turned 30 and will retire in exactly 35 years when you are 65.

• Your salary is $50,000 this year and you expect this to increase by 3% every year.

• You can choose to contribute either 3% or 8% of your salary into your KiwiSaver fund each year. https://www.kiwisaver.govt.nz/already/contributions/you/amount/

• Your employer must contribute 3% of your pay into your KiwiSaver fund each year. https://www.kiwisaver.govt.nz/already/contributions/employers/ You can ignore any tax implications and assume your account receives the full 3%. (KIWI SAVER ACC)

• You will be entitled to the annual member tax credit of $521.43 which will be credited into your KiwiSaver fund at the end of every year. https://www.kiwisaver.govt.nz/new/benefits/mtc/

• Your KiwiSaver fund will invest in a diversified portfolio of assets to earn a return on your investment. Of course, there is uncertainty around the actual annual rate of return that your fund will earn over the 35 years but you decide that 6% and 12% represent a good range of potential rates of return to conduct your analysis on.

• Regardless of the return earned, the manager of your KiwiSaver fund will charge a management fee of 1.0% at the end of each year, based on the opening balance of your fund each year.

• You will make no withdrawals or additional contributions (other than those mentioned above) to your fund until you retire in 35 years.

• For simplicity, assume that all contributions to your KiwiSaver fund are made once per year, at the end of the year. The first lot of contributions will be made in one year from today.

Construct a spreadsheet that will allow you to answer the following questions on Canvas.

How much will be in your KiwiSaver account at the start of that year if you contributed 3% of your salary and the fund earned 6%p.a.?

Options:

$89,396

$77,898

$99,061

$63,814

$98,769

$69,212

Answer 1

Please find the spreadsheet below

Age	Opening balance	Salary	Self contribution	Employer contribution	Tax credit	Management fee (1% of opening balance)	Total account balance
31	0	50000.00	1500.00	1500.00	521.43	0.00	3521
32	3521	51500.00	1545.00	1545.00	521.43	35.21	7309
33	7309	53045.00	1591.35	1591.35	521.43	73.09	11379
34	11379	54636.35	1639.09	1639.09	521.43	113.79	15747
35	15747	56275.44	1688.26	1688.26	521.43	157.47	20432
36	20432	57963.70	1738.91	1738.91	521.43	204.32	25453
37	25453	59702.61	1791.08	1791.08	521.43	254.53	30829
38	30829	61493.69	1844.81	1844.81	521.43	308.29	36582
39	36582	63338.50	1900.16	1900.16	521.43	365.82	42733
40	42733	65238.66	1957.16	1957.16	521.43	427.33	49305
41	49305	67195.82	2015.87	2015.87	521.43	493.05	56324
42	56324	69211.69	2076.35	2076.35	521.43	563.24	63814
43	63814	71288.04	2138.64	2138.64	521.43	638.14	71803
44	71803	73426.69	2202.80	2202.80	521.43	718.03	80321
45	80321	75629.49	2268.88	2268.88	521.43	803.21	89396
46	89396	77898.37	2336.95	2336.95	521.43	893.96	99061
47	99061	80235.32	2407.06	2407.06	521.43	990.61	109350
48	109350	82642.38	2479.27	2479.27	521.43	1093.50	120297
49	120297	85121.65	2553.65	2553.65	521.43	1202.97	131941
50	131941	87675.30	2630.26	2630.26	521.43	1319.41	144320
51	144320	90305.56	2709.17	2709.17	521.43	1443.20	157475
52	157475	93014.73	2790.44	2790.44	521.43	1574.75	171451
53	171451	95805.17	2874.16	2874.16	521.43	1714.51	186294
54	186294	98679.33	2960.38	2960.38	521.43	1862.94	202051
55	202051	101639.71	3049.19	3049.19	521.43	2020.51	218773
56	218773	104688.90	3140.67	3140.67	521.43	2187.73	236514
57	236514	107829.56	3234.89	3234.89	521.43	2365.14	255331
58	255331	111064.45	3331.93	3331.93	521.43	2553.31	275283
59	275283	114396.38	3431.89	3431.89	521.43	2752.83	296432
60	296432	117828.28	3534.85	3534.85	521.43	2964.32	318845
61	318845	121363.12	3640.89	3640.89	521.43	3188.45	342591
62	342591	125004.02	3750.12	3750.12	521.43	3425.91	367742
63	367742	128754.14	3862.62	3862.62	521.43	3677.42	394376
64	394376	132616.76	3978.50	3978.50	521.43	3943.76	422573
65	422573	136595.26	4097.86	4097.86	521.43	4225.73	452419

The number in the beginning of the year could be found out from the highlighted cells.

For beginning of 43rd year, it is 63814

For beginning of 46th year it is 89396

For beginning of 47th year, it is 99061