Question

Erika deposited $200 monthly in a fund earning j12 = 12%. The first deposit was made on June 1, 1990 and the last deposit on November 1, 2000.

From May 1, 2005, she plans to draw down the fund with monthly withdrawals of $1,000. Find the date and the size of the smaller concluding withdrawal one month after the last $1,000 withdrawal.

Answer 1

Number of months of periodical deposits commencing June 1, 1990 and ending on November 1, 2000= 126

Interest rate is given as j12 = 12% ie., 12% interest per year, compounded monthly.

The deposits constitutes an annuity due. Value of this annuity on December 1, 2000 = $50,569.23

Details of computation as follows:

А в с 1 Future Value of Annuity Due Payments at the beginning of each period 2 3 Future value of annuity is calculated using the formula PVA=P* {[(1+r)^n]-1*(1+r)]/r] 4 Where P= Periodical payments, 5 n= Number of payments. Eg. if number of years=N, then n=N*2 for semi annual payments 6 r= Rate of interest per period in decimals All amounts in $ Given that Periodical payments (P)= 200 No. of years (N)= 10 Rate of interest (% p.a)=R= 12 No. of payments a year (t)= 9 10.5 11 13 12 Calculations: Formula Cell reference 14 Interest rate per period in decimals (r )= R/(100*t) D10/(100*G10) 15 No. of periods (n)= N*t G9*G10 16 (1+r)= 1+G14 (1+r)^n= G164G15 [(1+r)^n)-1= G17-1 [(1+r)^n]-1*(1+r)]/r= G18*G16/614 20 Future value of annuity FVA Due= P*{[(1+r)^n]-1*(1+r)]/r} D9*619 Rounded to 22 Value 0.01 126 1.01 3.50342719 2.50342719 252.846147 50569.2293 $50,569.23

Future value of this amount on May 1, 2005 (after 53 months) with interest at 12% per year

= $50,068.54*(1+12%/12)^53= $85,687.83

Monthly withdrawals of $1,000 from May 1, 2005 constitutes an annuity due with present value of $85,687.83. Number of such monthly withdrawals is 189.59 calculated using the NPER function of Excel as follows:

D9 O f ic A 1 2 Future value required 3 Payment frequency 4 Number of payments a year (t) 5 Amount of periodical payment 6 Interest rate (YTM) 7 Interest rate per period 8 Present balance 9 Number of payments NPER(D7,D5,D8,D2,1) B C Formula Values Given Fv $0 Given Monthly 12 Given PMT ($1,000.00) Given 12.00% YTM/t Rate 1.00% PV $85,687.83 NPER 189.591

This means withdrawal of $1,000 during 189 months commencing May 1, 2005 and ending on January 1, 2021.

The smaller concluding withdrawal will be on February 1, 2021.

The amount of last withdrawal will be $1,000*59/100 =$590.