Investment interest calculator

[SteveT]

Financial planners often have tables that show investment growth on an “average” rate of return over a number of years. But the market doesn’t work like that. Annual rates of return vary greatly which, over time, produce an average. An investment that receives the same rate of return each year compared to one that has yearly variable growth rates can have a very different outcome at the end of the investment period, particularly if a draw down is occurring in retirement years. I would like to create a spreadsheet that can demonstrate this.

I can’t figure out how to have the spreadsheet produce a column for each year’s rate of return. These random rates would need to be within a specified range andaverage the specified rate of return over the term of the investment. I’d like to:

1. Enter the investment amount.
2. Enter the number of years for the investment period. Example 30 years.
3. Enter a range of the expected rate of return on investment. Example -5 to 20 percent.
4. Enter a variable for the average percent of return on investment over the time period specified above. Example 8%.

So in this case have the spreadsheet fill 30 cells in a column, each with a rate of return randomly selected within -5 and 20 percent and when all cells are averaged equals 8%.

Thanks in advance for any assistance.

[Lupp]

(Spoken aside:) Why do we talk about investments where the matter is gambling?

[anon87010807]

If all rates are equally likely, you can use =RAND() * 12.5 + 8. If you use enough numbers, and 30 sounds good to me, the random numbers should average at about 8%. If that doesn’t work, take 29 random values and set the 30th so that the total averages 8 ( =30*8-29*average(a1:a29)) ). Check out the Help on the RAND() function for the snags.
If they aren’t equally likely, you could use the normal distribution, and then you need a formula that generates random numbers that are normally distributed with mean 8 and standard deviation of your choice, probably 4 if you don’t want rates to differ more than 12 (I couldn’t find that function right now). Apply that to 30 cells.