Excel - What regular savings, increasing every year to reach goal

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

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 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/Excel-Wizards-How-do-I-get-the-Future-Value-if-the-payments-are-growing-by-a-fixed

'joeu2004[_2_ Wrote:

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

[....]

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



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.

'joeu2004[_2_ Wrote:

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




--
flossy129

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.