# Formula to calculate time needed for an investment to meet a goal

Given the following.

• Target (t): 100000
• Initial Capital (c): 2000
• Monthly Contribution (pmt): 1000
• Yearly contribution Growth Rate (k): 3
• Rate (r): 5
• C/Y (y): 1 (Compounded annually), 12 (Compounded Monthly)

I should calculate the time that needed in month for the future value of investment to meet the goal? Simply how long does it take to meet the goal?

It seems to be partially Constant Growth Annuity and partially Compound Interest. I need help to find a formula for calculating the time in month.

• Microsoft Excel will both do it for you and show you how it's done. Would that not be enough? Commented Jan 27, 2023 at 23:40

Building on my previous answer here

``````r is the monthly or quarterly interest rate
y is the number of years
m is the number of months or quarters per year
p is the initial regular deposit
x is the annual deposit percentage increase
c is the initial capital

fv = (p (1 + r) (-1 + (1 + r)^m) ((1 + r)^(m y) - (1 + x)^y))/
(r (-1 + (1 + r)^m - x)) + c (1 + r)^(m y)
``````

For 5% compounded annually the monthly rate `r = (1 + 0.05)^(1/12) - 1`

For 5% compounded monthly the monthly rate `r = 0.05/12`

See nominal rate calculation for clarification.

With the following values (compounded monthly rate)

``````r = 0.05/12
m = 12
p = 1000
x = 0.03
c = 2000
fv = 100000
``````

Solving for y : `y = 6.36822`

The target is reached in 6 years and 5 months.

Also showing the solution plotted.

Checking result for `y = 6` using formula and Excel.

``````fv = (p (1 + r) (-1 + (1 + r)^m) ((1 + r)^(m y) - (1 + x)^y))/
(r (-1 + (1 + r)^m - x)) + c (1 + r)^(m y) = 92988.93
``````

The compounded capital is listed separately in column H, i.e. `c (1 + r)^(m y)`

Note, probably due to the annual jumps in the monthly contribution only round year inputs exactly match the Excel figures, i.e for `y = 6 + 5/12`

``````fv = (p (1 + r) (-1 + (1 + r)^m) ((1 + r)^(m y) - (1 + x)^y))/
(r (-1 + (1 + r)^m - x)) + c (1 + r)^(m y) = 100936.25
``````

Also the first month's interest on the capital was missed. Excel cell J1 above should be 2008.333 and the formula should end `... + c (1 + r)^(m y + 1)`. The capital interest is correct in the version below.

Switching to monthly contribution increase of 0.5 %

``````fv = (p ((1 + r)^n - (1 + x)^n))/(r - x) + c (1 + r)^n
``````

where x is the monthly contribution percentage increase and n is the number of months.

The formula again cannot be expressed for n.

I will see about following up with Newton's method for solving this fast, computationally (without a 3rd party solver).

Newton-Raphson solution for n

We need to find a root for this formula with one unknown, n

``````(p ((1 + r)^n - (1 + x)^n))/(r - x) + c (1 + r)^n - fv = 0
``````

Using the algorithm here: https://en.wikipedia.org/wiki/Newton%27s_method#Code

This method requires the derivative of the formula. (The formula and its derivative will work for all the numerical variations of the calculation. Note `Log` is to base e.)

Quite what `n = 80.6977` means in this context is questionable. For sure you can know that the penultimate payment is the 80th.

``````r = 0.05/12; p = 1000; x = 0.005/12; c = 2000; n = 80;
fv = (p ((1 + r)^n - (1 + x)^n))/(r - x) + c (1 + r)^n = 98991.5145
``````

By the end of month 80 `fv = 98991.5145 (1 + r) = 99403.9792` and then the last contribution comes in and takes the balance over the target 100000. So what happens at `n = 80.6977` is a bit theoretical.

You could more easily find the penultimate month with a simple loop.

``````r = 0.05/12; p = 1000; x = 0.005/12; c = 2000; fv = 100000; n = 0;
While[c < fv,
u = {n++, c};
c *= (1 + r);
c += p;
p *= (1 + x)]

{u, {n, c}}
``````

{{80, 98991.5145}, {81, 100438.867}}

Adding a Newton-Raphson solution for the (corrected) annually increasing payment annuity.

The solution tells you the goal is reached in year 6. So adding a loop to find the penultimate and final month.

``````y = 6

fv = (p (1 + r) (-1 + (1 + r)^m) ((1 + r)^(m y) - (1 + x)^y))/
(r (-1 + (1 + r)^m - x)) + c (1 + r)^(m y + 1) = 93000.17

n = 0;
While[fv < 100000,
u = {n++, fv};
fv = (fv + p (1 + x)^y) (1 + r)]

{u, {n, fv}}
``````

{{4, 99386.06}, {5, 100999.2}}

In this case the goal is reached before the monthly payment is made. The case where the goal is reached or exceeded after the payment would also have to be handled.

• Thanks for your time. I really appreciate it. Can you solve this formula for y? I need to implement a software to calculate the time. I cannot do it manually and test numbers. Commented Jan 27, 2023 at 19:36
• I'm pretty certain it can't be expressed for y. For coding, maybe there's a way. Will see. Commented Jan 27, 2023 at 23:22
• Let's simplify the question. Please remove the year. I need a formula to calculate only how many month it may take to meet the goal. Let's assume that pmt growth monthly. let's say half percent per month. Would it help? Commented Jan 28, 2023 at 3:43
• @Hossein I've added to my answer. For programming you could use loops, but where's the fun in that? Commented Jan 28, 2023 at 14:28
• I don't know how many hours your worked on this but it deserve a big thanks dude! Really appreciated for your time and efforts. The fun is to calculate how far someone is from his / her goals. Let's imagine that someone wants to achieve a 10 million dollar goal in saving. Commented Jan 29, 2023 at 5:20

Do you need an exact formula or a very close approximation? Because a very close approximation would be (using TVM on a financial calculator):

Set=End

I/Y = 0.6677 or [(1+5/12)(1+3/12)-1]*100

PV = 2,000

PMT = 1,000

FV = -100,000

N=74.86 months

That actually gives you a PMT growth of about 3.04%/year assuming the PMT grows by 3%/12 monthly. If you wanted the growth of payments to be exactly 3% each year when accounting for the monthly growth (i.e. 1000 -> 1030 at the end of year 1), the I/Y calculation would be [(1+5/12)(1.03^(1/12))-1]*100 instead.

If you're looking for the monthly contributions to only grow once per year, that's going to be a wild formula. I'm not sure you can do anything except jump into Excel at that point.

• I need a formula to solve it by time. I need to use this formula in an application that I will write myself. Commented Jan 27, 2023 at 19:33