Figuring needed value of lump sum to provide given withdrawal rate
Figuring needed value of lump sum to provide given withdrawal rate
Wonder what the correct function is to determine the needed present value of a lump sum that will provide a fixed withdrawal amount assuming a fixed rate of return over 20 years? For example, what lump sum value would be needed to provide a $30K annual withdrawal for 20 years, assuming that the lump sum returns 3% per year on the remaining balance?
May you have the hindsight to know where you've been, The foresight to know where you're going, And the insight to know when you've gone too far. ~ Irish Blessing
 Tyler Aspect
 Posts: 1088
 Joined: Mon Mar 20, 2017 10:27 pm
 Location: California
 Contact:
Re: Figuring needed value of lump sum to provide given withdrawal rate
I looked up this study article for the safe withdrawal rate for a period of 20 years. I got the safe withdrawal figure of 5%, assuming 50% stock / 50% bond mix. $600k * 0.05 = $30k
The lump sum figure is $600k.
I discarded your given rate of return, because that is generally unknown in the future.
https://www.rbcwmusa.com/resources/file687839.pdf
The lump sum figure is $600k.
I discarded your given rate of return, because that is generally unknown in the future.
https://www.rbcwmusa.com/resources/file687839.pdf
Past result does not predict future performance. Mentioned investments may lose money. Contents are presented "AS IS" and any implied suitability for a particular purpose are disclaimed.

 Posts: 728
 Joined: Sat Feb 21, 2015 2:29 pm
Re: Figuring needed value of lump sum to provide given withdrawal rate
The correct function to determine the needed present value is the "present value" function, named PV in spreadsheet software. For the values you gave, having spreadsheet software evaluate PV(0.03, 20, 30000) returns 446,324.
Re: Figuring needed value of lump sum to provide given withdrawal rate
Thanks. Now, what if I want to take inflation into account? The dollar amount of the withdrawals would have to increase each year. How can that be handled?FactualFran wrote: ↑Fri Jan 12, 2018 4:52 pmThe correct function to determine the needed present value is the "present value" function, named PV in spreadsheet software. For the values you gave, having spreadsheet software evaluate PV(0.03, 20, 30000) returns 446,324.
May you have the hindsight to know where you've been, The foresight to know where you're going, And the insight to know when you've gone too far. ~ Irish Blessing

 Posts: 1455
 Joined: Tue Dec 04, 2012 11:05 pm
Re: Figuring needed value of lump sum to provide given withdrawal rate
Calculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.CULater wrote: ↑Fri Jan 12, 2018 5:46 pmThanks. Now, what if I want to take inflation into account? The dollar amount of the withdrawals would have to increase each year. How can that be handled?FactualFran wrote: ↑Fri Jan 12, 2018 4:52 pmThe correct function to determine the needed present value is the "present value" function, named PV in spreadsheet software. For the values you gave, having spreadsheet software evaluate PV(0.03, 20, 30000) returns 446,324.
TravelforFun
Re: Figuring needed value of lump sum to provide given withdrawal rate
That is also what I would do, but if CULater is interested, see Present value of a growing annuity for that equation. The equation for present value of a constant annuity, and other formulas, can also be found in that article.TravelforFun wrote: ↑Fri Jan 12, 2018 5:52 pmCalculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.
Re: Figuring needed value of lump sum to provide given withdrawal rate
Thanks. This is working wonderfully. One more dummy question. How can I turn this around to determine the inflationadjusted withdrawal amount produced by a given lump sum and growth rate?TravelforFun wrote: ↑Fri Jan 12, 2018 5:52 pmCalculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.CULater wrote: ↑Fri Jan 12, 2018 5:46 pmThanks. Now, what if I want to take inflation into account? The dollar amount of the withdrawals would have to increase each year. How can that be handled?FactualFran wrote: ↑Fri Jan 12, 2018 4:52 pmThe correct function to determine the needed present value is the "present value" function, named PV in spreadsheet software. For the values you gave, having spreadsheet software evaluate PV(0.03, 20, 30000) returns 446,324.
TravelforFun
May you have the hindsight to know where you've been, The foresight to know where you're going, And the insight to know when you've gone too far. ~ Irish Blessing

 Posts: 728
 Joined: Sat Feb 21, 2015 2:29 pm
