# Thread: calculating compound interest with non-linear interest growth

1. ## calculating compound interest with non-linear interest growth

Hello, I would really appreciate some help with the following problem.

I'd like to derive a formula to determine the value of an investment where the annual yield increases by a fixed percentage each year. The interest paid is re-invested each year. The standard compounding formulas use a fixed yield so I've not been able to use them in this case.

As an example, suppose I have 1000 dollars in an investment with an initial starting yield (y) of 3% which grows (g) at 5% each year.

End of Year 1 : Yield was 3.00%, Investment value = 1000 + (1000 * 3%) = 1030.00
End of Year 2 : Yield was 3.15% (3% increased by 5%), Investment value = 1030 + (1030 x 3.15%) = 1062.45
End of Year 3 : Yield was 3.31% (3.15% increased by 5%), Investment value = 1062.45 + (1062.45 x 3.31%) = 1097.59

I believe the calculation is a series in the form

Value = P.(1+y0).(1+y1).(1+y2).(...)

where P = initial investment amount, r0 = yield in year 0, r1 = yield in year 1, r2 = yield in year 2 etc.
and where y0 = y.(1+g)^t etc.

If I write the above formula out substituting y0, y1, etc. I get

Value = P.(1+y(1+g)^t).(1+y(1+g)^(t-1)).(1+y(1+g)^(t-2)).(...) where t ranges from 0 to the target year n.

Is it possible to convert this expression into a general formula in the form of Value = f(t) where t = time in years, e.g. some kind of exponential function and how might I go about doing that?

Thank you for any help or insight that you can provide me!

~ Trevor

2. ## Re: calculating compound interest with non-linear interest growth

One way of doing this would be to find a single yield that results in a same future value using the series of yields

$\displaystyle FV = R (1+i)^n$

$\displaystyle ln(1+i_{s})=\frac{1}{n}\sum_{k\ =\ 0}^{n-1} ln(1+r(1+g)^k)$

$\displaystyle i=e^{ln(1+i_s)}-1$

Code:
R=1000
r=3%
g=5%
n=3
$\displaystyle ln(1+i_{s})=0.031037549112832$

$\displaystyle i=e^{0.031037549112832}-1$

$\displaystyle i=3.15242359792658\%$

$\displaystyle FV = 1000 (1+3.15242359792658\%)^3$

$\displaystyle FV = 1000 (1.0315242359792658)^3$

$\displaystyle FV = 1000 (1.097585368)$

$\displaystyle FV = 1,097.59$

3. ## Re: calculating compound interest with non-linear interest growth

Hi GhostAccount,

Thank you for the fast response, that was very helpful! I've checked the results manually using Excel and your method generates all FV values that I expect from n=1 to n=10. There is one point that I don't fully understand however.

I understand that your answer is solving the formula below for i :

$\displaystyle R (1+i)^n = R\sum_{k\ =\ 0}^{n-1} (1+r(1+g)^k)$

This tells me that I should be able to calculate any given FV for a value of n by manually summing the rhs. However I don't get the expected answer when I tried to do this in Excel:

n | Expected vs Excel
1 | 1030.00 vs 1030.00 ( = 1000 + 30 )
2 | 1062.45 vs 1061.50 ( = 1000 + 30 + 31.5 )
3 | 1097.59 vs 1094.58 ( = 1000 + 30 + 31.5 + 33.075 )
4 | 1135.70 vs 1129.30 ( = 1000 + 30 + 31.5 + 33.075 + 34.7288 )

Did I interpret the FV summation formula correctly and just get Excel wrong?

