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