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

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

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

You make the following assumptions:

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

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

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

• Your employer must contribute 3% of your pay into yourKiwiSaver fund each year.https://www.kiwisaver.govt.nz/already/contributions/employers/ Youcan ignore any tax implications and assume your account receivesthe 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 endof every year. https://www.kiwisaver.govt.nz/new/benefits/mtc/

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

• Regardless of the return earned, the manager of your KiwiSaverfund 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 in35 years.

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

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

You and three friends have decided to jointly purchase aproperty to live in (it has four separate bedrooms and a communalbathroom, kitchen and living area). Your portion of the purchaseprice is $120,000. You have some money saved in your KiwiSaveraccount (see TASK 1) which you are able to withdraw to help financethe house purchase but you will still need to borrow $100,000.

Your local bank is willing to lend you $100,000 for a period of10 years at an interest rate of 5%p.a. The loan must be repaid,over the 10-year period, by equal monthly instalments. The firstpayment will occur exactly one month after you borrow themoney.

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

What is the amount of each monthlyinstalment?

$1,060.66

$694.44

$416.67

$1,111,11

$1,000.00

$666.66