Calculate Growth Value When Drawing Down Funds

[SimonHughes]

Hello,

I want to create a spreadsheet where I start with a fixed value, say £100,000 which will grow at a rate of (say) 4%pa for 10 years when I will start drawing down capital at 10%pa. This part of the calculation is easy but what I then want to do is to apply the original 4% growth to the diminishing total as I draw down the capital.

Further, I would like to original capital value, the growth rate (quoted here as 4%) and the drawdown rate (quoted here as 10%) to be variable.

Can anyone help with this one please?

 

[joeu2004]

It is unclear to me exactly what you want to calculate. Perhaps something like the following.

[TABLE="class: grid, width: 400"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Init bal[/TD]
[TD="align: right"]100,000.00[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Growth rate[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Withdrawal rate[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Accumulation phase[/TD]
[TD="align: right"]10[/TD]
[TD]years[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]Withdraw Phase[/TD]
[TD="align: right"]Withdrawal (at beg)[/TD]
[TD="align: right"]End Bal[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]End bal, year 10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]148,024.43[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Year 11[/TD]
[TD="align: right"]14,802.44[/TD]
[TD="align: right"]138,550.87[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13,855.09[/TD]
[TD="align: right"]129,683.61[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12,968.36[/TD]
[TD="align: right"]121,383.86[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]12,138.39[/TD]
[TD="align: right"]113,615.29[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]11,361.53[/TD]
[TD="align: right"]106,343.91[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10,634.39[/TD]
[TD="align: right"]99,537.90[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]9,953.79[/TD]
[TD="align: right"]93,167.48[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9,316.75[/TD]
[TD="align: right"]87,204.76[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]8,720.48[/TD]
[TD="align: right"]81,623.65[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]8,162.37[/TD]
[TD="align: right"]76,399.74[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
A7: =B4
C7: =FV(B2,B4,0,-B1)
A8: =A7+1
B8: =C7*$B$3
C8: =(C7-B8)*(1+$B$2)
Copy A8:C8 into A9:C9 and for as many rows as you wish

The key formulas are highlighted in red. They calculate the initial balance for the withdrawal phase (ending balance of the accumulation phase) in C7; and the ending balance for each year of the withdrawal phase in C8, after withdrawing the specified percentage at the beginning of each year.

 

[SimonHughes]

It is unclear to me exactly what you want to calculate. Perhaps something like the following.

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Init bal[/TD]
[TD="align: right"]100,000.00[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Growth rate[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Withdrawal rate[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Accumulation phase[/TD]
[TD="align: right"]10[/TD]
[TD]years[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]Withdraw Phase[/TD]
[TD="align: right"]Withdrawal (at beg)[/TD]
[TD="align: right"]End Bal[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]End bal, year 10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]148,024.43[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Year 11[/TD]
[TD="align: right"]14,802.44[/TD]
[TD="align: right"]138,550.87[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13,855.09[/TD]
[TD="align: right"]129,683.61[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12,968.36[/TD]
[TD="align: right"]121,383.86[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]12,138.39[/TD]
[TD="align: right"]113,615.29[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]11,361.53[/TD]
[TD="align: right"]106,343.91[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10,634.39[/TD]
[TD="align: right"]99,537.90[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]9,953.79[/TD]
[TD="align: right"]93,167.48[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9,316.75[/TD]
[TD="align: right"]87,204.76[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]8,720.48[/TD]
[TD="align: right"]81,623.65[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]8,162.37[/TD]
[TD="align: right"]76,399.74[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
A7: =B4
C7: =FV(B2,B4,0,-B1)
A8: =A7+1
B8: =C7*$B$3
C8: =(C7-B8)*(1+$B$2)
Copy A8:C8 into A9:C9 and for as many rows as you wish

The key formulas are highlighted in red. They calculate the initial balance for the withdrawal phase (ending balance of the accumulation phase) in C7; and the ending balance for each year of the withdrawal phase in C8, after withdrawing the specified percentage at the beginning of each year.

Yes, that works very well thank you. I have made two alterations; to substitute a draw-down of the remaining balance by 10% (your formula B8: =C7*$B$3) to a draw-down of the balance at the end of the accumulation phase so as to maintain a "fixed" income for the period (my formula =$C$7*$B$3). This shows how many years I have before running out of funds with this fixed income. I have also included an inflation rate to subtract from the projected (hoped for) growth rate to give me a net growth rate which is more realistic.

Thanks for providing this information, excellent!

 

[joeu2004]

You're welcome. Given your changes, the following might be helpful.

-----

With a fixed withdrawal amount, you can use NPER to calculate the number of years for the withdrawal phase, to wit (in E1):

=INT(NPER(B2,B8,-C7,0,1))

Note the type=1, since I assume that annual withdrawals are at the beginning of each year. The remaining balance is:

=FV(B2,E1,B8,-C7,1)

Interestingly, the NPER calculation is not affected by either the initial balance or the length of the accumulation phase. (Of course, the remaining balance at the end of the withdrawal phase is.)

So, the number of years for the withdrawal phase is simply:

=INT(NPER(B2,B3,-1,0,1))

-----

I would not apply in inflation rate to the investment growth or growth rate. Average investment returns are not affected by inflation.

Aside.... There is a difference between average investment growth rate, which is market-driven, and average required growth rate. If I did apply inflation to the average required growth rate, I would "add", not subtract. A 4% investment return is worth less a year from now due to inflation. But if the average inflation rate is 2%, the average inflation-adjusted required growth rate is not simply 6%. Instead, it is (1+4%)*(1+2%)-1, which is 6.08%.

However, your withdrawal requirement is affected by inflation, assuming that it largely funds expenses.

So, I would make the following changes to my previous example, given an average inflation rate in E2 (in order to minimize the changes to previous cell references).

B9: =B8*(1+$E$1)
Copy A9:C9 into A10:C10 until the ending balance in column C is negative.

Of course, the funds last until the year before.

-----

I might be able to develop a "closed-form" formula to calculate "nper", given an investment growth rate and a withdrawal inflation rate. Are you interested?

Also, usually the problem to solve goes in the reverse, to wit: what initial investment (and optional annual contribution, fixed or variable) is required in order to fund a withdrawal phase of n years after an accumulation phase of k years, given an average investment growth rate and (optionally) an average inflation rate applied to withdrawals. Is that really the problem that you would like to solve?

 

[SimonHughes]

You're welcome. Given your changes, the following might be helpful.

-----

With a fixed withdrawal amount, you can use NPER to calculate the number of years for the withdrawal phase, to wit (in E1):

=INT(NPER(B2,B8,-C7,0,1))

Note the type=1, since I assume that annual withdrawals are at the beginning of each year. The remaining balance is:

=FV(B2,E1,B8,-C7,1)

Interestingly, the NPER calculation is not affected by either the initial balance or the length of the accumulation phase. (Of course, the remaining balance at the end of the withdrawal phase is.)

So, the number of years for the withdrawal phase is simply:

=INT(NPER(B2,B3,-1,0,1))

-----

I would not apply in inflation rate to the investment growth or growth rate. Average investment returns are not affected by inflation.

Aside.... There is a difference between average investment growth rate, which is market-driven, and average required growth rate. If I did apply inflation to the average required growth rate, I would "add", not subtract. A 4% investment return is worth less a year from now due to inflation. But if the average inflation rate is 2%, the average inflation-adjusted required growth rate is not simply 6%. Instead, it is (1+4%)*(1+2%)-1, which is 6.08%.

However, your withdrawal requirement is affected by inflation, assuming that it largely funds expenses.

So, I would make the following changes to my previous example, given an average inflation rate in E2 (in order to minimize the changes to previous cell references).

B9: =B8*(1+$E$1)
Copy A9:C9 into A10:C10 until the ending balance in column C is negative.

Of course, the funds last until the year before.

-----

I might be able to develop a "closed-form" formula to calculate "nper", given an investment growth rate and a withdrawal inflation rate. Are you interested?

Also, usually the problem to solve goes in the reverse, to wit: what initial investment (and optional annual contribution, fixed or variable) is required in order to fund a withdrawal phase of n years after an accumulation phase of k years, given an average investment growth rate and (optionally) an average inflation rate applied to withdrawals. Is that really the problem that you would like to solve?

Thanks for this, I have incorporated the formula to calculate years left for a fixed withdrawal and take on board the point regarding inflation.

As I am no longer contributing to savings I start with the initial amount and am working backwards to calculate indicative draw-down rates and time-span under various growth/inflation scenarios. I now have the tools to do that and I thank you for your input.