### Calculate Interest Savings on a Credit Card Balance Transfer in Excel

A balance transfer for a credit card allows people to move the outstanding balance from one or more credit cards to a new card, often with a lower interest rate. The purpose of a balance transfer is to reduce the amount of interest paid on existing debt, which can help cardholders save money. Many credit card issuers offer promotional periods with significantly reduced interest rates, sometimes as low as 0%, for a specified duration, typically ranging from six months to 18 months.

By transferring high-interest credit card balances to a card with a lower or 0% interest rate, cardholders can save a substantial amount of money on interest payments. For example, if an individual has a \$5,000 balance on a card with a 20% annual percentage rate (APR), they would accrue approximately \$1,000 in interest over a year. However, if they transfer that balance to a card offering a 0% APR for 12 months, they can avoid paying any interest during the promotional period, allowing them to allocate more of their payments towards the principal balance.

Additionally, a balance transfer can simplify debt management by consolidating multiple balances into one monthly payment. This can make it easier to keep track of payments and avoid missed or late payments, which can result in additional fees and negatively impact one’s credit score. However, it’s important to be aware of balance transfer fees, which typically range from 3% to 5% of the transferred amount. Even with these fees, however, the potential interest savings often outweigh the cost, making balance transfers an attractive option for those looking to manage their debt more effectively.

In the example below, I’ll show you how an Excel spreadsheet can help you estimate just how much you might save with a balance transfer.

## Calculating credit card interest savings in Excel

You can estimate the interest savings from a balance transfer in Excel using the future value (FV) function. The function takes multiple arguments:

• Interest Rate
• Number of Periods
• Payment Amount
• Present Value
• Payment Type (beginning or end of period)

Since we are looking at monthly payments, we will need to convert the interest rate to a monthly percentage. For this, take the annual credit card rate and divide it by 12. If your credit card charges a 20% interest rate annually, dividing that by 12 will give you a monthly percentage of 1.67%. In Excel, this would be input as 0.0167.

The number of periods would represent the number of months. If it’s a six-month promotional period, then six would be your input, 12 if it’s for 12 months, and so on.

As for the payment, let’s suppose that you will pay 3% of the balance. On a \$1,000 credit card balance, that would translate into a monthly payment of \$30.

For the present value, we’ll enter a negative value to indicate an amount owing. The input will be -\$1,000.

The last argument, for the type of payment, can be left blank, assumes that a payment will be made at the end of the period. By setting it to a 1, that will assume the payment is made at the beginning. For the purposes of estimating, however, this won’t have a significant impact on the calculation.

The complete formula for the calculation based on the above assumptions for a 12-month period is as follows:

=FV(0.2/12,12,30,-1000)

This returns a value of \$824.49. This is what the balance would be after 12 months of payments. Next, let’s compare this to what the balance would be if the interest rate was 0%. This what that formula would look like:

=FV(0,12,30,-1000)

This returns a value of \$640, and it’s the same as if you were to deduct \$360 (12 payments of 30) from the balance. Thus, the savings from the balance transfer, before accounting for fees, would be:

824.49 – 640.00 = 184.49

Furthermore, let’s assume there is a 4% balance transfer fee. On a \$1,000 balance, that would be \$40. The final cost savings would be as follows:

184.49 – 40.00 = 144.49

This is just one example of a savings calculation, but let’s adjust this so that it is more adaptable to other scenarios, and create a template which can be easily modified.

## Creating a template to calculate savings from balance transfers

With the logic setup, to create a template is just a matter of determining the inputs to plug into the calculation. The variables which a user should be able to adjust are as follows:

• The current interest rate
• The promotional rate
• The monthly payment amount
• The credit card balance
• The promotional period in months
• The balance transfer fee

For this example, I’m going to use much larger amounts to help emphasize the potential savings. These are the inputs I’ve created in my sheet:

Now, it’s a matter of setting up the formula which links to these values. To do this, we need to combine the following calculations:

• The FV based on the current credit card interest rate,
• The FV based on a 0% credit card interest rate
• Calculating the difference between the two FV calculations
• Deducting the balance transfer fee from the above calculation

