ExcelBanter - View Single Post

jIM

wrote:
[Taking the liberty of quoting from your two postings out of
context....]

jIM wrote:
I might debate that the "sum of the PV's is the total amount needed",
[....]
I need to be convinced the PV in column A is for real...

The "proof" was intended to be the table that I constructed in columns
C and D. But in abstract terms, column A is the PV of the part of the
investment required to fund the future withdrawal (income) required at
the end of the year presented by the row. "The whole is equal to the
sum of its parts".

why would value of this be declining to generate an increasing income?

Because the investment rate of return (5%) exceeds the inflation rate
(3%). The PV() formula rolls back the future required withdrawal
(income) to the same time frame in each row, namely to the initial
investment at the beginning of the first year.

What is purpose of column C?

The table in columns C and D is what you called an "amortization"
table. You could add an "interest" column, if you like. Column C
represents the investment balance at the beginning of the year. C1 is
the initial investment. C2 et al are the previous balance plus the
investment return (presumed to be 5%) minus the income withdrawal in
column D, which grows at the presumed inflation rate of 3%.
Equivalently, C2 et al are the ending investment balance for the
previous row.

Thus, C33 should be zero, per your requirement ("an ending balance of
zero"). Since C1 is the sum of the PVs in column A, that should
convince you that "the sum of the PVs is the total amount needed". QED
;-).

E2:E33 in my spreadhseet came out as divide by zeros (was that first
percentage inverted?)

What is this doing?
ROW()-ROW($B$7)
ROW()-ROW($A$2)+1

I am not sure what this notation or syntax is calculating.
Later you said that you corrected your formula errors. But to address
your last question....

Column E computes the percentage of the ending investment balance that
is withdrawn. The formula is D1 / (C1*(1+5%)) because C1 is the
beginning balance, not the ending balance. Algebraically, that is the
same as (D1 / C1) / (1+5%), which is how Excel evaluates D1 / C1 /
(1+5%).