Question

If you deposit $45,000 into an account earning 6% interest, compounded monthly. And in addition to the initial investment, save $600 each month, how many months will it take to save $67,000?

Please explain with an Excel function if possible?

Answer 1

Solution

Total future value needed= 67000

Total future value needed=Future value of 45000+Future value of monthly amounts added

Future value of a cashflow=Cashflow*(1+r)^n

Assuming periodic payments made at end of month

According to Future value of ordinary annuity formula

Future value of monthly payments=Monthly payments*(((1+r)^n)-1)/r

where

Monthly payment=600

Total future value needed= 67000

r-intrest rate per period=6/12=.5% per month

n=number of periods=?

Putting values in formula

67000=45000*(1+.005)^n+600*(((1+.005)^n)-1)/.005

Solving we get

n=25.095 months (Number of months to accumulate 67000)

Using Excel formula

Values to be entered

NPER--number of periods-To be calculated

rate-.5%(Intrest rate per period)

PV-Present value 45000

FV-Future value- -67000

PMT (Periodic payments)-600

Type-0(End of period periodic payments)

Values entered

=NPER(0.005,600,45000,-67000,0)

Excel formula

=nper(rate,pmt,pv,fv,type)

Solving we get

nper=25.095 months (Number of months to accumulate 67000)

If you are satisfied with the answer,please give a thumbs up