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 with appropriate Excel Function. Please show detailed Formulas.

Answer 1

Annual investment(A) = $5,000

Time period(n) = 20 years

Annual interest rate (r) = 8%

(A) When investment is made at the begining of a year, then future value of a series of equal cash flows can be calculated as under:

FVA = A[ {( 1 + r )ⁿ - 1}/ r] (1 + r )

= 5,000 [ { ( 1 + 0.08 )²⁰ - 1 } / 0.08] ( 1 + 0.08 )

= 5,000 [ { (1.08)²⁰ - 1}/0.08] (1.08)

= 5,000 [ (4.661 - 1)/0.08 ] (1.08)

= 5,000 [ 3.661/0.08 ] (1.08)

= 5,000 x 45.76 x 1.08

= $247,104

Hence, $5,000 invested annually @ 8% p.a.in the begining of a year for 20 years will grow to $247,104.

(B)

When investment is made at the end of a year, then future value of a series of equal cash flows can be calculated as under:

FVA = A[ {( 1 + r )ⁿ - 1}/ r]

= 5,000 [ { ( 1 + 0.08 )²⁰ - 1 } / 0.08]

= 5,000 [ { (1.08)²⁰ - 1}/0.08]

= 5,000 [ (4.661 - 1)/0.08 ]

= 5,000 [ 3.661/0.08 ]

= 5,000 x 45.76

= $228,800

Hence, $5,000 invested annually @ 8% p.a.in the end of a year for 20 years will grow to $228,800