Question

Question 2. Upon starting your new job after college, you've been confronted with selecting the investments for your 401(k) retirement plan. You have four choices for investing your money:

• a money market fund that has historically returned about 0.50% per year.
• A long-term bond fund that has earned an average annual return of 4.0%
• A conservative common-stock fund that has earned 6.0% per year.
• An aggressive common-stock fund that has earned 9.0% per year.

a. If you were to contribute $5,500 per year for the next 35 years, how much would you accumulate in each of the above funds?

b. Now, change your worksheet so that it allows for less than annual investments (monthly, weekly, etc.). The annual investment will be the same, but it will be made in smaller, more frequent, amounts.

c. Set up a scenario analysis that shows your accumulated value in each fund if you were to invest quarterly, monthly, biweekly, and weekly. Create a scenario summary table of your results.

d. What relationship do you notice between the frequency of investment and future value? Create a column chart of the results that more clearly shows the outcome from more frequently investing.

I need to know the excel formula step by step to get this done. I have A but need B C and D

Answer 1

a. (Just added this part for completion, for the benefit of others too)

	Money Market Fund	Long-Term Bond	Conservative Common Stock	Aggressive Common Stock
Annual Return	0.50%	4.00%	6.00%	9.00%
Contribution per year	$            5,500.00	$         5,500.00	$                       5,500.00	$              5,500.00
No. of periods (in years)	35	35	35	35
No. of instalments per year	1	1	1	1
Total no. of instalments [Row 4 x Row 5]	35	35	35	35
Accumulated at the end:	$         2,9,799.58	$     405,087.24	$        612,891.29	$      1,186,409.15
Formula for the above:	=FV(B2,B6,-B3)	=FV(C2,C6,-C3)	=FV(D2,D6,-D3)	=FV(E2,E6,-E3)

b. I hope this is what you need (other than this, see screenshots at the end of the answer to see answer to this part properly)

	Money Market Fund	Long-Term Bond	Conservative Common Stock	Aggressive Common Stock
Annual Return	0.50%	4.00%	6.00%	9.00%
Contribution per year	$            5,500.00	$         5,500.00	$             5,500.00	$                5,500.00
No. of periods (in years)	35	35	35	35
No. of instalments per year
No. of instalments [Row 4 x Row 5]	0	0	0	0
Return per period [Row 2 / Row 5]	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Contribution per period [Row 3 / Row 5]	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Accumulated at the end:	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Formula for the above:	=FV(B7,B6,-B8)	=FV(C7,C6,-C8)	=FV(D7,D6,-D8)	=FV(E7,E6,-E8)

When formulas for Rows 6, 7, 8, 9 and 10 are applied exactly as given, it will calculate automatically.

c.
Summary:

	Money Market Fund	Long-Term Bond	Conservative Common Stock	Aggressive Common Stock
When invested quarterly [4 per year]	$        210,227.64	$     416,226.14	$       645,316.14	$       1,316,049.25
When invested monthly [12 per year]	$        210,323.08	$     418,793.35	$         652,992.22	$   1,348,317.88
When invested biweekly [26 per year]	$        210,348.79	$     419,490.45	$       655,090.05	$               1,357,245.73
When invested weekly [52 per year]	$        210,359.81	$     419,789.99	$         655,993.24	$   1,361,104.01

I simply entered 4, 12, 26 and 52 in the yellow box [after doing all the formulas as explained in (a) above], to find the summary values. Took less than a minute.

d. The relationship noticed is that when the frequency has increased, the future value has increased. For example, when it was just one-time investment per year, the future value of Money Market Fund was $209,799.58, but when it was quarterly, the amount became $210,227.64. Similar trend is observed for all the investments. Column Chart below [It was created by simply adding a row for annual results to the summary chart above, and then using the Column Chart in Excel in the insert tab - won't take more than a couple of minutes]:

SCREENSHOTS FOR ANSWER (b).

Another screenshot just to demonstrate the formula:

You can use the same table to find answer to the requirement (a) as well, by simply entering 1 in the yellow boxes.
And you can use the same table to find answer to requirement (c) by entering 4, 12, 26 and 52 in the yellow boxes.
You can use answer to requirement (c) along with entering 1 in yellow boxes, to make Column Chart for requirement (d)

Hope this helps. Feel free to ask for clarifications. Note that formulas may not work if you copy paste my table to Excel. That's why I added two screenshots, so you can see formula.

Consider leaving a thumbs up - that enables us to keep solving.

Good luck!