Applications Programming --- Lab 8

Objective:

• Learn to develop a VBA program with (nested) loop statements

References:

• Lab 7 referneces
• VBA Repetition (III)
• VBA Repetition (IV)

Problem Description

A hypothetical province uses the following rule to calculate your provincial income tax:

• 5.06% on the first $42,184 of taxable income, plus
• 7.7% on the next $42,185 (i.e., the amount from $42,184 to $84,369), plus
• 10.5% on the next $12,497 (i.e., the amount from $84,369 to $96,866), plus
• 12.29% on the next $20,757 (i.e., the amount from $96,866 to $117,623), plus
• 14.7% on the next $41,860 (i.e., the amount from $117,623 to $159,483), plus
• 15.3% on the next $62,937 (i.e., the amount from $159,483 to $222,420), plus
• 16.2% on the next $34,000 (i.e., the amount from $222,420 to $256,420), plus
• 16.8% on the next $41,000 (i.e., the amount from $256,420 to $297,420), plus
• 17.5% on the next $55,000 (i.e., the amount from $297,420 to $352,420), plus
• 18.6% on the next $63,740 (i.e., the amount from $352,420 to $416,160), plus
• 20.5% on the amount over $416,160.

For example, if a person's taxable income is $90,000, then the provincial tax he/she needs to pay is calculated as:

    first $42,184 *  5.06% = $2,134.5104
     next $42,185 *  7.70% = $3,248.2450
remaining  $5,631 * 10.50% =   $591.2550
--------------------------------------
total $80,000 to pay tax:    $5,974.01

Your Tasks:

Download the Lab 8's input file.

In the input file, the tax rate parameters are stored in the yellow area. And starting from row 20, there are a list of customer's information, including customer's taxable income.

Develop a VBA program that calculates each customer's provincial tax based on the customer's taxable income and writes the result to the corresponding cells of column C.

Note: Your program must use a loop statement (instead of an extended if-else statement) to decide which range the user's taxable income falls into.

For the lab exercise, I provided the answer for each customer in column D. You can use the sample answer to check whether your program works correctly.

There is a weekly Assignment 8 following this lab.