You have a large box of receipts, payment stubs, invoices and forms two days before your tax is due. And you don’t want to pay another late fee for filing after the deadline. What do you do?
You can spend hundreds or thousands of dollars on an emergency tax session with an accountant. Or you can take advantage of the power of Excel to get everything in order. Here are 5 formulas that can make your life easier during the tax season.
1. Using VLOOKUP for Tax Table
VLOOKUP is a very useful alternative operator in the formula. If that operator is set to FALSE, the formula will return an error if the value you searched for will not be displayed.
If it is set to TRUE, then it will return the next smallest number. It is perfect for tax tables. Here is a hypothetical tax table:
Suppose you need to get tax information for three different people. This means that you will need to do the same calculation on three different incomes. Use VLOOKUP to speed up the process. Here we will use the syntax:
= VLOOKUP (A2, A1: B6, 2, TRUE)
A2 is the income amount, A1: B6 is the range of cells that contain tax rates, the number 2 indicates that we want the value from the second column, and TRUE tells the formula that if it is not found It should round down an exact match.
Here is when we run it on cells that include $ 37,000, $ 44,000 and $ 68,000 for income values:
As you can see, the formula returned the appropriate tax rate for all three. Multiplying the tax rate by total income is simple, and you will have to pay tax on each amount.
It is important to remember that VLOOKUP rounds down if it is not an exact value to view. Because of this, if you set a table near me here, you should be listed in the maximum income limit.
VLOOKUP can be extremely powerful. You can use the power of many Excel formulas to do some pretty amazing things.
2. If IF formula for multiple thresholds
Some tax credits depend on how much money you have made. For these credits, nafing statements and other Boolean operators can make it easier to find out how much you can claim.
We will use the Earned Income Credit (EIC) to create an example. I have highlighted the relevant part of the EIC table here (the four rightmost columns are for married couples filing jointly, and the left four for single filers):
Excel first looks at the AND statement. If both logical operators in the AND statement are true, it will return TRUE and then 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 argument [value_if_false], which in this case is 0.
In our actual example, we have used another IF statement for [value_if_false], which allows Excel to run through IF statements, unless one of them is true.
If your income makes it through the last statement without any of those limits, it will return the string “double check”. Here’s what it looks like in Excel:
In many cases, you can use VLOOKUP to speed up this process. However, understanding nested IF statements can help you in many situations that are likely to come across. And if this is something you do often, you can build a financial spreadsheet template for reuse in these types of formulas.
3. Calculation of interest payment with ISPMT
Knowing how much interest you have paid on a loan while paying your tax can be valuable, but if your bank or lender does not give you this information, it can be difficult to find out. Fortunately, providing a little information to the ISPMT will calculate it for you. Here is the syntax:
[Rate] is the interest rate for the payment period, [period] is the period for which the interest will be calculated (for example, if you made your third payment, it would be 3). [NPR] is the number of periods of payment you will take to pay off the loan. [Value] is the value of debt.
Suppose you have a mortgage of $ 250,000, which has an annual interest rate of 5%, and you will pay it off in 20 years. Here’s how we calculate how much you paid after the first year:
When you run it in Excel, you get a result of $ 11,875 (as you can see, I set it as a table and select 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 repaid after the third month of a $ 10,000 one-year loan and an interest rate of 7% would use the following formula:
4. Converting annual interest with nominal interest
When you are given a nominal rate of interest several times throughout the year, it can be difficult to know what you are paying. EFFECT will help you detect this.
[nominal_rate] is the nominal rate of interest, and [nper] is how often interest is earned throughout the year. We will use the example of a loan with a nominal interest rate of 7.5% per quarter.
Excel gives us a 7.71%, effective annual interest rate. This information can be used with many other formulas that use interest rates to determine how much you have paid or how much you owe. If you are using Excel this can be useful for creating a personal budget.
5. Depreciation Assets with D.B.
Excel includes many different depreciation formulas, but we take a look at DB, the fixed degradation balance method. Here is the syntax:
= DB ([Cost], [Rescue], [Life], [Duration])
The [cost] argument represents the initial cost of the asset, [disposition] is the value of the asset at the end of the depreciation period, [life] is the number of periods over which the asset will be depreciated, and [period] is the period number for which You want to get information.
Interpreting the results of a DB statement can be a bit complicated, so we’ll look at a series of data. We will take an asset with an initial cost of $ 45,000 which will be depreciation for $ 12,000 over the course of eight years. Here’s the formula:
= DB (45000, 12000, 8, 1)
I am repeating this formula eight times, so the last argument will be in 1, 2, 3, 4, 5, 6, 7 and 8 consecutive rows. What happens when we do this:
The number in the depreciation column is the quantity of value that is lost. So to see the value of the investment at the end of the year, you have to subtract the number in the depreciation column from the value of the investment at the beginning of that year.
To get the value at the end of a year, 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 tax
These five formulas are available in large numbers and should help you get a good start in using the power of Excel to get your taxes. If you’re more comfortable, don’t learn to record macros in Excel and let your sheet work for you?
If you are not a fan of Excel, you can also use money management tools in Google Drive. And don’t forget that there are many other great resources from the IRS including some useful tools and a wide range of downloadable Excel programs.