Figuring needed value of lump sum to provide given withdrawal rate

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
CULater
Posts: 1316
Joined: Sun Nov 13, 2016 10:59 am

Figuring needed value of lump sum to provide given withdrawal rate

Post by CULater » Fri Jan 12, 2018 3:52 pm

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

User avatar
Tyler Aspect
Posts: 1085
Joined: Mon Mar 20, 2017 10:27 pm
Location: California
Contact:

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by Tyler Aspect » Fri Jan 12, 2018 4:32 pm

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.rbcwm-usa.com/resources/file-687839.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.

FactualFran
Posts: 715
Joined: Sat Feb 21, 2015 2:29 pm

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by FactualFran » Fri Jan 12, 2018 4:52 pm

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.

CULater
Posts: 1316
Joined: Sun Nov 13, 2016 10:59 am

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by CULater » Fri Jan 12, 2018 5:46 pm

FactualFran wrote:
Fri Jan 12, 2018 4:52 pm
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.
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?
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

TravelforFun
Posts: 1424
Joined: Tue Dec 04, 2012 11:05 pm

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by TravelforFun » Fri Jan 12, 2018 5:52 pm

CULater wrote:
Fri Jan 12, 2018 5:46 pm
FactualFran wrote:
Fri Jan 12, 2018 4:52 pm
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.
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?
Calculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.

TravelforFun

User avatar
FiveK
Posts: 5409
Joined: Sun Mar 16, 2014 2:43 pm

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by FiveK » Fri Jan 12, 2018 6:28 pm

TravelforFun wrote:
Fri Jan 12, 2018 5:52 pm
CULater wrote:
Fri Jan 12, 2018 5:46 pm
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?
Calculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.
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.

CULater
Posts: 1316
Joined: Sun Nov 13, 2016 10:59 am

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by CULater » Fri Jan 12, 2018 6:29 pm

TravelforFun wrote:
Fri Jan 12, 2018 5:52 pm
CULater wrote:
Fri Jan 12, 2018 5:46 pm
FactualFran wrote:
Fri Jan 12, 2018 4:52 pm
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.
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?
Calculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.

TravelforFun
Thanks. This is working wonderfully. One more dummy question. How can I turn this around to determine the inflation-adjusted withdrawal amount produced by a given lump sum and growth rate?
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

FactualFran
Posts: 715
Joined: Sat Feb 21, 2015 2:29 pm

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by FactualFran » Sat Jan 13, 2018 1:06 pm

TravelforFun wrote:
Fri Jan 12, 2018 5:52 pm
Calculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.
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%).

FactualFran
Posts: 715
Joined: Sat Feb 21, 2015 2:29 pm

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by FactualFran » Sat Jan 13, 2018 1:42 pm

CULater wrote:
Fri Jan 12, 2018 6:29 pm
Thanks. This is working wonderfully. One more dummy question. How can I turn this around to determine the inflation-adjusted withdrawal amount produced by a given lump sum and growth 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.

CULater
Posts: 1316
Joined: Sun Nov 13, 2016 10:59 am

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by CULater » Sat Jan 13, 2018 2:35 pm

FactualFran wrote:
Sat Jan 13, 2018 1:06 pm
TravelforFun wrote:
Fri Jan 12, 2018 5:52 pm
Calculate based on today dollars by using a real return rate (actual return minus inflation rate), and keep the withdrawal amount the same.
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%).
Thanks for this and the other posts. I would not have known to do this. Can you explain the basis for using geometric difference?
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

FactualFran
Posts: 715
Joined: Sat Feb 21, 2015 2:29 pm

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by FactualFran » Sat Jan 13, 2018 2:47 pm

CULater wrote:
Sat Jan 13, 2018 2:35 pm
Thanks for this and the other posts. I would not have known to do this. Can you explain the basis for using geometric difference?
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 year-by-year 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 year-by-year 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

Year-by-year 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
When using the arithmetic difference, the account is depleted before all the withdrawals have been made. When using the geometric difference, the account is exactly depleted by the final withdrawal.

[Edit: Replaced example.]
Last edited by FactualFran on Tue Jan 16, 2018 12:47 pm, edited 2 times in total.

User avatar
FiveK
Posts: 5409
Joined: Sun Mar 16, 2014 2:43 pm

Re: Figuring needed value of lump sum to provide given withdrawal rate

Post by FiveK » Sat Jan 13, 2018 4:09 pm

CULater wrote:
Sat Jan 13, 2018 2:35 pm
Thanks for this and the other posts. I would not have known to do this. Can you explain the basis for using geometric difference?
See Real interest rates.

Within the accuracy of projected returns, 1% is just as good as 0.98%. ;)

Post Reply