Trouble with payments

[fcastrofilippo]

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

[Fred Smith]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>