Financial Analysis and Modeling Using Excel and VBA

Chapter 27. Financial Planning and Investments

In this chapter, we will develop in VBA some of the same models we developed in the corresponding chapter in Part Two. As we have seen before, the VBA models are generally more flexible and will overcome some of the limitations of the Excel models. We do not need any additional theory or concepts for these models.

Modeling Examples

MODEL 1: SAVING FOR RETIREMENT, VERSION 1

The Problem

You are currently 40 years old. Starting today, you want to save in 25 growing annual installments enough money to accumulate $I million (after all taxes) in today's dollars by the time you retire at age 65. You expect to be able to save every year an amount 2% more than the previous year. You currently have $100,000 saved, and you plan to invest this money and all new savings in a stock fund that is expected to return 8% per year, of which 2% will be dividends and 6% will be capital gains. All the money will be in taxable accounts; you will have to pay income taxes at a 15% rate on the dividend incomes as you earn them at the end of each year. In addition, assume that you will sell all holdings at the time you retire and pay the necessary capital gains taxes at the rate of 15%. Assume inflation rate will average 3% per year over the period.

How much money will you have to save in the first year?

Set this up as a model so that a user can input other values (for example, different tax rates) in order to do similar calculations for his own situation. The output ...