Wealth Management

APPENDIX

Simulating Portfolio Values

This appendix shows how you can simulate and present future portfolio values using the embedded functions in Excel. Even if you do not fully understand each step below, you can construct your own simulation by replicating the Excel content.

Step 1

I first created a spreadsheet that calculates the value of a portfolio at the end of each of 10 years, as depicted in Figure A.1. The input variables include:

• The expected return of the portfolio per year (set to 8 percent in cell B3);

• The standard deviation of the portfolio returns per year (set to 14 percent in cell B4);

• The beginning value (set to $30,000 in cell B5); and

• The annual investment, which is assumed to occur at the beginning of each year (set ...