Posts in category: personal finance

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.

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.

Tracking your goals

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.

Download the template

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.

A great way to start finding opportunities to save money is by simply looking at your monthly expenses. It can be as easy as downloading your recent transaction data from your bank or credit card issuer. While there are apps that can do that for you, by downloading it you have more control over the information and you also don’t have to give access to a third-party application. With the monthly expenses template, you can easily analyze your expenses in seconds.

The template has three tabs: DATA, CATEGORIES and SUMMARY. Let’s start with the most critical step, and that’s downloading your data and getting it into the template.

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.