The first FV calculation is a copy of what was used before, except this time the values are not hardcoded and are instead linked back to the input cells above:

=FV(B1/12,B5,B3,-B4)

For the second FV calculation, we just need to reference the promotional rate:

=FV(B2/12,B5,B3,-B4)

Then, we deduct the difference between these calculations:

=FV(B1/12,B5,B3,-B4)-FV(B2/12,B5,B3,-B4)

The following formula also factors in the balance transfer fee:

=(FV(B1/12,B5,B3,-B4)-FV(B2/12,B5,B3,-B4))-(B6*B4)

Assuming a \$20,000 credit card balance, with a monthly payment of \$600, an interest rate of 20%, a 12-month promotional period at 0%, and a 4% balance transfer fee, the total cost savings would be approximately \$2,889.74. The completed template is setup as follows:

By setting up this template in Excel, you can adjust these inputs to do your own what-if analysis. These calculations assume that your credit card balance does not change and that you are simply paying it down and not adding to it.

If you like this post on How to Calculate Interest Savings on a Credit Card Balance Transfer 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

### Free Credit Card Payment Calculator

Staying on top of your credit cards is an important goal when trying to get from out of debt. A credit card payment calculator is a valuable tool that can help you do just that, helping you to become debt-free sooner. The credit card payment calculator template I’ll go over in this post is free and user-friendly as well, designed to be simple to use, even for novice Excel users.

## How to use the credit card payment calculator

The credit card payment calculator template in Excel is designed to provide a clear picture of your credit card debt and help you with developing a strategy to pay it off. All the fields in yellow require user input:

For the monthly payment, you need to enter at least a minimum balance. This is if you plan to pay the same amount every month. You can also specify a percentage of the balance you want to pay down each month. However, if you enter a percentage, you’ll also want to enter a minimum. Otherwise, your balance will never end up going to zero.

What’s new in this template compared to the previous version of the calculator are a couple of things. The first is the ability to add an extra payment:

As with the yellow input fields, you can enter any one-off payments in the extra payment field. This will get applied directly to your balance and reduce your principal. It will also update the rest of your payment schedule.

Another new feature of this template is the ability to enter a goal for when you want to pay the credit card off by:

In the above example, I have entered a date of Jan. 1, 2025. And based on my starting dating of Oct. 1, 2023, it calculates that I will need to make a monthly payment of at least \$344.55 to have the credit card balance paid off by that date. Now, if I go back and enter this as my minimum payment, this is what the calculator looks like:

There’s only a minimal balance remaining by the end of Dec. 2024 based on the new minimum payment but it is effectively paid off by that point.

## Why managing your credit card balances is important

One of the pillars of personal finance is understanding and managing debt effectively. Credit cards, while useful, can become a financial problem when not handled effectively. This calculator highlights how much in total interest you will pay based on your payment schedule, to help show just how much the debt is costing you. And the greater the debt and the higher the rate of interest, the more costly it will be.

For instance, consider a credit card balance of \$20,000 with an annual interest rate of 15%. With a monthly payment of \$300, it would take approximately 12 years to pay off the debt, accruing more than \$23,300 in interest — that’s more than the initial balance. If you increased the payment by just \$50, that would mean paying off the credit card in about 8.4 years and the interest costs would drop to \$15,330. And if you paid \$400 per month, you would pay off the card in less than seven years, while incurring interest costs of \$11,600. You can do all these calculations right within this calculator. You can see that even with a \$50 increase you can drastically speed up your repayment schedule.

Credit card debt can be a significant hurdle when aspiring to larger financial goals like purchasing a home. Lenders scrutinize your debt-to-income ratio, and a high ratio could result in unfavorable loan terms or even disqualification. By using the credit card payment calculator to expedite your debt payoff, you enhance your credit profile, bringing you a step closer to home ownership and other financial goals.

