Pension with no COLA. How much to spend?

[Adam Zapple]

This may be a stupid question be here goes.  Let's say I have a pension with no cost of living adjustments that, for arguments sake, I want to use to cover 100% of my living expenses for the rest of my life.  How much of that monthly check do I need to put aside for future savings so that inflation doesn't eat up the pension over time?  Let's just say it is $50,000 a year for easy math. 

[MarciaB]

I think this is not a stupid question, but a fun mathy one.

My first thought was to set aside a fund (not from the pension checks, but from separate monies) in order to offset the shrinkage of buying power of the pension checks as the years went on.

Which would look like a Present Value thing.

Let's assume that inflation is an even 3% per year, and you will draw 40 years of pension checks.

Year 1 of the pension checks cover 100% of the buying. Year 2 the checks cover only 97% and you'll need money to cover the 3% the checks can't stretch to. Year 3 the pension checks cover 94% and you'll need funds to cover the 6%...all the way to Year 40 when the checks can only cover [insert percentage here] and you'll need funds to cover the [insert percentage here].

So what you're looking at is a series of numbers (the 3%, the 6%, etc.) that need to be safely stored in a pile of funds of [size] here.

Oh hell, can someone dive in here and help a girl out?

[marty998]

We would need to know how long is the rest of your life...

[BTDretire]

This may be a stupid question be here goes.  Let's say I have a pension with no cost of living adjustments that, for arguments sake, I want to use to cover 100% of my living expenses for the rest of my life.  How much of that monthly check do I need to put aside for future savings so that inflation doesn't eat up the pension over time?  Let's just say it is $50,000 a year for easy math.

 I haven't done any calculations. And no one knows what the future inflation rate will be.
Any chance you can just save the first year of payments?
If you invest it a 7%, by the end of the second year you woud be near 10% ahead.
Withdraw 3% in Dec. of the second year to cover inflation.
At the end of the 3rd year you would have 107% of 104% of the original $50,000,
or $55,640. Three years of inflation on $50k is $50k x 1.03 x1.03 x 1.03 =$54,626
So you withdraw $4,626 to add to your $50k.
That leaves you $51,014. at the end of 4 years you have $54,585. But you will need $6265.
 By my very rough calculations you already running short by $6265 - $4585 or $1680.
 If I get some time, I'll try to run a spreadsheet (I need the practice) I'm thinking around $42k
 will get you out 12 to 20 years before you run out of the savings you acrude over the years.

[BTDretire]

 Using 3% inflation and an investment growth rate of 7%,
 and with the caveat, I'm a newby with Excel.
Spending $42k with a 3% inflation raise each year will get out out to 15 years, before you need to spend less.
 Spending $40k with a 3% inflation raise each year will get out out to 20 years, before you need to spend less.
Spending $38k with a 3% inflation raise each year will get out out to 25 years, before you need to spend less.
Spending $36.5k with a 3% inflation raise each year will get out out to 30 years, before you need to spend less.
 Never mind I found an error, fixable but it changes the years.
 If you have any experience with Excel or Openoffice, I can give you the formulas for each column.

[chasesfish]

I have a non-COLA pension and I'm retiring 17 years before eligible.  In my situation, I just ignore the money in most of my analysis.

When I want to get really into the math, I'll present value the pension through Fidelity's calculator and include it in my net worth, then base my withdraw rate on it.

We really don't know what inflation will be in the future.  This comment is specifically for US retirees, but one argument says government debt, deficit spending, low rates will cause inflation to take off.   The other argument points out the US is the largest producer of food, is just about energy independent, and every time our interest rates go up a little, all this capital floods into the country from Europe and Japan to keep rates low.

We'll probably have some inflation, but I don't expect it to be crushing.  If it is, then equities and income producing real estate are the only two assets that beat inflation over the long term and I will happen to own both.

[BTDretire]

Ok, I made a spreadsheet in OpenOffice.
First Column (A) is your $50k pension.
Second column (B) is your spend rate.
This is adjusted to see when you run out of savings used to supplement your inflation adjustment.
Third column (C) is =A-B and is placed in next row because it takes a year to accumulate.
the fourth column is tricky because at some point your inflation adjusted income is above your $50k pension.
This is when you start withdrawing from the savings that are growing at 7% a year.
So, we have an IF/THEN situation, If C4 is less then 0 (or negative) we perform D3*1.07+C4
and If C4 is positive we perform (D3+C4)*1.07.
Fourth column (D) =IF(C4<0;D3*1.07+C4;(D3+C4)*1.07)
 Now we can change the spend rate in column B and see where Column D goes negative, ie. you no longer have enough savings to perform an inflation adjustment.
 Remember I used 3% inflation and 7% earnings on savings. No one knows what it will be.
