Amortization based withdrawal formulas

These are the formulas used for Amortization based withdrawal.

Amortization Formula
If $$P$$ is the lump sum value, $$A$$ is the amount paid over $$n$$ periods, and $$r$$ is the interest rate, then



A = \frac{P*r}{1-\frac{1}{(1+r)^n}} \; $$

Note that this formula assumes that the periodic payment $$A$$ begins next period, not immediately. The lump sum $$P$$ is the value today (period 0), and the periodic payments $$A$$ are being made in periods 1 through $$n$$. This is natural in the context of a loan: the loan is taken out today and the repayments only start next period.

The formula does not work for $$r=0$$. You can input $$r$$ close to zero in the above formula to get an approximate answer. Or you can use simple division to get the exact answer.

In Excel, the PMT function can be used for this calculation:



A=\operatorname{PMT}(r,n,-P,0,0) $$

Amortization Based Withdrawal Formula
To calculate portfolio withdrawals, set $$P=$$ current portfolio value, $$n=$$ number of years over which withdrawals are to be spread out, and $$r=$$ expected return of the portfolio.

The amortization formula above assumes that payments begin next year, not immediately. If withdrawals are to start this year, the formula needs to be divided by $$(1+r)$$:



A = \frac{P*r}{1-\frac{1}{(1+r)^n}}*\frac{1}{1+r} \; $$

In Excel, the PMT function can be used for this calculation:



A=\operatorname{PMT}(r,n,-P,0,1) $$

Adding a Terminal Balance
Instead of fully depleting the portfolio, the amortization can be modified to leave behind a terminal balance. If the target terminal balance is $$B$$ dollars, withdrawal is:



A = \frac{(P-\frac{B}{(1+r)^n})*r}{1-\frac{1}{(1+r)^n}}*\frac{1}{1+r} \; $$

In Excel, the PMT function can be used for this calculation:



A=\operatorname{PMT}(r,n,-P,B,1) $$

Allowing for Rising or Falling Withdrawal Schedules
Instead of a constant withdrawal schedule, the amortization can be modified to generate a withdrawal schedule that grows at a rate of $$g$$ per year. First year's withdrawal is:



A = \frac{(P-\frac{B}{(1+r)^n})*(r-g)}{1-(\frac{1+g}{1+r})^n}*\frac{1}{1+r} \; $$

$$g>0$$ generates a rising withdrawal schedule, $$g<0$$ generates a declining withdrawal schedule, and $$g=0$$ generates a constant withdrawal schedule.

The formula does not work for $$r=g$$. You can input $$r$$ close to $$g$$ in the above formula to get an approximate answer. Or you can use simple division to get the exact answer.

In Excel, the PMT function can be used for this calculation:



A=\operatorname{PMT}(\frac{1+r}{1+g}-1,n,-P,\frac{B}{(1+g)^n},1) $$

Net Present Value (NPV) Calculations
To include future income in the amortization, some methods add the net present value (NPV) of future net income to the savings portfolio.

Future net income constitutes a cash flow, which is a stream of payments over time. The payments could be positive (inflow, as in income) or negative (outflow, as in expenses). The NPV of a cash flow, discounted by the rate $$r$$, is the lump sum amount required today that, if it grows at the rate $$r$$, can reproduce the cash flow.

Suppose the cash flow is $$Y_0$$ in period $$0$$ (present), $$Y_1$$ in period $$1$$, $$Y_2$$ in period $$2,...,Y_T$$ in period $$T$$. If the discount rate is $$r$$, then the NPV of this cash flow is given by



NPV = Y_0 + \frac{Y_1}{(1+r)^1} + \frac{Y_2}{(1+r)^2} + ... + \frac{Y_T}{(1+r)^T} $$

In Excel, this calculation can be performed using the NPV function



\operatorname{NPV}(r,Y_0:Y_T)*(1+r) $$

or the XNPV function



\operatorname{XNPV}(r,Y_0:Y_T,D_0:D_T) $$

where $$D_0:D_T$$ contains the dates of the cash flow $$Y_0:Y_T$$.