Question

PLEASE SOLVE IN EXCEL SHOW ALL WORK! MUST USE FORMULAS!! Thank you!!

You are saving for retirement in 40 years. You deposit $20,000 in a bank account today that pays 3.5% interest, compounded semiannually. You leave those funds on deposit until you retire. You also contribute $5,000 a year to a pension plan for 20 years and then you stop making contributions to the fund. You leave your money in the pension fund until you retire. The pension plan grows at a rate of 4% a year. How much will you have when you retire?

Answer 1

Solution

Here 2 investment are being made

1. 20000 @ 3.5% semi-annual compounding for 40 years

2. 5000 per year @4% for 20 years, then the amount accrued through this annuity for again 20 years @4%

Now first let us calculate the future value of 20000 @ 3.5% semi-annual for 40 years

FV = Amount invested*(1+r/2)^2n

where r= 3.5% and n=40

FV= 20000*(1+.035/2)^80= 20000*(1.0175)^80

= 80127.84

Now

Coming to the second investment

First, let us find the future value of the annuity

Since it is not given in the question if it is an annuity due, we will take it as an ordinary annuity

FV of annuity= Amount*[((1+r)^n-1))/r)]

n=20 years

r=4%

FV = 5000*[((1.04^20)-1)/.04)

=5000*[(2.191123-1)/.04=

=148890.4

Now this amount is invested for 20 years @ 4%

FV = Amount invested*(1+r)^n

= 148890.4*(1.04)^20

=148890.4*2.191123

=326237.2

Therefore total money at retirement=326237.2+80127.84

=406365.02