jIM wrote:
Here is the problem I am trying to solve:
Have $X at age 68, which must generate $Y of income from age 68-100 (32
years). $Y must increase by inflation percentage a and $X will change from
market growth b and reduce by a*$Y each year.
[....]
So the root cause questions:
which function would you use to determine the principal amount needed
to generate an annual income of $250,000 for 32 years, assuming a 3%
withdraw rate and an ending balance of zero?

Perhaps the root of your confusion is: those are two very different
problem statements, with unrelated answers. Moreover, neither of those
problem statements seems to match the example solution that you offered
in your initial posting.

In your initial posting, "$5,097,000" (really $5,097,191) seems to be
the result of the formula PV(3%,32,-250000). That tells you what
investment is needed to generate an annual income of $250,000 with an
annual investment __return__ (not a withdrawal rate) of 3%.

Your second question above suffers from the same ambiguous use of
terms, because in one part you ask about generating a fixed income of
$250,000 and in another part you seem to ask about a variable income
(withdrawal) of 3% (of the investment balance, presumably.

Since your first question above is the more realistic one -- and the
more tractable problem to solve -- I will address that. I believe you
are asking: what initial investment (X) is required, assuming that the
initial income (Y) is $250,000?

Assume the inflation rate is 3% (applies to Y), and assume that your
expected average annual return is 5%. You could compute X with the
following array formula (use ctrl-shift-Enter to compute):

=SUM(PV(5%, ROW(A1:A32)-ROW($A$1)+1, 0, FV(3%, ROW(A1:A32)-ROW($A$1),
0, 250000)))

My answer is $5,744,716. For this formulation, I assume that the
income is taken at the end of each investment year. That is, the first
$250,000 is withdrawn after one year of investment returns. The array
formula might be easier to understand by constructing the following
table (copy A1:B1 down through A32:B32):

A1: =PV(5%, ROW()-ROW($A$1)+1, 0, -B1)
B1: =FV(3%, ROW()-ROW($B$1), 0, -250000)

B1 is the income for the year, adjusted for 3% inflation. A1 is the
investment required to generate that income, assuming an average return
of 5%. The total required investment is the sum of the PVs, namely
SUM(A1:A32). (For below, assume that is in A33.)

I confirm this by constructing the following table. The table
demonstrates some subtle assumptions. If you make different
assumptions, you need to adjust the PV() and FV() formulas accordingly.
My table is (copy C2 down through C33 and copy D2 down through D32):

C1: =A33
D1: =250000
E1: D1 / C1 / (1+5%)
C2: =IF(ROUND(C1-D1,0)<=0, 0, C1*(1+5%) - D1)
D2: =D1*(1+3%)

C1 is the investment balance at the beginning of each year. D1 is the
income withdrawn at the end of each year. E1 is the percentage of the
investment balance withdrawn.

You asked about "interest". If you want to compute the "taxable
income" each year, that depends on the type of investment. For cash
investments, the taxable income is the 5% return. For securities, the
taxable capital gain is the income withdrawn, assuming you could sell
only as much as you need.

Does that help?