Hello,

I new to posts so bare with me.

I am having a problem with a certain financial equation.

Here is the guts of the info
I have a present value of a stream of payments (1,029,498).

The benefit is 100,000 payable for 15 years annually. Payable at the
begging of the period at a rate of 6%.

I have 10 years to fund for this benefit earning 7%. which gives me a
level payment of 69,638 a year.

I then figured out how to tie this to compensation with an expected
salary increase rate of 4%. So the funding would grow with his comp.

First contribution for funding purposes is 63,431 with the last payment
being 90,282. (simple Pmt function with an effective rate (1.07/1.04)
and 10 year time limit) then discounted back 9 years at 4%.

This much I know. I did this using the PV and PMT functions in excel.

Now the question.

I would like to back load the funding contributions. For example If the
person's compensation for the first year is $634,310. He would have to
allocate 10% of comp to fund the benefit each year for 10 years. I want
to say 5% for the first couple years and 15% for the last couple. I
would love to use different variations but am unsure how to keep the
presnt value of my stream consistant. I think excel has a function but
can figure it out.

This may be a bit much but if there is anyone out there that can
understand my scenario and help me that would be great.

Thanks
Frank


--
fcastrofilippo
------------------------------------------------------------------------
fcastrofilippo's Profile: http://www.excelforum.com/member.php...o&userid=36542
View this thread: http://www.excelforum.com/showthread...hreadid=562960

Here's what I would do, although it may not be the most effective:

Your constant number is that you must accumulate $1,029,498 after 10 years.

I would set up a 10-year table, calculate the future value of each payment, and
sum the 10 FVs. That way you can play around with each payment, and see how
close you get to the required amount. If you want to increase the payments by
something other than 4%, you can easily put the rate in a variable somewhere,
have a formula to calculate the payments, then use Goal Seek to vary the
increase factor until you get the desired FV.

Hope this helps.

--
Regards,
Fred


"fcastrofilippo"
wrote in message
news:fcastrofilippo.2b7ca5_1153328435.331@excelfor um-nospam.com...

Hello,

I new to posts so bare with me.

I am having a problem with a certain financial equation.

Here is the guts of the info
I have a present value of a stream of payments (1,029,498).

The benefit is 100,000 payable for 15 years annually. Payable at the
begging of the period at a rate of 6%.

I have 10 years to fund for this benefit earning 7%. which gives me a
level payment of 69,638 a year.

I then figured out how to tie this to compensation with an expected
salary increase rate of 4%. So the funding would grow with his comp.

First contribution for funding purposes is 63,431 with the last payment
being 90,282. (simple Pmt function with an effective rate (1.07/1.04)
and 10 year time limit) then discounted back 9 years at 4%.

This much I know. I did this using the PV and PMT functions in excel.

Now the question.

I would like to back load the funding contributions. For example If the
person's compensation for the first year is $634,310. He would have to
allocate 10% of comp to fund the benefit each year for 10 years. I want
to say 5% for the first couple years and 15% for the last couple. I
would love to use different variations but am unsure how to keep the
presnt value of my stream consistant. I think excel has a function but
can figure it out.

This may be a bit much but if there is anyone out there that can
understand my scenario and help me that would be great.

Thanks
Frank


--
fcastrofilippo
------------------------------------------------------------------------
fcastrofilippo's Profile:
http://www.excelforum.com/member.php...o&userid=36542
View this thread: http://www.excelforum.com/showthread...hreadid=562960