The what-if scenario feature can be especially useful, allowing you to determine a strategy that aligns with your financial objectives so that you know how much you need to pay every month. Whether you aim to be debt-free before a significant life event or simply wish to reduce the interest burden, this calculator lays the groundwork for a well-informed plan.

The credit card payment calculator template is more than just a tool—it’s a catalyst for proactive debt management. It nudges you to think beyond the minimum payment, encouraging a more aggressive approach towards debt elimination. By visualizing the impact of your payment strategy, you are better positioned to make choices that propel you towards financial freedom faster.

If you like this Free Credit Card Payment Calculator template, 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.

### How to Calculate Credit Card Interest

Credit card interest is a cost that cardholders incur when they carry a balance beyond the grace period, which is typically between 25 to 30 days after a billing cycle ends. This interest is calculated based on the Annual Percentage Rate (APR) provided by the credit card issuer. Understanding how this interest is computed is vital for managing financial liabilities and making informed decisions. This article goes over how to calculate credit card interest in Excel, in a step-by-step process.

## Getting the correct rate

Before we get started, they key is finding out what your APR is. This is a yearly interest rate which is provided by credit card companies. It tells you how much you’ll be paying. The APR is usually stated in the credit card agreement or on the credit card statement. It’s essential to note that different transactions may have varying APRs; for instance, cash advances often have higher APRs compared to purchases. If you have cash advances to consider that are at difference rates, then you may need to break this out into two separate calculations.

Once you have APR, you need to convert that into a daily rate as credit card interest is usually calculated on a daily basis. To calculate the Daily Periodic Rate (DPR), this involves taking the APR and dividing it by 365.

DPR = APR / 365.

This can involve a lot of decimal places so you may need to do some rounding. But if you do this in Excel, you don’t have to, and that means a more precise calculation.

## Calculating your average daily balance

To determine your interest expense, you’ll also need to determine what your balance was during each day that fell within your billing cycle. To calculate the Average Daily Balance (ADB), sum up the total of those daily balances and divide it by the number of days. The formula is the same as if you were to calculate any average:

ADB = Sum of daily balances / Number of days in billing cycle

It’s important to note that you’ll also need to carry over any balance from your last bill if it was unpaid. And you’ll also want to deduct any payments you make from the balance and add any purchases to ensure the balance is always correct and up to date.

## Calculating the interest charge

To get your daily interest charge, simply multiply the two variables, DPR and ADB by one another.

Daily interest charge: DPR x ADB

And if you want to calculate the monthly charge, then you take the daily charge and multiply it by the number of days in your billing cycle.

Monthly interest charge = Daily Interest * Number of days in billing cycle

## Creating a template to calculate monthly interest costs in Excel

Now it’s time to create a template in Excel which will make it easy to adjust for different scenarios. First off, we need to get the daily balances in a table format. Ideally, there should be a column for the starting balance, the day, purchases, payments, and the ending daily balance. This way, you can easily account for purchases and payments should you want to determine what your balances and interest expenses will be ahead of time.

The formula for the ending balance will be as follows:

Ending balance = Starting balance + Purchases – Payments

In the above example, the assumption is you start with a starting balance of 0. However, if you’re carrying over a balance from the previous period, then you can enter it in the starting balance.

Next, we need to enter the APR. Refer to this page on how to calculate APR. In this example, I’m going to use a rate of 17%. Here’s how my template looks thus far:

The values highlighted in dark grey are reserved formulas while the yellow cell pertaining to APR indicates that this is value that requires manual entry.

The formula for DPR just needs to reference the APR and divide it by 365:

DPR = APR/365

This returns a value of approximately 0.047%.

This will also have a named range of DPR to make it easier to reference later on. The benefit of using Excel for these calculations is that they will be more accurate; there’s no need to do any rounding.

For the ADB, a simple AVERAGE function can be used on column F, which in my spreadsheet, contains the ending balances.

The average in my spreadsheet is a value of \$301.67.

Since there is nothing else in column F, we can just average everything that’s in there. The function will ignore any blank values. This will be another named range, ADB.

To calculate the daily interest, the formula will should look familiar, this time, it’s within Excel as this involves named ranges:

