Doing Your Taxes? 5 Excel Formulas You Must Know

It’s two days before your taxes are due.

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 Excel to get everything in order.

Using VLOOKUP for Tax Tables

The VLOOKUP function has a very useful optional operator; if that operator is set to FALSE, the function 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:

excel-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, 2 indicates that we want values from the second column returned, and TRUE tells the function that it should round down if it doesn’t find an exact match.

Here’s what happens when we run it on cells that include $37,000, $44,000, and $68,000 for income values:

excel-vlookup-taxes

As you can see, it 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 maximums from applicable ranges, and not the minimums.

VLOOKUP can be extremely powerful; check out Ryan’s article on Excel formulas that do crazy thingsto get a glimpse at what it’s capable of!

The IF Function for Multiple Thresholds

Some tax credits depend on how much money you’ve made; in this case, nesting IF statements and other boolean operatorscan 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):

earned-income-credit

Let’s write a statement that will determine how much we can claim back via EIC:

=IF(AND(A2 >= 36800, A2 = 36850, A2 = 36900, 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 function 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” to remind you that something is off. Here’s what it looks like in Excel:

excel-nested-if-taxes

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 templatewith these kinds of functions built in for reuse.

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).

excel-ispmt-taxes

If you use this for monthly payments, be sure to convert the annual interest rate into a monthly one. 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)
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 tell you.

=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 functions 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.

Depreciating Assets with DB

Excel includes a number of different depreciation functions, 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:

excel-db-taxes

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 Taxes

These five functions are among the huge number that are available, and should help you get a good start on using the power of Excel to get your taxes done. 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 IRSand a wide range of downloadable Excel programs.

If you use Excel to do your taxes, please share your tips below! We’d love to hear which functions you use the most. And if you want to use Excel for taxes, and you aren’t sure how to do something, leave a comment with a question and we’ll do our best to answer.

Thanks for subscribing. We've sent you a confirmation link you'll need to click on to verify your email address.

Join live MakeUseOf Groups on Grouvi App Join live Groups on Grouvi

Windows Tips