Question

It is December 10th and you just met with your financial advisor, who suggested that you begin putting $5,000 into a Roth RIA at the beginning of each year, starting next month.

Your advisor says that, although there is no guarantee, you could average an annual return of 8% over the next twenty (20) years with the right mix of investments. You want to know just how big of a "nest egg" you can create if you take her advide and then actually average an 8% annual rate of return.

(A) How big will your nest egg be at the end of the year in which you make your 20th annual contribution?

(B) How big will your nest egg be at the end of the year in which you make your 20th annual contribution if you invest at the end of each year instead of at the beginning of each year as your advisor recommends?

PLEASE answer in Excel (or at least showing the appropriate Excel Function that can be used) Please show detailed Formulas. Please do not just answer, make it applicable to excel.

Answer 1

1	A	B	C	D	E
2
3	pmt	$5000
4	rate	8.00%
5	number of years	20
6
7			A. how big will your nest egg be at the end of year in which you make 20th annual contribution
8					EXCEL FUNCTION
9			future value of $5000 contribution per year for 20 years @8% =	$247114.61	FV(B4,B5,-B3,0,1)
10			FV (rate, nper, pmt, [pv], [type])
11			rate - The interest rate per period.
12			nper - The total number of payment periods.
13			pmt - The payment made each period. Must be entered as a negative number.
14			pv - [optional] The present value of future payments. If omitted, assumed to be zero. Must be entered as a negative number.
15			type - [optional] When payments are due. 0 = end of period, 1 = beginning of period. Default is 0.
16			Here type shall be 1 as each contributions are made at the beginning of each year
17
18			B. How big will your nest egg be at the end of year in which you make 20 annual contributions at the end of each year
19					EXCEL FUNCTION
20			future value of $5000 contribution per year for 20 years @8% at the end of each year=	$228809.82	FV(B4,B5,-B3,0,0)
21			FV (rate, nper, pmt, [pv], [type])
22			rate - The interest rate per period.
23			nper - The total number of payment periods.
24			pmt - The payment made each period. Must be entered as a negative number.
25			pv - [optional] The present value of future payments. If omitted, assumed to be zero. Must be entered as a negative number.
26			type - [optional] When payments are due. 0 = end of period, 1 = beginning of period. Default is 0.
27			Here type shall be 0 as each contributions are made at the end of each year