2

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.

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

2 Answers 2

4

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.

enter image description here

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)

enter image description here

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.

Additional question

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

enter image description here

enter image description here

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

enter image description here

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}}

Addendum

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

enter image description here

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.

enter image description here

6
  • 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.
    – Hossein
    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. 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?
    – Hossein
    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? 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.
    – Hossein
    Jan 29, 2023 at 5:20
4

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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .