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

I obviously need to know the starting payment but it would also be helpful to know the amount of the final payment. This is to calculate how much someone needs to start saving into a pension now to achieve their retirement goal where payments are linked to salary which it is assumed will increase at 3% p.a. The whole fund will grow at say 4% p.a.

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

Flossy129

"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

"flossy129" wrote:
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 account for this difference

As I explained previously, a large part of the difference is due to
different compounding assumptions.

With the Goal Seek model, we are compounding interest monthly after each
payment.

With the formula B1*(B4-B3)/((1+B4)^(B2/12)-(1+B3)^(B2/12))/12, we are
compounding interest annually.

However, I have learned that there is another factor contributing to the
inaccuracy of the formula. Apparently, it works (best) if the number of
monthly payments is a multiple of 12; that is, B2/12 is an integer. That is
not the case with your example of 12y 7m (151 payments).


"flossy129" wrote:
I cannot account for this difference [...]. if this
can be corrected that would also be much appreciated.

Hold onto your hat.... :-)

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%).

Also, I assume that C4 contains the monthly investment growth rate, namely:
(1+B4)^(1/12)-1.

And I sassume that C2 contains the number of complete years, namely:
=INT(B2/12).

And like you, I assume: "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".

Then the following formula computes the initial payment (in C5):

=B1 / (IF(B2=12, SUMPRODUCT((1+B3)^(ROW(INDIRECT("1:" & C2))-1)
*(((1+C4)^13-1)/C4-1)*(1+C4)^(B2-12*ROW(INDIRECT("1:" & C2)))))
+ IF(MOD(B2,12)0,(1+B3)^C2*(((1+C4)^(MOD(B2,12)+1)-1)/C4-1)))

The last payment is: =C5*(1+B3)^C2.

That is not an elegant formula. But it does seem to work.

Caveat: The use of INDIRECT makes this a "volatile" formula. It will be
recalculated usually unnecessarily whenever Excel recalculates anything in
the Excel workbook; for example, whenever any cell in any worksheet is
modified. INDIRECT could be avoided by the judicious use of INDEX, which is
not "volatile". However, that has its downside as well. For now, I would
suggest that you stick with INDIRECT as long as there are not many such
formulas in the workbook.

I am awed by your solution, inelegant or otherwise (sadly not that I would know an inelegant formula). It works.

The spreadsheet it is being used in has many formulas - mostly simple IF or VLOOKUP functions - but it seems to work perfectly.

I cannot express how grateful I am to you for the solution and your perseverance.

Thank you again.

flossy129

I wrote:
Then the following formula computes the initial payment (in C5):

=B1 / (IF(B2=12, SUMPRODUCT((1+B3)^(ROW(INDIRECT("1:" & C2))-1)
*(((1+C4)^13-1)/C4-1)*(1+C4)^(B2-12*ROW(INDIRECT("1:" & C2)))))
+ IF(MOD(B2,12)0,(1+B3)^C2*(((1+C4)^(MOD(B2,12)+1)-1)/C4-1)))

The last payment is: =C5*(1+B3)^C2.

Combining and rearranging some terms, the formula in C5 can be simplified
somewhat as follows:

=B1 / (IF(B2=12, SUMPRODUCT((1+B3)^(ROW(INDIRECT("1:" & C2))-1)
*(1+C4)^(B2-12*ROW(INDIRECT("1:" & C2))+1))*((1+C4)^12-1)/C4)
+ IF(MOD(B2,12)0, (1+B3)^C2*(1+C4)*((1+C4)^MOD(B2,12)-1)/C4))

It more-closely follows the formula in the Excel PV help page, so it might
be easier to understand.

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

I put your data through Excel PMT function http://tadxl.com/excel_pmt_function.html that is part of TADXL add-in http://tadxl.com/ and I noticed that I get a payment amount that is slightly different the one you stated as £4,348.97 (Goal Seek)

