I am trying to simulate some market returns for a retirement investment
portfolio.

I have the PMT function working well.

example:

want income of $250,000, for 32 years, 3% withdraw rate, overall
portfolio value (PV) is $5,097,000.

This is only an estimate- the purpose was to find the aprox value of an
investment portfolio which would yield $250k of income.

On another spreadsheet I was trying to calculate an ammortization table
to know how much principal and how much interest makes up the
$5,100,000.

My confusion is with the NPER and PER inputs- what do these mean? Is
one the 32 years and the other which of the 32 years it's calculating?

Fred Smith wrote:
I can't help you with the NPER and PER, because I don't know what function you
are referring to.

However, it's easy to calculate the interest and principal portion using simple
math.

The principal is, of course, $5,097,000.

The interest is Total Payments - Principal, or 250000*32-5097000 = 2,903,000

--
Regards,
Fred


"jIM" wrote in message
oups.com...
I am trying to simulate some market returns for a retirement investment
portfolio.

I have the PMT function working well.

example:

want income of $250,000, for 32 years, 3% withdraw rate, overall
portfolio value (PV) is $5,097,000.

This is only an estimate- the purpose was to find the aprox value of an
investment portfolio which would yield $250k of income.

On another spreadsheet I was trying to calculate an ammortization table
to know how much principal and how much interest makes up the
$5,100,000.

My confusion is with the NPER and PER inputs- what do these mean? Is
one the 32 years and the other which of the 32 years it's calculating?


I am using the IPMT function- what is difference between NPER and PER?

You figured out the difference correctly in your first post. If I have
to pay back in 32 payments, nper=32. For the 4th payment per=4.

HTH
Kostis Vezerides

jIM wrote:
Fred Smith wrote:
I can't help you with the NPER and PER, because I don't know what function you
are referring to.

However, it's easy to calculate the interest and principal portion using simple
math.

The principal is, of course, $5,097,000.

The interest is Total Payments - Principal, or 250000*32-5097000 = 2,903,000

--
Regards,
Fred


"jIM" wrote in message
oups.com...
I am trying to simulate some market returns for a retirement investment
portfolio.

I have the PMT function working well.

example:

want income of $250,000, for 32 years, 3% withdraw rate, overall
portfolio value (PV) is $5,097,000.

This is only an estimate- the purpose was to find the aprox value of an
investment portfolio which would yield $250k of income.

On another spreadsheet I was trying to calculate an ammortization table
to know how much principal and how much interest makes up the
$5,100,000.

My confusion is with the NPER and PER inputs- what do these mean? Is
one the 32 years and the other which of the 32 years it's calculating?


I am using the IPMT function- what is difference between NPER and PER?

vezerid wrote:
You figured out the difference correctly in your first post. If I have
to pay back in 32 payments, nper=32. For the 4th payment per=4.

HTH
Kostis Vezerides

jIM wrote:
Fred Smith wrote:
I can't help you with the NPER and PER, because I don't know what function you
are referring to.

However, it's easy to calculate the interest and principal portion using simple
math.

The principal is, of course, $5,097,000.

The interest is Total Payments - Principal, or 250000*32-5097000 = 2,903,000

--
Regards,
Fred


"jIM" wrote in message
oups.com...
I am trying to simulate some market returns for a retirement investment
portfolio.

I have the PMT function working well.

example:

want income of $250,000, for 32 years, 3% withdraw rate, overall
portfolio value (PV) is $5,097,000.

This is only an estimate- the purpose was to find the aprox value of an
investment portfolio which would yield $250k of income.

On another spreadsheet I was trying to calculate an ammortization table
to know how much principal and how much interest makes up the
$5,100,000.

My confusion is with the NPER and PER inputs- what do these mean? Is
one the 32 years and the other which of the 32 years it's calculating?


I am using the IPMT function- what is difference between NPER and PER?

So I have the correct definition of the variables, the IPMT function,
PMT function and FV function do not appear to "mesh".

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.

I have the spreadsheet set up, but with PV it suggests amount is
$5,097,000 for an income needed of $250,000. When I crunch spending
this in reverse, it does not last 32 years. I assume the inflation
factor is the error, but when I set the inflation to zero percent, the
money lasted longer, but not 32 years.

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?

which function would you use to show the ammortization table for this
draw down? Some of money each year would be interest and some of it
each year would be principal. My goal is to have a table to look at
average market returns during the retirement.

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?

wrote:
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.

The excel help assumes I know per and nper when reading what to input
for them. It also assumes I understood what the percentage was used
for.

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%.

I figured this out when it suggested $236,000 was needed for an income
of $250,000. That is when I posted for help, because I obviously did
not know what formula was doing.


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.

The amount of $250,000 is the starting point... there are investment
techniques which may allow the inflation number to be neglected. So I
want two scenarios- one where $250,000 stays constant for 32 years and
other where $250,000 is indexed to inflation.


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):

this part was easy


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

this made sense

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 might debate that the "sum of the PV's is the total amount needed",
but I am assuming you are correct for now.

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):

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

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.

Taxes not needed, some of this is a Roth IRA and the investment
assumptions will take taxes into consideration anyway.

Does that help?

Some... and more help than anyone else. I do not full understand
column C or E.

wrote:
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?

I have tweaked spreadsheet and found my formula error. What is purpose
of column C?

In addition, I need to be convinced the PV in column A is for real...
why would value of this be declining to generate an increasing income?

[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?)

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%).

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%).

jIM wrote:
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 a little slow sometimes, this appears to be adding the number of
rows to tell the formulas what period it's in?

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

For the PV() formula, I wrote:

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

ROW()-ROW($A$1)+1 computes the number of years (periods) to discount
over. This evaluates to 1, 2, 3, etc for rows A1, A2, A3, which
represent years 1, 2, 3 respectively. Recall that I assume that the
withdrawal is at the end of the year; so the investment appreciates in
the first year. ROW() is the current row number. ROW($A$1) is the row
number for the first row of the table. This formulation works even if
the table is subsequently moved or cut-and-pasted (not copied), for
example by inserting title rows and columns.

For the FV() formula, I wrote:

B1: =FV(3%, ROW()-ROW($B$1), 0, -250000)

ROW()-ROW($B$1) computes the number of inflation years (periods). This
evaluates to 0, 1, 2 etc for rows B1, B2, B3, which represents years 1,
2, 3 respectively. We want zero inflation for the first year; one
inflation factor (i.e. 250000*(1+3%)^1) the second year; etc. The
choice of $B$1 or $A$1 is arbitrary because the row number is the same.
So I use $A$1 in the array formula SUM(PV(...,FV(...))).