Visual Basic Applications

Spreadsheet Modeling in Corporate Finance, September 27, 2007

Visual Basic Applications

Introduction to VBA

Macro Recorder and Editing the Codes
Change the currency of a cell from $ to Rs.
Find the EMI of a loan
Communicating with VBA
User communicates with VBA
User enters data in Excel
User enters data in a user form
VBA Communicates with the user
VBA records the data in Excel
VBA shows the output in a message box
Using the in-built functions of Excel
Find EMI of a loan
Find the NPV of a series of cash flows
Find the IRR of a series of cash flows

Decision Making in VBA

Change the font of a cell to red if it is negative
Find the color-index of different colors
Change the font to red if it is negative, pink if it is zero, and blue if it is positive
Goalseek to find the loan duration for a particular EMI (For example, if I can pay an EMI of Rs.10,000 only, then a loan of Rs.10 lakhs can be repaid over how many months if the interest rate is 9%?)
Give error message to the user if he gives meaningless EMI figures
Find the income tax by using information from the following table.

Income Slab / Tax rate
Up to Rs.1,00,000 / Nil
Rs.1,00,001 / 10%
Rs.1,50,001 to Rs.2,50,000 / 20%
Above Rs.2,50,000 / 30%
Above Rs.10,00,000 / 30% + 10% surcharge on the total income tax payable.

Advanced Usage of VBA

Write a code to find the sum of 1 to ‘n’.
The user gives the value of ‘n’ and VBA gives the sum

Change the font of a selected range to red if it is negative.
The range can have any number of numbers.

Find the payback period of a series of cash flows

Difference between function and subroutines

Write a function to find the payback period of a series of cash flows
The user gives the range of cash flows as the input. Thus for example, suppose the input is in B1 to B15. Then the user will enter a formula like “=payback(b1:b15)” and Excel should give the payback period.

Create a loan amortization table
The loan amortization table should have the opening balance, the interest, the principal repaid and the closing balance of the loan. Here, the user gives the number of periods over which the loan is repaid, and VBA computes the above numbers for all the periods.

Create a user-form to write something about the program
This can be a simple introduction to the program like who has prepared t, what it does, etc.

Create a user form to do project appraisal
The user form asks the user to give the cost of capital and the range of cash flows. The user form also asks the user whether he wants to estimate the NPV or the IRR, etc. Accordingly it generates the output.

Application of VBA in Financial Models

Use of User form to put notices
Make sure when you open one particular sheet opens
Getting the sheet names
Insert a drop down box with link to all the sheets
Doing sensitivity analysis
Inserting the organization name and author name in status bar and caption