Question

Tom White has just started work at 18 years of age and intends to retire at 55. He will be saving $150 per month during his working life. He invests his money in a superannuation fund paying 8.5% p.a. compounding half- yearly. a) How much will he have in this fund when he retires? Provide a clear explanation of why you think this is a future/present value annunity question. (4 marks) b) On retirement, Tom rolls this money over into an allocated pension paying 6.4%p.a. compounding fortnightly. Calculate how much per fortnight he could withdraw, if the pension were to last 15 years. (3 marks) c) What is the outstandarding balance after 10 years? (2 marks) d) If an emergency withdrawal of an extra $25,000 was needed at the end of the 5th year, what effect would that have on how long the annuity would last? (4 marks)

Answer 1

Part A:

Solution for Part A is as follows -

Remember that compounding frequency is half yearly, so all the numbers have been considered at the scale of 6 months.

Total value of investment can be calculated using the Excel Formula"FV".
FV takes following inputs -

Rate - Interest earned on the investment
NPER - Total time period
PMT - Investment amount
PV - Present value of investment
Type of investment - Beginning or end of period

Because Tom starts paying from the very first month by the end of first 6 months he must have already accumulated 900$, s0 we can assume it to be beginning of period investment.

55 1 Tom Starts Work at age 2 Tom Retires at age 3 Savings per Month 4 Compounding Frequency 5 Interest of Super Annuation Fund 150 Half Yearly 0.085 7 Total No. of years worked 8 Total No. of "Half-Years" Worked 9 Total Savings in 6 months 10 Half Yearly Interest =B2-B1 =B7*2 =B3*6 =B5/2 =-FV(B10,B8, B9,0,1) 12 Total Future Value of investment 13

1 Tom Starts Work at age 2 Tom Retires at age 3 Savings per Month 4 Compounding Frequency 5 Interest of Super Annuation Fund Half Yearly 6 7 Total No. of years worked 8 Total No. of "Half-Years" Worked 9 Total Savings in 6 months 10 Half Yearly Interest 11 12 Total Future Value of investment 12 900 4.25% *4,58,274.15

Part B:

Again, as compounding frequency is fortnightly, all calculations have been done at fortnight level. We have assumed that there are 52 weeks in an year and hence 26 fortnights.

Value of annuity is calculated using PMT formula of Excel, which takes following inputs -
Rate - rate earned on investment
NPER - total number of payments received
PV - Present value of investment
FV - Total Value at the end of investment period
Type - Beginning or end of period.

We assume that Tom starts getting paid at the end of first fortnight, hence it is an end of period type payment.


=-FV(B10,88,89,0,1) 0.064 =B15/2 12 Total Future Value of investment 13 14 After Retirement interest earning 15 Total no. of weeks in year 16 Total No. of fortnights 17 Period of pension investment in years 18 Period of pension investment in fortnights 19 Fortnightly Interest Rate 20 Annuity Value 21 =B17*B16 =B14/B16 --PMT(B19,B18,B12,0,0)

А 4,58,274.15 6.40% 12 Total Future Value of investment 13 14 After Retirement interest earning 15 Total no. of weeks in year 16 Total No. of fortnights 17 Period of pension investment in years 18 Period of pension investment in fortnights 19 Fortnightly Interest Rate 20 Annuity Value 26 390 0.246% * 1,829.32 21

So, Tom can withdraw $1829.32 every year.

Part C:

To calculate outstanding balance after 10 years, again use FV formula of Excel. FV takes following inputs -

Rate - Interest earned on the investment
NPER - Total time period
PMT - Investment amount
PV - Present value of investment
Type of investment - Beginning or end of period

Notice that PV of investment and PMT should have opposite sign(Negative and Positive) to correctly calculate the future value.


|--FV(B10,18,19,0,1) 0.064 52 =B15/2 12 Total Future Value of investment 13 14 After Retirement interest earning 15 Total no. of weeks in year 16 Total No. of fortnights 17 Period of pension investment in years 18 Period of pension investment in fortnights 19 Fortnightly Interest Rate 20 Annuity Value 21 22 To calculate value at end of (Years) 23 To calculate value at end of (Fortnights) 24 Value at end of 10 years =B17*B16 =B14/B16 |--PMT(B19, B18,B12,0,0) 110 =B22*B16 =-FV(B19,B23,-B20,B12,0) 25

12 Total Future Value of investment *4,58,274.15 13 14 After Retirement interest earning 15 Total no. of weeks in year 16 Total No. of fortnights 17 Period of pension investment in years 18 Period of pension investment in fortnights 19 Fortnightly Interest Rate 20 Annuity Value 390 0.246% 1,829.32 22 To calculate value at end of (Years) 23 To calculate value at end of (Fortnights) 24 Value at end of 10 years 260 *2,03,303.15 25

Part D:

This part can be answered in 3 steps.
1. First find value of investement after 5 years.
2. Deduct an extra withdrawal of 25,000
3. Then find out how many periods the remaining value lasts given same amount is getting withdrawn.

For step 3 - NPER formula of excel is used which takes following inputs -
Rate - rate of interest
PMT - payment done every period
PV - Present value of investment
FV - Future value of investment
Type - beginning or end of period payment

17 Period of pension investment in years 18 Period of pension investment in fortnights 19 Fortnightly Interest Rate 20 Annuity Value 15 =B17*B16 =B14/B16 --PMT(B19,818,B12,0,0) 22 To calculate value at end of (Years) 23 To calculate value at end of (Fortnights) 24 Value at end of 10 years 10 =B22*B16 --FV(B19,B23,-B20,B12,0) 25 25000 26 Extra Withdrawal Amount 27 Withdrawal At the end of(Years) 28 Withdrawal at the end of(Fortnights) 29 Value of investment at end of 5 years 30 Value of investment at end of 5 years after extra withdrawal 31 Periods above investment will last after 5 years(in fortnights) 32 Total Time investment will last(in fortnights) =B27*B16 --FV(B19,828,-B20,B12,0) =B29-B26 =NPER(B19,-B20, B30,0,0) =B28+B31 33

17 Period of pension investment in years 18 Period of pension investment in fortnights 19 Fortnightly Interest Rate 20 Annuity Value 15 390 0.246% * 1,829.32 21 10 260 * 2,03,303.15 25000 22 To calculate value at end of (Years) 23 To calculate value at end of (Fortnights) 24 Value at end of 10 years 25 26 Extra Withdrawal Amount 27 Withdrawal At the end of(Years) 28 Withdrawal at the end of(Fortnights) 29 Value of investment at end of 5 years 30 Value of investment at end of 5 years after extra withdrawal 31 Periods above investment will last after 5 years in fortnights) 32 Total Time investment will last(in fortnights) 33 130 * 3,50,989.60 *3,25,989.60 234.86 364.86


Hence, investment will last till 364 fortnights and Tom will withdraw only a partial amount in in 365th fortnight.