Excel Formula for Compound Interest with Regular Deposits

_pete_

I'd like to make some best guesses as to what my pension investments will be worth in a few years time. Does anyone know what the Excel formula is for calculating compound interest over a set period years when one is making regular investments? I've not found anything by googling.

Thank you.

cjking

I assume you want to specify the rate of return and want the final balance.

Try the FV function.

For example, if:-
1. expected rate of return is 4%/12 per month
2. saving for 10 years
3. saving 1000 a month
4. initial balance 100,000

Then FV(4%/12,10*12,-1000,-100000) = £296,333

TheTracker

Please see http://bfy.tw/5dOV
4th result

_pete_

TheTracker wrote: »

Please see http://bfy.tw/5dOV
4th result

Thanks for the suggestion, however I couldn't get that to work - hence my asking for help on this forum.

I should probably be more specific with my request:

- I'd like to set up an Excel spreadsheet that will enable me to work out the future value of my investments, based on certain assumptions (eg estimated growth each year).

- I want to be able to change the value of my monthly contribution (which I plan to increase by 3% annually), the annual growth rate, the number of years invested.

I'd love to hear from anyone who has set up a similar spreadsheet successfully.

Thank you.

Ainsley1

Formulas in Excel are extremely powerful.......but for what you want it can be done easily with simple cell based calculations that will have the advantage of visibility and flexibility.

Consider a row per month with a column for the month date , the contribution amount which can be a manual entry or be based upon the previous month's amount multiplied by the increase rate which can be in the column next to it. Another column to calculate the interest that accrues, with a further column containing the pertinent interest rate you wish to use for that period and then a total column giving a running total.

A formula approach sounds good and I am a fan of using them . There are many built in ones or that can be added in but the advantage of the simple approach is that the rates can be set for particular time frames, you can see these (and chart them and other entries) at a glance.
..... Flexible, visible and given the number of rows can cope with more months than you will need. You can even use if formulas in the cells to change the interest or contribution amounts at annual boundaries (or other date changes as you wish) or even add an inflation column so you see the results in today's value rather than pure monetary quantity.

If you would like an example p.m. me as it will not take long to knock together the basic sheet. There are more complicated elegant ways with lists etc. but why make it complicated?

AnotherJoe

Just use the formula in post 2 but instead of fixed values for term, interest rate etc, make them references to cells where you then enter the actual info you want to experiment with

Eg instead of
=FV(4%/12,10*12,-1000,-100000)

You have

=FV( a1/12,a2*12,-a3,-a4)

LXdaddy

Ainsley1 wrote: »

Formulas in Excel are extremely powerful.......but for what you want it can be done easily with simple cell based calculations that will have the advantage of visibility and flexibility.

Consider a row per month with a column for the month date , the contribution amount which can be a manual entry or be based upon the previous month's amount multiplied by the increase rate which can be in the column next to it. Another column to calculate the interest that accrues, with a further column containing the pertinent interest rate you wish to use for that period and then a total column giving a running total.

A formula approach sounds good and I am a fan of using them . There are many built in ones or that can be added in but the advantage of the simple approach is that the rates can be set for particular time frames, you can see these (and chart them and other entries) at a glance.
..... Flexible, visible and given the number of rows can cope with more months than you will need. You can even use if formulas in the cells to change the interest or contribution amounts at annual boundaries (or other date changes as you wish) or even add an inflation column so you see the results in today's value rather than pure monetary quantity.

If you would like an example p.m. me as it will not take long to knock together the basic sheet. There are more complicated elegant ways with lists etc. but why make it complicated?

+1

certainly if you want to reflect changing patterns - like increasing your saving rate each year or different return rates over time

But beware the inflation question - if you simply started with £1,000 balance brought forward, save £100 per month get growth on the balances at the gross rate of 3% per year and every year you increase your contribution by 4% and run it for 20 years - your initial £1,000 would have grown to £18,007*

But that £18,007 in 20 years time does not have the same buying power as it does today - the price of a loaf of bread will have increased over that time

So, as Ainsley1 says, you should really be looking to take the impact of inflation into account as well.



* assuming that growth happens each month ie this is an investment rather than a savings account, months are equal length and ignoring any management charges

JohnRo

The problem with accounting for inflation, or trying to, is that it's entirely personal and constantly shifting, to the point it cannot be measured in any meaningful way unless you've kept a precise record of all your essential & regular expenditure then cobbled the net effect into an annual rate of some sort.

It always makes me chuckle when the phrase 'outstripping inflation' is used.

bigadaj

JohnRo wrote: »

The problem with accounting for inflation, or trying to, is that it's entirely personal and constantly shifting, to the point it cannot be measured in any meaningful way unless you've kept a precise record of all your essential & regular expenditure then cobbled the net effect into an annual rate of some sort.

It always makes me chuckle when the phrase 'outstripping inflation' is used.

Yes but adopting an approximate figure, or projection is better than ignoring it, which would give far too rosy a picture of future relative value.

Ainsley1

Quite right Bigadaj.

Consider the compounding on both the investment and inflation, they both have an enormous effect. To consider one without the other is foolish, is it not?

Yes my personal rate might be different to 'yours' and probably higher than CPI !!!! but at least with a spreadsheet you can enter some presumed figures and see how the result is affected......and choose to ignore it or not rather than stick your head in the sand and have no idea whatsoever!:rotfl:

sandsy

An easier way to allow for inflation is to use a growth rate which is net of inflation then all the future values are automatically in today's money.

My spreadsheet is relatively simple; there's so much uncertainty it's pointless doing anything too complicated.