I didn't realize that the compound growth + compound interest could be expressed as a summation like that - I thought this was only calculating the amount of interest that $1000 would produce in n years with a compounded rate and not compounding the principal amount as well. I was thinking that$\displaystyle FV=R(1+r(1+g)^0)(1+r(1+g)^1)(1+r(1+g)^2)$etc A summation formula is what I really want to reach as I can then try to substitute a general identity for it and derive a formula in terms of r,g and n that I can use to calculate each expected value for a given t, for example using$\displaystyle \sum_{i\ =\ m}^{n-1} a^i = \frac{a^m - a^n}{1-a}$Thanks again for your help! 4. ## Re: calculating compound interest with non-linear interest growth Originally Posted by tw35758 Hi GhostAccount, Thank you for the fast response, that was very helpful! I've checked the results manually using Excel and your method generates all FV values that I expect from n=1 to n=10. There is one point that I don't fully understand however. I understand that your answer is solving the formula below for i :$\displaystyle R (1+i)^n = R\sum_{k\ =\ 0}^{n-1} (1+r(1+g)^k)$This tells me that I should be able to calculate any given FV for a value of n by manually summing the rhs. However I don't get the expected answer when I tried to do this in Excel: n | Expected vs Excel 1 | 1030.00 vs 1030.00 ( = 1000 + 30 ) 2 | 1062.45 vs 1061.50 ( = 1000 + 30 + 31.5 ) 3 | 1097.59 vs 1094.58 ( = 1000 + 30 + 31.5 + 33.075 ) 4 | 1135.70 vs 1129.30 ( = 1000 + 30 + 31.5 + 33.075 + 34.7288 ) Did I interpret the FV summation formula correctly and just get Excel wrong? Thanks again for your help! The formula I presented in two parts at first finds the continuously compounded yearly rate (geometric average) that has to be converted to a yearly rate See the following for complete formula$\displaystyle R (1+i)^n = R(e^{\sum_{k\ =\ 0}^{n-1} (1+r(1+g)^k)})$Edit: This formula should work as I am short on time now thus I didn't find time to confirm the results 5. ## Re: calculating compound interest with non-linear interest growth Was trying for geometric series...like the 1000 bucks creates a guaranteed 30 bucks annual deposit (the 3%), so we'd have FV of 1000 plus FV of 30 annuity....but finally realised that won't work... i = initial interest rate (.03) f = increase factor (1.05) n = number of years (make it 10) FV = (1 + i*f^0)(1 + i*f^1)(1 + i*f^2)...........(1 + i*f^(n-1)) = 1.4481148... That's for the proverbial one dollar, of course; so 1,448.11 if$1000.

Soooo....looks like I'm kinda repeating GA's "spectacular!" work.

Whadda hell is the matter with the dollar sign here??

6. ## Re: calculating compound interest with non-linear interest growth

Originally Posted by GhostAccount
The formula I presented in two parts at first finds the continuously compounded yearly rate (geometric average) that has to be converted to a yearly rate

See the following for complete formula

$\displaystyle R (1+i)^n = R(e^{\sum_{k\ =\ 0}^{n-1} (1+r(1+g)^k)})$

Edit: This formula should work as I am short on time now thus I didn't find time to confirm the results
Beautiful.
It should work but there's a slight typo on the exponent of e; $\displaystyle ln$ is missing.This could have been

