# Doing Your Taxes? 5 Microsoft Excel Formulas You Must Know

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
3 Crazy Excel Formulas That Do Amazing Things
Excel formulas have a powerful tool in conditional formatting. This article covers three ways to boost productivity with MS Excel.
Read More
.

## 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 15 Excel Spreadsheet Templates for Managing Your Finances Always keep track of your financial health. These free Excel spreadsheet templates are the tools you need to manage your money. Read More 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 Make a Personal Budget With Microsoft Excel in 4 Easy Steps Do you have so much debt that it will take decades to pay off? Make a budget and use these Excel tips to pay off your debt sooner. Read More .

## 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 7 Money Management Tools in Google Drive You Should Start Using Keeping track of your finances on paper can get messy. Use these expense trackers and templates to manage your finances instead. 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 and a wide range of downloadable Excel programs.

Explore more about: Microsoft Excel, Productivity Tricks, Tax Software.

Not so helpfull

tax rates are not flat

what about:

0-5.000 0%

5.001-15.000 10%

15.001-20.000 15%

etc

Nice collection..Helpful.Appreciated

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,

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.

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.

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!

hey bro..

really appreciated with ur work..

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.

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.

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.

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

Having a good tax spreadsheet is nice for estimating. Say you want to sell a stock and need to figure estimated payments or how much extra to have taken out of your paycheck.

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.

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.

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

LOL,

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

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

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

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.

2 days before taxes are due?

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!