Daily Interest = DPR * ADB

This returns a value of \$0.14 (rounded).

Lastly, to collect monthly interest we take the Daily Interest and multiply it by the number of days. For this example, I’ve set a named range of DailyInterest. And to calculate the number of days, I can use the following MAX formula to get the largest value in column B, which has the day numbers:

=MAX(B:B)

There are 30 days within the billing period in my example.

Then, this gets multiplied by the DailyInterest named range to arrive at the total monthly interest cost. Here is the full formula within the cell for Monthly Interest:

Monthly Interest =MAX(B:B)*DailyInterest

The monthly interest in my example computes to \$4.22.

Understanding this process sheds light on the significance of the APR and how maintaining a lower balance or paying off the balance before the end of the grace period can mitigate the interest charges. It also underscores the importance of being aware of the different APRs for various types of transactions.

Many credit card issuers offer a grace period during which no interest is charged on new purchases if the previous month’s balance was paid in full. Utilizing such grace periods effectively can lead to substantial savings on interest charges.

If you liked this post on How to Calculate Credit Card Interest, 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.

### How to Calculate APR in Excel

The Annual Percentage Rate (APR) on a loan tells you what your total borrowing cost is per year. It’s different from just the interest rate because it will include other expenses and fees relating to a loan. And for that reason, the APR will be higher than the interest rate. If there are no additional fees, then it will be the same. In this post, I’ll show you how you can calculate APR in Excel and how it compares with just the interest rate itself.

## Functions used to calculate APR in Excel

In order to calculate APR in Excel, there’s a simple function that can allow you to do that quickly and easily; there’s no need to draw out and complicated formulas for APR that you might find online. The key is to just determine your inputs and the variables you will be using for the calculation.

The RATE function in Excel will take care of the calculation but it requires the following inputs:

• Number of periods
• Payment amount
• Present value
• Future value

The only one of these that may require some additional work is the payment amount. However, there’s also a function for that too, called PMT.

## Calculating APR using an example

I’ll illustrate how you can calculate APR in Excel by using an example. Suppose you’re taking out a loan for \$200,000 where you’ll incur financing fees of \$30,000. The interest rate will be 4% and term is 10 years. And payments are made on a monthly basis.

For starters, we need to calculate the monthly payment amount. That requires similar inputs to the RATE function except instead of a payment amount, we need the interest rate. And since payments are going to be made on a monthly basis, both the interest rate and the term needs to be expressed in months. Instead of 4%, the rate we’ll need to use is 4%/12 and the term will be 120 months.

The PMT formula will look as follows:

=PMT(0.04/12,120,-200000,0)

Instead of entering in the interest rate to the decimal point, I’ve left it is a fraction so that the calculation is more precise. The present value is entered as a negative since the 200,000 is what is currently owed, while the future value (0) is what it will be when the loan is paid off.

The result of this calculation results in a monthly payment amount of \$2,024.90.

In this scenario, there are no finance charges included. To factor those in, simply change the present value to -230,000. By doing that, you’ll arrive at a monthly payment amount of \$2,328.64.

Using those payment amounts, we can now calculate the APR, using the RATE function:

=RATE(120,2024.9,-200000,0)*12

The result of this formula is multiplied by 12 to get to annual rate. This is because the RATE function gives you the rate per individual period. At the monthly payment of \$2,204.90 (i.e. when there are no financing charges), the formula for APR results in a value of 4%, which is equivalent to the interest rate. Since there are no additional fees here, it makes sense that the percentages are the same.

However, when using the higher payment amount for the loan that includes finance charges, there will be a more noticeable difference. For that calculation, the formula is as follows:

=RATE(120,2328.64,-200000,0)*12

What you’ll notice here is that while the payment amount has changed, the loan remains the same. This is because while we need to pay for the extra finance charges and they are added to the monthly payment, the value we would be receiving remains just \$200,000. Through this calculation, we arrive at an APR of 7.06%.

The higher the additional fees and charges, the bigger the delta will be between the APR and the interest rate.

## Creating the amortization tables

