a financial scenario using a 17/19BII

[Justin]

Hi there.

I came up with a scenario that I'm not sure how to solve with my 17BII:-

John is retiring in 20 years. He wants to be able to withdraw the inflation adjusted equivalent of $50,000 a year for 10 years after retirement. How much does he need to deposit annually up until retirement for his needs to be met, if he can get 8% on his money and inflation is assumed to be 3.5%?

To tackle this I worked out the inflation adjusted payment for each of the ten years after retirement, but as the cash flow isn't a normal annuity (equal payments for each period) I don't know how to work out what the savings schedule should be because I can't figure out the lump sum that gets John his payments while still taking advantage of interest over the 10 years.

Any gurus out there know the way to solve one of these using a 17BII?

**********************************************************

EDIT:

Actually, I think I've got it....

I would do a CFLO list of the ten payments and use the NPV at an I of 8% for the lump sum target amount.

Can someone confirm this is correct?

Thanks in advance!

Edited: 22 June 2003, 7:14 p.m.

[Stephan (Germany)]

Dear Justin,

before trying to solve the actual problem, I'd like to know if you assume the yield of 8 % as constant over the whole 30 years. It might be reasonable to set the yield rate to 8 % for the saving period (until retirement), but a lower rate should be considered for the time after, e.g. 5 % as John will shift his investment from long term high-yield type to short term safe havens.

In general, if inflation rate is set to a constant figure, you could do all calculations in "today's value" by just considering the real interest rate (after deduction of constant inflation). First step would be to calculate the demand for ten years at the beginning of retirement. Second to calculate the annual saving rate from today until retirement. Meaning two separate calculations.

Please clearify boundary conditions. Then this problem can be solved either with a financial calc or by geometric series on any calc.

Best regards,

Stephan

[Justin]

Stephan:

Yes, the decreased return is more realistic.

I was more trying to get my thinking around the calculations.

Below, Werner has provided a great working example elaborating the scenario.

I'm going through it now to make sure I fully understand it.

[Werner Huysegoms]

I did this once before (albeit a bit differently), so here goes:

0. Data

-------

 . capital build-up during 20 years, with an annual payment adapted to inflation (PMT, negative).

 . capital depletion over 10 years, again with an annual payment (now received) that is adapted to inflation (pmt, positive)

 . basically, we'll pay PMT*E^i for i=1..20, then we'll receive pmt*E^j for j=1..10

 . E = 1.035 (inflation), B = 1.08 (intrest rate)

 . we assume payments to be made at the end of each period



1. Depleting the built-up sum

-----------------------------

The first sum to be received will be pmt*E, the second pmt*E^2 and so on.

Call pv the built-up sum, then at the age of retirement the following equation holds:

    0 = pv + pmt*E/B + pmt*E^2/B^2 + ... + pmt*E^n/B^n

or

    0 = pv + pmt/C + pmt/C^2 + ... pmt/C^n

This is the normal TVM equation with 1+i/100 = C = B/E

now we know pmt = $50,000 in today's money.  Adjusted for inflation,

that becomes:

  50,000 * 1.035^20 = $ 99,489.44

with n = 10

   pmt = 99,489.44

  p/yr = 1

  i%yr = [(1.08)/(1.035)-1)]*100 = 4,347826

    fv = 0

 payments at END of period

this gives a pv of $ -793,155.34, and the first pmt will be $ 99,489.44 * 1.035,

because it will be made a year after retirement.



2. Building up the sum

----------------------

    0 = FV + PMT*E*B^(N-1) + PMT*E^2*B^(N-2) + ... + PMT*E^(N-1)*B + PMT*E^N

after division by B^N, where C = B/E:

    0 = FV/B^N + PMT/C + PMT/C^2 + ... PMT/C^N

or  0 = (FV/C^N)/E^N + ..

of course, FV = -pv

again, solve with:

    n = 20

 p/yr = 1

 i%yr = 4,347826 (no change!)

   pv = 0 (don't forget!)

   fv = 793,155.34 / (1.035)^20 =  $ 398,612.82

PMT =  -12,910.09 $, first PMT will be * 1.035

an overview of the cash flows and capital build-up and depletion:

(CAP[i] := CAP[i-1]*1.08 - PMT[i])

YEAR	PMT		CAP

 1	-13361,94	 13361,94

 2	-13829,61	 28260,50

 3	-14313,64	 44834,99

 4	-14814,62	 63236,41

 5	-15333,13	 83628,45

 6	-15869,79	106188,52

 7	-16425,24	131108,84

 8	-17000,12	158597,67

 9	-17595,12	188880,61

10	-18210,95	222202,01

11	-18848,34	258826,50

12	-19508,03	299040,65

13	-20190,81	343154,71

14	-20897,49	391504,58

15	-21628,90	444453,84

16	-22385,91	502396,06

17	-23169,42	565757,16

18	-23980,35	634998,08

19	-24819,66	710617,59

20	-25688,35	793155,34

21	102971,57	753636,20

22	106575,58	707351,51

23	110305,72	653633,91

24	114166,42	591758,20

25	118162,25	520936,60

26	122297,93	440313,60

27	126578,36	348960,34

28	131008,60	245868,57

29	135593,90	129944,15

30	140339,69	0,00

[Justin]

Werner:-

Thanks for your brilliantly detailed answer.

I am going through it, and may have some questions later if that's OK with you.

Once again, thanks!

**********************************************************

EDIT:-

Werner:

Am I right in assuming that if you didn't want to adapt the payments in for inflation but instead keep them the same throughout the build up period to give a lump sum of 793,155.34 that that annual figure would be a payment of
-17,332.20?

Thanks in advance

Edited: 24 June 2003, 2:20 a.m.

[Werner Huysegoms]

Yes, that's 'conventional' TVM.
Werner

[Justin]

Thanks Werner!

Your explanation has lifted a cloud of confusion.

Much obliged.