Excel VBA Fundamentals

Participants will learn the fundamentals of Excel VBA and prepare themselves to create simple financial models in Part II. Small programs will be used to illustrate the underlying programming concepts.

• The VBA programming environment
• Variables and constants
• Macros
• Built-in VBA functions and operators
• User-defined functions
• Branching and looping
• Object-based concept: Objects, methods and properties
• The Range object
• Debugging 
• The following additional topics may also be covered if time allows:
• Arrays
• Procedures
• Custom dialog boxes
• Workbook and worksheet events
• Chart objects

Financial Modeling

Participants will apply VBA programming concepts and skills to build simple yet real-world financial models. The techniques of writing good models will also be discussed. As the scope of financial modeling is huge, topics have to be selective. The following shows a list of possible topics. Actual coverage depends somewhat on the finance background of the class.

Loan amortization

• Simple model 
• Iteration: The bisection approach 
• Allowance for changing interest rates or repayment period

Financial planning 

• Retirement planning 
• Portfolio restructuring

Market history analysis 

• Normal and real growth of stocks 
• Growth of investment in different assets

Stock simulation 

• Stock volatility estimation 
• The geometric Brownian motion and the log-normal distribution 
• Stock price simulation

Option pricing 

• Different option strategies 
• The Black-Scholes option pricing model 
• Implied volatility estimation 
• Options portfolio 
• The binomial models