Are you sure you didn't make a typo because I am getting an initial amount of £4328.97 (difference of £20 exactly from your amount). I checked the calculations manually through a long table of interest factors and my results show that an initial payment of £4328.97 is required to reach a terminal amount of £1,000,000 in 12 years and 7 months when the interest of 4% is compounded monthly and payment amount grows by 3% every 12 months. Assuming that payments are made at the start of each month.

Here is how I calculated the initial payment using Excel tadPMT function with the following items as input

RATE: 4%
GRADIENT: 3%
TAXRATE: 0%
NPER: 12*12+7
PV: 0
FV: 1000000
TYPE: 1
GTYPE: 0
COMPOUNDING: 1/12
PERIOD: 1/12
DISTRIBUTION: 1
GPERIOD: 12

And here is the call to tadPMT function itself shown as

=tadPMT ( 4%, 3%, 0%, 12*12+7, 0, 1000000, 1, 0, 1/12, 1/12, 1, 12 )

It gives a start of period payment in amount of £4328.97 that earns an interest of 4% compounded monthly and inflates at the end of every 12 months at a rate of 3%

"Michael Marshall" wrote:
joeu2004 Wrote:
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.
[....]
Are you sure you didn't make a typo because I am getting an initial amount
of £4328.97

No typo. The difference is explained in the footnote above.

Since I assume the investment growth rate is an annual yield (compounded
rate), the monthly growth rate is (1+4%)^(1/12)-1.

Apparently, "you" (tadPMT from tadxl.com) use a monthly growth rate of
4%/12.

The latter is incorrect because it results in an annual yield of about
4.0742% = (1+4%/12)^12-1.

On Thursday, April 4, 2013 2:19:41 PM UTC-5, joeu2004 wrote:
"Michael Marshall" wrote:

joeu2004 Wrote:

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.

[....]

Are you sure you didn't make a typo because I am getting an initial amount

of �4328.97



No typo. The difference is explained in the footnote above.



Since I assume the investment growth rate is an annual yield (compounded

rate), the monthly growth rate is (1+4%)^(1/12)-1.



Apparently, "you" (tadPMT from tadxl.com) use a monthly growth rate of

4%/12.



The latter is incorrect because it results in an annual yield of about

4.0742% = (1+4%/12)^12-1.

One has to use annualized yield in calculating interest when a nominal annual rate is given.

So it all depends on your interpretation of the annual rate and in this case you assumed that 4% (in the example) is the annual yield when it may have been the nominal rate instead

On Thursday, April 4, 2013 2:19:41 PM UTC-5, joeu2004 wrote:
"Michael Marshall" wrote:

joeu2004 Wrote:

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.

[....]

Are you sure you didn't make a typo because I am getting an initial amount

of �4328.97



No typo. The difference is explained in the footnote above.



Since I assume the investment growth rate is an annual yield (compounded

rate), the monthly growth rate is (1+4%)^(1/12)-1.



Apparently, "you" (tadPMT from tadxl.com) use a monthly growth rate of

4%/12.



The latter is incorrect because it results in an annual yield of about

4.0742% = (1+4%/12)^12-1.

tadPMT and other TVM functions in TADXL accept a nominal rate that is then turned into an effective annual yield depending on compounding periods.

If 4% was to be the annual effective yield, then you would have to get the nominal rate to be used in tadPMT function

And now that I use tadNOMINAL(4%, 1/12) to get the nominal rate, tadPMT reports a monthly payment of £4,348.97

=tadPMT ( tadNOMINAL(4%, 1/12) , 3%, 0%, 12*12+7, 0, 1000000, 1, 0, 1/12, 1/12, 1, 12 )

Gives a monthly payment of £4,348.97

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/

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/

-------------------------

Before you get confused with the wrong values for COMPOUNDING and PERIOD in the last example that I copy/pasted in my NotePad file

The actual values for COMPOUNDING and PERIOD in the last example are 1/12 and the wrong numbers of 12*12+7 were the work of Ctrl+C Ctrl+V

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: 1/12
PERIOD: 1/12
DISTRIBUTION: 1
GPERIOD: 12