Retirement Corpus Calculation

[mactoolsix]

I'm trying to find the required corpus for the following:
Present Value = 200,000
20 years to retirement
30 years total retirement
Projected inflation = 2%
Projected growth = 6%
Annual withdrawal will be $115,000

To meet the $115,000 with 2% inflation and 6% growth requires a corpus of $1,733,000 be attained in 20 years. (figured with trial and error)

Is there a formula that will determine a corpus large enough to last 30 years given the above information?
Thanks in advance,
Mike

 

[joeu2004]

I don't know how you derived $1,733,000. Assuming withdrawals at the beginning of each retirement year, Solver derived $2,086,390 (2086390.41327541). Even with withdrawals at the end of each year, Solver derived $1,968,293 (1968292.84271265).

I do not believe we can use the Excel financial functions to derive the required balance at the beginning of retirement.

Instead, I derived a formula mathematically. The formula (B5) assumes withdrawals at the beginning of each retirement year.

I also provide a formula (B4) to determine the annual contribution required during pre-retirement years in order to have the requirement balance at the beginning of retirement. The formula assumes contributions at the beginning of each retirement year.

	A	B	C	D	E	F
1	Init invest	$200,000		Retire Yr	Withdrawal	Yr-End Bal
2	Yrs to retire	20				$2,086,390
3	Invest growth	6.00%		1	$115,000	$2,089,674
4	Annl contrib	$37,057		2	$117,300	$2,090,716
5	Beg retire bal	$2,086,390		3	$119,646	$2,089,334
6	Yrs retired	30		4	$122,039	$2,085,333
7	Init withdraw	$115,000		5	$124,480	$2,078,505
8	Inflate rate	2.00%		6	$126,969	$2,068,628
30				28	$196,292	$392,880
31				29	$200,218	$204,222
32				30	$204,222	$0

<tbody>
</tbody>

Formulas:
B4: =PMT(B3,B2,B1,-B5,1)
B5: =B7*SUMPRODUCT((1+B3)^ROW(X1:INDEX(X:X,B6,1)) * (1+B8)^(ROW(INDEX(X:X,B6,1))-ROW(X1:INDEX(X:X,B6,1)))) / (1+B3)^ROW(INDEX(X:X,B6,1))

F2: =B5
E3: =B7
F3: =(F2-E3)*(1+$B$3)
E4: =E3*(1+$B$8)
Copy F3 and E4 down the columns

The references to X1 and X:X are arbitrary; any column reference will do. I choose an unused column (X) in order to minimize recalculations.

For the same reason, I prefer to use X1:INDEX(X:X,B6,1) instead of INDIRECT("X1:X" & B6).

The amortization table on the right (colums D:F) is provided as proof of concept. It is not necessary.

 

[joeu2004]

B5: =B7*SUMPRODUCT((1+B3)^ROW(X1:INDEX(X:X,B6,1)) * (1+B8)^(ROW(INDEX(X:X,B6,1))-ROW(X1:INDEX(X:X,B6,1)))) / (1+B3)^ROW(INDEX(X:X,B6,1))

Got a little silly with the formula. Not thinking clearly. Obviously:

=B7*SUMPRODUCT((1+B3)^ROW(X1:INDEX(X:X,B6,1)) * (1+B8)^(B6-ROW(X1:INDEX(X:X,B6,1)))) / (1+B3)^B6

 

[MickG]

Not as comprehensive as joeu's but.
This formula:-
=115000((1.06^31-(1.02*1.06^30)))/1.06^30-1.02^30
This gives a "Corpus" of 1,968,292.84 for withdrawals at the end of each period

 

[joeu2004]

=B7*SUMPRODUCT((1+B3)^ROW(X1:INDEX(X:X,B6,1)) * (1+B8)^(B6-ROW(X1:INDEX(X:X,B6,1)))) / (1+B3)^B6

Sorry for the incessant posting, but this can be simplified to (thanks to WolframAlpha):

=B7*(1+B3)*((1+B3)^B6 - (1+B8)^B6)/(B3-B8) / (1+B3)^B6

Again, this assumes withdrawals at the beginning of each retirement year.

 

[mactoolsix]

WOW!! Fabulous responses. Thanks to each of you for the help.
Mike