$\displaystyle R (1+i)^n = R(e^{\sum_{k\ =\ 0}^{n-1} ln(1+r(1+g)^k})$

Must agree with Sir Wilmer; spectacular work indeed.
Was about to work on a derivation for this one when i saw it earlier but got too sober for a day and a half.
Drunk as I am right now, I am nevertheless definitely green with envy for this piece of beauty.
I'm almost certain I'm not the only one who'd like to see the analysis of this derivation Sir GhostAccount.
But as you said, this is one way of doing this. There might be another (or other's).
Might have to soak me brain with me favorite tequila brand for one such other future possibe derivation.

7. ## Re: calculating compound interest with non-linear interest growth

Thanks everyone, I've learned a lot so far!

I did some more research and realized that the correct way to represent the FV formula for the compounded growth of compounded interest is

$\displaystyle FV = R(\prod_{k\ =\ 0}^{n-1} (1+r(1+g)^k)})$

Since FV is essentially R(a)(b)(c)(...) we can convert this to a summation using logs.

Since

$\displaystyle e^{ln(x)} = x$

and

$\displaystyle ln(abc) = ln(a)+ln(b)+ln(c)$

so

$\displaystyle \boxed{FV= R(e^{\sum_{k\ =\ 0}^{n-1} ln(1+r(1+g)^k)})}$

$\displaystyle FV=R(1+i)^n$

so

$\displaystyle R(1+i)^n= R(e^{\sum_{k\ =\ 0}^{n-1} ln(1+r(1+g)^k)})$

eliminating R and taking the log of both sides we get

$\displaystyle nln(1+i) = ({\sum_{k\ =\ 0}^{n-1} ln(1+r(1+g)^k)})$

$\displaystyle ln(1+i) = \frac{1}{n}({\sum_{k\ =\ 0}^{n-1} ln(1+r(1+g)^k)})$

$\displaystyle 1+i = e^{\frac{1}{n}({\sum_{k\ =\ 0}^{n-1} ln(1+r(1+g)^k)})}$

and now we're back to the answer GhostAccount initially provided as

$\displaystyle \boxed{i = e^{\frac{1}{n}({\sum_{k\ =\ 0}^{n-1} ln(1+r(1+g)^k)})} - 1}$

One last question...is it possible to express

$\displaystyle {\sum_{k\ =\ 0}^{n-1} ln(1+r(1+g)^k)}$

in a single function f(n) where it outputs the expected FV for n=0, n=1, n=2 etc?

I could do something similar for the normal compounded case, e.g.

$\displaystyle \sum{r(1+g)^k} = \frac{r((1+g)^{(k+1)}-1)}{g}$

but I'm completely lost on how to do a similar conversion for the FV formula or if it's even possible. I'm trying to create an Excel file comparing the result of various investments with different dividend yields and dividend growth rates, so having a single formula in a cell for each value of n would make creating that file much easier.

8. ## Re: calculating compound interest with non-linear interest growth

Whadda hell is the matter with the dollar sign here??
Dollar sign invokes the LaTeX editor. Confused me when I started posting here. See Dollar Signs

9. ## Re: calculating compound interest with non-linear interest growth

I realized I should make my question more specific...
Suppose I have two different dividend paying stocks - one has an initial yield r1 with a growth rate of r1 and the second has an initial yield of r2 with a growth rate of r2.
I'd like to be able to analyze / compare the two performance stocks when I re-invest the dividend payments and answer questions such as:

Q1: With a constant stock price, what is the total return of principal + dividends after time t?

Q2: How many years will either stock take to reach a certain dividend payout?

I was able to do this for the non-compounding case and I posted an article on my blog explaining the formulae. The formulae and explanations above are really helpful but I'm stuck in transforming the summation formula above to a more usable form. I want to avoid continous compounding but keep to annually compounded calculations.

10. ## Re: calculating compound interest with non-linear interest growth

Originally Posted by tw35758
but I'm completely lost on how to do a similar conversion for the FV formula or if it's even possible. I'm trying to create an Excel file comparing the result of various investments with different dividend yields and dividend growth rates, so having a single formula in a cell for each value of n would make creating that file much easier.

For now, see the following code in Excel to use the tadFVSchedule function that answers the original question

Code:
Public Function tadFVSchedule(ByVal pv As Double, ByVal r As Double, ByVal g As Double, ByVal n As Double, Optional ByRef c As Double = 1, Optional ByRef p As Double = 1, Optional ByRef d As Double = 1) As Double
Dim fraction As Double
Dim sum As Double
Dim k As Integer

sum = 0#

fraction = n - Int(n)

For k = 0 To Int(n - 1)
sum = sum + p * d * Log(1 + r * (1 + g) ^ k)
Next k

If (fraction <> 0) Then
k = Int(n + 0.5)
sum = sum + ((fraction - 1) * p + p * d) * Log(1 + r * (1 + g) ^ k)
End If

End Function

 pv r g n c p d fv 1000 3% 5% 3 1 1 1 1097.59 1000 3% 5% 10 1 1 1 1448.11 1000 3% 5% 3.5 1 1 1 1117.42 1000 3% 5% 10.5 1 1 1 1484.80 1000 3% 5% 3 =1/12 1 1 1097.59 1000 3% 5% 10 =1/12 1 1 1448.11 1000 3% 5% 3 1 =1/12 1 1007.79 1000 3% 5% 10 1 =1/12 1 1031.34 1000 3% 5% 3 =1/12 =1/12 1 1007.79 1000 3% 5% 10 =1/12 =1/12 1 1031.34 1000 3% 5% 3 1 1 =1/2 1047.66 1000 3% 5% 10 1 1 =1/2 1203.38 1000 3% 5% 3.5 1 1 =1/2 1047.66 1000 3% 5% 10.5 1 1 =1/2 1203.38 1000 3% 5% 3 =1/12 1 =1/2 1047.66 1000 3% 5% 10 =1/12 1 =1/2 1203.38 1000 3% 5% 3 1 =1/12 =1/2 1003.89 1000 3% 5% 10 1 =1/12 =1/2 1015.55 1000 3% 5% 3 =1/12 =1/12 =1/2 1003.89 1000 3% 5% 10 =1/12 =1/12 =1/2 1015.55

11. ## Re: calculating compound interest with non-linear interest growth

Thank you GhostAccount! That's great - I can use that macro.

I made a typo in my question - I meant:

Suppose I have two different dividend paying stocks - one has an initial yield r1 with a growth rate of g1 and the second has an initial yield of r2 with a growth rate of g2. The stocks pay dividends annually and the dividends are re-invested to purchase more of the stock at the end of each year.

Q1: With a constant stock price, what is the total return of principal + dividends after time t?

Q2: How many years will either stock take to reach a certain dividend payout?

Thanks again for your help and patience in answering - I've learned a lot so far!

12. ## Re: calculating compound interest with non-linear interest growth

I must be missing something...but, if you're able to use a looper, then isn't that enough?

I love UBasic ([]= notes):

i = .03 : f = 1.05 : b = 1000 : p = 1000 : k = 30 [f = increase factor, k = years]
FOR n = 1 TO k
e = b * i [e = year's earnings]
b = b + e
y = (b / p)^(1 / n) [y = yield so far]
PRINT n, e, b, i * 100, y * 100
i = i * f [next year's rate]
NEXT n

OUTPUT:
Code:
YEAR  INTEREST  BALANCE  YEAR'S RATE  YIELD-SO-FAR
0             1000.00
1      30.00  1030.00    3.0000        3.0000
2      32.45  1062.45    3.1500        3.0750
3      35.14  1097.59    3.3075        3.1524
...
10      64.40  1448.11    4.6540        3.7720
...
20     185.22  2628.54    7.5808        4.9507
...
30     749.55  6819.59   12.3484**      6.6085
** .03 * 1.05^29 = 12.3484...

Isn't that all you need?

13. ## Re: calculating compound interest with non-linear interest growth

Hi Wilmer,

You're correct - I can certainly compute the answer using an excel macro. However I'm curious if there's a mathematical solution to this question.

The only mathematical solution I know of would be to approximate the answer using a Taylor series e.g.

$\displaystyle FV = Re^{\sum_{k\ =\ 0}^{n-1}ln({1+r(1+g)^k)}}$

$\displaystyle ln(1+x) \approx x - \frac{x^2}{2} + \frac{x^3}{3} - {...}$

taking the first 2 terms as an example then

$\displaystyle \sum_{k\ =\ 0}^{n-1}ln({1+x}) \approx \sum_{k\ =\ 0}^{n-1}(x - \frac{x^2}{2})$ where $\displaystyle x = r(1+g)^k$

$\displaystyle \sum_{k\ =\ 0}^{n-1}(x - \frac{x^2}{2}) = \sum_{k\ =\ 0}^{n-1}x - \sum_{k\ =\ 0}^{n-1}\frac{x^2}{2}$

and

$\displaystyle \sum_{k\ =\ 0}^{n-1}x = \sum_{k\ =\ 0}^{n-1}r(1+g)^k = \frac{r((1+g)^n-1)}{g}$

$\displaystyle \sum_{k\ =\ 0}^{n-1}\frac{x^2}{2} = \frac{1}{2}\sum_{k\ =\ 0}^{n-1}r^2(1+g)^{2k} = \frac{r^2((1+g)^{2n}-1)}{2g(g+2)}$

so I could say that

$\displaystyle FV \approx Re^{\frac{r((1+g)^n-1)}{g} - \frac{r^2((1+g)^{2n}-1)}{2g(g+2)}}$

and solve this equation for n with a given FV.

I'm wondering if there's a more elegant equivalent expression of this function out there that doesn't involve approximations.

14. ## Re: calculating compound interest with non-linear interest growth

Originally Posted by tw35758
I'm wondering if there's a more elegant equivalent expression of this function out there that doesn't involve approximations.
After 5 beer bottles and 2 shots of tequila, I've been wondering about that too. You could always repost your questions on an actuarial forum. You might get lucky there. Having seen your analyses, I'm somewhat inclined to believe that you might have some background in actuarial mathematics yourself. If you're lucky, Sir TKHunny, the resident actuary here might get interested in your situation and come out of retirement from this often thankless business of math knight-errantry.

15. ## Re: calculating compound interest with non-linear interest growth

Originally Posted by jonah
After 5 beer bottles and 2 shots of tequila, I've been wondering about that too...
Hey, that was my breakfast 30 years ago, at end of my drinking...!!

Page 1 of 2 12 Last