Minimum rate of return in retirement

[Samsung4321]

what you are trying to ask the group or tell the group

In essence: there are useful spreadsheet functions that conveniently enable alternative approaches.

Thanks: I am familiar with the Monte Carlo simulation approach; others may not be.

 

[Samsung4321]

@Samsung4321 Are you a poster who originally registered under a different username?

No.

 

[NW-Bound]

Adverse SORR results in a small average rate of return. Between the start and end points can be much 'Irrational Exuberance' and 'Desperate Despair'.

A bad SORR is when "Desperate Despair" happens during your retirement first, before "Irrational Exuberance".

If you don't draw from your stash, the sequence of return does not matter.

When you are drawing for living expenses, an early period of poor returns may cause you to deplete your stash, with a much smaller portfolio to enable the enjoyment of the exuberance period that comes later.

 

[Samsung4321]

This is the best illustration of SORR I can come up with.

The graph shows balances but not withdrawals.

 

[corn18]

The graph shows balances but not withdrawals.

What does it matter? The average return succeeds, the worst historical sequence of returns fails miserably with the exact same withdrawal profile and starting balance.

 

[Samsung4321]

When you are drawing for living expenses, an early period of poor returns may cause you to deplete your stash, with a much smaller portfolio to enable the enjoyment of the exuberance period that comes later.

Quite so where there is no flexibility in withdrawal rate.


Not so when withdrawal rate is conservative and in proportion to capital at time of withdrawal.

 

[Samsung4321]

same withdrawal profile and starting balance.

Monotonous withdrawal rate is deleterious when combined with higher risk investments.

 

[corn18]

Monotonous withdrawal rate is deleterious when combined with higher risk investments.

I don't know what that means.

 

[Samsung4321]

I don't know what that means.

Constant, or constantly increasing, withdrawal with erratic capital value results in increased possibility of capital depletion.

 

[NW-Bound]

Quite so where there is no flexibility in withdrawal rate.

Not so when withdrawal rate is conservative and in proportion to capital at time of withdrawal.

True this. If your WR is low enough, you can survive any poor return.

If you are rich enough (relative to your spending), or spend little enough (relative to your stash), you will always survive.

 

[pb4uski]

I don't think that the RATE function works where the cash flows (PMT) vary or even increase at a fixed rate.

 

[RobbieB]

I've been spending well over 4% ever since I retired. Seven years now going on eight. I've been drawing on the exuberance the whole time. Got way more dough than I started with.

Yeah, the SORR doesn't bother me anymore. At 66, less than 30 years to go. Don't see me going much past 90.

Investments? Still like equities. Still dancing with the girl who brought me to the ball. Still believe in the people who provide the products and services we use every day.

 

[Samsung4321]

I don't think that the RATE function works where the cash flows (PMT) vary or even increase at a fixed rate.

Often only the start capital, end capital ($0?) and period is 'known', individual cash flow amounts and timing not known with accuracy. The task being to estimate the required minimum nominal rate of return from that input.

RATE provides correct CAGR result:

= 1 * (1 + 10%) ^ 10
= 2.59374246

= RATE(10, 0, -1, 2.59374246, 0, 0)
= 10%

 

[Samsung4321]

Got way more dough than I started with.

You may be investing dough that you will never spend.

In the event that the future is less bountiful:

What proportion of wealth could you lose and have a rate of return that covers LIVING expenses (not entertainment expenses) is not worrisomely risky?

We find our LIVING expenses to be $A20,000 / y - which is less than the (means tested) Age Pension of $A38,000 / y. So we could lose the lot.

 

[pb4uski]

Often only the start capital, end capital ($0?) and period is 'known', individual cash flow amounts and timing not known with accuracy. The task being to estimate the required minimum nominal rate of return from that input.

RATE provides correct CAGR result:

= 1 * (1 + 10%) ^ 10
= 2.59374246

=RATE(10, 0, -1, 2.59374246, 0, 0)
= 10%

No, that doesn't make sense. Let's say that one retires with $1 million, a 4% WR and a 30 year time horizon. The cash flows, aka PMTs will be $40k in year 1 and $40k + inflation in year 2, and increase each year for inflation thereafter. Since PMT is not fixed, the RATE function cannot be used... one has to schedule out the expected cash flows using an inflation assumption and solve for the rate using the IRR function.

 

[Samsung4321]

No, that doesn't make sense. Let's say that one retires with $1 million, a 4% WR and a 30 year time horizon. The cash flows, aka PMTs will be $40k in year 1 and $40k + inflation in year 2, and increase each year for inflation thereafter. Since PMT is not fixed, the RATE function cannot be used... one has to schedule out the expected cash flows using an inflation assumption and solve for the rate using the IRR function.

Correct where the Future Value is not fixed. Often it is known or guessed in advance: $0, or age care capital or bequest.

There is a more complicated formula for the calculation you describe but it is not implemented as a single function in spreadsheets. Hence need for 'schedule' / table.

The alternative is to calculate using real rates and values only. I did not make clear in the OP that the part before '*' calculates the real rate using real capital values, the part after converts the real to nominal:
= (1 + RATE((AgeDie-AgeRetire), AnnualWithdrawal, -CapitalRetire, 0, 0)) * ( 1 + Inflation%) - 1

The real rate is:
= (1 + NominalRate) / (1 + InflationRate) - 1


