It's two days before your taxes are due. You have a big box of receipts, pay stubs, invoices, and forms. And you don't want to pay another late fee for filing after the deadline. What do you do?
You could spend hundreds or thousands of dollars on an emergency tax session with an accountant. Or you could leverage the power of Microsoft Excel to get everything in order. Here are 5 formulas that can make your life easier during tax season.
1. Using VLOOKUP for Tax Tables
The VLOOKUP formula has a very useful optional operator. If that operator is set to FALSE, the formula will return an error if the value that you searched for doesn't show up.
If it's set to TRUE, however, it will return the next smallest number. This is perfect for tax tables. Here's a hypothetical tax table:
Let's say you need to get tax information for three different people. This means you'll need to make the same calculation on three different incomes. Let's use VLOOKUP to speed up the process. Here's the syntax we'll use:
=VLOOKUP(A2, A1:B6, 2, TRUE)
A2 is the income amount, A1:B6 is the range of cells that contains the tax rates, The number 2 indicates that we want values from the second column returned, and TRUE tells the formula that it should round down if it doesn't find an exact match.
Here's what happens when we run it on cells that contain $37,000, $44,000, and $68,000 for income values:
As you can see, the formula returned the proper tax rate for all three. Multiplying the tax rate by the total income is simple, and will give you the amount of tax that you owe on each amount.
It's important to remember that VLOOKUP rounds down if it doesn't find the exact value it's looking for. Because of this, if you set up a table like the one I have here, you need to have the maximum income listed in the range.
VLOOKUP can be extremely powerful. You can use the power of many Excel formulas to do some pretty amazing things.
2. The IF Formula for Multiple Thresholds
Some tax credits depend on how much money you've made. For these credits, nesting IF statements and other boolean operators can make it easy to figure out exactly how much you can claim back.
We'll use the Earned Income Credit (EIC) to create an example. I've highlighted the relevant part of the EIC table here (the four rightmost columns are for married couples filing jointly, and the four to their left are for single filers):
Let's write a statement that will determine how much we can claim back via EIC:
=IF(AND(A2 >= 36800, A2 < 36850), 1137, IF(AND(A2 >= 36850, A2 < 36900), 1129, IF(AND(A2 >= 36900, A2 < 36950), 1121, IF(AND(A2 >= 36950, A2 < 37000), 1113, "double check"))))
Let's break this down a bit. We'll take just a single statement that looks like this:
=IF(AND(A2 >= 36800, A2 < 36850), 1137, 0)
Excel first looks at the AND statement. If both of the logical operators in the AND statement are true, it will return TRUE and then return the [value_if_true] argument, which in this case is 1137.
If the AND statement returns FALSE (if A2 = 34,870, for example), the formula returns the [value_if_false] argument, which in this case is 0.
In our actual example, we've used another IF statement for the [value_if_false], which lets Excel keep running through IF statements until one of them is true.
If your income makes it through the final statement without being in any of those ranges, it will return the string "double check". Here's what it looks like in Excel:
In many cases, you can use VLOOKUP to speed this process up. However, understanding nested IF statements can help you in many situations that you're likely to come across. And if this is something you do often, you could build a financial spreadsheet template with these kinds of formulas built-in for reuse.
3. Calculating Interest Paid With ISPMT
Knowing how much interest you've paid on a loan can be valuable when you're doing your taxes, but if your bank or lender doesn't give you this information it can be hard to figure out. Fortunately, providing ISPMT with a bit of information will calculate it for you. Here's the syntax:
=ISPMT([rate], [period], [nper], [value])
[rate] is the interest rate per payment period, [period] is the period for which the interest will be calculated (for example, if you've just made your third payment, this will be 3). [nper] is the number of payment periods that you'll take to pay the loan off. [value] is the value of the loan.
Let's say you have a $250,000 mortgage that has an annual interest rate of 5%, and that you'll pay it off in 20 years. Here's how we'll calculate how much you've paid after the first year:
=ISPMT(.05, 1, 20, 250000)
When you run this in Excel, you get a result of $11,875 (as you can see, I've set this up as a table and selected the values from there).
If you use this for monthly payments, be sure to convert the annual interest rate to monthly interest. For example, finding the amount of interest paid after the third month of a one-year loan of $10,000 and an interest rate of 7% would use the following formula:
=ISPMT((.7/12), 3, 12, 10000)
4. Turning Nominal Interest Into Annual Interest With EFFECT
Calculating the actual annual interest rate of a loan is a great financial skill to have. When you're given a nominal interest rate that compounds a number of times throughout the year, it can be tough to know exactly what you'll be paying. EFFECT will help you figure it out.
=EFFECT([nominal_rate], [nper])
[nominal_rate] is the nominal rate of interest, and [nper] is the number of times the interest is compounded throughout the year. We'll use the example of a loan with a nominal interest rate of 7.5% compounded quarterly.
=EFFECT(.075, 4)
Excel gives us 7.71%, the effective annual interest rate. This information can be used with a number of other formulas that use interest rates to determine how much you've paid or how much you owe. It can also be useful if you're using Excel to set up a personal budget.
5. Depreciating Assets With DB
Excel includes a number of different depreciation formulas, but we'll take a look at DB, the fixed declining balance method. Here's the syntax:
=DB([cost], [salvage], [life], [period])
The [cost] argument represents the initial cost of the asset, [salvage] is the value of the asset at the end of the depreciation period, [life] is the number of periods over which the asset will depreciate, and [period] is the period number that you want to get information for.
Interpreting the results of the DB statement can be a bit complicated, so we'll look at a series of data. We'll take an asset with an initial cost of $45,000 that will depreciate to $12,000 over the course of eight years. Here's the formula:
=DB(45000, 12000, 8, 1)
I'll be iterating this formula eight times, so the final argument will be 1, 2, 3, 4, 5, 6, 7, and 8 in consecutive rows. Here's what happens when we do that:
The number in the Depreciation column is the amount of value that's been lost. So to see the value of the investment at the end of the year, you'll need to subtract the number in the Depreciation column from the investment's value at the beginning of that year.
To get the value at the end of year one, we subtract $6,840 from $45,000 and get $38,160. To get the value at the end of year two, we subtract $5,800.32 from $38,160 and get $32,359.68, and so on.
Excel at Doing Your Taxes
These five formulas are among the huge number available and should help you get a good start on using the power of Excel to get your taxes done. If you're more comfortable why not learn how to record a macro in Excel and let your sheets do the work for you?
If you're not a fan of Excel, you can also use the money management tools in Google Drive. And don't forget that there are a lot of other great resources out there, including quite a few useful tools from the IRS and a wide range of downloadable Excel programs.