How do I calculate the starting balance required

Suppose I require a payment of \$25,000 per year over 20 years, with an inflation rate of 3%, an earning rate of 5%, a tax rate of 20% and a final balance of \$0. What is my starting balance to achieve this?

What is the formula to implement this calculation?

• This sounds a lot like homework. You need to disclose that if it is, and we generally aren't willing to just do your homework questions for you unless the question is useful for personal finance. – JohnFx Dec 23 '15 at 4:24
• @JohnFx The OP stated in a comment on an answer: "However, it not homework. This is part of the requirements I have for an insurance calculator I've been commissioned to write." – user32479 Dec 24 '15 at 14:06
• How is the 20% tax calculated? Is it 20% of the distributions? 20% of the real (after-inflation) return? 20% of the nominal (earning rate) return? 20% of the all amounts received after the initial nominal balance is paid back? – Jasper Dec 24 '15 at 14:13
• How does the 3% inflation rate affect the problem? For example, do the payments need to increase at a 3% annual rate, with compounding? – Jasper Dec 24 '15 at 14:14
• When are the payments due? Monthly, with the first payment due about 30 days after the "present" in "present value"? Annually, with the first payment due at the end of the first year? Daily (a.k.a, approximately continuously)? – Jasper Dec 24 '15 at 14:19

Assumptions and clarifications:

• The compounding and payouts occur annually, so the annual percentage rates (APRs) equal the corresponding annual percentage yields (APYs, aka EARs).
• Payouts start at the end of the first year.
• The \$ 25,000 per year requirement is a requirement for a real (inflation-adjusted) after-tax cash flow. In other words, the first year's payout is \$ 25,750 after taxes; the second year's payout is \$ 26,522.50 after taxes, et cetera.
• The return of the original principal is not taxed.
• Interest is taxed as it accumulates. The tax on the interest is not deferred until payout. In this example, this is equivalent to paying out interest (and the associated taxes) as the interest is earned, and postponing paying out principal as long as possible (while still satisfying the cashflow and final value requirements).

The amount of original principal that is required can be calculated using the standard PVIFA formula, with the \$ 25,000 annual payment, but with an adjusted interest rate that accounts for the inflation and taxes. The formulas are:

r = 5% = 0.05 =nominal pre-tax annual interest rate (APR = APY, because 1 period/year)
i = 3% = 0.03 =annual inflation rate (ditto)
t = 20% = 0.20 =tax rate on interest as it accumulates
R = \$ 25,000 / year =annual after-tax cash flow, after adjusting for inflation
q = (1 + r * (1 - t)) / (1 + i) - 1 = 0.0097087… = 0.97087…% =adjusted interest rate.
n = 20 years * 1 period/year = 20 periods =number of periods
PV = R * (1 - (1+q)^(-n))/q = \$ 452,464.60 =initial principal required at x = 0.
x =year for which after-tax nominal payout is being calculated,
...with first payout occurring when x = 1, and last payout when x = n.
M = R * (1 + i)^(x) =after-tax nominal payout at the end of each year.

If 20% tax is paid on distributions and the net is to be \$25,000 the gross per year needs to be

25000/(1 - 0.2) = 31250

However these amounts need to be adjusted for inflation so at the end of year 1 the gross distribution should be

31250 (1 + 0.03) = 32187.50

and at the end of year 2: 31250 (1 + 0.03)^2 = 33153.125 etc.

This means at the end of year 3 when the expected gross distribution is 31250 (1 + 0.03)^3 = 34147.71875 this is the inflation-adjusted future value, equivalent to \$31,250 in present value, which after 20% tax yields £25,000 present day value.

Using the model for an ordinary annuity from here: Calculating The Present And Future Value Of Annuities

E.g. The summation for your fund would be giving the present value required as \$513,866.47

The formula for this summation is where i and r are inflation and the growth rate respectively.

i.e.

p = 31250
i = 0.03
r = 0.05
n = 20

((1 + i) p (1 + r)^-n ((1 + i)^n - (1 + r)^n))/(i - r) = 513866.47

Using

P = p = 31250
g = i = 0.03
r = r = 0.05
n = n = 20

the formula referred to by THEAO produces a different result: \$498,899.49 As the page describes, it is derived as follows: So we can see, if the distribution at the end of year 1 is \$31,250 we need present value capital of \$498,899.49. However, at the end of year 1 the value of the distribution should be inflation-adjusted by 1 year to be equivalent to present value gross \$31,250 or \$25,000 net.

If we adjust for one year's inflation and start with P = 31250 (1 + 0.03) = 32187.50 then the formula referenced by THEAO also results in \$513,866.47, as I calculated.

Assuming your question doesn't ask for the \$25K to go up by the value of inflation (which is Master's level math, if not PhD), and assuming tax is irrelevant (unless you didn't put it in when you transcribed the problem)...

Draw 5 boxes, and label 1) Present Value, 2) Final Value, 3) # of Periods, 4) Periodic Rate, and 5) Payment. Check off the ones you've got and then use the Excel formula for the one you don't. (Present value).

Updating with the actual "simple" formula:

PV = PMT * [(1 - (1 / (1 + r)^NPER)) / r]

If you really need to have the PMT value be pre-tax, to account for taxes, that's simply (\$25,000 / (1 - tax rate)).

If you want it to go up by the value of inflation and account for taxes, and can't do the math yourself, maybe you shouldn't have taken the contract.

Kidding aside, the more complex formula is here: Happy coding!

• Thanks. However, it not homework. This is part of the requirements I have for an insurance calculator I've been commissioned to write. The \$25K does go up by the value of inflation and the tax is relevant on the earnings. – Brett G Dec 22 '15 at 5:16