Financial function PMT

[nrlincoln]

PMT calculates the monthly or annual premium needed to arrive at a lump sum, given a certain interest rate and time period.
Does anyone know how to amend PMT so as the same lump sum is generated by a premium that escalates over the time period, at a rate chosen?
For instance, what monthly or annual premium (escalating at 5% per annum) is necessary to generate £100,000 over 25 years, assuming a 6% growth rate?

[Dave Patton]

[quote]
On 2002-05-24 04:31, nrlincoln wrote:
...how to amend PMT so as the same lump sum is generated by a premium that escalates over the time period, at a rate chosen?
For instance, what monthly or annual premium (escalating at 5% per annum) is necessary to generate £100,000 over 25 years, assuming a 6% growth rate?
______

One way is to create a schedule that shows the escalating payments and the interest on the accumulated amounts, with a few guesses, you could derive the initial payment amount.

You could use Solver to do the above.

[nrlincoln]

Thanks for this - but what is Solver?

[paulyf]

I'm equally poor at maths and VBA but I think the following will provide you with the schedule Dave refers to. Just sum cells B2:B26 to get the return on £1 invested @ time0.

If it's hideously wrong - don't shout at me!!! Only trying to (learn and) help.

Cheers - Paul


Sub ()
Dim incpremium As Double
Dim incgrowth As Double
Dim n As Variant
Dim f As Variant
For n = 0 To 24
f = 25 - n
incpremium = Application.WorksheetFunction.Power(1 * 1.05, n)
Sheet1.Cells(n + 2, 1).Value = incpremium
incgrowth = Application.WorksheetFunction.Power(1 * 1.06, f)
Sheet1.Cells(1, f + 1).Value = incgrowth
inctotal = incpremium * incgrowth
Sheet1.Cells(n + 2, 2).Value = inctotal
Next n
End Sub

[Seti]

For annual calculations you could try the following:

Cell A1: 100,000 (target Present Value)
Cell B1: .05 (annual growth rate of payment)
Cell C1: .06 (annual interest rate for discounting)
Cell D1: 25 (number of years to make payments)
Cell E1: this is the cell that will calculate the annual payment at time 0

=A1/((1-((1+A2)/(1+A3))^A4)/(A3-A2)).

If you really need monthly, it gets much uglier and you could try goal seek. If you need more help, post back.

Seti

[paulyf]

Did you want a lump sum of £100,000, or a lump sum with a NPV of £100,000?

Cheers - Paul

[nrlincoln]

A lump sum of £100,000.

Thanks.


On 2002-05-24 08:52, paulyf wrote:
Did you want a lump sum of £100,000, or a lump sum with a NPV of £100,000?

Cheers - Paul
[/quote]

[Seti]

Sorry,

My formula give an NPV of 100,000. I got confused by your original question. Upon rereading it, you appear to ask for a function similar to PMT which generates an NPV type number. Could you be a little more specific?

Are you really looking for a formula to compute the time 0 payment such that the summ of all payments (growing at 5%) is equal to 100,000, or are you looking for a time 0 payment such that at 6%, the NPV is 100,000?

Seti

[nrlincoln]

I am looking for both combined!
In 25 years time, I want a lump sum of 100,000. No revaluation of this amount or anything like that - just a plain 100,000.
I want to assume that the premiums (annual or monthly) will acheive investment growth of 6% per annum over this period.
I am disconcerted by how much this is going to cost me, so I decide to start the premiums at a lower level and increase them each year, say, by 5%. In other words, relatively in line with my earnings.
I have put this problem in basic terms, as I get rapidly confused by the NPVs etc of this world!
Thanks everyone for your ongoing help with this.

[paulyf]

I think my calculations hold up, albeit there must be a simpler formula based solution.

If investing £1 at time0 (and incremented @ 5% thereafter on an annual basis) results in a return of c£95.98, the first sum invested would be £1,041.83 (??) to return a lump sum of £100,000.

Does this seem in line with your expectations?

I'm now off for the weekend, so don't be offended by lack of response.

Cheers - Paul