Assignment Description

Presentation

All your code, data inputs and outputs should be in the same workbook. Credit is given for good presentation ie. efficiency, elegance clarity of the code and professional model design. Therefore please remember to choose your data variable names carefully, indent your code, comment extensively to explain your code. When there are multiple ways of doing the same thing, use more efficient commands and richer data structures to achieve the same result.

Make the necessary assumptions to complete your VBA models in a clear and easy to follow structure. When documenting your models, assume that you are an investment analyst, portfolio manager, bank fund manager etc where you are introducing a new approach to analysing your client’s reports by using VBA programming.. You want to persuade them to appreciate the advantages of VBA modelling.

Question

You are currently 30 years old. Starting today, you want to save in 35 growing annual instalments enough money to accumulate $1 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. Your VBA code should create a new table every time by recreating all the necessary formulas so that the output table will have only as many lines as necessary.

Tips:

Documentation and discussion of model should include analysis of aspects such as:

  1. Modeling strategy,
  2. How to test the model,
  3. Limitations or weaknesses of the model
  4. Any special features like loops, msgBoxes, user forms etc
  5. Uses of the model – who uses the model and how frequent 
  6. Plus any other analysis that would make the user understand the advantages of your VBA model better. This is why you will be allowed you to make any  assumptions that would make your modeling more interesting.

Overall need

One VBA workbook

One report: 5 pages limit for the report (800-1000 words)

Leave a Reply

Your email address will not be published. Required fields are marked *