Math challenge! Looking to solve a compound interest problem.

[hawaean]

I'll need a math wiz to help me with solving this question. I'd like to input this in Excel, so please make the solution formula-friendly.

How long will it take to turn investing P into A, via dividend growth stocks?

I know most of the calcs out there help people figure out what A would be if they invested P in a certain scenario. I'd like to know given all the same variables, how long it would take to take, say $10,000 and make it $500,000.

Additionally, I'd like to account for a few other factors:
1) regular contributions
2) a projected dividend growth rate (so not only the current interest rate, but a growing interest rate, which is a common metric of dividend analytics)
3) the growth rate of the stock itself (which contributes to the overall value of A)

Here's my starting point:

	A = P*(1+r/n)^(n*t)
A	final amount
P	initial principal balance
r	dividend/interest rate
n	number of times interest applied per time period
t	number of time periods elapsed

This was an attempt to solve for (t)

a = A/P

b = (1+r/n)

a = b^(n*t)

log(a) = log(b^(n*t))

log(a) = n*t

ln(a)/ln(b) = n*t

(ln(a)/ln(b))/n

(ln(A/P)/ln(1+r/n))/n

This was an attempt to solve for (t) with contributions. The extra variables were just to make my life easier.

A = P*(1+r/n)^(n*t) + PMT((1+r/n)^(n*t)-1)/(r/n)

a =(r/n)

b = (1+r/n)

c = nt

A = P*b^c + PMT(b^c-1)/a

A - P*b^c = PMT(b^c -1)/a

(A-P*b^c)*a = PMT*(b^c - 1)

A*a - a*P*(b^c) = PMT*(b^c) - PMT

A*a+PMT = PMT*(b^c) + a*P*(b^c)

A*a+PMT = (b^c) * (PMT+a*P)

(A*a+PMT)/(PMT+a*P) = (b^c)

log((A*a+PMT)/(PMT+a*P)) = log(b^c)

log((A*a+PMT)/(PMT+a*P)) / log(b) = c

ln((A*a+PMT)/(PMT+a*P)) / ln(b) = c

ln((A*r/n+PMT)/(PMT+P*r/n)) / ln(1+r/n) = c

ln((A*r/n+PMT)/(PMT+P*r/n)) / (n*ln(1+r/n)) = t

Even if I did this correctly, it only figures out the time needed based on a consistent compounding interest. This doesn't factor in growth in the interest rate or growth of the principal over time.

I don't mind if this is broken into multiple formulas, especially if it helps make the calculations make more sense.

I know what I'm asking for is complicated. However, I think this is also a very reasonable question to ask and I don't see any resources that tackle this at all. If there's already a site that has this setup, please let me know.

 

[Cubist]

Check out NPER function.

 

[hawaean]

Check out NPER function.

Thanks for the reply Cubist.

NPER seems to be a good start and does calculate the time given a start and end value. Could NPER still be useful to factor an increasing principal value, interest rate (dividend growth) and extra principal payments? The NPER payment value seems to factor an interest/principal payment, which is not what we want in this situation.

Maybe a example would help.

I have $10,000 that I use to purchase Stock X.

I want to know how long it will take to grow this single portfolio to $500,000 given the following:

1) Stock X average growth per year is 5%
2) Stock X gives an annual dividend of 3%
3) Stock X grows its dividend payout on average by 10% annually
4) I make monthly investments of $1,000 of Stock X

How long will it take my initial $10,000 to reach $500,000

Ideally, I would like to plug variables in an excel sheet to play around with the end value, monthly contributions and the various growth rates.

 

[Cubist]

1) Stock X average growth per year is 5% - This is the same as interest.
2) Stock X gives an annual dividend of 3% - 3% of what?
3) Stock X grows its dividend payout on average by 10% annually- You said fixed 3% in bullet 2. Here you say it increases 10% annually. Can you clarify?
4) I make monthly investments of $1,000 of Stock X -This is the PMT field

 

[KRice]

Regarding the dividend...presumably the annual dividend is about 3% of the then-current value of your stock X holdings. And moving forward, the dividend factor increases by about 10% per year...so the following year would be about 3.3%, then 3.63%... So what do you do with the dividend?...reinvest it immediately back into Stock X, or pocket it? If the latter, then that cash flow has no bearing on the value growth of Stock X.

 

