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.

It is calculated that your expected annual salary when you are 45 years old is $77,898

1. 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.?

Answer 1

		A	B	C	D=C*3%	E=C*3%	F	G=D+E+F	H	I=H*6%	J	K=H*1%	L=H+I+J-K
		Age	year from today	Salary	Employee Contribution	Employer Contribution	Tax Credit	Total Deposit	Opening Balance	Interest for the Year	Deposit At end of year	Management Fees	Closing Balance
		30	0	$50,000
		31	1	$51,500	$1,545	$1,545	$521.43	$3,611.43
		32	2	$53,045	$1,591	$1,591	$521.43	$3,704.13	$3,611.43	$216.69	$3,704.13	$36.11	$7,496.13
		33	3	$54,636	$1,639	$1,639	$521.43	$3,799.61	$7,496.13	$449.77	$3,799.61	$74.96	$11,670.55
		34	4	$56,275	$1,688	$1,688	$521.43	$3,897.96	$11,670.55	$700.23	$3,897.96	$116.71	$16,152.03
		35	5	$57,964	$1,739	$1,739	$521.43	$3,999.25	$16,152.03	$969.12	$3,999.25	$161.52	$20,958.89
		36	6	$59,703	$1,791	$1,791	$521.43	$4,103.59	$20,958.89	$1,257.53	$4,103.59	$209.59	$26,110.42
		37	7	$61,494	$1,845	$1,845	$521.43	$4,211.05	$26,110.42	$1,566.63	$4,211.05	$261.10	$31,626.99
		38	8	$63,339	$1,900	$1,900	$521.43	$4,321.74	$31,626.99	$1,897.62	$4,321.74	$316.27	$37,530.08
		39	9	$65,239	$1,957	$1,957	$521.43	$4,435.75	$37,530.08	$2,251.80	$4,435.75	$375.30	$43,842.33
		40	10	$67,196	$2,016	$2,016	$521.43	$4,553.18	$43,842.33	$2,630.54	$4,553.18	$438.42	$50,587.63
		41	11	$69,212	$2,076	$2,076	$521.43	$4,674.13	$50,587.63	$3,035.26	$4,674.13	$505.88	$57,791.14
		42	12	$71,288	$2,139	$2,139	$521.43	$4,798.71	$57,791.14	$3,467.47	$4,798.71	$577.91	$65,479.41
		43	13	$73,427	$2,203	$2,203	$521.43	$4,927.03	$65,479.41	$3,928.76	$4,927.03	$654.79	$73,680.41
		44	14	$75,629	$2,269	$2,269	$521.43	$5,059.20	$73,680.41	$4,420.82	$5,059.20	$736.80	$82,423.63
		45	15	$77,898	$2,337	$2,337	$521.43	$5,195.33	$82,423.63	$4,945.42	$5,195.33	$824.24	$91,740.15
	Balance in Account at the age45=$91,740.15