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.

 

[Micron]

I entered your title in a Google search and got 535,000 hits for doing the calculations without knowing the formula I guess. If you want the formula, add the word Excel and it jumps to 900K+.

Did you try that?

 

[Dave Patton]

What result do you expect with the information that you presented?
N.B. You can post a concise extract of your sheet with the forum's tool named XL2BB.

Review and edit the following:

T202404.xlsm
	A	B	C	D	E
1
2		Principal	100,000.00		$2.61
3		Rate	3%
4		Drawdown	4%
5
6	Year	Interest	Drawdown	100,000.00
7	1	3,000.00	4,000.00	99,000.00
8	2	2,970.00	3,960.00	98,010.00
9	3	2,940.30	3,920.40	97,029.90
1d

Cell Formulas
Range	Formula
E2	E2	=FV(C3-C4,1050,,-C2)
D6	D6	=C2
B7:B9	B7	=D6*$C$3
C7:C9	C7	=D6*$C$4
D7:D9	D7	=D6+B7-C7

 

[SimonHughes]

What result do you expect with the information that you presented?
N.B. You can post a concise extract of your sheet with the forum's tool named XL2BB.

Review and edit the following:

First, thanks for the response. What I want to do is to calculate, within various growth and drawdown scenarios, is how long will an initial investment last. I have looked at your formula, and the only thing I do not understand is the nper value of 1050 - can you explain that to me? Also, what are the value (£2.61) dependents? It reduces to zero if I change the growth rate or capital value significantly and I am not sure what it signifies.

 

[Dave Patton]

"I have looked at your formula, and the only thing I do not understand is the nper value of 1050 - can you explain that to me? Also, what are the value (£2.61) dependents? It reduces to zero if I change the growth rate or capital value significantly and I am not sure what it signifies."

If you review/extend the sample calculations for 1050 rows, the balance will be down to 2.61. The amount continue to decrease by each year.

The 1050 is just the number I picked since the balance was down to just 2.61.

Please review your requirement and try the formulas with some of your test data.
There may be other formulas or ways to achieve what you require.

 

[Cubist]

Mathematically speaking you'll never reach 0 exactly. However, you can define a relatively small threshold say 1 instead. The equation to solve for the number of periods when the fund dips below 1 threshold is

In general, the formula is this where n= number of periods, P is the principal amount, i% is the interest rate, d% is the drawback rate, and k is the threshold.

 

[SimonHughes]

"I have looked at your formula, and the only thing I do not understand is the nper value of 1050 - can you explain that to me? Also, what are the value (£2.61) dependents? It reduces to zero if I change the growth rate or capital value significantly and I am not sure what it signifies."

If you review/extend the sample calculations for 1050 rows, the balance will be down to 2.61. The amount continue to decrease by each year.

The 1050 is just the number I picked since the balance was down to just 2.61.

Please review your requirement and try the formulas with some of your test data.
There may be other formulas or ways to achieve what you require.

Hi Dave, got it and many thanks. I have tinkered with this by adding an inflation rate and got it up to speed. I am next going to do a calculator that shows fund longevity with a fixed drawdown value. Again, thanks for your help

 

[SimonHughes]

Mathematically speaking you'll never reach 0 exactly. However, you can define a relatively small threshold say 1 instead. The equation to solve for the number of periods when the fund dips below 1 threshold is
View attachment 109564
In general, the formula is this where n= number of periods, P is the principal amount, i% is the interest rate, d% is the drawback rate, and k is the threshold.
View attachment 109565

Thanks Cubist but this is too much for me - but I appreciate your response.

 

[Cubist]

Thanks Cubist but this is too much for me - but I appreciate your response.

No problem. It's a simple Excel implementation. Maybe this will help. See cell E3. You can validate the answer by dragging the formulas down until the year 1146. The ending balance in D should be less than 1 beyond this point.

Book1
	A	B	C	D	E	F
1		Threshhold	1
2		Principal	100000		$2.61
3		Rate	0.03		1145.52644
4		Drawdown	0.04
5
6	Year	Interest	Drawdown	100000
7	1	3000	4000	99000
8	2	2970	3960	98010
9	3	2940.3	3920.4	97029.9
10
Sheet6

Cell Formulas
Range	Formula
E2	E2	=FV(C3-C4,1050,,-C2)
E3	E3	=LN(C1/C2)/LN(1+C3-C4)
D6	D6	=C2
B7:B9	B7	=D6*$C$3
C7:C9	C7	=D6*$C$4
D7:D9	D7	=D6+B7-C7

 

[Dave Patton]

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