The differences in these rates can also be demonstrated through an amortization table to show how the loan is paid off. For an amortization table, we need the following fields:

• Payment #
• Beginning Balance
• Principal Payment
• Interest Expense
• Ending Balance

The beginning balance will be the total that needs to be paid off. The interest expense is the calculated by taking the balance and multiplying it by the interest rate. What’s left over from the monthly payment goes towards the principal. How much is paid off is reduced from the beginning balance to arrive at the ending balance. Here’s how the table will look in the first scenario, where there were no additional finance charges:

This excerpt shows the first 20 payments. The following is the amortization table for the second scenario, where finance charges total \$30,000:

Here you can see that with a higher balance, more of the payments are going towards interest at the start. Although it takes the same amount of time to pay off the loan, higher payments are necessary to account for the increase in the beginning loan amount.

If you like this post on How to Calculate APR 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.

### Free Credit Card Payment Calculator Template in Excel

Are you wondering how much you should put down on your credit card each month to ensure you aren’t taking decades to pay off your balance? With this free credit card payment calculator template, you can plug in your balance, interest rate, and your expected monthly payment (either fixed or as a percentage of the balance) to determine just how long it may take you to pay off your debt.

## How the template works

The cells that require data entry are highlighted in yellow on the template. At a minimum, the balance, interest rate, starting date, and one of the two fields for the monthly payment need to be entered in:

For the monthly payment field, you can enter in both a fixed dollar amount and a percentage of the balance. How it works is that if you enter in both values, the higher payment calculation will be used.

For example, if you had a \$10,000 balance and planned to pay 5% of it, the monthly payment would be \$500 and decline along with the balance. If you also set a fixed payment of \$400, then that would ensure your monthly payment would never fall below \$400, even as the balance declines. In effect, the payment will be the greater of these two values. If you only enter a percentage of the balance, then only a percentage will be used. Similarly, if you only have a value for the fixed amount, then that’s what your payment will remain at.

Once you’ve entered in these fields, the rest of the template will populate. This includes calculations on how many years it will take for you to pay off your balance, the end date, how much your payments will total, and the amount of interest you will have paid:

If you enter a payment that is so small it won’t even cover your interest, then the payment amount will be highlighted in red:

In the above example, the years and end date will not be accurate since, under this example, the balance will grow rather than decline over time, and thus, will never be paid off.

If you liked this free Free Credit Card Payment Calculator Template, 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.

### How to Calculate Taxes in Excel

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:

=IF(Income>=IncomeLevel2,IncomeLevel2-IncomeLevel1,Income)*TaxRate1

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:

=IF(Income>=IncomeLevel3,IncomeLevel3-IncomeLevel2,MAX(0,Income-IncomeLevel2))*TaxRate2

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(Income>=IncomeLevel7,Income-IncomeLevel7,0)*TaxRate7

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.

### Mortgage Payment Calculator in Excel

Many people are out buying homes this year as interest rates are at record lows. But figuring out what you can afford can sometimes be a bit challenging. You need to factor in how much of a downpayment you can afford, what your monthly payments will be, and your income. Oftentimes you’ll end up doing multiple what-if scenarios. However, with the free mortgage payment calculator in Excel, you can get a quick snapshot of all the pertinent information to help you determine which house prices are within your range. You can download the template here.

## How the calculator works

There are three sets of inputs on the calculator. The first section relates to the mortgage itself — how much of a downpayment you can make, the interest rate that’s available to you today, and how many years long your mortgage will be.

The second section relates to how much income you make as well as how much of your income you want going to cover your mortgage. This is a good way to gauge affordability to ensure that the monthly mortgage payment is within your means. For the monthly income, you’ll want to use after-tax income since this is what will actually be available to you to pay your mortgage payments and other expenses. This is also tied to the worksheet’s conditional formatting. When the % of the monthly mortgage rises above your maximum%, the cells will highlight in red to show that these house prices will be too expensive based on your threshold.

This is an optional section and if you don’t enter it then the spreadsheet simply won’t populate the % of monthly income and there won’t be any conditional formatting applied.

