One way of doing this would be to find a single yield that results in a same future value using the series of yields
Code:R=1000 r=3% g=5% n=3
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
One way of doing this would be to find a single yield that results in a same future value using the series of yields
Code:R=1000 r=3% g=5% n=3
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 :
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
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
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
Edit: This formula should work as I am short on time now thus I didn't find time to confirm the results
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??
Beautiful.
It should work but there's a slight typo on the exponent of e; is missing.This could have been
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.
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
Since FV is essentially R(a)(b)(c)(...) we can convert this to a summation using logs.
Since
and
so
Per GhostAccount's initial answer,
so
eliminating R and taking the log of both sides we get
and now we're back to the answer GhostAccount initially provided as
One last question...is it possible to express
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.
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.
Dollar sign invokes the LaTeX editor. Confused me when I started posting here. See Dollar Signs
Whadda hell is the matter with the dollar sign here??
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.
Thanks for your help!
I will reply to your new post tomorrow
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 tadFVSchedule = pv * Exp(sum) 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
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!
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:
** .03 * 1.05^29 = 12.3484...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
Isn't that all you need?
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.
taking the first 2 terms as an example then
where
and
so I could say that
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.
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.