Applications Programming --- Lab 6

Objective:

• Learn to design and use a userform to collect user input
• Learn to develop an event-driven VBA program
• Learn to develop a VBA program with conditions

References:

• Lab 5 referneces
• VBA Decision (I)
• VBA Decision (II)
• VBA Userform
• VBA Userform Programming

Simplified problem description:

BC government charges property transfer tax for each residential property purchase transaction. The amount of tax charged is based on the fair market value of the property.

The property transfer tax is charged at a rate of:

• 1% of the fair market value up to and including $200,000;
• 2% of the fair market value greater than $200,000 and up to and including $2,000,000; and
• 3% of the fair market value greater than $2,000,000.

For example, if the value of the property is $570,000, then 1% of the first $200,000 is $2,000, and 2% of the remaining value ($370,000) is $7,400. So the total tax payable is $9,400.

If the property is purchased by a BC resident and the buyer is a first-time home buyer, then the property transer is eligible for an exemption. The exemption is calculated according to the following rules:

• If the property's value is $500,000 or less, the full amount of transfer tax is exempted. (Meaning: you don't need to pay the transfer tax at all.)
• If the property's value is over $500,000 but no more than $835,000, the exemption amount is $8,000 flat.
• If the property's value is over $835,000 but under $860,000, then the exemption amount is proportionally reduced suing the following formula:

  8000 - 8000 * (value - 835000) / 25000

• If the property's value is greater than $860,000, the exemption amount is 0. (Meaning: you have to pay the full amount of the transfer tax.)

If you want to know more about the property tansfer tax in BC, visit: http://www2.gov.bc.ca/gov/content/taxes/property-taxes/property-transfer-tax

Your tasks:

Download the Lab 6 input file, where you can view all the parameter information and a set of testing data with their corresponding calculated results.

Design a userform to collect the following information:

• The property's fair market value;
• Is the buyer a BC resident?
• Is the buyer a first-time home buyer?

On the userform, there should be a commond button called "Calculate", which, upon clicking, should calculate and present the amount of the property transfer tax the user actually need to pay based on the user input and the BC government's rules. Write the input and calculated data to the proper cells in the transaction record area of the Excel file.

Save your work to a Macro-enabled Excel file (with the extension name .xlsm).

Suggestions:

You can divide the problem into two sub-problems:

1. Calculate the normal property transfer tax;
2. If the user qualifies to have exemption, calculate the exemption amount; otherwise, the exemption amount is 0.

Then the actual transfer tax payable can be calculated as normal amount minus the exemption amount.

There is NO assignment following this lab.