[hawaean]

1) Stock X average growth per year is 5% - This is the same as interest.
2) Stock X gives an annual dividend of 3% - 3% of what?
3) Stock X grows its dividend payout on average by 10% annually- You said fixed 3% in bullet 2. Here you say it increases 10% annually. Can you clarify?
4) I make monthly investments of $1,000 of Stock X -This is the PMT field

1) I think I need to add a clarifying point, which is a real-world factor. The growth and dividend (interest) would need its own period. So, in this example, let's say the growth applies monthly, but the dividend only happens quarterly. While I understand your point about the growth = interest, each should have an independent timeframe.
2) The dividend payouts are not technically tied to any metric. It's a company decision. That being said, investors typically quantify this with company performance, like earnings, or stock price. For simplicity, let's base it on the stock price. In our example, the initial dividend payment = $75/quarter

To further illustrate, here's what I think 6 months would looks like:

Month	Principal	Growth (5%/12)	Dividend Payment (reinvested)	Contributions
1	$10,000	$41.67	$75	$1,000
2	$11,117	$46.32	-	$1,000
3	$12,163	$50.68	-	$1,000
4	$13,214	$55.06	$99.10	$1,000
5	$14,368	$59.87	-	$1,000
6	$15,428	$64.28	-	$1,000

3) The dividend growth is on the dividend rate (which I never said was fixed). So, it's the 3% that would go up by 10% in 12 months, or 3.3%. And so forth. To me, this compounding rate seems like a complicated variable. However, a dividend growth rate is an extremely important metric and would be important to include.
4) Fair enough. I just wanted to make sure all variables are accounted for in this scenario.

 

[hawaean]

Regarding the dividend...presumably the annual dividend is about 3% of the then-current value of your stock X holdings. And moving forward, the dividend factor increases by about 10% per year...so the following year would be about 3.3%, then 3.63%... So what do you do with the dividend?...reinvest it immediately back into Stock X, or pocket it? If the latter, then that cash flow has no bearing on the value growth of Stock X.

You are correct. I should have stated the dividends would be reinvested. On the excel sheet, I think it should be a toggle to provide the option of reinvestment or not.

 

[Cubist]

1) I think I need to add a clarifying point, which is a real-world factor. The growth and dividend (interest) would need its own period. So, in this example, let's say the growth applies monthly, but the dividend only happens quarterly. While I understand your point about the growth = interest, each should have an independent timeframe.
2) The dividend payouts are not technically tied to any metric. It's a company decision. That being said, investors typically quantify this with company performance, like earnings, or stock price. For simplicity, let's base it on the stock price. In our example, the initial dividend payment = $75/quarter

To further illustrate, here's what I think 6 months would looks like:

Month	Principal	Growth (5%/12)	Dividend Payment (reinvested)	Contributions
1	$10,000	$41.67	$75	$1,000
2	$11,117	$46.32	-	$1,000
3	$12,163	$50.68	-	$1,000
4	$13,214	$55.06	$99.10	$1,000
5	$14,368	$59.87	-	$1,000
6	$15,428	$64.28	-	$1,000

3) The dividend growth is on the dividend rate (which I never said was fixed). So, it's the 3% that would go up by 10% in 12 months, or 3.3%. And so forth. To me, this compounding rate seems like a complicated variable. However, a dividend growth rate is an extremely important metric and would be important to include.
4) Fair enough. I just wanted to make sure all variables are accounted for in this scenario.

I get the first dividend payment is 3% of 10,000. How did you get $99.10 for the next dividend payment?

 

[hawaean]

I get the first dividend payment is 3% of 10,000. How did you get $99.10 for the next dividend payment?

I took 3% x Principal ($13,214) / 4

 

[hawaean]

I took 3% x Principal ($13,214) / 4

Sorry, I just googled dividend increases. Typically, companies will do annual increases. So, the argument could be made that all 4 dividend payments for the year are based on the initial $10k, or $75 payments. I think that's perfectly fine.