Spend $43.0k and your money lasts 15 years.
Spend $40.8k and your money lasts 20 years.
Spend $39.1k and your money lasts 25 years.
Spend $37.5k and your money lasts 30 years.
I have attached a picture just to give you a headstart.
If you can get a spreadsheet going, then you can adjust inflation rate, savings growth rate and spend rate.
You could break out those adjustments out into separate columns for easy adjustment.
 Any experts feel free to correct my work, I'm just slogging through, learning as I go.
Got some good help on Excel Forum.
 

[Dee18]

This doesn't exactly answer your question, but cfiresim and firecalc allow you to do modeling with a pension that does not have COL.  You can easily determine your maximum spend rate.

[MDM]

Assuming income and spending both occur at the beginning of a time period (e.g., pension check arrival and bill paying for the month occur on the 1st of the month), one can use the formulas for Future value of an annuity due and Future value of a growing annuity due to get an analytic solution.

P = Pension amount
S = Spending amount for the first time period
r = investment rate of return (including inflation)
i = inflation rate of spending increase
n = number of time periods

The cumulative pension amount is P*((1+r)^(n+1)-(1+r))/r.

The cumulative spending amount (if r<>i) is S*(1+r)*((1+r)^n-(1+i)^n)/(r-i).
The cumulative spending amount (if r=i) is S*n*(1+r)^n.

Equating cumulative pension to cumulative spending, and solving for S, we get
S=P*(r-i)*((1+r)^n-1)/(r*((1+r)^n-(1+i)^n))   if r<>i
S=P*((r+1)^n-1)/(n*r*(1+r)^(n-1))  if r=i

One can put the following into cells A1:B5 of a spreadsheet
r   0.07
i   0.03
n   16
P   50000
S   =IF(B1=B2,B4*((1+B1)^B3-1)/(B3*B1*(1+B1)^(B3-1)),B4*((1+B1)^B3-1)*(B1-B2)/(B1*((1+B1)^B3-(1+B2)^B3)))

Then the following into cells A7:D10

Code: [Select]

Time Excess Income Ending balance Next spending
1 =B4-B5 =B8*(1+$B$1) =B5*(1+B2)
2 =($B$4-D8) =(C8+B9)*(1+$B$1) =D8*(1+$B$2)
3 =($B$4-D9) =(C9+B10)*(1+$B$1) =D9*(1+$B$2)

...and then copy the last two rows down as far as one wishes.

The "ending balance" column should be ~0 for whichever time row is chosen in cell A3.

E.g., for the inputs shown above, the initial spending for time periods of 1 to 9 is
1    $50,000
2    $49,286
3    $48,594
4    $47,925
5    $47,277
6    $46,650
7    $46,043
8    $45,456
9    $44,888

Using different assumptions may lead to different answers.

[Adam Zapple]

Ok, I made a spreadsheet in OpenOffice.
First Column (A) is your $50k pension.
Second column (B) is your spend rate.
This is adjusted to see when you run out of savings used to supplement your inflation adjustment.
Third column (C) is =A-B and is placed in next row because it takes a year to accumulate.
the fourth column is tricky because at some point your inflation adjusted income is above your $50k pension.
This is when you start withdrawing from the savings that are growing at 7% a year.
So, we have an IF/THEN situation, If C4 is less then 0 (or negative) we perform D3*1.07+C4
and If C4 is positive we perform (D3+C4)*1.07.
Fourth column (D) =IF(C4<0;D3*1.07+C4;(D3+C4)*1.07)
 Now we can change the spend rate in column B and see where Column D goes negative, ie. you no longer have enough savings to perform an inflation adjustment.
 Remember I used 3% inflation and 7% earnings on savings. No one knows what it will be.
Spend $43.0k and your money lasts 15 years.
Spend $40.8k and your money lasts 20 years.
Spend $39.1k and your money lasts 25 years.
Spend $37.5k and your money lasts 30 years.
I have attached a picture just to give you a headstart.
If you can get a spreadsheet going, then you can adjust inflation rate, savings growth rate and spend rate.
You could break out those adjustments out into separate columns for easy adjustment.
 Any experts feel free to correct my work, I'm just slogging through, learning as I go.
Got some good help on Excel Forum.

This is awesome.  I appreciate your nerdiness like you wouldn't believe.