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.