I need to calculate the present value of an annuity where the base payment
increases by a fixed percent each period, thus compounds on itself each
period.

To clarify what I mean by compounding on itself: e.g. payment increases by
3% each period. So, period 1 payment is $1,000, period 2 payment is $1,030
(1,000*(1+.03)), period 3 payment is $1,060.90 (1,030*(1+.03)), and so on for
however many periods.

The base information for one annuity case I am working with is a monthly
payment to a beneficiary of $1,889 that increases 3% annually (so, .25%
monthly) for a period of 30 years (360 months). The annual interest rate to
apply is 4.9%. Is there an Excel function that can handle this all in one
step? In order to calculate the correct present value, I have had to setup
several columns worth of formulas that do the monthly payment compounding and
then figure the present value based on the series of continually increased
payments. I've tried using the PV function alone by inputting the base
information into the PV function, but I do not get a proper PV calculation.

I hope I have explained this clearly enough. I'd be happy to answer any
questions about what I am trying to explain if needed.

Any thoughts would be greatly appreciated. Thank you in advance for your
time.

-bk

First, you don't have continuously compounded annuity payments, you have
discretely compounding periods. You either have monthly compounding (at .25% per
month), or annual compounding (at 3% per year). Note that .25% per month is
*greater than* 3% per year.

The formula to calculate the present value of an annuity (A) increasing at a
rate of j invested at an interest rate of i for n periods is:

PV=A*((1-(1+j)^n*(1+i)^-n)/(i-j))

--
Regards,
Fred


"bk" wrote in message
...
I need to calculate the present value of an annuity where the base payment
increases by a fixed percent each period, thus compounds on itself each
period.

To clarify what I mean by compounding on itself: e.g. payment increases by
3% each period. So, period 1 payment is $1,000, period 2 payment is $1,030
(1,000*(1+.03)), period 3 payment is $1,060.90 (1,030*(1+.03)), and so on for
however many periods.

The base information for one annuity case I am working with is a monthly
payment to a beneficiary of $1,889 that increases 3% annually (so, .25%
monthly) for a period of 30 years (360 months). The annual interest rate to
apply is 4.9%. Is there an Excel function that can handle this all in one
step? In order to calculate the correct present value, I have had to setup
several columns worth of formulas that do the monthly payment compounding and
then figure the present value based on the series of continually increased
payments. I've tried using the PV function alone by inputting the base
information into the PV function, but I do not get a proper PV calculation.

I hope I have explained this clearly enough. I'd be happy to answer any
questions about what I am trying to explain if needed.

Any thoughts would be greatly appreciated. Thank you in advance for your
time.

-bk

On Mar 15, 12:00 pm, bk wrote:
I need to calculate the present value of an annuity where the base payment
increases by a fixed percent each period, thus compounds on itself each
period.
To clarify what I mean by compounding on itself: e.g. payment increases by
3% each period. So, period 1 payment is $1,000, period 2 payment is $1,030
(1,000*(1+.03)), period 3 payment is $1,060.90 (1,030*(1+.03)), and so on for
however many periods.

The base information for one annuity case I am working with is a monthly
payment to a beneficiary of $1,889 that increases 3% annually (so, .25%
monthly) for a period of 30 years (360 months). The annual interest rate to
apply is 4.9%.

Are you sure that the annuity payment increases each month?

The annuities I am familiar with that increase based on some index
(e.g. COLA) increase the monthly amount on an annual anniversary date,
then pay the same amount for 12 months.

PS: When you wrote "0.25% monthly", it is unclear whether you are
computing 3%/12 or rounding the correct figure just for presentation
purposes. If the payment does increase monthly with an effective
annual increase of 3%, the monthly increase is (1+3%)^(1/12)-1 or
RATE(12,0,-1,1+3%) -- which is approximately 0.2466%.

On Mar 15, 12:00 pm, bk wrote:
I need to calculate the present value of an annuity where the base payment
increases by a fixed percent each period, thus compounds on itself each
period.
To clarify what I mean by compounding on itself: e.g. payment increases by
3% each period. So, period 1 payment is $1,000, period 2 payment is $1,030
(1,000*(1+.03)), period 3 payment is $1,060.90 (1,030*(1+.03)), and so on for
however many periods.
The base information for one annuity case I am working with is a monthly
payment to a beneficiary of $1,889 that increases 3% annually (so, .25%
monthly) for a period of 30 years (360 months). The annual interest rate to
apply is 4.9%.

