"flossy129" wrote:
I need to find a formula (not using Goal Seek or Solver)
to find what regular monthly payments need to be made to
achieve a target amount where the payments increase every
year by a fixed %.
The known information - as an example only - is as follows:
Target Amount: £1,000,000
Term: 12 years 7 months
Payment frequency: Monthly (but could also be annual)
Payments to Increase: Annually (assume on anniversary of 1st
payment)
Payments increase by: 3% p.a.
Annual Growth Rate: 4% p.a. compound

Why not use Goal Seek or Solver?

For the terms above, I used Goal Seek to determine that the initial payment
is about 4348.97, and the last payment is about 6200.59.

Note: I assume that the investment growth rate of 4% is an annual yield
when compounded monthly (the payment frequency). In other words, the
monthly growth rate is (1+4%)^(1/12)-1.


"flossy129" wrote:
I have searched high and low for this solution so any help
will be very much appreciated.

I adapted the following formula based on something I found a year ago [1].

I assume B1 contains the target amount (1,000,000), B2 contains the number
of payments (151 = 12*12+7), B3 contains the annual payment increase rate
(3%), B4 contains the annual investment growth rate (4%).

Then the initial payment can be calculated as follows (in B5):

=IF(B4<B3, B1*(B4-B3)/((1+B4)^(B2/12)-(1+B3)^(B2/12))/12,
B1/B2/(1+B4)^(B2/12-1))

Caveat: I have not vetted the value-if-false expression for the case where
B4=B3. And I have not explored the behavior and potential correction when
B4<B3 is true.

The final payment can be calculated as follows: =B5*(1+B3)^INT(B2/12).

For your terms above, the initial payment is about 4443.82, and the final
payment is about 6335.83.

Those figures are more than Goal Seek result in part because the first
formula compounds the investment annually instead of monthly.

PS: I am exploring different formulas. WIP. I might post an update later.


-----
[1]
http://ask.metafilter.com/19455/Exce...ing-by-a-fixed