Budgets and Goals on Mint.com

My wife and I have used Mint.com for personal finance management for over three years now. While it is not perfect, it is low-maintenance, fairly intuitive, and completely free. After signing up, Mint accesses the transactions from your financial accounts and helps you categorize income and expenses, establish budgets, set goals, and generally stay on top of your personal finances.

Many Mint users have expressed frustration at understanding the relationship between Mint's Budgets, fund transfers reporting, and Goals; this is one of the areas of Mint that is not always intuitive. Hopefully, the following explanation helps.

The first thing to understand is how income, expense, and transfers are handled in Mint. When you deposit your paycheck or pay for movie tickets with a debit card, you have income and expense respectively; Mint's treatment of cash coming in from or going out to an outside source is straightforward.

If you pay for a movie with a credit card, and pay off the credit card at the end of the month, Mint records three transactions: the charge to the credit card, the debit to the checking account when the payment is made, and the credit to the credit card account when the payment is received. The net result is the movie expense; the two payment transactions, if properly classified as a Transfer, net to zero. Everyday income, expense, and credit card transactions are grouped in the Cash and Credit section of the Transactions menu and appear on Mint's Budgets.

All payments or transfers from one account to another, where both accounts are Cash and Credit accounts, should be categorized as a Transfer and net to zero. If you have a loan such as a mortgage, activity on the mortgage account will appear on the Loans section of the Transactions menu, as opposed to Cash and Credit. Payments from your checking account to your mortgage account should be categorized as an expense, as opposed to a Transfer, and will appear as a cash outlay in Budgets.

In contrast to Mint's treatment of a traditional loan, suppose a large emergency expense occurs in one month, such as a medical bill, and you charge it to a credit card and plan on carrying a balance. In this situation, the "loan" will appear as a large expense in one month and the payments will be categorized as a Transfer in subsequent months. In other words, there will be no budgeted cash outlay in Budgets in subsequent months; each time a payment is made on the credit card, there will still be a debit to the checking account when the payment is made and a credit to the credit card account when the payment is received.

If you classify the debit to the checking account as an expense so that it shows up on Budgets as a cash outlay, the credit to the credit card account will still be listed as a Transfer and ignored on the Budgets, and your budget over time will ultimately count the large expense twice. The initial credit card charge will be an expense in month one, and each subsequent credit card payment will also be an expense. You are faced with either double counting your expense over time or not being able to effectively budget for the cash outlay each month a payment is made. The solution to this problem may be Goals, which I will discuss in my next post.

Read More

Educational Assistance Programs

Companies who hire students or potential students can offer educational benefits that would be more beneficial to both employer and employee than a standard wage offer would be. This can be accomplished through the fringe benefit known as an Educational Assistance Program.

An Educational Assistance Program allows an employer to provide an employee assistance in paying for tuition, fees, books, and supplies for education. Benefits provided under this program will not be subject to income or employment taxes, and the first $5,250 will be excluded from the employee's income each year.

The requirements are as follows: (1) The program must benefit employees who qualify under rules set up by the employer but that do not favor 5%-or-more owners or employees earning more than $115,000; (2) the program may not provide more than 5% of its benefits during the year for shareholders or owners; (3) the program may not allow employees to choose to receive cash or other benefits that must be included in gross income instead of the educational assistance, (4) the employer must give reasonable notice of the program to eligible employees, and (5) the program must be in writing.

To illustrate how such a program can be better for both an employer and an employee who is indifferent about receiving wages or educational assistance as compensation, assume that an employer is contemplating offering an employee an annual raise of $5,000. Were this raise to come in the form of a salary increase, the employer would need to first deduct 6.2% for social security, 1.45% for medicare, and some amount for income tax withholding, say 5%, for a total of $632.50, leaving the employee with $4,367.50. The employer must pay out, in addition to the $5,000, an additional $382.50 for the employer portion of social security and medicare, for a total of $5,382.50.

