Math challenge! Looking to solve a compound interest problem.

hawaean

New Member
Joined
Aug 25, 2016
Messages
32
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)
Afinal amount
Pinitial principal balance
rdividend/interest rate
nnumber of times interest applied per time period
tnumber 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.
 
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)
Afinal amount
Pinitial principal balance
rdividend/interest rate
nnumber of times interest applied per time period
tnumber 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.

Even if we're not able to solve my riddle, can someone let me know if I correctly solved for (t)? It gets a little iffy with the log functions (and making sure it's still Excel friendly)
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top