1
   

Investment Returns within a Normal Distribution (Confidence Intervals)

 
 
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!
  • Topic Stats
  • Top Replies
  • Link to this Topic
Type: Question • Score: 1 • Views: 924 • Replies: 1
No top replies

 
engineer
 
  1  
Reply Thu 8 Aug, 2013 12:23 pm
@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.
0 Replies
 
 

Related Topics

 
  1. Forums
  2. » Investment Returns within a Normal Distribution (Confidence Intervals)
Copyright © 2019 MadLab, LLC :: Terms of Service :: Privacy Policy :: Page generated in 0.03 seconds on 10/19/2019 at 04:06:03