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.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Init invest[/TD]
[TD="align: right"]$200,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Retire Yr[/TD]
[TD="align: right"]Withdrawal[/TD]
[TD="align: right"]Yr-End Bal[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Yrs to retire[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$2,086,390[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Invest growth[/TD]
[TD="align: right"]6.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$115,000[/TD]
[TD="align: right"]$2,089,674[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Annl contrib[/TD]
[TD="align: right"]$37,057[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$117,300[/TD]
[TD="align: right"]$2,090,716[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Beg retire bal[/TD]
[TD="align: right"]$2,086,390[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$119,646[/TD]
[TD="align: right"]$2,089,334[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Yrs retired[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$122,039[/TD]
[TD="align: right"]$2,085,333[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Init withdraw[/TD]
[TD="align: right"]$115,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$124,480[/TD]
[TD="align: right"]$2,078,505[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Inflate rate[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$126,969[/TD]
[TD="align: right"]$2,068,628[/TD]
[/TR]
[TR]
[TD="align: center"]

---

[/TD]
[TD="align: right"]

---

[/TD]
[TD="align: right"]

---

[/TD]
[TD="align: right"]

---

[/TD]
[TD="align: right"]

---

[/TD]
[TD="align: right"]

---

[/TD]
[TD="align: right"]

---

[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]$196,292[/TD]
[TD="align: right"]$392,880[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]$200,218[/TD]
[TD="align: right"]$204,222[/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]$204,222[/TD]
[TD="align: right"]$0[/TD]
[/TR]
</tbody>[/TABLE]

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