As I noted in a previous posting, I question whether the annuity
payment grows monthly or annually. I suspect it is the latter, based
on my experience.

Whichever your answer is, one of the following array formulas (commit
with ctrl-shift-Enter, not Enter) should work for you:

Monthly annuity grows monthly:
=SUM(PV(A2, ROW(A1:A360), 0, -1889*(1+A1)^(ROW(A1:A360)-1)))

Monthly annuity grows annually:
=SUM(PV(A2, 12*(ROW(A1:A30)-1), 0, -PV(A2, 12,
-1889*(1+3%)^(ROW(A1:A30)-1))))

In both cases, A2 is the monthly investment rate. That depends on how
you choose to interpret the annual rate of 4.9%: a simple interest
rate with a monthly rate of 4.9%/12; or an annual percentage yield
with a monthly rate of (1+4.9%)^(1/12)-1 or RATE(12,0,-1,1+4.9%). I
would choose the latter.

In the first array formula, A1 is the monthly rate of increase of the
annuity. Again, that depends on how you choose to interpret the
annual rate of 3%: monthly increase is 3%/12; or monthly increase is
(1+3%)^(1/12)-1 or RATE(12,0,-1,1+3%). Again, I would choose the
latter, if I believed the annuity payment changes monthly. (I don't.)

ROW(A1:A30) and ROW(A1:A360) are simply ways to generate the arrays
{1,2,...,30} and {1,2,...,360} respectively. The cell contents are
irrelevant.

If you understand present value calculations, hopefully the formulas
are intuitive -- at least the first formula. The first formula is
simply the sum of the PV of each monthly payment. The second formula
is the sum of the PV of each constant payment over 12 months, rolled
forward (PV) monthly for each year (multiple of 12 months).

It should be noted that both formulas make the assumption that the
present value is invested one month before the first annuity payment.

HTH.

Or, just use the formula:

PV=A*((1-(1+j)^n*(1+i)^-n)/(i-j))

Where j is the amount the payment increases per period, and i is the periodic
interest rate.

--
Regards,
Fred


"joeu2004" wrote in message
oups.com...
On Mar 15, 12:00 pm, bk wrote:
I need to calculate the present value of an annuity where the base payment
increases by a fixed percent each period, thus compounds on itself each
period.
To clarify what I mean by compounding on itself: e.g. payment increases by
3% each period. So, period 1 payment is $1,000, period 2 payment is $1,030
(1,000*(1+.03)), period 3 payment is $1,060.90 (1,030*(1+.03)), and so on for
however many periods.
The base information for one annuity case I am working with is a monthly
payment to a beneficiary of $1,889 that increases 3% annually (so, .25%
monthly) for a period of 30 years (360 months). The annual interest rate to
apply is 4.9%.

As I noted in a previous posting, I question whether the annuity
payment grows monthly or annually. I suspect it is the latter, based
on my experience.

Whichever your answer is, one of the following array formulas (commit
with ctrl-shift-Enter, not Enter) should work for you:

Monthly annuity grows monthly:
=SUM(PV(A2, ROW(A1:A360), 0, -1889*(1+A1)^(ROW(A1:A360)-1)))

Monthly annuity grows annually:
=SUM(PV(A2, 12*(ROW(A1:A30)-1), 0, -PV(A2, 12,
-1889*(1+3%)^(ROW(A1:A30)-1))))

In both cases, A2 is the monthly investment rate. That depends on how
you choose to interpret the annual rate of 4.9%: a simple interest
rate with a monthly rate of 4.9%/12; or an annual percentage yield
with a monthly rate of (1+4.9%)^(1/12)-1 or RATE(12,0,-1,1+4.9%). I
would choose the latter.

In the first array formula, A1 is the monthly rate of increase of the
annuity. Again, that depends on how you choose to interpret the
annual rate of 3%: monthly increase is 3%/12; or monthly increase is
(1+3%)^(1/12)-1 or RATE(12,0,-1,1+3%). Again, I would choose the
latter, if I believed the annuity payment changes monthly. (I don't.)

ROW(A1:A30) and ROW(A1:A360) are simply ways to generate the arrays
{1,2,...,30} and {1,2,...,360} respectively. The cell contents are
irrelevant.

If you understand present value calculations, hopefully the formulas
are intuitive -- at least the first formula. The first formula is
simply the sum of the PV of each monthly payment. The second formula
is the sum of the PV of each constant payment over 12 months, rolled
forward (PV) monthly for each year (multiple of 12 months).

It should be noted that both formulas make the assumption that the
present value is invested one month before the first annuity payment.

HTH.

Fred and Joe,

I apologize for the delay in responding. Unexpected busier times have
crossed my path that need my attention.

So, I wanted to take some time now to thank you both very much for posting
suggestions to help calculate the present values I need to. I am
experimenting with both of your suggestions as I can in hopes I can get your
ideas to work. As soon as I have more time, I will expirment further and let
you know if this helps. On top of this, I have found there to be some other
complications in the calculations I need to do that I didn't previously know
I had to deal with. I may post another question or two to ask for ideas and
any suggestions are greatly appreciated.

Joe, to answer your question about the annuity - it does increase annualy,
however for the types of calculations I'm doing I need to convert that
increase into a monthly calculation.

Again, thank you both for your time and suggestions.

-bk

"Fred Smith" wrote:

Or, just use the formula:

PV=A*((1-(1+j)^n*(1+i)^-n)/(i-j))

Where j is the amount the payment increases per period, and i is the periodic
interest rate.

--
Regards,
Fred


"joeu2004" wrote in message
oups.com...
On Mar 15, 12:00 pm, bk wrote:
I need to calculate the present value of an annuity where the base payment
increases by a fixed percent each period, thus compounds on itself each
period.
To clarify what I mean by compounding on itself: e.g. payment increases by
3% each period. So, period 1 payment is $1,000, period 2 payment is $1,030
(1,000*(1+.03)), period 3 payment is $1,060.90 (1,030*(1+.03)), and so on for
however many periods.
The base information for one annuity case I am working with is a monthly
payment to a beneficiary of $1,889 that increases 3% annually (so, .25%
monthly) for a period of 30 years (360 months). The annual interest rate to
apply is 4.9%.

As I noted in a previous posting, I question whether the annuity
payment grows monthly or annually. I suspect it is the latter, based
on my experience.

Whichever your answer is, one of the following array formulas (commit
with ctrl-shift-Enter, not Enter) should work for you:

Monthly annuity grows monthly:
=SUM(PV(A2, ROW(A1:A360), 0, -1889*(1+A1)^(ROW(A1:A360)-1)))

Monthly annuity grows annually:
=SUM(PV(A2, 12*(ROW(A1:A30)-1), 0, -PV(A2, 12,
-1889*(1+3%)^(ROW(A1:A30)-1))))

In both cases, A2 is the monthly investment rate. That depends on how
you choose to interpret the annual rate of 4.9%: a simple interest
rate with a monthly rate of 4.9%/12; or an annual percentage yield
with a monthly rate of (1+4.9%)^(1/12)-1 or RATE(12,0,-1,1+4.9%). I
would choose the latter.

In the first array formula, A1 is the monthly rate of increase of the
annuity. Again, that depends on how you choose to interpret the
annual rate of 3%: monthly increase is 3%/12; or monthly increase is
(1+3%)^(1/12)-1 or RATE(12,0,-1,1+3%). Again, I would choose the
latter, if I believed the annuity payment changes monthly. (I don't.)

ROW(A1:A30) and ROW(A1:A360) are simply ways to generate the arrays
{1,2,...,30} and {1,2,...,360} respectively. The cell contents are
irrelevant.

If you understand present value calculations, hopefully the formulas
are intuitive -- at least the first formula. The first formula is
simply the sum of the PV of each monthly payment. The second formula
is the sum of the PV of each constant payment over 12 months, rolled
forward (PV) monthly for each year (multiple of 12 months).

It should be noted that both formulas make the assumption that the
present value is invested one month before the first annuity payment.

HTH.