VBA Payroll Program

The Problem

I work for a small mortgage company licensed in a handful of the western states. Outside of my normal responsibilities of loan origination, I am in charge of our paid online-advertising. One of the challenges our company faces is knowing how much money is spent on paid advertising, how efficient our advertising money is, and if we are making enough to pay for all other business expenses.

My manager approached me with the task of creating a collaborative method of keeping a record of each loan closed, how much revenue it brought in, along with an allocation into different accounts of the revenue received. Also, the passing of the Dodd-Frank Act requires that our company keep a record of employee compensation plans, showing that our company is not involved in predatory lending (charging borrowers differently for their loans when each borrower should be charge a set amount).

A payroll spreadsheet was also in need. The program I developed created a method to determine payroll numbers along with the needed records stated above. No two transactions are exactly the same, so specific variables needed to be taken into account. Each variable and their implication can be summed up in the following table.

Broker	Each broker has his or her own compensation plan. Depending on the state in which each broker is employed, appropriate taxes need to be withheld.
State	Each state in which the company is licensed has its own set of laws and regulations. The state must be noted and appropriate documentation made accordingly following payroll.
Method of compensation	The company receives payments through two methods: “lender-paid” and “borrower-paid”. Lender paid means that the lender pays our company directly a predetermined percentage of the loan amount. Borrower paid allows for negotiations to be made in the fees charged, and the amount can be lowered from a set percentage of the loan amount.
Lender used	Each lender we work with has different percentages for the amount of the “lender paid” compensation.
Reception of lead	Remaining revenue (after paying the broker and plowing back necessary funds into the business) is set aside into two holding accounts. One account shows the balance of funds that can be used for future paid advertising. The other account shows the balance of excess funds the broker has for finding a lead on their own.

The Solution

I created the needed payroll spreadsheet program using VBA to keep track of each loan closed by the company. The program allows my manager to input necessary information to keep a record of each transaction, and then choose between the different variables which play into the specifics of the revenue allocation. The program determines how much revenue the company receives depending on the method of compensation, how much should be paid to the employee, and then ensures that a designated amount is set aside for necessary company expenses before the excess revenue is allocated into the two different holding accounts.

My manager has been using the program for over a month, and has found it to be “perfect.” He now has the ability to track how effective our advertising costs are, and how much excess revenue can be spent on future advertising. More importantly he knows how the company revenue is allocated, and that the business is covered for its necessary expenses. 

Written Report (PDF file)

Excel File - VBA Payroll Program