30 December 2019

Make a Personal Budget With Microsoft Excel in 4 Easy Steps


You don’t need expensive software to help you become financially stable. Today, I’m going to share a few of the nifty spreadsheet techniques that I used to generate a useful budget.

Unlock the FREE "Essential Excel Formulas" cheat sheet now!

This will sign you up to our newsletter

Enter your Email

Also, I’m going to share a technique to pay down your debt in a fraction of the time using the same exact payments you’re making today.  It’s a trick that I’ve seen a lot of people trying to sell elsewhere on the net — I’m going to share it with MakeUseOf readers right here, for free.

I want to show you the four ways Excel helped create a stable personal budget.

The Power of Budgeting

Years ago, my wife and I were saddled with so much debt that we thought it would take the next sixty years to pay it all off. There came a moment when we realized that we either had to make a personal budget that could outsmart the system or it would keep us enslaved for our entire adult lives.

That’s when I sat down with a blank Microsoft Excel spreadsheet and just started playing around, using various techniques to cut down our budget to bare bones.

My goal was to create a debt plan that wouldn’t take decades to eliminate our debt, but also wouldn’t keep us eating macaroni and cheese for dinner until we retire. With this plan, we were able to eliminate all of our credit card debt in only five years. In the end, we even had good enough credit to get approved for a low-rate mortgage to buy our first home!

1. Structure Your Personal Budget Spreadsheet

If you’ve ever tried to make a personal budget you know the basics. You need to make a log of all of your bills and all of your income. Your bottom line is how much you have leftover for fun, or tells you how much spending to cut.

It sounds easy, but when you start entering all of your details into a budget things can get very messy very quickly. Sometimes the numbers look bleak, a lot of people give up after the first attempt.

A basic layout is easy enough. List your bills in the first left column. Then in the next few columns list total balance you owe, monthly required payments, and the date that the bill is usually due.  These four columns are really all you need to create a budget.

Excel Budgeting Tools

Here I’ve gone an extra step and added a column to the right for each month for easy expense tracking.

However, as your budget grows to a large number of columns and rows you’re going to run into a problem. The screen will start to scroll and you can’t always see the bills to the left or the header at the top. The quick and easy technique to fix this is using the Freeze Panes feature.

Excel Freezing Panes

First, select the box where the intersection at the upper left represents the row and column that you don’t want to scroll when you use the spreadsheet’s scrollbars. Select View > Freeze Panes.

Freeze Pane Demo for Excel

 

When you scroll up or down, the header and left column remain in view so you always know what the value you’ve selected applies to. This has saved me a great deal of frustration where I normally would have had to keep scrolling back to check which bill I’d selected.

If you’re new to Excel and need some tips on getting started with creating spreadsheets, make sure to check out Excel courses for beginners.

2. Organize Your Budget Using Shading

I remember searching for a free budget spreadsheet and finding all of these templates filled with data that just made my head spin. Without clear lines separating the major sections of your budget, you’ll have a hard time zoning in on the area that you’re interested in.

The best way to organize a budget spreadsheet is by shading each summary section between your major groups.

Excel Formatting for Personal Finance Sheet

As you can see here the first section of the budget pertains to bills, including household utilities and fixed bills. Another section is devoted to credit cards. At the bottom of this particular section, the total for fixed bills is highlighted with light green shading so it’s clear and easy to find.

Excel Budgeting Sheet

Once you start shading rows, the entire spreadsheet is easier to read.

Excel Formatting Using Fill Tool

Getting started with shading rows is very easy to do.

The Fill tool is located on the Excel menu bar under the Home menu and looks like a paint can tipping over with paint pouring out. Just highlight the entire row and then click the Fill button and select what color you’d like to use.

Try to avoid colors that are too bright or too dark. The shading provides a nice divider when looking at your budget, it should not be overwhelming.

3. Use Excel Formulas to Project Your Credit Card Balances Into the Future

Now that you can make a personal budget that is well organized and easy to follow, the next step is attacking credit card debt.

Credit Card Chart in Excel

 

Set up your debt log in the same way. Split and freeze the panes, but this time list each month along the left and your credit card balances (and monthly payments) to the right.

After you’ve entered your current balance in the top cell (In this example Capital One is $3,000), in the next cell below it you would enter a formula that multiplies that balance by your card’s interest rate and divides by twelve. That is your estimated monthly interest.

Then you subtract your monthly payment from the balance and add the interest that you just calculated.

Now you can duplicate the formula for every month below it by clicking and holding the small box to the lower right of the cell you just calculated and dragging it down as far as you like.

Each month will have a new calculated balance based on the previous month’s balance.

Chart for Paying Off Credit With Excel

When you do this formula, you’ll eventually find the date where the balance is completely paid off. As you can see, when I maintain a $250 payment every month it’ll take me until July 2021 to pay off the entire Advanta credit card balance.

For more on saving money and reducing your spending, take a look at these helpful apps and sites.

4. Recalculate Payments Based on Interest and Eliminate Your Debt

By playing around with this kind of spreadsheet I uncovered a common-sense solution that a lot of finance gurus out there are charging people for.

Instead of maintaining constant payments on each of your credit cards until it’s paid off, you pay the minimum balance on all of them and divert all of your current “debt-payment” money toward the credit card with the highest interest.

Here is how it works.

Chart for Paying off Credit in Excel

According to my sheet, the Advanta card will be paid off in May of 2021. Instead of saving this money, it’s going to be used to pay more debt.

I took the extra $200 I was paying on the Advanta and added it to the Capital One payment. This takes the payment from $100 to $300.

Now the Capital One card will be paid off by February 2022. If you have additional credit card balances, you simply “snowball” the payment and eliminate your debt in months rather than years.

This is why I love Excel. By using the ability to autofill the monthly balance calculations, I was able to test different scenarios to pay off debts faster.

Using Excel for Budgeting

The power of Excel for budgeting and debt planning like this can’t be understated. And even if you don’t use Excel, you should at least explore the various budget and debt planning tools available out there. Make the commitment to try a few out and work on it regularly.

In the beginning, it will take a lot of work to set up, but it will be worth it in the end.

And, be sure to check out these ways to avoid overspending and then bookmark some of these helpful sites that keep you on top of the financial market.

Read the full article: Make a Personal Budget With Microsoft Excel in 4 Easy Steps


Read Full Article

No comments:

Post a Comment