Question

Hi, I have a database that I would like to use for forecasting an investment.

Below screen shot is an example.

I have an initial investment (in this case $2976), plus a figure that is invested annually (in this case $672/month).Each year should increase by the 'Return % p.a' (in this case 6% per year).

The last column, 'Est Future Value', should be a result of the initial investment, plus $672 per year, with each year increasing by 6%, for a total of 25 years.

For example, if the 'Term' was 1 year, the 'Est Future Value' would be approx (2976 * 1.06 ) + 672 = $3826.56. But, I want it to be over whatever I put in the 'Term (years)' column, in this case, 25 years.

So, the second year would be (3826.56 1.06) + 672 = $4538.88, the third (4538.88 1.06) + 672 and so on.

I need the formula to take into consideration that the initial investment changes each year due to the compound interest of 6%.

Hopefully this makes sense. Any helpers? Thanks.

Answer 1

This simple solution can help you achieve that.

Create a new database for the years.
Relate it to the investments database.
Relate each investment with the year it corresponds.
Create a formula or rollup property to sum everything.
Create a formula property to do the math you need.

That will do the trick and you will have a years database to see the information exactly as you need per year.