Reply
Thu 8 Aug, 2013 07:33 am
I work for a Financial Planning company and what I am trying to do is to project a client’s investment assets forward over several years. This is easy if we assume a constant return e.g. 7% every year. However I want to take account of fluctuations of returns and have therefore built in some random returns using excel following a normal distribution. I then want to show a 95% confidence interval for each return and show the client we are 95% confident that their £100k initial investment will be within £X and £Y in 5 years’ time. A mini example is as follows. The random returns below are generated by the function =NORM.INV(RAND(),6.8%,7.5%) in excel.
Yr 1 Yr 2 Yr 3 Yr 4 Yr 5
4.4% 14.8% -3.3% 9.9% 4.4%
9.7% 12.3% 11.2% 21.8% 4.6%
-3.2% 14.0% 7.0% 10.7% -6.5%
5.4% 0.6% 0.0% 7.4% 6.9%
11.7% 6.4% -6.3% 17.8% -8.1%
26.9% -1.4% 2.3% 9.9% 4.5%
4.0% -1.6% 8.4% 3.8% 1.5%
10.7% -13.1% 7.1% 9.3% 17.7%
-0.7% 0.8% -1.1% 0.1% 7.0%
9.6% 1.4% 20.3% 13.2% -3.4%
Average 7.9% 3.4% 4.6% 10.4% 2.9%
Stan Dev 8.3% 8.6% 7.9% 6.3% 7.5%
CI Upper 13.0% 8.8% 9.4% 14.3% 7.5%
CI Lower 2.7% -1.9% -0.3% 6.5% -1.8%
Investment Amount £100,000
Future Fund Values
Yr 1 Yr 2 Yr 3 Yr 4 Yr 5
Upper £112,995 £108,765 £109,434 £114,272 £107,528
Average £107,850 £103,420 £104,560 £110,390 £102,860
Lower £102,705 £ 98,075 £ 99,686 £106,508 £ 98,192
The question I have is because we are assuming the upper return and the lower return year on year, are we exaggerating the range? Although the confidence interval for each return is fine, it’s the cumulative effect of this year on year that I’m concerned about. Is what I’m doing statistically correct or can you advise on an alternative method. Any alternative method needs to be able to be iterated easily thousands of times and therefore cannot be too data intensive in excel.
Thanks the most!
@Nutty2013,
Your approach is fine and you can easily do many thousands of runs in Excel. I did a quick test and the range varies from around break even to 100% gain.