Quote:
Originally Posted by joeu2004[_2_]
"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 |
Thank you joeu2004.
Very, very, much appreciated.
I have tested this using a range of criteria including different annual payment increase rates and annual fund growth rates and this seems to work in every scenario. I also tested the formula against a manual calculation and it works, albeit that the final balance (based on the example) is 2.18% out, i.e. the final balance is £1,021,810. If using an annual growth rate of 10% the inaccuracy is 5.357%
Your formula provides an initial payment of £4,443.82 for the example.
Using the Goal Seek initial payment of £4,348.97 does in fact produce a goal amount of exactly £1,000,000.
I cannot use Goal Seek as this needs to go into a protected spreadsheet that will be used by people who have no knowledge of Excel.
I cannot account for this difference however I am not that concerned as the answer is close enough. Of course if this can be corrected that would also be much appreciated.
In my manual calculation the monthly payment is made at the beginning of the month and one month’s interest is added at the end of the month. In the next month the new payment is added and 1 months interest is added to the whole balance.
Calculating savings towards a goal is always a ‘best guess’ because in reality it will never work out as planned so having a payment that is a little more than is theoretically needed is no great problem.
Really appreciate your help on this.
Regards
Flossy129