The last section is simply what house price you want to start at, the minimum value that you want to look at. There’s also an area where you can determine at what increments each option should increase by. For instance, if you want to look at a very narrow range, you might put \$10,000 to see the different scenarios if the house price increased by \$10,000. If you’re looking at a much wider range, you could increment the values by more, such as \$50,000 or \$100,000.

As you enter values in these fields, the mortgage payment calculator will update its results and show you how the different scenarios look like at the different prices.

The above table will be updated immediately as you make changes to your inputs. Please note the spreadsheet is locked and you only can enter data in the inputs. This is to prevent user error and the possibility that formulas are overwritten.

If you liked this post on the mortgage payment calculator 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.

### Income Tracker Template

If you earn income from multiple jobs, you know how important it is to keep track of how much you’re making. If you work too little, you may not be making as much as you were planning. And if you’re working a lot more, you could afford to give yourself a break.

Whether you’re self-employed or you supplement your income by driving for Skip the Dishes or Uber, there’s no shortage of ways to make money these days which doesn’t involve working a regular 9-to-5 job. The income tracking template I’ve created will allow you easily track your income from various sources and help you stay on track if you’re targeting a certain income figure for the month or year.

## How the income tracker template works

There are two tabs on this template: inputs, and calendar. The inputs are where you’ll go to enter in your income, so let’s start there.

On the Inputs tab you’ll enter the date you earned the income, the source, and how much you made. You can setup hourly or item rates. You can also just enter in a fixed income amount. Here’s an example of some sample inputs:

For jobs like Skip the Dishes or Uber where you don’t earn an hourly wage, you’ll need to enter a manual amount. If you sell items on eBay or Amazon where you might have an average price, you could potentially use a rate. And that’s where the rate schedule comes in handy:

This could also work if you work a part-time job or something where the rate is fairly steady. Whether it’s per hour or per item is irrelevant. The point is to try and make the input section as easy as possible and you don’t need to necessarily use the rate schedule if you don’t need it.

But if you enter a number under the hours/items section, the income earned formula will be looking for a rate to multiply that by — you’ll get an error if it can’t find one (unless you enter a manual total, which will override the calculation). So if you don’t have a rate for that source of income, don’t enter anything in the hours/items section and just use the manual input column.

Once all your data’s entered in, it’s time to head over to the Calendar tab.

## A summary of all your income

For the week that I entered the weekly income for, here’s what the calendar shows:

You can visually see how much you’ve made each day and there is also a weekly total at the end. The key to everything calculating correctly is the value in the source column needs to match what you entered on the inputs tab.

There’s also a column further down titled Annual Run Rate which will tell you how much you’ll earn over the course of the year if you work a full year at your current weekly pace.

It’s simply a way to gauge whether you’re on track that week for your annual goal or not. That brings me to the next section: the setup.

## Settings and goals for the income tracker template

If you scroll over to the right on the calendar tab, you’ll see an area where you can specify a number of different settings and goals. Only change the items that are highlighted in grey. Let’s go over each one:

• Work weeks. This is how many weeks you plan to work during the year. If you are going to work every week then you can leave this at the default value of 52. If you’re going to take some weeks off, then deduct from that total. The purpose of this is for calculating the annual run rate.
• Include partial month. If you strictly only want to include the days that fall in the month when calculating your weekly totals, then set this to ‘N’. If you set this to ‘Y’, then the first and last weeks of the month could include parts of the previous and upcoming months, depending on where the month ends and starts. The purpose of setting it to ‘Y’ would be so that every week is a full week. For instance, July 2020 began on a Wednesday. If you set partial months to ‘Y’, then your July calendar and weekly totals would include June 28-30. If you set partial months to ‘N’, then those days would not show up on the calendar and they wouldn’t be included in your weekly totals.
• Monthly goal. This is the total income you want to earn on a monthly basis.
• Annual goal. This is the total income you want to earn for the full year.

Below the setup options, you’ll also see a summary of your sales by month. You can enter the year if you’ve got multiple entered. But by default, I’ve set this to 2020.