[To make clear: AnnualWithdrawal would be a constant. $40k (real) every year to use your example.]

 

[pb4uski]

Correct where the Future Value is not fixed. Often it is known or guessed in advance: $0, or age care capital or bequest.

There is a more complicated formula for the calculation you describe but it is not implemented as a single function in spreadsheets. Hence need for 'schedule' / table.

The alternative is to calculate using real rates and values only. I did not make clear in the OP that the part before '*' calculates the real rate using real capital values, the part after converts the real to nominal:
= (1 + RATE((AgeDie-AgeRetire), AnnualWithdrawal, -CapitalRetire, 0, 0)) * ( 1 + Inflation%) - 1

The real rate is:
= (1 + NominalRate) / (1 + InflationRate) - 1


[To make clear: AnnualWithdrawal would be a constant. $40k (real) every year to use your example.]

No, your formula doesn't work. It can't be used to replicate the 4.05% from the table in post 3 of this thread.

=(1+RATE(30,40000,-1000000,0))/((1+3%))-1 = -1.73%... NOT 4.05%

Until you can come up with a formula using RATE that can replicate the 4.05% and -3.83% in post 3 using the same assumptions as used in the tables I think that you are wrong.

 

[brett]

I have a much simpler calculation.

I take the Government annual inflation rate. I add about 2 points (more or less depending) to it to reflect what we consider to be the estimated real change in cost of our basket of goods. Simply a finger in the wind adder based on our gut feel.

That percentage is now our base line break even RoR. We measure investment return from that net baseline. It is rudimentary but probably just as good as any other calculation.

It is why we nave never kept much money in our current, HISA accounts, or in TDs. Keeping it there becomes an expense rather than a gain because of the inflation rate and the low bank interest rates.

We prefer to draw it down when needed and in smaller amounts from our investment accounts. Our combined HISA balances just hit $7K. We just drew down enough money from our investment account to cover off tax installments, a charitable donation, and some other expenses over the next six months.

I am a huge fan of the KISS principle.

In my business career I saw far too many spreadsheets and supposed conclusions that were closer to a fairy tale than to reality.

 

[Exchme]

I think OP would be best served by downloading Shiller's historical data, located at:
Online Data - Robert Shiller

Then I suggest using those spreadsheet skills to look at the historical data, which show that averages are not very useful.

 

[Samsung4321]

No, your formula doesn't work. It can't be used to replicate the 4.05% from the table in post 3 of this thread.

=(1+RATE(30,40000,-1000000,0))/((1+3%))-1 = -1.73%... NOT 4.05%

Until you can come up with a formula using RATE that can replicate the 4.05% and -3.83% in post 3 using the same assumptions as used in the tables I think that you are wrong.

Error in your formula: For '/' substitute '*'. (For divide substitute multiply.)
Error in your table: Withdrawal at year 1 is not inflated from year 0 by 3%.

The two methods then produce the same result: Nominal rate 4.25566%

Nominal Formula		= (1 + RATE(30, 4, -100, 0, 0, 0)) * (1 + 3%) - 1
Nominal rate		4.25566%
Inflation	3%
Withdraw	-4%
Year	Withdrawal	Return	Capital
0	-4.00	4.25566%	100.00
1	-4.12	4.26	100.14
2	-4.24	4.26	100.15
3	-4.37	4.26	100.04
4	-4.50	4.26	99.80
5	-4.64	4.25	99.41
6	-4.78	4.23	98.86
7	-4.92	4.21	98.15
8	-5.07	4.18	97.26
9	-5.22	4.14	96.18
10	-5.38	4.09	94.90
11	-5.54	4.04	93.40
12	-5.70	3.97	91.67
13	-5.87	3.90	89.70
14	-6.05	3.82	87.47
15	-6.23	3.72	84.96
16	-6.42	3.62	82.15
17	-6.61	3.50	79.04
18	-6.81	3.36	75.59
19	-7.01	3.22	71.80
20	-7.22	3.06	67.63
21	-7.44	2.88	63.06
22	-7.66	2.68	58.08
23	-7.89	2.47	52.66
24	-8.13	2.24	46.77
25	-8.38	1.99	40.39
26	-8.63	1.72	33.48
27	-8.89	1.42	26.02
28	-9.15	1.11	17.97
29	-9.43	0.76	9.31
30	-9.71	0.40	0.00

 

[Samsung4321]

fan of the KISS principle

And lots would agree.


And many have problem with calculations involving PV, FV, PMT and RATE - which are easily done in spreadsheets - provided enough time is spent learning how.

 

[corn18]

And lots would agree.


And many have problem with calculations involving PV, FV, PMT and RATE - which are easily done in spreadsheets - provided enough time is spent learning how.

Love the backhanded "you all are stupid" comment.

 

[Samsung4321]

Love the backhanded "you all are stupid" comment.

I'd say ignorant. That can be solved by learning. Stupid is where can not learn.

 

[MichaelB]

Let’s all remember our Dale Carnegie andy keep this thread civil and polite.

 

[Samsung4321]

Then I suggest using those spreadsheet skills to look at the historical data, which show that averages are not very useful.

Knowing the rate of return which must be exceeded to attain a goal is very useful.


If rate exceeds that available at a risk that is acceptable then goal may be unattainable.