How can I calculate (approximate) the future value of my investments? Is this formula appropriate?

Question

I want to check to see if I'm meeting my investment goals, so I want to project forward my assets given a growth rate and a monthly deposit amount.

What is the best formula to use for this? Is this good enough (assuming 5% growth):

(Current Value + Annual Deposit) * 1.05  

(Repeated for each year I'm projecting.)

I can see that the 1st month's deposit will be compounded all year where as the last month's won't, but I figure that given the volatility of the stock market, this might be good enough for an approximation?

p.s. I am using Excel to do this, if it makes any difference.

Answer 1

An improved approximation using your growth rate assumption would be:

value at year end = (value at year start * 1.05) + (total monthly deposits * 1.025)

i.e.

• apply the full growth rate (5%) only to the beginning-of-year balance, and,

• apply half of the growth rate (2.5%) to the total monthly deposits made in the year.

  Why half? Consider: If each monthly deposit is, say, made mid-month, then the January deposit would get 11.5/12 of the year's growth rate applied, and the December deposit would get only 0.5/12 of the rate applied. The average ratio across all 12 months would be 6/12.

Answer 2

Using only the expected value as you have is much easier to manipulate. Taking volatility into account is a bit more intense and probably better suited for the quant stack.

If you want to determine this monthly, everything must be put in monthly terms, so the monthly rate of return on investment assuming 5% annually is 0.4%.

It appears as if the deposit rate grows as fast as the rate of return by your equation, so the equation can be simplified to

I * [ 1 + 0.004 + D / I ] ^ n

where I is the initial investment, D is the amount of the first month's deposit, and n is the number of months.

When taking into account the other mathematical moments, the Variance Gamma process can be used for a realistic projection over time, but although it will be more precise, it will still be inaccurate because of the random walk.

For simplicity's sake, 0.004 can be multiplied by the standard normal random variable to account for volatility. The CDF with a standard deviation equal to the historical standard deviation of log investment will be more realistic, and using the Variance Gamma process would be even more realistic.

Then again, one could always use actual past investment monthly returns randomly chosen to simulate future returns. That might take a little more than the standard programming Excel allows.