Intrest v Drawdown Calculation Required

[SimonHughes]

Hello All, a very basic question - I would like to calculate the value of an investment, say £100,000 which is growing at a nominal rate, say 3% per annum with an annual drawdown at another nominal rate, say 4%. The objective is to see how long the money lasts at differing interest/drawdown rates. I will make the values all variables and will reference the cells but I am stumped for the basic calculation.

Thanks in advance!
Just noticed typo in the thread title - I do know how to spell interest but cannot edit it.

 

[Dave Patton]

Another idea to try

T202404.xlsm
	A	B	C	D	E	F
1		Principal	100,000.00
2		Rate	3%
3		Drawdown	4,000.00	Fixed amount accelerating at 3.5%
4		Final drawdown		25
5	Year	Interest	Drawdown	100,000.00
6	1	3,000.00	4,000.00	99,000.00
7	2	2,970.00	4,140.00	97,830.00
8	3	2,934.90	4,284.90	96,480.00
9	4	2,894.40	4,434.87	94,939.53
10	5	2,848.19	4,590.09	93,197.62
1d

Cell Formulas
Range	Formula
B6:B10	B6	=D5*$C$2
D4	D4	=XLOOKUP(0,D6:D31,A6:A31,1,1)
D5	D5	=C1
D6:D10	D6	=D5+B6-C6
C7:C10	C7	=MIN(D6*1.03,IF(D6>1,C6*1.035,0))

 

[SimonHughes]

You could also experiment with Goal Seek.

T202404.xlsm
	A	B	C	D	E	F
1				458.21058
2		Principal	100,000.00		1,000.00	Goal Seek
3		Rate	3%		1,000.00	Goal Seek
4		Drawdown	4%
5
1d

Cell Formulas
Range	Formula
E2	E2	=FV(C3-C4,D1,,-C2)
E3	E3	=C2*(1+0.03-0.04)^D1

Thanks Dave, yes, I will have a look at this

 

[Dave Patton]

Did you consider post #11?

 

[SimonHughes]

Did you consider post #11?

Hi Dave, yes, and it works really well. I have tweaked it to make it as I would like it and am happy to show it here but I cannot find a link to download XL2BB - can you help? Apologies for tardy response but I am on holiday in New Zealand and the time difference is 12 hours.

 

[Dave Patton]

Thanks for the feedback.
ink to download XL2BB

• Want to help your helpers by posting a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest October 2023 v 2.1 )

 

[SimonHughes]

Thanks for the feedback.
ink to download XL2BB

• Want to help your helpers by posting a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest October 2023 v 2.1 )

Hi Dave, got there in the end and this is what my sheet looks like now. I added an escalator for the pension data as well as for the investment amount and have offset that against investment growth rate which can be proxy for inflation. All playing around with numbers I know but it is indicative and interesting. Thanks for your help.

Drawdown Calculator 04.2024.xlsx
	A	B	C	D	E	F	G	H	I
1	Year	Interest	Capital	Drawdown	Pension Income	Total Income		ITEM	VALUE
2	1	-£3,000	£93,000	£4,000	£21,000	£25,000		Principal	£100,000
3	2	-£2,790	£86,090	£4,120	£21,525	£25,645		Growth Rate	-3.0%
4	3	-£2,583	£79,264	£4,244	£22,063	£26,307		Pension Growth Rate	2.5%
5	4	-£2,378	£72,515	£4,371	£22,615	£26,986		Drawdown Rate	4.0%
6	5	-£2,175	£65,837	£4,502	£23,180	£27,682		Annual Drawdown Increase %	3.0%
7	6	-£1,975	£59,225	£4,637	£23,760	£28,397		Years to Zero	17
8	7	-£1,777	£52,672	£4,776	£24,354	£29,130
9	8	-£1,580	£46,173	£4,919	£24,962	£29,882
10	9	-£1,385	£39,720	£5,067	£25,586	£30,654
11	10	-£1,192	£33,310	£5,219	£26,226	£31,445
12	11	-£999	£26,935	£5,376	£26,882	£32,257
13	12	-£808	£20,590	£5,537	£27,554	£33,091
14	13	-£618	£14,269	£5,703	£28,243	£33,946
15	14	-£428	£7,967	£5,874	£28,949	£34,823
16	15	-£239	£1,677	£6,050	£29,672	£35,723
17	16	-£50	-£101	£1,728	£30,414	£32,142
18	17	£3	£6	-£104	£31,175	£31,071
19	18	£0	£113	-£107	£31,954	£31,847
Alt

