Excel Formula for Compound Interest with Regular Deposits

JohnRo

The final investment return will be whatever it'll be as will your chosen inflation benchmark. The only thing you have any control over in the mean time is how much you spend and save.

It's been shown over a long enough time frame sensible equity investment will return a greater amount than that lost to the hidden hand of the money printers and that's all that matters.

_pete_

Thanks very much indeed for the constructive suggestions. As a complete novice when it comes to Excel, I think I'm going to go with the approach suggested by Ainsley1, and use a growth rate net of inflation.

Could I run my proposed approach past you all to make sure I'm on the right lines.....

One row for each year, going forward.
Column C = Monthly contribution to fund
Column D = Annual Contribution (C X 12)
Column E = Expected growth rate (after inflation)
Column F = Fund value at start of year
Column G = Fund value at end of year.

I *think* that the Excel formula for Column G should be:

=SUM(F5+D5)+((F5+D5)*(E5/100))

The figure this gives me looks about right....

Thank you again for all the suggestions.

JohnRo

The monthly and annual contributions don't need to be separated.

All you need is an accumulating investment contributions column and calculate a projected return by applying the annual growth rate to the relevant amount invested in proportion to the time it's been invested.

Column one - date
Column two - contribution
Column three - annual net growth rate (not really needed as a column)
Column four - valuation based on pro rata annual growth rate
Column five - investment return (valuation minus contribution)

The formula to calculate the projected valuation is

The annual net growth rate divided by 365.25 multiplied by the number of days that amount has been invested.

User the DAYS function to obtain the difference between the start date and the date of your entry.

I'm a little confused why you're inventing a growth rate though? (whether net of inflation or otherwise).

The rate of growth will depend entirely on the investments held and their performance, so that's where the growth rate should be derived from. Which means recording real valuations at specific points in time and then obtaining a real growth rate and investment return.

_pete_

JohnRo wrote: »

T

I'm a little confused why you're inventing a growth rate though? (whether net of inflation or otherwise).

The rate of growth will depend entirely on the investments held and their performance, so that's where the growth rate should be derived from. Which means recording real valuations at specific points in time and then obtaining a real growth rate and investment return.

Thank you for the feedback JohnRo.

I've invented a growth rate because, to be honest, I'm a passive and relatively uninformed investor, and my understanding is that previous performance doesn't predict future performance. If this is true, then extrapolating from growth in previous years may not be a valid prediction of growth over the next 10 years or so. Or am I missing something?

JohnRo

I've done something similar, projecting a series of potential outcomes based on various contributions and growth rates. The only thing they are really good for is making lines on a chart though. They predict absolutely nothing where as a long term historic view of real investments currently held might be ballpark in terms of projecting a similar performance forwards to a future outcome.

LXdaddy

_pete_ wrote: »

Thanks very much indeed for the constructive suggestions. As a complete novice when it comes to Excel, I think I'm going to go with the approach suggested by Ainsley1, and use a growth rate net of inflation.

Could I run my proposed approach past you all to make sure I'm on the right lines.....

One row for each year, going forward.
Column C = Monthly contribution to fund
Column D = Annual Contribution (C X 12)
Column E = Expected growth rate (after inflation)
Column F = Fund value at start of year
Column G = Fund value at end of year.

I *think* that the Excel formula for Column G should be:

=SUM(F5+D5)+((F5+D5)*(E5/100))

The figure this gives me looks about right....

Thank you again for all the suggestions.

If you used that formula in cell G5 you would be overstating the fund value at the end of the year.

The monthly contributions made in December does not grow as much as the contribution made in January.
You've made the same mistake that is regularly discussed in relation to Monthly Saver Accounts.

=F5 + D5 + (F5 + (C5 * 6.5)) * (E5/100)

This recognises that on average your monthly contributions have been invested for 6.5 months

Using an "anticipated growth rate after inflation" will give you a projection in today's values so gives you numbers that you can relate to.

Of course the anticipated growth rate is just a guess - none of us KNOW what next years growth rate will be - but by running the calculation with different assumptions you can get to an idea of what range of possible outcomes could be.

_pete_

LXdaddy wrote: »

If you used that formula in cell G5 you would be overstating the fund value at the end of the year.

The monthly contributions made in December does not grow as much as the contribution made in January.
You've made the same mistake that is regularly discussed in relation to Monthly Saver Accounts.

=F5 + D5 + (F5 + (C5 * 6.5)) * (E5/100)

This recognises that on average your monthly contributions have been invested for 6.5 months

Thanks so much taking the time to look at my calculation, and for pointing out the error. I've amended my spreadsheet.

Ainsley1

Some good responses for you there and a great way to compute average rate for monthly contributions.

You seem to be doing fine ....provided your increases in 'contributions' fall at annual boundaries. If you were to increase mid year then you might consider each row as months rather than years so you can enter the increased as they happen.

Another potential is to mirror the rows and columns with a straight copy/paste and that would give you the possibility subsequently to enter actual effective interest rate (rather than that assumed for the future) to see how actuality compared with estimated plan.

I would disagree with one post in that you can enter (or just copy if no change) interest above inflation on each individual row as then increases or reductions over time can be utilised instead of making the assumption of a static rate.

LXdaddy

Ainsley1 wrote: »

I would disagree with one post in that you can enter (or just copy if no change) interest above inflation on each individual row as then increases or reductions over time can be utilised instead of making the assumption of a static rate.

Yes.
But I'd make the assumed growth rate on each row as a formula based on the row above like for E6 it would be =E5
That way if you make a change in one row it will continue in subsequent rows until you decide to make a new manual value