Pinterest Stumbleupon Whatsapp
Ads by Google

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.

Ads by Google

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 things 3 Crazy Excel Formulas That Do Amazing Things 3 Crazy Excel Formulas That Do Amazing Things I have always believed that Excel is one of the most powerful software tools out there. It's not just the fact that it's spreadsheet software. No, Microsoft Excel 2013 simply has an awesome collection of... Read More  to 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 operators Mini Excel Tutorial: Use Boolean Logic to Process Complex Data Mini Excel Tutorial: Use Boolean Logic to Process Complex Data Logical operators IF, NOT, AND, and OR, can help you get from Excel newbie to power user. We explain the basics of each function and demonstrate how you can use them for maximum results. Read More 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):

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), 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 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 template 10 Helpful Spreadsheet Templates To Help Manage Your Finances 10 Helpful Spreadsheet Templates To Help Manage Your Finances Wouldn't it be great if you knew where your money was, at all times? Read More with 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 Make a Personal Budget on Excel in 4 Easy Steps Make a Personal Budget on Excel in 4 Easy Steps Read More .

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 10 Money Management Tools Inside Google Drive You Should Use Today 10 Money Management Tools Inside Google Drive You Should Use Today The problem with money is that if you don't manage it, you end up without it. How about some useful money management tools to help you get started right inside your Google Drive account? Read More . And don’t forget that there are a lot of other great resources out there, including quite a few useful tools from the IRS 7 IRS Website Tools That Could Save You Time And Money 7 IRS Website Tools That Could Save You Time And Money Here are a few online IRS tools for U.S. citizens diligently slaving away over their taxes. They make your job a lot easier. Don't give up just yet. Read More  and a wide range of downloadable Excel programs Top 3 Websites To Download Useful Free Excel Programs Top 3 Websites To Download Useful Free Excel Programs Read More .

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.

  1. Consultant
    May 22, 2016 at 8:10 am

    Can you post excel calculations for set off of business loss as per Indian Tax Laws. That is carry forward eligible for 8 years, losses not utilized within the period expires.

    Regards,

    • Dann Albright
      May 25, 2016 at 12:56 pm

      That's an awfully specific request! I don't know anything about Indian tax laws, but someone else might be able to help you out.

  2. Chinmay S
    April 12, 2015 at 3:16 am

    I am a programmer, I use PHP to generate dynamic PDF's for invoices. I have created a whole system that can do the hard work for me. All I need to do is click a couple of buttons and everything gets generated.

    • Dann Albright
      April 19, 2015 at 3:39 pm

      You've created a PHP system that will do your taxes, you mean? Or create invoices? I imagine that would be a really useful system!

    • bharat Agarkar
      February 20, 2016 at 5:54 am

      hey bro..
      really appreciated with ur work..

  3. dragonmouth
    April 10, 2015 at 11:42 pm

    Very timely indeed. By the time I figure out how to use these functions, my return will be past due. This article could have been useful in January.

    • Dann Albright
      April 11, 2015 at 6:20 am

      It's a bit late for people in the States, true, but people around the world have different filing dates. And this article will still be here next year if people decide they'd like to use it.

  4. Zhong
    April 10, 2015 at 8:59 pm

    I have never done my own taxes before. I don't understand why you would need to use Excel for your tax purposes except from a business perspective.

    • dragonmouth
      April 10, 2015 at 11:50 pm

      "I don’t understand why you would need to use Excel for your tax purposes "
      Excel in this case is being used as a glorified calculator.

      IIRC, there are sets of Excel spreadsheets that you can use to do your taxes but they are nowhere near as slick as TurboTax, TaxCut or TaxAct. Even the Linux tax calculating program Open TaxSolver is less clumsy to use than Excel spreadsheets.

    • Dann Albright
      April 11, 2015 at 6:19 am

      Zhong, if you've never done your own taxes, I'm not surprised that you don't know why you'd need to use Excel. As dragonmouth points out, there are many different pieces of tax software that people use, both as individuals and as businesses, to do their taxes; some prefer to use Excel because it's how they keep their records already, and because it's free (well, if you already own a copy), no matter how complicated of a tax situation they're in. It's also an extremely powerful program, and with some time, can help a lot with complex calculations.

  5. Howard Pearce
    April 10, 2015 at 6:19 pm

    Paying my taxes . You mean where the state takes my money/property which is rightfully mine (not contracted for already) without my permission ?

    I always called that theft .... the fact the state does it means nothing.

    • Dann Albright
      April 11, 2015 at 6:18 am

      Please feel free to think of this article as being titled, "Dealing with State-Sponsored Theft? 5 Excel Formulas You Must Know". :-)

    • Howard Pearce
      April 11, 2015 at 8:37 am

      LOL,

      OK, I do understand. But I like to take some of these times to make my point from time to time.

    • Tina
      April 17, 2015 at 11:36 am

      Curious now, where do you live, Howard?

      Don't you get anything back for your taxes? Who paid for the roads you're driving on, the schools you went to, maintenance of landmarks, monuments, parks, and all the other public infrastructure and services most of us depend on?

      It may feel like theft because you personally didn't agree to paying for all this, but you are inevitably using and benefiting from it.

      I get your sentiment though. This was all once decided, with or without public consent, and it feels forced and wrong in many cases. And I bet we can disagree on what's justifiable and what's just outrageous over spending (military?).

    • Howard Pearce
      April 17, 2015 at 3:49 pm

      I use them because they are public property and the state says they are free to use. If they want money for the use, they should consider charging for it.
      Does that mean I should forgive them for theft because people view some things as necessary ? I'd consider a plan to privatize the roads.

      As for getting taxes back, I suppose I could steal $100 from you and give you $10 back if that would make you feel good.

      Keep in mind too that I oppose all coercive monopolies ..... including the state.

      And I live in Florida, U.S. :)

    • Tina
      April 19, 2015 at 11:36 am

      I live in a social democracy and (to some degree) I'm totally fine with paying taxes. I think a lot of people would be worse off if they had to pay for everything themselves, i.e. if there was no public property or services.

      It's got a lot to do with trust. As long as people trust that the state is acting in the people's best interest, people will be happy to pay taxes. As soon as people get wind of corruption and big corporations running the show to push their limited interests, that trust is lost.

      Overall, the world is a better place if people can trust. Just look at countries where trust is high. They tend to also have higher levels of well being and happiness (e.g. Denmark or Bhutan). Note that this is not directly correlated with affluence, although Denmark is a pretty wealthy country.

      Anyway, I can see why people don't want to pay taxes, if they don't feel like their needs are being taken care of by those who receive the money. I would hate to pay money to a malicious Mafia for example, but I'm fine paying an entity that supports me in meeting my needs / goals and makes me feel safe. In some places, government may feel more like the Mafia than the well meaning organization it was meant to be.

  6. Jim
    April 10, 2015 at 5:53 pm

    2 days before taxes are due?

    • Dann Albright
      April 11, 2015 at 6:17 am

      I was trying to get readers to imagine the stress of this particular situation. Taxes aren't actually due in the States for another four days (at the time of this comment). But people do their taxes all around the world, and there are many different deadlines, so it could actually have been accurate for someone out there!

Leave a Reply

Your email address will not be published. Required fields are marked *