Cell Formulas
Range	Formula
B2	B2	=I2*$I$3
C2	C2	=I2+B2-D2
D2	D2	=I2*I5
F2:F19	F2	=SUM(D2+E2)
B3:B19	B3	=C2*$I$3
C3:C19	C3	=C2+B3-D3
D3:D19	D3	=MIN(C2*1.03,IF(C2>1,D2*(1+$I$6),0))
E3:E19	E3	=E2+(E2*$I$4)
I7	I7	=XLOOKUP(0,C2:C41,A2:A41,1,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F35	Expression	=$A2=$I$7	text	NO

 

[Dave Patton]

I made a few changes that may or may not be relevant or applicable.
Please review and edit to your requirements.

T202404.xlsm
	A	B	C	D	E
1	ITEM	VALUE
2	Principal	100,000.00
3	Interest	2.5%
4	Pension Growth	2.0%
5	Drawdown	25.0%
6	Drawdown Increase	3.0%
7	Years to Zero	18
8
9	Year	Interest	Pension Income	Drawdown	Capital
10	1	2,500.00	21,000.00	-25,000.00	98,500.00
11	2	2,462.50	21,420.00	-25,750.00	96,632.50
12	3	2,415.81	21,848.40	-26,522.50	94,374.21
13	4	2,359.36	22,285.37	-27,318.18	91,700.76
14	5	2,292.52	22,731.08	-28,137.72	88,586.63
15	6	2,214.67	23,185.70	-28,981.85	85,005.15
16	7	2,125.13	23,649.41	-29,851.31	80,928.38
17	8	2,023.21	24,122.40	-30,746.85	76,327.14
18	9	1,908.18	24,604.85	-31,669.25	71,170.91
19	10	1,779.27	25,096.94	-32,619.33	65,427.80
20	11	1,635.70	25,598.88	-33,597.91	59,064.47
21	12	1,476.61	26,110.86	-34,605.85	52,046.09
22	13	1,301.15	26,633.08	-35,644.02	44,336.30
23	14	1,108.41	27,165.74	-36,713.34	35,897.11
24	15	897.43	27,709.05	-37,814.74	26,688.84
25	16	667.22	28,263.24	-38,949.19	16,670.12
26	17	416.75	28,828.50	-40,117.66	5,797.71
27	18	144.94	29,405.07	-35,347.72	0.00
1dd

Cell Formulas
Range	Formula
B7	B7	=XLOOKUP(0,E10:E50,A10:A50,1,1)
B10	B10	=B2*$B$3
D10	D10	=B2*-B5
E10	E10	=B2+SUM(B10:D10)
B11:B27	B11	=E10*$B$3
C11:C27	C11	=C10+(C10*$B$4)
D11:D27	D11	=-MIN(-D10*(1+$B$6),(E10+B11+C11))
E11:E27	E11	=E10+SUM(B11:D11)

 

[Dave Patton]

Another idea assuming post #17 is a reasonable starting point

We could ask a question like the following.

I would like a balance of say 200,000 after 15 years. What change to the drawdown % will yield that result.

Goal Seek would yield that information
- select E24
- move to Goal Seek in menu
Change cell to 200000
by changing B5
Press Enter and a result will soon appear
Click Ok.

With the example that would change the Drawdown from 25% to 17.1%

 

[SimonHughes]

Another idea assuming post #17 is a reasonable starting point

We could ask a question like the following.

I would like a balance of say 200,000 after 15 years. What change to the drawdown % will yield that result.

Goal Seek would yield that information
- select E24
- move to Goal Seek in menu
Change cell to 200000
by changing B5
Press Enter and a result will soon appear
Click Ok.

With the example that would change the Drawdown from 25% to 17.1%

Hi Dave, that is an interesting function. Thanks for mentioning it!