Question

3. Nancy just had a new baby boy and plans to send him to college 18 years from now. She wants to deposit each winter in an education account which pays 11% (compounded annually) so that her boy will have enough money set aside that he can take out $20,000 at the beginning of each year to pay tuition, room and board, etc., for each of his five-year integrated master degree in finance. How much will Nancy need to deposit at the end of each year in order to meet the goal (Note: There will be 18 total deposits and the first deposit will be made in one year. Also, the last deposit will be made the day before the first withdrawal.) Interest rate is the same all through the periods. Hint: 0-18: ordinary annuity, deposit PMT at the END of every year for 18 times; 18-22: annuity due, withdraw 20,000 at the BEGINNING of every year for five times. Can you get the PVA of those four 20,000 @ point 18? The number you get is the future value of those 18 pmts (deposits). Then use that value as FV of the ordinary annuity (the 18 pmts), can you get the PMT?

a. 1,261.99

b. 1,471.84

c. 1,628.09

d. 1,831.73

Answer 1

1. First use, PV function in EXCEL to find the amount needed for study.

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

rate=11%

nper=5 years

pmt=20000

fv=0

type=1 because payments need to pay at the begining of each year

=PV(11%,5,20000,0,1)=$82,048.91

2. Now use PMT function to find the deposits made at end of each year.

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

rate=11%

nper=18 years

pv=0

fv=82048.91

type=0 becasue payments made at the end of each year.

=PMT(11%,18,0,82048.91,0)=$1628.09

option c is correct