Re: Figuring needed value of lump sum to provide given withdrawal rate
Be careful to use the geometric difference between the nominal return and the inflation rate, not the arithmetic difference. For (constant) nominal return rate of 3% and inflation rate of 2%, the real return rate is 0.98% (=(1+3%)/(1+2%)1), not 1% (=3%2%).TravelforFun wrote: ↑Fri Jan 12, 2018 5:52 pmCalculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.

 Posts: 728
 Joined: Sat Feb 21, 2015 2:29 pm
Re: Figuring needed value of lump sum to provide given withdrawal rate
I would use the PMT function of the spreadsheet software that I use. It takes the rate, number of periods, and present value as arguments. It also takes future value and type as optional arguments, with the type indicating whether payments were make at the start of end of each period.
With a constant real interest rate of 1.22% over 30 years, an account with an initial balance of $10,000 would have supported an initial withdrawal of $400 (=PMT(1.22%,30,10000) with later withdrawals being adjusted for inflation.
Re: Figuring needed value of lump sum to provide given withdrawal rate
Thanks for this and the other posts. I would not have known to do this. Can you explain the basis for using geometric difference?FactualFran wrote: ↑Sat Jan 13, 2018 1:06 pmBe careful to use the geometric difference between the nominal return and the inflation rate, not the arithmetic difference. For (constant) nominal return rate of 3% and inflation rate of 2%, the real return rate is 0.98% (=(1+3%)/(1+2%)1), not 1% (=3%2%).TravelforFun wrote: ↑Fri Jan 12, 2018 5:52 pmCalculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.
May you have the hindsight to know where you've been, The foresight to know where you're going, And the insight to know when you've gone too far. ~ Irish Blessing

 Posts: 728
 Joined: Sat Feb 21, 2015 2:29 pm
Re: Figuring needed value of lump sum to provide given withdrawal rate
Using the geometric difference makes the initial account value returned by the previously mentioned spreadsheet functions the same as the value used when using a spreadsheet set up to do the detailed yearbyyear calculations. When using the arithmetic difference, the initial account value returned by using the spreadsheet functions is not exactly the same as the value used detailed yearbyyear calculation.
Addendum: Here is an example.
Code: Select all
Parameter Values
10 Number of Annual Withdrawals
$10,000 Initial Balance
3.00% Annual Inflation Rate
7.00% Annual Nominal Return
Calculated Values
4.00% Arithmetic Difference Real Return Rate
$1,232.91 Periodic Payment Returned by PMT Function
3.88% Geometric Difference Real Return Rate
$1,225.77 Periodic Payment Returned by PMT Function
Yearbyyear calculated nominal withdrawals and balances. First withdrawal
adjusted by inflation rate because it is taken at end of the first year.
Arithmetic Difference Geometric Difference
Real Return Rate Real Return Rate
Year Cash Flow Balance Cash Flow Balance
$10,000.00 $10,000.00
1 $1,269.90 $9,430.10 $1,262.54 $9,437.46
2 $1,307.99 $8,782.22 $1,300.42 $8,797.66
3 $1,347.23 $8,049.74 $1,339.43 $8,074.07
4 $1,387.65 $7,225.57 $1,379.61 $7,259.64
5 $1,429.28 $6,302.08 $1,421.00 $6,346.81
6 $1,472.16 $5,271.07 $1,463.63 $5,327.45
7 $1,516.32 $4,123.72 $1,507.54 $4,192.83
8 $1,561.81 $2,850.57 $1,552.77 $2,933.56
9 $1,608.67 $1,441.44 $1,599.35 $1,539.56
10 $1,656.93 $114.59 $1,647.33 $0.00
[Edit: Replaced example.]
Last edited by FactualFran on Tue Jan 16, 2018 12:47 pm, edited 2 times in total.