Question

Simone is now 50 years old and plan to retire at age 67 (in 17 years). She currently has a share portfolio worth $750,000, a superannuation fund worth $1,200,000, and a money market (similar to cash) account worth $500,000. Her share portfolio is expected to provide annual returns of 12% p.a. (compounded annually), her superannuation will earn her 9% p.a. (compounded annually), and the money market account earns 1.2% p.a. (compounded monthly). Assume all these returns are aftertax. Assume Simone’s superannuation contribution is $25,000 (after-tax) per year for the next 17 years (starting 1 year from now).

a. If Simone stick to her current investment choices, how much will the total value of her investments be worth when she retires at 67 (in 17 years)?

Need complete formula and calculation process.

Answer 1

FUTURE VALUE OF SHARE PORTFOLIO:

Rate

Annual Return Rate

12%

Nper

Number of years

17

Pv

Current Value

$750,000

FV1

Future Value at 67

$5,149,531

(Using FV function of excel with Rate=12%, Nper =17, Pv=-750000)

FUTURE VALUE OF MONEY MARKET ACCOUNT:

Rate

Annual Return Rate

1%

Nper

Number of years

17

Pv

Current Value

$500,000

FV2

Future Value at 67

$612,405

(Using FV function of excel with Rate=1.2%, Nper =17, Pv=-500000)

FUTURE VALUE OF SUPERANNUATION FUND:

Rate

Annual Return Rate

9%

Nper

Number of years

17

Pv

Current Value

$1,200,000

Pmt

Annual Contribution

$25,000

FV3

Future Value at 67

$6,117,503

(Using FV function of excel with Rate=9%, Nper =17, Pmt=-25000Pv=-120000000)

Total Value of her investment at retirement (at age 67)

FV1+FV2+FV3

$11,879,438

fe =FV(D3,D4,-D5) D6 Н FUTURE VALUE OF SHARE PORTFOLIO: Annual Return Rate 12% Rate Number of years 4 Nper 17 $750,000 $5,149,531 l(Using FV function of excel with Rate=12%, Nper =17, Pv=750000) Current Value 5 Pv Future Value at 67 б FV Angment Mumpeт эулез Cens Cipooaru fe =FV(D9,D10,-D11) D12 Н FUTURE VALUE OF SHARE PORTFOLIO: Annual Return Rate 12% Rate Number of years 4 Nper 17 $750,000 Current Value Pv $5,149,531 (Using FV function of excel with Rate=12%, Nper =17, Pv=750000) FV1 Future Value at 67 6. FUTURE VALUE OF MONEY MARKET ACCOUNT: Annual Return Rate 1% Rate Number of years 10 Nper 17 $500,000 $612,405 l(Using FV function of excel with Rate=1.2%, Nper =17, Pv=500000) Current Value 11 Pv FV2 Future Value at 67 12 13 14 ш =FV(D15,D16,-D18,-D17) D19 Н K L. FUTURE VALUE OF SHARE PORTFOLIO: Annual Return Rate 12% Rate Number of years 4 Nper 17 Current Value $750,000 Py $5,149,531 Future Value at 67 (Using FV function of excel with Rate=12%, Nper =17, Pv=-750000) FV1 FUTURE VALUE OF MONEY MARKET ACCOUNT: Annual Return Rate 1% Rate Number of years 10 Nper 17 $500,000 Current Value 11 Pv (Using FV function of excel with Rate=1.2%, Nper =17, Pv=-500000) $612,405 Future Value at 67 12 FV2 13 14 FUTURE VALUE OF SUPERANNUATION FUND: Annual Return Rate 9% 15 Rate Number of years 16 Nper 17 $1,200,000 Current Value 17 Pv Annual Contribution $25,000 18 Pmt $6,117,503 Future Value at 67 (Using FV function of excel with Rate=9%, Nper =17, Pmt=-2500OPV=-120000000) 19 FV3 20