In contrast, if the annual raise is in the form of educational assistance, neither the employee nor the employer is subject to any of the above taxes. If the employer offers a raise of $5,250.00, the total payout will be $5,250.00, a savings of $132.50. The employee will have $5,250.00 of his or her educational expenses paid for, a net benefit of $882.50 compared to receiving a salary and paying for tuition with after-tax dollars.

Certainly, an educational assistance program isn't appropriate in all cases, but it can provide savings and incentives to both the employer and employee for whom the situation is right.

Read More

Valuation Discounts

One of the primary objectives of estate planning is to arrange for the transfer of wealth to the next generation at the lowest possible cost. For large estates, the most significant cost is the gift and estate tax. These two tax regimes are essentially a single tax imposed on total lifetime gifts plus the value of property transferred at death. As mentioned in a previous post, gifting during lifetime can be part of an estate planning strategy.

For a gifting example, assume a gift tax rate of 40% and a donor who has previously utilized his or her entire tax exemption and who desires to make a gift of $1,000,000 of a $4,000,000 investment in a publicly-traded company.

After making the gift of the $1,000,000 asset, the donor will pay a $400,000 gift tax. Obviously, a key factor in the calculation of the gift tax is the valuation of the stock that is the subject of the gift. In this case, the valuation is straight-forward because the stock is easy to sell and has a ready market.

However, consider the gift of a small, privately-owned family business. In this case, the value of the asset will reflect the fact that there is not a ready market for the business; it is more difficult to sell. In addition, the value of a minority interest in a private business will reflect a lower value if the owner does not have managerial control.

For planning purposes, both the "lack of control" and "lack of marketability" discounts can be effectuated in not only the small, privately-owned family business context, but also for nearly any other asset. For example, suppose that the owner of the $4,000,000 stock investment first transfers the stock into a limited partnership. Subsequently, if the owner transfers a 25% limited partnership interest to a donee, the value of the gift for gift tax purposes will be less than $1,000,000.

This is because there is not a ready market for a privately-owned partnership interest. Furthermore, instead of owning $1,000,000 worth of publicly traded stock outright, the donee merely owns a 25% limited interest in a private partnership. Since the donee lacks managerial control over that interest, it does not matter that the underlying asset is publicly-traded stock; the lack of control discount would apply in addition to the lack of marketability discount.

If the total valuation discount in this case works out to be 30%, this results in a gift valuation of $700,000 instead of $1,000,000. This results in an accompanying gift tax of $280,000 instead of $400,000, an immediate cash savings of $120,000 simply by utilizing the limited partnership.

Source: Valuation, Jonathan C. Lurie and Edwin G. Schuck, Jr., The American Law Institute - American Bar Association Continuing Legal Education, 2008

Read More

Basic Visual Basic for Excel Part IV

This is the fourth and last post in my introduction to learning and utilizing Visual Basic in Excel. As you may have guessed or experienced, writing Visual Basic code from scratch is extremely difficult. Realistically, there are simply too may classes, properties, commands, and syntax rules to learn before a beginner can automate a task by coding from a blank slate. Fortunately, there is an easier way.

If you need to automate a repetitive task in Excel, consider recording a macro, whereby the code for the actions you take is written automatically. The Record Macro button is located in the Macros menu, which is within Excel's View menu. The "Use Relative References" button at the bottom toggles between relative and absolute references on your spreadsheet. If the task you are trying to automate will always occur at the same location on the spreadsheet, make sure Use Relative References is off. However, if the task you are trying to automate will happen at different locations on the spreadsheet, highlight Use Relative References.

One task I find myself repeating a lot, when working with a filtered table in Excel and after having implemented multiple filters, is needing to unfilter the whole table. To do this, I usually just click the Filter button from the Data menu to turn off filtering, select the whole sheet, and click the Filter button again. To demonstrate how to automate this repetitive task, I will record a macro. In order to follow along, you will need a filtered table in Excel (if you need a quick data set, feel free to copy the entity table I used in this previous post).

