I created a retirement calculator
The idea is if you save enough you can so when you withdraw say 4% you don't eat too much into your savings.
But based on my calculation 4% of 500000 is $20k but next year it will be 4% of 480000 and so on.
What formula will determine what % of withdrawal will keep the withdrawal amount the same over a set time period or until the principal is exhausted?
The formula that "will determine what % of withdrawal [of the initial principal] will keep the withdrawal amount the same over a set time period or until the principal is exhausted" is
percentage = 100/number of years
i.e. with savings s = 500000
no. years n = 25
percentage = 100/n = 4
Check: annual withdrawal w = 0.04 s = 20000
n w = s
However, taking into account interest earned on the savings
with interest r = 5% annually
percentage = 100 r (1 + 1/((1 + r)^n - 1)) = 7.095246
i.e. annual withdrawal w = 0.07095246 s = 35476.23
This depends on the withdrawals being at year-end, so the last withdrawal exhausts the principal at the end of the last year. It also means the first withdrawal happens at the end of the first year, so the savings interest accrual of the first year is included in the calculation. If you want immediate withdrawal you can adjust the start point.
For example, with 25 withdrawals starting immediately
no. years n = 24
r = 0.05
s = 5000000
percentage = 100 r (1 + 1/((1 + r)^n - 1)) = 7.24709
annual withdrawal w = 0.0724709 (s - w)
∴ w = 0.0724709 s/(1 + 0.0724709) = 33786.88
The way the question is written, the answer is that your withdrawal rate must be whatever the constant growth rate of the account is. In other words, if your account is worth $100 and makes 5% each year, you can safely withdraw $5, or about 4.762%, of the EOY balance (or 5% of the BOY balance) each year perpetually.
However, you what you probably mean to ask is what the withdrawal amount should be to amortize the account over a given period. The balance B at month m = 0 is B(0) = p0. Assume a monthly interest rate of r and a total number of months M. The balance at month B(m + 1) = r * p0 - X, where X is the monthly withdrawal rate. If we want to exhaust the money after M months then we get B(M) = 0. Let's write out some terms:
m B(m)
---------
0 p0
1 r * p0 - X
2 r * (r * p0 - X) - X = r^2 * p0 - r * X - X
3 r * (r^2 * p0 - r * X - X) - X = r^3 * p0 - r^2 * X - r * X - X
…
k r^k * p0 - X * (r^(k-1) + r^(k-2) + … + 1)
We can use the partial sum formula to simplify the sum of powers of r to get:
B(k) = r^k * p0 - X * (r^k - 1) / (r - 1)
When k = M we require that B(k) = 0:
B(M) = r^M * p0 - X * (r^M - 1) / (r - 1) = 0
r^M * p0 = X * (r^M - 1) / (r - 1)
r^M * p0 * (r - 1) / (r^M - 1) = X
So, if p0 = $100k, r = 5% per year = 1.004074 and M = 240 (20 years), then the monthly withdrawal rate is:
X = 2.653219 * $100k * (0.004074) / (1.653219)
~ $653.82
This is basically the same way mortgage amortization is done. Note that this is taking out the same payment but different percentages, which is probably a lot closer to what you're really looking for.
