What regular savings, increasing every year to reach goal

[flossy129]

I need to find a formula (not using Goal Seek or Solver) to find wha
regular monthly payments need to be made to achieve a target amoun
where the payments increase every year by a fixed %. The know
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 1s
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 b
helpful to know the amount of the final payment. This is to calculat
how much someone needs to start saving into a pension now to achiev
their retirement goal where payments are linked to salary which it i
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 ver
much appreciated.

Flossy12

 

[joeu2004]

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.

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.

 

[flossy129]

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 initia
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 yiel

when compounded monthly (the payment frequency). In other words, the
monthly growth rate is (1+4%)^(1/12)-1.

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 ag
[1].

I assume B1 contains the target amount (1,000,000), B2 contains th
number
of payments (151 = 12*12+7), B3 contains the annual payment increas
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 cas
where
B4=B3. And I have not explored the behavior and potential correctio
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 th
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 updat
later.

Thank you joeu2004.

Very, very, much appreciated.

I have tested this using a range of criteria including different annua
payment increase rates and annual fund growth rates and this seems t
work in every scenario. I also tested the formula against a manua
calculation and it works, albeit that the final balance (based on th
example) is 2.18% out, i.e. the final balance is £1,021,810. If using a
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
goal amount of exactly £1,000,000.

I cannot use Goal Seek as this needs to go into a protected spreadshee
that will be used by people who have no knowledge of Excel.

I cannot account for this difference however I am not that concerned a
the answer is close enough. Of course if this can be corrected tha
would also be much appreciated.

In my manual calculation the monthly payment is made at the beginning o
the month and one month’s interest is added at the end of the month. I
the next month the new payment is added and 1 months interest is adde
to the whole balance.

Calculating savings towards a goal is always a ‘best guess’ because i
reality it will never work out as planned so having a payment that is
little more than is theoretically needed is no great problem.

Really appreciate your help on this.

Regards

Flossy12

 

[joeu2004]

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

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.

 

[flossy129]

;1602824']"flossy129"

Caveat: The use of INDIRECT makes this a "volatile" formula. It wil
be
recalculated usually unnecessarily whenever Excel recalculates anythin
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,
would
suggest that you stick with INDIRECT as long as there are not many suc

formulas in the workbook.

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

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

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

Thank you again.

flossy12

 

[joeu2004]

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.

 

[Michael Marshall]

'joeu2004[_2_ Wrote:

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.

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,


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

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 thecalculations 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%

 

[joeu2004]

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.

 

[Michael Marshall]

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 havebeen the nominal rate instead

 

[Michael Marshall]

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

 

[Michael Marshall]

'joeu2004[_2_ Wrote:

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.

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,


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

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 RATEof 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 Exchequerwould 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 increaseby £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 aterminal 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/

 

[Michael Marshall]

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 wouldbe required to make an initial payment of £4585.14 for the fund to reacha 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 inthe 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