5 useful Microsoft Excel formulas for calculating taxes

2 days before you have to complete the job tax, but a lot of receipts, payslips, invoices, etc . you have not dealt with. And of course you do not want to pay the costs incurred for filing expired. So what to do in this situation?

You can spend big money to hire an experienced accountant handles this, or make use of Excel power immediately to arrange things in order. Here are five recipes that can make your life 'breathable' than in 'season' tax.

1. Use VLOOKUP for tax tables

VLOOKUP with an optional operator is useful. If the operator is set to FALSE, the formula will return an error if the value you seek is not showing.

However, when set to TRUE, it returns the next smallest number. This is a perfect option for the tax table. There is a hypothetical tax table as follows:

5 useful Microsoft Excel formulas for calculating taxes Picture 1

Suppose you need to obtain tax information for three different people. This means you will need to perform the same calculation, but on 3 different incomes. Use VLOOKUP to accelerate this process. Here, the syntax to use is:

 =VLOOKUP(A2, A1:B6, 2, TRUE) 

A2 is the amount of income, A1: B6 is the cell range containing tax rate, number 2 says we want the values from the second column returned and TRUE that formula will round down if not find a price exact value.

This is what happens when running it on the box containing $ 37,000, $ 44,000 and $ 68,000 for the value of income:

5 useful Microsoft Excel formulas for calculating taxes Picture 2

As you can see, the formula returns the appropriate tax rates for all three. Human total income tax rate with a very simple and will give you the amount of taxes that you lack on each earnings.

The important thing to remember is rounded down if it VLOOKUP not find the exact value. Because of this, if you set up a table like the article here, you need to have the maximum income listed in the scope.

VLOOKUP can be extremely powerful. You can use the power of multiple Excel formula to do some pretty amazing things.

2. IF formula for multiple thresholds

Some tax credits (tax credit) depends on the amount you've earned. For this credit, the nested IF statement and many other boolean operators can help you easily find out the exact amount of money you can reclaim.

Posts will use the Earned Income Credit (EIC) to create an instance. Relevant section of the table here EIC (4 rightmost column for registered couples share the left column and 4 for individual subscribers) have been highlighted:

5 useful Microsoft Excel formulas for calculating taxes Picture 3

Write a command to determine if the amount can reclaim through EIC How much:

 =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")))) 

Learn more about this a bit. Articles will only give a command like this:

 =IF(AND(A2 >= 36800, A2 < 36850), 1137, 0) 

Excel first looks at the AND statement . If both logical operators of AND statements are true, it returns TRUE and then returns the argument [value_if_true], in this case 1137.

If returns FALSE AND statements (for example, if A2 = 34 870), the formula returns the argument [value_if_false], in this case is 0.

In practical examples, the article used a different IF statement to [value_if_false], allowing Excel continues to run through the IF statement until one of them is correct.

If the last statement that your income still does not belong to any extent of which, it will return the string 'doublecheck'. This is what you will see in Excel:

5 useful Microsoft Excel formulas for calculating taxes Picture 4

In many cases, you can use VLOOKUP to accelerate this process. However, understanding the IF statement are nested can be helpful in many situations you encounter. And if this is routine work to do, you can build a financial spreadsheet template for the type of formula is built for reuse.

3. Calculate interest paid by ISPMT

Knowing the amount of interest you pay on a loan can provide value when you calculate the taxes themselves, but if the bank or lender that you do not provide this information, it's hard to know OK. Fortunately, just provide a little information, ISPMT will calculate it for you. Here is the syntax:

 =ISPMT([rate], [period], [nper], [value]) 

[rate] is the interest rate per payment period, [period] is the period of time that interest will be charged (for example, if you only perform payments Tuesday, figures here will be 3), [nper] is the maturity that you will take to pay the loan, [value] is the value of the loan.

Suppose you have a $ 250,000 mortgage with an annual interest rate of 5% and will pay off in 20 years. This is how the article will calculate the amount you paid after the first year:

 =ISPMT(.05, 1, 20, 250000) 

When you run this command in Excel, you receive a $ 11,875 result (as you can see, the article has set as a table and select values from that).

5 useful Microsoft Excel formulas for calculating taxes Picture 5

If you use the monthly payment, remember to convert the annual interest rate of monthly interest. For example, to find the amount of interest payable after the third month of the loan one year and worth $ 10,000 7% interest rate will be using the following formula:

 =ISPMT((.7/12), 3, 12, 10000) 

4. Turn the nominal interest rate with annual interest rates EFFECT

Calculate the actual annual interest rate of the loan is a great financial skills required. While providing a level of nominal interest rates combined several times a year, it's hard to know exactly what you will pay. EFFECT will help you find it.

 =EFFECT([nominal_rate], [nper]) 

[nominal_rate] is the nominal interest rate and [nper] is the number of times interest is included in both years. Posts will use the example of a loan with an interest rate of 7.5% gross nominal per quarter.

 =EFFECT(.075, 4) 

Excel result in 7.71%, the beneficial interest. This information can be used with a number of different formulas, using interest rates to determine the amount you paid or the amount you owe. It can also be useful if you use Excel to set up personal budgets.

5. Depreciation of assets with DB

Excel includes a number of different depreciation formulas, but the article will consider DB, method depreciated on a declining balance certain. Here is the syntax:

 =DB([cost], [salvage], [life], [period]) 

For some [cost] represents the original cost of the property, [Salvage] is the value of assets at the end of the amortization period, [Salvage] is the number of periods that assets will depreciate and [period] is the period time you want to receive information.

The interpretation of the results of the command DB can be a bit complicated, so this article will look at a variety of data. Examples would get a property at a cost of $ 45,000 initially, will be depreciated to $ 12,000 in 8 years. Here is the formula:

 =DB(45000, 12000, 8, 1) 

For example, this formula will be repeated 8 times, so arguments will eventually be 1, 2, 3, 4, 5, 6, 7 and 8 in consecutive rows. This is what happens when you do that:

5 useful Microsoft Excel formulas for calculating taxes Picture 6

Depreciation figure in the column is the amount of value lost. So to see the value of the investment at the end of the year, you will need to deduct the depreciation from the value in the column at the beginning of the year of investment.

To get the value at the end of the first year, we subtract $ 6,840 from $ 45,000 and recognized figure $ 38.160. To get the value at the end of the second year, we subtract $ 5,800,32 from $ 38.160 and $ 32,359,68 received, etc .

5 This formula is one of the large number of options available and will help you have a good start when using the power of Excel to complete your taxes. If you feel comfortable, why not learn how to write macros in Excel worksheets and to the work for you, right?

If not the fans Excel, you can also use the tools to manage the money in your Google Drive. And do not forget that there are many other great resources, including plenty of useful stuff from the IRS and a variety of programs downloadable Excel.

Hope you are succesful.