2

I have a retirement fund into which I make regular deposits, however the deposits vary to some extent in size. Furthermore, the growth of the account is also dependent on stock prices. I am tracking a running average of my deposits and running average annual growth rate. I can use these numbers in a future value formula (Excel has one built in) to predict the value at retirement, but I wanted to simply use the actual value of the account as data points and just run a regression on it. However, I can't find how to do that in Excel or by any other means. I know Excel does have an exponential regression function but that just generates a formula of the form a*e^(bx) which does not quite match the equation for future value. Does anyone know how I might be able to achieve this?

1

I'm guessing that your regression data includes both the contributions and the intrinsic growth. The problem is that the FV formula adds in both intrinsic growth AND the contributions, so you're double-counting the growth attributable to the contributions if you use the rate of return from the regression variables.

If your regression data includes both contributions and growth, then you could just use the same regression variables to roughly predict the future value using the formula FV = PV * E^rt.

If you want to separate the intrinsic growth from the contributions (which would be a bit more accurate), then you can line out each monthly contribution and use the IRR function to calculate the average rate of return based on the final value (adding a negative cash flow at the end equal to the present value). That rate of return could then be used in the FV formula.

1

a*e^(bx) is the logarithmic form of the future value. E.g. with 10% rate of return, a principal of 100 compounded for 2 years

r  = 0.1
fv = 100 (1 + r)^2 = 121

Using logs, as shown here, produces the same result, 121.

i  = ln(1 + r) = 0.0953102
fv = 100 e^(2*i) = 121

r  = e^i - 1

Portfolio index projection

To predict future values based on regular index values, i.e. weekly, monthly, etc. you can simply use b = average return

for fv = a*e^(bx)

The formal method shown in this link - detailed here - works with irregular data, factoring each return by its time period, but with regular data the average return works fine, (usually a better match than log mean return, curiously).

Confidence bounds can also be plotted as a*e^((b - σ^2/2)*x + c*σ*Sqrt[x])

where σ = standard deviation of returns and

c = -1.64485 for the  5th percentile
c = -0.67449 for the 25th percentile
c =  0       for the 50th percentile (median)
c =  0.67449 for the 75th percentile
c =  1.64485 for the 95th percentile

Here is an example stock price projection with confidence bounds. The 25 to 75% confidence bounds indicate that there is 50% confidence that the projection will be within the bounds. Also shown are the 5 and 95% percentiles and the mean return projection.

With times x and prices S

x = 0, 1, 2, 3, 4, 5, 6
S = 1000, 1100, 1150, 1300, 1250, 1150, 1200

a = last price = 1200

returns = 0.1, 0.0454545, 0.130435, -0.0384615, -0.08, 0.0434783

b = average return = 0.0334844

σ = standard deviation = 0.0801521

enter image description here

Applying investment amounts

With three investment amounts at times 0, 3 and 4

d0 = 3000
d3 = 6500
d4 = 2500

the value at time 6 is

fv6 = d0*1200/1000 + d3*1200/1300 + d4*1200/1250 = 12000

and the mean projected value at time 12 is

fv12 = fv6*e^(bx) = fv6*e^(0.0334844*6) = 14670.12

with 50% confidence of being between 12605.28 and 16427.61, e.g.

fv6*e^((b - σ^2/2)*6 + c*σ*Sqrt[6]) = 16427.61

where c = 0.67449

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.