Faculty of Business and Information Technology
Assignment 3
Value: 7.5%, Due: Nov. 18th @ 11:59 PM
This assignment should be completed individually. We take academic integrity seriously. Any
student found to be involved in plagiarism or cheating will be penalized in accordance to the
Ontario Tech University Calendar, Section 5.15.
To assert that you have not given or received, or used unauthorized assistance, write the following
pledge in the comment textbox when you submit your files through Canvas system.
"I have not given, received, or used any unauthorized assistance."
Durham Auto (100 Points) Start-up file needed for this exercise: None
Durham Auto is a family-owned new and used car dealership, specializing in quality GM models for every
udget and style. You have been recently hired as a salesperson and you decided to create a simple VBA
application to help you calculate auto loan payments based on the following information.
The interest rates and periodic payments are calculated as follows:
1) Interest Rate
• If the loan term is less than or equal to 3 years, the interest rate is 1.5%.
• If the loan term is greater than 3 years but less than or equal to 5 years, the interest
ate is 2.5%.
• If the loan term is greater than 5 years, the interest rate is 4%.
2) The loan payment calculations done using a VBA function called Pmt(Rate, NPER, PV).
Rate: is a number specifying interest rate per period. For example, if you get a car loan at an
annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is
0.1/12, or 0.0083
NPER: is a number specifying total number of payment periods in the annuity. For example, if
you make monthly payments on a four-year car loan, your loan has a total of 4 * 12 (or 48)
payment periods.
PV: is an amount specifying present value (or loan amount). For example, when you bo
ow
money to buy a car, the loan amount is the present value to the lender of the monthly car
payments you will make.
Note: for the bi-weekly payment you must divide the rate by 26 and multiply the term by 26.
Also, for the weekly payment you must divide the rate by 26 and multiply the term by 26.
For your reference you can use the Assignment3.xlsm file attached as you complete this assignment.
Your task is to create an application similar to this file. Note that the code in this file is password-
protected.
Task 1: Prepare Excel Macro-Enabled file
Refer to Fig. 1 as you complete Task 1.
(a) Create a Macro-Enabled workbook named Durham Auto.xlsm. Insert a Documentation
worksheet and add appropriate title for the workbook in cell A1. In cell B3 enter your name.
In cell B4 enter your student number. In cell B5 enter the completion date of your assignment
and in cell B6 enter a sentence to describe the purpose of the workbook. In cell A100, enter
your ontariotechu.net email address.
(b) Add a new worksheet called Auto Loan Calculator.
(c) Add a button (from the ActiveX Control group). Change the caption to display Open Calculator.
(d) Add a button (from the ActiveX Control group). Change the caption to display Reset Worksheet.
(e) Create a reporting area similar to Fig. 1 (the company logo is included in the assignment document).
Save your file.
Task 2: Create Income Tax Calculator User Form
Refer to Fig. 2 as you complete Task 2.
Create a user form similar to the design shown in Fig. 2. Be sure to change the user form caption to
Durham Auto Financing Calculator (the company logo is included in the assignment document).
Fig. 1: Initial Setup
Fig. 2: Durham Auto Loan Calculator User Form
Task 3: Write codes for each button
Refer to Fig. 3 as you complete Task 3.
(a) The Calculate Payment button should compute and display the monthly, bi-weekly, and weekly
payment amounts based on the information provided by a user (do not forget to format this value).
(b) The Reset Form button should clear the user form (three textboxes and three labels).
(c) The Generate Report button compute should display the required information onto the Auto Loan
Calculator worksheet as shown in Fig. 3.
(d) The Exit button should close the user form.
Task 4: ActiveX Buttons and E
or Handling
(a) The Open Calculator ActiveX control button should open the Auto Loan Calculator user form.
(b) The Reset Worksheet ActiveX control button should clear the contents of the worksheet (C13:C16
and C19:C21).
(c) The Auto Loan Calculator user form should have an e
or handling mechanism to avoid application
malfunction due to empty textboxes. For example, if any of the textboxes is empty, then the program
should display an e
or message as shown in Fig. 4.
(d) Test your application. Save and close your workbook.
NOTE: ALL CALCULATION SHOULD BE DONE THROUGH VBA.
Fig. 3: Durham Auto Loan Calculator Sample Run
Fig. 4: Durham Auto Loan Calculator E
or Message
Submission Guideline:
Using the Canvas system, please submit your ONE solution file by no later than Nov. 18th, 2020
at 11:59PM.