Question

1. Estimate the future value of your retirement account based on the following assumptions. You contribute $500 per month and your employer matches 50% of your contribution. You invest the monthly contribution into two funds, equities and fixed income, which are expected to earn 9% and 3% annual rates of return, compounded monthly. You allocate your monthly contribution between equities and fixed income, 75% and 25%, respectively. Your current retirement account investments consist of $12,000 of equities and $5,000 of fixed income. You just turned 27 and will maintain your current investment program until you turn 65. Please answer the following questions. (Hint – perform separate calculations for the equity and fixed income account balances).

1. Write out the excel formula you will use to perform the calculation(s).
2. Calculate the future value of equity and fixed income account balances when you are 65 as well as the total investment account balance.

Answer 1

Your Contribution Per month	$ 500.00
Employer's Contribution Per month (500*50%)	$ 250.00
Total Monthly contribution	$ 750.00
Allocation of monthly contribution
Equity (750*75%)	$ 562.50
Fixed (750*25%)	$ 187.50
Total Monthly contribution	$ 750.00
Number of years (65-27)	38
Contribution made for the number of months (38*12)	456

Future value of equity	$                                         2,550,816
Fixed income account balances	$                                             174,786
Total investment account balance	$                                         2,725,602