On Saturday, June 16, 2012 7:07:03 AM UTC-4, flossy129 wrote:
'joeu2004[_2_ Wrote:
;1602733']"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://tinyurl.com/8yx7g86
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
--
flossy129
You know the popular phrase "There are two things for certain; TAXES and DEATH"
And Uncle Sam would like to have his share of the interest that your investment earns
And if the taxes on bank savings is 10% of the gross amount then you would probably have to make higher deposits each month to reach £1,000,000 in 12 years and 7 months
Here we will once again use tadPMT but this time we will pass it a TAX RATE of 10% and ask it to tell us the initial payment required to reach £1,000,000 in 12 years and 7 months where our deposits earn an annual effective yield of 4% and inflate at 3% per annum and the Chancellor of the Exchequer would want his 10% of the earnings
RATE: tadNOMNAL( 4%, 1/12 )
GRADIENT: 3%
TAXRATE: 10%
NPER: 151
PV: 0
FV: £1,000,000
TYPE: 1
GTYPE: 0
COMPOUNDING: 1/12
PERIOD: 1/12
DISTRIBUTION: 1
GPERIOD: 12
=tadPMT ( tadNOMINAL( 4%, 1/12 ), 3%, 10%, 12*12+7, 0, 1000000, 1, 0, 1/12, 1/12, 1, 12 )
PMT: £-4459.98
Now that you have to pay 10% tax, a monthly payment of £4459.98 will be required compare this to a monthly payment of £4348.97 when you got away without paying taxes
Now say that instead of a percentage inflation, your deposits will increase by £100 every 12 months. So from month 13 to 24 monthly payments will drive up by a £100 and from month 25 to 36 the payments will go up by £200 and so on
We will now provide tadPMT with a money amount of £100 as a GRADIENT rather than the 3% percentage rate we use in our last example and we will tell Excel that this time we have a scalar GRADIENT by placing a value of 1 in variable called GTYPE
RATE: tadNOMINAL( 4%, 1/12 )
GRADIENT: £(100.00)
TAXRATE: 0%
NPER: 12*12+7
PV: 0
FV: £1,000,000
TYPE: 1
GTYPE: 1
COMPOUNDING: 12*12+7
PERIOD: 12*12+7
DISTRIBUTION: 1
GPERIOD: 12
=tadPMT ( tadNOMINAL( 4%, 1/12 ), -100, 0%, 12*12+7, 0, 1000000, 1, 1, 1/12, 1/12, 1, 12 )
PMT: £-4585.14
Thus, if the deposits were to grow by £100 each twelve months you would be required to make an initial payment of £4585.14 for the fund to reach a terminal value of £1,000,000
Bet Excel doesn't have any functions like tadPMT and the other 5 TVM functions of TADXL Excel add-in
http://tadxl.com/