After filtering one of the columns in your table, click View, from the Macros menu click Record Macro, select a shortcut key (such as Ctrl+Shift+A), and press OK. Everything you do until you click Stop Recording from the same Macros menu will be automatically coded into a new module. While recording, first unfilter the entire sheet by clicking View, Filter; second, select the entire sheet by clicking in the blue box to the top left of A1; and third, click Filter again. Press the Stop Recording button, and within your Visual Basic editor (click Alt+F11 to get there) you should have the following code in Module1:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Selection.AutoFilter
    Cells.Select
    Selection.AutoFilter

End Sub

From the spreadsheet, you are now able to unfilter the entire table easily by clicking Ctrl+Shift+A each time. This has been an example of recording a macro, and this concludes my four-part primer on how to automate tasks in Excel with Visual Basic. I anticipate posting subroutines that I use from time to time, but feel free to comment or contact me with your coding challenges or successes; I may address them in a future post as well.

Read More

Basic Visual Basic for Excel Part III

This is the third post in a series explaining how I use Visual Basic in Microsoft Excel to increase productivity. In my previous post, I discussed the If Then statement as well as different ways to reference cells on a sheet; in this post, I will discuss the Do Loop.

The Do Loop allows you to repeat the same block of code until a condition is satisfied. To demonstrate, I will be using the client entity table from this earlier post; this Do Loop will concatenate a list of all a client's entities into a single field.

After recreating the client entity table in your own spreadsheet, select blank cell G2.  The code below will list all the entities in the active cell, starting with the entity name in the same row as the active cell, and ending with the last entity name before the client's name changes.

Before moving further, however, it will be important to declare a variable that can be used to keep track of which of the client's entities will appear next on the list. A good practice when declaring any variable is to use Option Explicit (for an explanation why, see this article). Declaring a variable is accomplished by typing "Dim" followed by the name for the variable you select, followed by the data type, which can be an "Integer" in this case (other data types are capable of storing different sizes or kinds of data). After declaring the existence of the variable "r," we assign it a value of 0 for reasons described below. After doing all of this, I have the following as the starting point for my code:

Option Explicit
Sub concatenateEntities()

    Dim r As Integer
    r = 0

End Sub

With "r" set to zero, we will follow with our Do Loop. The basic Do Loop to accomplish the procedure described above is as follows:

    Do Until ActiveCell.Offset(0, -6) <> ActiveCell.Offset(r, -6)

        ActiveCell.Value = ActiveCell.Value & "; " & ActiveCell.Offset(r, -5)
        r = r + 1

    Loop

By running the macro with cell G2 selected on a spreadsheet containing my entity table, the active cell should now read as follows: ";  ABC Corporation;  Doe Family Partnership;  Doe Rental Property, LLC;  Doe Equipment, LLC". Here is how this was accomplished:

The "Do Until" line tells the computer to repeat the code that follows until it says "Loop," repeating until the client name changes. It starts by comparing the value in column A in the same row as the active cell with the value in column A in each subsequent row, one at a time. The "r = r + 1" line keeps increasing the count of the row after the prior line sets the active cell equal to whatever it's current value, followed by a semicolon, followed by the value of the cell in column B that contains the next entity name on the list.

Notice that we probably don't want to have the first semicolon that appears in the active cell, which occurs due to the fact that the first time the code sets the active cell equal to itself before adding the semicolon, the active cell has nothing in it. See if you can use an If Then statement to fix that problem; I will post my solution in the comments that follow. For a tutorial on recording a macro, please stay tuned for my next installment of Basic Visual Basic for Excel.

Read More

Basic Visual Basic for Excel Part II

In a previous post, I demonstrated how to create and execute a very simple macro in Microsoft Excel. In this post, I will demonstrate a number of fairly simple enhancements to that macro. The code we will start with is as follows:

Sub lastRun()

    ActiveCell.Value = "Subroutine last run at " & Time()

End Sub

