Question

Suppose you want to have $800,000 for retirement in 35 years. Your account earns 7% interest.

a) How much would you need to deposit in the account each month?
$
b) How much interest will you earn?
$

Answer 1

a. Excel can be used to calculate this.

=PMT function has to be used in Excel.

=PMT(rate,nper,pv,fv)

Where rate is the interest rate per period. Taking monthly periods, the monthly rate = 1.07^(1/12) - 1 = 0.5654%

Nper is number of periods which is (35*12) = 420 here

Pv is present value which is zero here

Fv is future value which is 800,000 here.

=PMT(0.005654,420,0,800000) = $467.45

Ignore negative sign of excel as it shows this much amount needs to be deposited to get 800,000 later. Cash flow signs are thus opposite.

Thus, $467.45 needs to be deposited each month.

b. The amount of interest earned can be calculated as under

Future value - Amount deposited

= future value - (number of periods*amount deposited per period)

= 800,000 - (420*467.45)

= $ 603,671.07

Thus, interest earned is $603,671.

Comment in case of any query.