Next to the calendar, you’ll also see charts showing you the progress that you’re making relative to your goals for the month and year:

The different gauges show different things. The first one is how close you are to reaching your monthly goal. The next one is how close you are to your annual run rate based on the monthly income you’ve earned thus far. And the third takes a tally of all of the income you’ve entered on the inputs page and compares it to your annual goal.

This template is free to use and allows you to stay on top of all your income sources. The version is locked down to minimize data entry errors and does come with an ad. But as it is, it will allow you to enter in your data and the template is fully functional. You can download it here.

Currently, the template supports five income sources and you can adjust those in the free version. With the premium version, everything is unlocked and there is no ad. And you could add more income sources on the calendar tab if you’re comfortable adding rows and updating the formulas.

If you liked this post on the income tracker template, 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.

## 1. Getting Your Expenses In the Right Format

The most important part of the process is just getting the data correct from the start. On the DATA tab, there are four main headers (highlighted in blue) that you’ll want to populate information for: Transaction Date, Posting Date, Description, and Amount. You don’t need to use Posting Date since the monthly expenses template only uses the Transaction Date, but I included it since I found that many downloads included both dates.

The Description is a key field since it includes the Doing Business As (DBA) name, and this is crucial to ensuring that the right vendor name is extracted. Normally you’ll see the vendor name somewhere in the description. Have a look at this post as an example of how to extract the DBA name. It may not work 100% of the time as it ultimately depends on how the merchant sets up their DBA name, but generally, it should give you a pretty accurate result. There may also be instances where you have multiple vendors that are the same, and this again will likely be due to inconsistency in how one location inputs its DBA name versus another. In my sample data set, here’s how two purchases from Amazon can show up differently:

Without changing the actual description, we’d end up with two different vendors. This isn’t ideal, but it also shouldn’t be all that common. It’s likely a result of one purchase being made for a different type of product or service on Amazon and a different DBA name being used in that instance. What you could do is create a category for similar-named vendors to help at least group them at the category level (see step 2 for that).

Lastly, there is the amount field. Whether this is positive or negative doesn’t really matter as long as your expenses are consistent.

Once all your data is in, the columns in red will autofill as they contain formulas. If they don’t, you may need to copy the formulas down but they should be automatic as long as the data is in the table directly below the most recent row. At this point, you can go setup categories for your vendors if you want to slice the data a bit further, or you can go straight to updating the report (step 3)

## 2. Setting Up Categories to Track Your Monthly Expenses (Optional)

Quick note: on the CATEGORIES tab, you’ll see an option to change the month-day-year format into day-month-year. This is only necessary if your date is downloaded in text (e.g. doesn’t read as a date) and it’s in day-month-year format (month-day-year format is the default). However, if you’re just downloading data for the last week or month, this may be irrelevant for you if you’re not looking to analyze expenses over a longer period of time.

In this tab, you’ll be able to set up any categories that you want to track. However, you don’t have to add any categories and the analysis will simply look at the expenses at the vendor level.

To set up a category, all you need to do is enter the vendor name and next to it the category that you want to map the vendor to. This will ensure that on the DATA tab, the category will automatically be pulled in for a particular vendor. However, it’s important to note that the matches need to be exact, otherwise this won’t work. Even an extra space in the vendor name won’t result in the formula pulling the correct category.

## 3. Updating the Reports

The SUMMARY tab creates a dashboard for all your expenses. The only step you need to do here is to go to refresh all the tables. To do this, on the ribbon, select the Data tab and click on Refresh All. This will ensure all the pivot tables are updated.

From there, it’s just a matter of how you want to filter or view your data. You can select the slicers if you want to narrow in on a certain month or category.

There will be two columns that you can look at: one will be for the total spend, and the other for the % of the total. If you have set up categories, you can narrow in to see which vendors made up a given category and the percentage will show their overall share.

You can download the monthly expenses template here. It is completely free and unlocked so you can make any changes you’d like to it. If you find this or other templates on this site useful, please give us a like on our Facebook page.