Simulation Modeling for Financial planning
What will your investment portfolio be worth in 10 years? In 20 years? When you stop working? The Human Resources Department at Four Corners Corporation was asked to develop a financial planning model that would help employees address these questions.
Tom Gifford was asked to lead this effort and decided to begin by developing a financial plan for himself. Tom has a degree in business and, at the age of 40, is making $85,000 per year.
Through contributions to his company’s retirement program and the receipt of a small inheritance, Tom has accumulated a portfolio valued at $50,000. Tom plans to work 20 more years and hopes to accumulate a portfolio valued at $1,000,000. Can he do it?
Tom began with a few assumptions about his future salary, his new investment contributions, and his portfolio growth rate. He assumed a 5% annual salary growth rate and plans to make new investment contributions at 6% of his salary.
After some research on historical stock market performance, Tom decided that a 10% annual portfolio growth rate was reasonable. Using these assumptions, Tom developed the following Excel worksheet:
A A B C D E F C
1 Four Corners
2
3 Age 40
4
S Current Salary 585.000
Current Portfolio 350.000
6 Annual Investment Rate 6%
7
8 Salary Growth Rate 5%
Portfolio Growth Rate 10%
9
10
11
12
13
14
15 Year Beginning Balance Salary New Investment Earnings Ending Balance Age
I 350.000 585.000 $5,100 $5255 560.355 41
2 S60,355 389.250 $5,355 56.303 $72,013 42
3 $72,013 $93,713 $5,623 $7,482 $85,118 43
4 585,118 $98,398 $5,904 58.807 $99,829 44
5 $99,829 $103.318 $6,199 $10.293 $116,321 45
