I want to calculate the number of years required before an investment reach a certain value assuming increased payments.

Ignoring the increase of payments, I can calculate using NPER (Google Sheets, Excel).

For example, given 179,936.93 as a starting investment, 57,660 as yearly payment, investment return of 7%: the number of years required to reach a future value of 2,201,322 is:

NPER(0.07, 57660,-179936.93, -2201322) =~ 22.85 years, hence 23 years.

How can I calculate the required years if the yearly payment is increased by a constant % for each year ?


Unfortunately money.SE doesn't support MathJax like math.SE does, but I'll try to explain anyway. Consider the level payment scenario first. Let K be the starting investment amount, L be the annual level payment (in arrears--i.e., occurring at the end of each year), and AV be the desired accumulated value. Suppose further that interest accrues at an effective annual interest rate of i%. Then the equation of value is

K(1+i)^n + L((1+i)^n - 1)/i = AV,

and the goal is to solve this equation for n. This gives us

n = (log(L + i AV) - log(L + i K))/log(1 + i).

For your case, this gives n = 16.3 years, not 23 years as your calculation shows. The reason is because your choice of signs is incorrect: The correct syntax should be:

NPER(i, -L, -K, AV)

because you have K already in savings, you are contributing L to the fund, and the accumulated (future) value of this is positive at the end of the term. If the third argument is positive, this represents an outstanding balance on a loan, rather than an amount already invested; put another way, in your investment scheme, the fund is paying you the interest, not the other way around, so the second and third arguments must be negated.

You can also confirm by doing a simple calculation with i = 10%, and payments of L = 10 per year added to an initial investment of K = 100. At the end of 2 years, you should have

 AV = 100(1.1)^2 + 10(1.1) + 10 = 142.

But if you use NPER(i, L, -K, -AV), you get an error.

Now let's look at the non-level payment scenario. In this case, suppose that the regular payments into the investment fund increase by j% per year. That is to say, the first payment is L, the second is L(1+j), the third is L(1+j)^2, and so on. Then it becomes clear that the annuity portion of the cash flow has accumulated value

 L(1+i)^(n-1) + L(1+j)(1+i)^(n-2) + L(1+j)^2 (1+i)^(n-3) + ...

that is to say, we can pull out a common factor of L(1+j)^(n-1), and define a new effective rate r = ((1+i)/(1+j)) - 1:

 L(1+j)^(n-1) ((1+r)^(n-1) + (1+r)^(n-2) + ... + (1+r) + 1).

Hence the equation of value is

 K(1+i)^n + L(1+j)^(n-1) ((1+r)^n - 1)/r = AV

or equivalently,

 K(1+i)^n + L((1+i)^n - (1+j)^n)/(i-j) = AV,     j not equal to i,
 K(1+i)^n + n L (1+i)^(n-1) = AV,                j = i.

It is worth mentioning that in this second form, we find that we easily recover the level payments scenario formula by setting j = 0.

Unfortunately, an exact closed-form solution for this equation for n is not possible; we can use numeric methods to obtain the solution for certain choices of K, L, i, AV, and j. For your specific choices, I am getting the following values of n for various choices of j:

 j = 0.01:  n = 15.8139
 j = 0.02:  n = 15.3405
 j = 0.03:  n = 14.8875
 j = 0.04:  n = 14.4557
 j = 0.05:  n = 14.0452
 j = 0.06:  n = 13.6557
 j = 0.07:  n = 13.2866
 j = 0.08:  n = 12.9374
 j = 0.09:  n = 12.6069
 j = 0.10:  n = 12.2944
 j = 0.15:  n = 10.9653
 j = 0.20:  n = 9.94298
 j = 0.25:  n = 9.13903
 j = 0.30:  n = 8.49240
 j = 0.35:  n = 7.96161
 j = 0.40:  n = 7.51813.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.