Before beginning, we should assign a shortcut to the macro to make it easier to run. This is accomplished by clicking Alt+F8 from within Excel, selecting the lastRun macro, clicking "Options," and assigning a shortcut key such as Ctrl+Shift+A. Now you can run the macro from Excel simply by clicking Ctrl+Shift+A.

The first enhancement to lastRun is to have the macro enter the time it was last run not in the active cell, but in a cell relative to the active cell on the spreadsheet:

    ActiveCell.Offset(2, 4).Value = "Sub last run at " & Time()

The numbers following "Offset" are the number of respective rows and columns from the active cell into which the time will be entered. Positive numbers offset rows down and columns to the right, while negative numbers offset rows up and columns to the left.

We can also refer to cells without reference to whatever cell is currently selected; in the "Cells" reference below, the row number of the spreadsheet appears first and the column number follows. Of course, the cell we are changing can be set equal to any value we describe as well. Type a message into cell A1 on your spreadsheet; then try the following code:

    Cells(2, 1).Value = "Cell A1 said " & Cells(1, 1).Value & " when the sub ran at " & Time()

Another useful function in Visual Basic is an If Then statement, which works similar to the IF function in Excel. Here is an example of how the lastRun function can run an exception if cell A1 is blank by using an If Then statement:

    If Cells(1,1) <> "" Then

        Cells(2, 1).Value = "Cell A1 said " & Cells(1, 1).Value & " when the sub ran at " & Time()

    Else

        MsgBox "Cell A1 is blank!", vbOKOnly, "For your information..."
        Cells(2, 1).Value = "Cell A1 was blank at " & Time()
        'This is a sample comment in VB

    End If

Note that it is good practice to insert comments throughout your code, as above, so it is easy to remember what the section is supposed to accomplish; the first character of a comment line is an apostrophe, followed by your notes. Another tip is to use blank lines and tabs often to separate and group different sections of your code. In my next post, we will examine the Do Loop, a powerful tool in Visual Basic for people who use Excel frequently.

Read More

Basic Visual Basic for Excel

This will be the first post in a series of posts explaining how to use Visual Basic with Microsoft Excel to accomplish a number of useful tasks. While I am by no means a programmer, I hope I can demonstrate some practical ways to use Visual Basic to help you increase your productivity on a PC.

The first step after opening Excel is to open the Visual Basic Editor. This is accomplished by clicking on the Developer tab within Excel and clicking the Visual Basic button. Alternatively, you can press Alt+F11 to open the editor.

The next step is to insert a new module. In the column on the left, the Visual Basic Editor displays the various Excel spreadsheets that are open. Make sure that the spreadsheet you want the code associated with is selected, and click the Insert menu and select Module. This will bring up a blank screen into which you will type your code.

The next step is to create a subroutine within the module window. This is accomplished by typing "Sub" followed by the name of subroutine followed by "()". The name can be almost anything you want, but it can't have spaces and should describe what the code does so that you can select the subroutine you want from a potentially long list. For a very simple example, I will create a subroutine named lastRun; it will display the time that the subroutine was last executed. Type the following to set up the lastRun macro (the "End Sub" usually appears automatically):

    Sub lastRun()

    End Sub

Between these two lines will be the rest of the code, which in the lastRun example, will be the following:

    ActiveCell.Value = "Subroutine last run at " & Time()

The lastRun subroutine will change the value of the active cell, or the cell that is highlighted on the spreadsheet, to the text in parentheses followed by the time that the subroutine was run. In order to run the subroutine, save your spreadsheet as an .xlsm file (always, always save before testing new code!) and then click the green triangle "Play" button at the top of the Visual Basic Editor. Alternatively, from within the spreadsheet, click View, Macros, make sure lastRun is highlighted, and click the Run button. Note that the text and time appear in whatever cell on the spreadsheet is highlighted when the macro is run.

This has been an introduction to utilizing Visual Basic with a very simple subroutine; in my next post, I will show how to increase the functionality of this simple bit of code.

Read More