Do you want to create a budget which tracks income and expenses on just one chart? There’s an easy way you can combine them where you can show positive and negative values in a single column. Suppose we have the following income and expenses over a 14-day period:
You might be tempted to plot these values on a simple column chart like this:
This, however, means you’re using up two columns for each day. One for income, and one for expenses. While it is effective, what you can also do is combine the income and expense amounts into a single column. The key is to change the chart type and instead of using the default Clustered ColumnChart, you select the option for a Stacked Chart. By doing this, you’ll now have both values on a single column:
This is still not optimal, however, as now we are just adding the income and expense together. Even though they are color coordinated, there is a better way to display this. Ideally, we will show expenses being a negative outflow on a given day while income will be positive. To fix this, let’s flip the expenses so that they are negative. But rather than doing this manually, you can do this with just a few steps.
To flip values from positives to negatives in Excel, do the following:
1. Enter a value of -1 into a cell.
2. Copy that cell.
3. Select the range which contains the values you want to flip from positive to negative.
4. With those cells selected, right-click and select Paste Special and select Multiply
Doing this will flip your values negative by multiplying all the values by a factor of -1. You can also follow these steps if you want to flip negative values into positives.
After updating the formatting, my table now looks like this:
And now my chart has also updated to show negatives beneath the income.
This ensures I’m not taking up extra space with an extra column and it still makes it easy to compare the outflows versus the inflows.
If you like this post on How to Track Income and Expense in a Single Chart, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
Want to estimate how much you might owe in taxes next year? If you are self-employed or have other income besides what you get from an employer, then you may find it useful to plan ahead of time and determine how much you might owe to ensure that you are putting aside enough money for taxes. It’s not a fun process but it can save some headaches later on. The good news is that Excel can make that process easy. Below, I’ll show you how you can calculate and estimate your taxes in Excel. And if you’d just prefer to download the file that I have created, scroll to the bottom of this page.
Determining your marginal tax rate
To estimate your taxable income and marginal tax rate, the first thing you’ll need is a table for the tax brackets. For this, I will use the schedule for federal income tax brackets 2021 found here.
I can’t simply copy the table into Excel as I will need to format it a little differently (the values contain text and won’t be helpful if I need to do a lookup). The table needs to be organized by income threshold rather than tax rate. This is how I have set it up in Excel:
To make this table easier to reference to, I am going to create named ranges for these tax brackets plus the income I am going to enter in. This will make it easier to follow along.
If I want to look up the incremental tax bracket for a given level of income, I can accomplish this using a VLOOKUP formula. This is the formula I would use to accomplish that:
=VLOOKUP(Income,TaxBrackets,2)
What it is doing is taking the income number, and looking up the tax bracket table, and pulling in the second column (the tax rate). The VLOOKUP formula doesn’t look for an exact match (as I have left the last argument empty) and it will pull the closest number without going over. This is where it’s important to put in the numbers that the tax bracket start at, rather than a range. Using this formula, it correctly tells me that income of $100,000 would be at the 24% tax bracket as it does not yet reach the minimum amount for the next bracket — $164,926:
That tells me the correct tax bracket but I still need to calculate the taxes that are due at each level, which I will cover in the next section.
Determine how much you owe at each tax bracket
For the first tax bracket, I will need to determine if the income level reaches the second tax bracket. If it does and the income is at least $9,951, then I can multiply that by the tax rate of 10% as that would be the maximum that can be taxed at the first bracket — 9,951 x 10%. If the income is not at least $9,951, then I just multiply the total income by the tax rate. Here is what the formula looks like using named ranges:
For the second tax bracket calculation, I can follow similar logic. I will multiply the difference between the start of the third and second income levels. Here’s how that calculation looks:
I also use the MAX function just in case there is a negative number (where the income doesn’t even reach the next level). The same logic can now be applied for all of the remaining tax brackets except for the last one. Like the first one, it needs to be calculated differently. In that case, I just need to know if the income is above that threshold. And if it is, I take the difference between it and the total income, and multiply it by the highest rate:
If the income isn’t above the last level, then I put a 0 and multiply that by the tax rate. Now, when I’m all finished, I can sum up the tax owing at each level and come to a total tax number that would be due based on a given income number:
At this stage, you could now decide to deduct how much you may have already paid in taxes and any deductions or credits that you are entitled to.
But I’m not going to go any deeper here because there are too many different variations from one country and jurisdiction to the next when it comes to taxes. However, this should at least give you a good starting point for doing the rest of your estimation, however detailed you want it to be. But by at least estimating the taxes owing and deducting how much you have already paid, you should have a good idea of how much you might owe come tax time, under a worst-case scenario.
If you’d like to just download the file that I created when making this post, you can do so here.
If you liked this post on How to Calculate Taxes in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
The project budget template is designed to help track expenses that you do not need to compare against multiple time periods. That being said you could copy the template and create a separate instance for each period you want to cover. This template does not use macros so does not requiring enabling content.
The budget categories can easily be added by just entering a new category in the space below and the formulas will autofill and the chart adjust to contain the new category. To remove a category you can delete the cells and adjust the table by pulling on the corner in the bottom right section of the table (in the overbudget column). This will re-size the data to ensure the chart is not pulling up blank values and making the chart show blank values.
See above for the sample categories, and the chart below summarizes the data visually to show how much of a budget remains, how much has been spent, and how much is overbudget.
This template is designed to allow you to easily track and manager your expenses using budgets and copying transactions downloaded from your bank or credit card statement.
Once you have a download of your transactions from your statement, copy the data into the Import tab. The transaction dates into column A, the description from your statement in column C, and the amount in column D. Important to note that expenses should be positive (refunds negative), as should income. For payments/transfers I will cover further down but it will be negative if it is an outflow from the account/payment source or positive if an inflow. The source of your transactions will be selected in cell G2.
The suggested vendor column will autofill once it finds vendors matching the description you copied in column C. If not, you will want to setup the vendors using the setup button on the right of the page and click on manage vendors from where you can add, modify, and delete vendors. Once vendors are setup and all the suggested vendors are filled, click on the next button which is to Apply Names. This will fill the vendor column. After this is done you are ready to click on Copy Expenses. This will now move your transactions into the All Transactions tab. We focus on this tab next to go over how to manually enter transactions.
MANUALLY ENTERING TRANSACTIONS
You can manually enter transactions from the All Transactions tab by clicking the New Expense button at the top (or CTR:+SHIFT+T as the shortcut key is noted in each of the buttons up top).
MODIFY TRANSACTIONS AND ALLOCATIONS
You can also modify existing transactions by clicking on the Modify Transaction button on the same tab which is the same screen as the new expense screen (see below) except filled out with the selected transaction’s details.
Additionally, you can change the allocation. When setting up a vendor you assign a default category. However in this template you can break out a line item into as many as ten different cost categories. For example, if you go to a department store it may not be as simple as saying all those expenses relate to groceries, clothing, baby items, electronics, or whatever else is sold there. You could have all those cost categories and more in one receipt. This is where you can break out that detail, by clicking on the Change button next to the category drop down.
ACCOUNT TRANSFERS/CREDIT CARD PAYMENTS
Payments from one account to another are not an expense and may not be important enough for you to track. However, for the sake of completeness and making sure all your transactions are accounted for and balances reconciled, this can be accomplished here by setting up a vendor equal to the name of another payment source. In this template if you make a payment from one payment source to another it is recognized as a transfer rather than an expense (in the manual entry screen you can also specify transfer rather than expense). If the source of your payment is the bank and your vendor is your credit card (e.g. you are paying your credit card from your bank account) then on your bank account this amount should be negative and on the credit card it will be a positive. Transfers should add up to 0 every month unless you are missing one side of the entry. You can also manually enter transfers from the AllTransactions tab. Again, transfers are not necessary but helpful for the sake of reconciling to make sure all balances match.
ACCOUNT BALANCES
The Balances tab is used for reconciliation purposes to make sure the balance on your statement at the end of the month matches the balance here. It will reconcile according to the statement month and year rather than just looking at the calendar month and year. This will allow you to more easily reconcile to a specific statement. In row 17 you will want to enter any opening balance you have from these accounts.
BUDGET CATEGORIES
You can create budgets and also break them into multiple categories. By default I have setup my categories into whether the expenses are essential, discretionary, or irregular. However you can change these from the setup button (from the All Transactions or Import tabs), selecting Manage Expense Categories, and then selecting Manage Budget Categories.
From the Managing Expense Categories section you can create, modify, and delete budgets as well as change monthly amounts.
CASH FLOW AND PER DOLLAR SPENDING
These tabs show spending based on the budgets created and arranged in accordance with the different budget categories.
SUMMARY
Lastly, the summary tab provides a summary of the expenses. In row 1 you can specify the month, year, and budget category you wish to review. The bar graphs below show in red how much an expense is over budget, and if there is dark green that indicates the budget has been partially used and the dollar amount specifies how much has been used with the light green portion what is remaining
Further down below the graph you will see a summary of the largest expense items, as well as top items by category where you will be able to select multiple expense categories to review at once. Alternatively, you could also go to the All Transactions tab and filter the data from there to view what made up an expense category. NOTE ABOUT CUSTOM FUNCTIONS There are multiple custom functions in this template and occasionally they might get stuck – show an error instead of the calculated value. To correct this just hit ctrl+alt+F9 if you notice error values as this should fix the issue.
