Managing your money with a personal financial planning spreadsheet

in #money8 years ago (edited)

PostImg

TL;DR: In this post I offer an Excel spreadsheet that you can download and freely use for managing your personal finances. I start by covering the advantages of having such a planning aid, and then move into a detailed discussion of how to use the spreadsheet.

One essential life skill is the ability to balance a monthly budget for your household. Doing so in an effective way takes a disciplined approach. This is especially true if there's not much fat in your budget and you have to economize to make ends meet every month, as I do. Writing down a plan and following it religiously is one of the best ways to accomplish such a feat.

The benefits of having a monthly written plan are numerous:

  • You always know exactly how much money you have available to spend at any given time.
  • You take a lot of the emotion and guesswork out of spending money by turning it into a mechanical, step-by-step process.
  • You never pay bills late because you always have enough money budgeted for those expenses.
  • You learn to live within your means, and thus reduce credit card expenses.
  • You can see how many months it will take to pay off debt or achieve some savings goal.
  • If your wife says "honey, let's get away for the weekend!" or your daughter says "Daddy I need a new iPhone", you know instantly if you can afford to say yes.
  • You control your finances, instead of letting your finances control you.
  • You can judge whether it's realistic to quit your job and make a full-time living from Steemit. ;-)

But isn't it a lot of work to make a detailed financial plan each and every month?

Not necessarily. It usually takes me 2-3 hours a month to do my plan. Lunch breaks at work make perfect planning time, and psychologically have the advantage of not making me feel like I'm wasting precious free time at home. I typically spread out the effort over a few lunches in the week leading up to payday.

Of course you need to have an organized, systematic approach to making a plan. Don't just grab a piece of blank paper and start jotting down whatever comes to mind. That's a recipe for either writer's block or a complete mess.

So how exactly do you make this plan?

My preferred method is to use an Excel spreadsheet that acts as a monthly template. I know that my pattern of spending will not change drastically from month to month. So I filled in all the appropriate values at the start, and then for each new month I just make a copy of last month's sheet and tweak some rows as necessary.

The spreadsheet itself is basically a list of assets (things that give me money each month) and liabilities (things that take money away each month). The goal is simple: add together the assets, subtract all the liabilities, and make sure the net total is positive (meaning I won't plan to spend more than I have for any given month).

The formatting and exact content of the spreadsheet is something that I have carefully honed and added to over a period of many years, until finally it reached its current nicely optimized form.

I am making my spreadsheet publicly available so you can adapt it for your own life. You have my permission to download it and freely use it in whatever way you see fit.

Here is the Google Drive link to it: https://drive.google.com/open?id=0B03QfEeDXBoiRU5XaUlNYTVNR0k

Note: the online document is locked for editing. You must download it to your computer and open in Excel in order to edit.

PostImg

The Personal Budget Planner.xlsx file has two tabs, Monthly Budget and Credit Card Expenses. We'll look at each in turn, but first some important points:

  • Information in the file is my real budget for October 2016. I chose to leave actual numbers in place to give you a feel for how the spreadsheet should be used (and because I have nothing to hide).
  • I live in Japan so amounts are given in Japanese yen. You can of course change this to be whatever currency you desire.
  • I get paid monthly, so the planner reflects that. I suggest keeping it as a monthly plan, but if you get paid twice a month and want to adapt it to be a two week plan, that's fine too. Do keep in mind a shorter timeframe means you'll have to make new plans more often.
  • Totals at the bottom are computed automatically. You just have to fill in the numbers for each asset / liability (which of course you will have to edit to reflect your own circumstances).

Monthly Budget tab

PostImg

This is the main tab of the spreadsheet, where your ordinary living expenses should be listed. Let's examine each individual column:

Assets

A list of everything that gives you money. The amounts for these items should be the actual amount you get in your bank account minus any deductions, fees, etc. For example "Monthly Salary" is not the gross amount of my paycheck; it's the leftover amount after my company deducts health insurance, pension, and taxes.

These items represent positive numbers in the Value column.

Liabilities

A list of everything that takes money away from you, such as bills, food costs, car payments, mortgage / rent payments, etc (if you're wondering where my food money goes, that's part of the "Wife Weekly Money" item that I give to her for shopping).

These items represent negative numbers in the Value column. A couple important points:

  • Liabilities is split into 2 columns so you can group items by category, as I do with "Utilities".
  • "Money Sent Home" is a special value. It represents a monthly overseas remittance from my Japanese bank account to my US bank account and gets copied automatically into the spreadsheet's second tab. More on this later (if you don't send money to other countries you won't need this).

Value (Yen)

The actual amount of an asset (positive number) or liability (negative number). Obviously you should edit the column heading and change this to whatever your native currency is. I live in Japan so I denominate everything in Japanese yen. The "Monthly Extra" number at the bottom sums together all the rows to give you the net total.

Note that "Leftover from Last Month" is computed from the mini-worksheet at the bottom (the olive colored box):

PostImg
The numbers you should edit are the "Money left in wallet" and "Money left in bank" values.

Multipliers

Sometimes you may have assets that reward you multiple times, or liabilities that represent repeated payments, during the course of a single month. This behavior can be modeled with the Multipliers column. Each number in this column gets multiplied with the corresponding number in the Value column before they all get added together to produce the total.

As an example, I give my wife spending money every Saturday, so the multiplier for "Wife Weekly Money" is the number of weeks in the month. Well, not quite. It's actually one less than the number of weeks in the month (I have a separate value "Wife First Weekly Payment" just in case I ever have a total amount that's not perfectly divided by the multiplier, so this value can take the remainder). And yes, when you count weekly payments you will have some months where you make 5 payments (1 "Wife First Weekly Payment" + 4 "Wife Weekly Money").

What's that last column with no name?

Glad you asked. That's just where I write miscellaneous notes to myself.

So there you have it. That's the whole first tab of the planner file, pretty simple really. Now on to the second tab:

Credit Card Expenses tab

I probably should have named this tab better. It started out purely as a way to plan credit card payments, but has since turned into a general budget planner for my US bank account. If you send money to another account in a different country, denominated in a different currency, and pay expenses from that account, then this is the tab for you. Quite useful for expats like myself, who often make regular remittances to their native countries. But if that doesn't describe you, then you really have no reason to use this tab and can simply delete it.

PostImg

You'll notice the same Assets, Liabilities, Value, and Multipliers columns from the first tab are present here. They are meant to be used in exactly the same way. The one key difference is that Value is denominated in dollars this time instead of yen, since this tab represents a US bank account instead of a Japanese bank account.

What's interesting here are the additional columns at the top of the spreadsheet. These are for dealing with the exchange rate and remittance fees for transferring money from the source account (Monthly Budget tab) to the destination account (Credit Card Expenses tab).

PostImg

Let's look at each column in turn:

Money Sent Home

This is how much yen I am sending from my Japanese bank account to my US bank account. This value is automatically copied from the "Money Sent Home" value in box D14 of the first tab.

Fees

A description of important quantities that determine how many dollars my yen is worth.

  • GoRemit's processing fee - GoRemit is an overseas remittance service provided by Shinsei Bank. They charge a 2000 yen flat fee per transaction. You should replace this with whatever local remittance service you use (Western Union, MoneyGram, TransferWise, etc).
  • GoRemit's daily exchange rate - the exchange rate for your remittance service, in my case the USD/JPY rate from GoRemit's web page.
  • US Bank wire transfer fee - as if the GoRemit fee wasn't enough, my bank also charges a fee for incoming wire transactions. And I suspect yours will too. Note: this amount is in dollars.

Variables

The values that go with the above descriptions. Fees are expressed in negative numbers while the exchange rate is a positive and means "1 dollar = 102.03 yen".

Unnamed Column E

The units that Variables are expressed in.

Box J3

The total dollar amount that will be deposited in my US bank account. This cell contains a formula that automatically calculates the value based on the supplied numbers in the Variables column. So we see that for the month of October, I sent 130,000 Japanese yen home to the US, which resulted in a deposit of $1215 in my US account after fees. This number gets automatically copied into cell C10 as part of the asset / liability list.

The only number you have to change each month is the exchange rate. The remittance service fee & bank fee should remain constant.

Wait a minute, I see some more stuff you haven't explained yet:

PostImg

Move along, nothing to see here... seriously, just ignore this part of the spreadsheet for now. This is for remitting money via Bitcoin instead of a traditional remittance service. Next week's blog post will focus on the steps involved in a Bitcoin remittance process, and show how to use my spreadsheet to calculate your savings vs. using something like GoRemit.

What if I decide not to send any money home this month?

You can just set the "Money Sent Home" value in cell D14 of the Monthly Budget tab to zero. But if you do this, the value in C10 of the Credit Card Expenses tab will be negative due to the way fees are subtracted in the formula for this cell. To compensate, set the "Offsetting negative money sent home" value in cell C26 so that it exactly cancels out the negative "Money Sent Home". For example, if "Money Sent Home" is -60 then "Offsetting negative money sent home" should be 60.

Note that the "Current US Savings" value represents the total amount in my US bank account at the start of the month, exactly analogous to the "Leftover from Last Month" value on the Monthly Budget tab.

So is that it?

Almost. Every month I reserve a small amount of money for Bitcoin investments. The mini-worksheet at the bottom of the Credit Card Expenses tab is used to compute the cost based on how much Bitcoin I want to buy:

PostImg

The total in B35 gets rounded and copied into the asset / liability list as the "Send to Coinbase" value in cell C24. If you don't invest in cryptocurrencies, simply delete this value and the mini-worksheet. Or adapt it to suit the nature of whatever you do invest in.

And that's it. I hope my spreadsheet proves helpful to you! Feel free to let me know your thoughts in the comments below. Your own financial planning tips or suggestions for improving the spreadsheet are quite welcome.

Links to Awesome Niftiness

Once again, here's the Google Drive link to Personal Budget Planner.xlsx: https://drive.google.com/open?id=0B03QfEeDXBoiRU5XaUlNYTVNR0k

My fellow expats living in Japan may like to learn more about GoRemit: http://www.shinseibank.com/goremit/en/

GoRemit daily exchange rates: http://www.shinseibank.com/goremit/en/exchange/

For more posts about cryptocurrency, finance, travels in Japan, and my journey to escape corporate slavery, please follow me: @cryptomancer


Image credits: all images in this post are screenshots taken from my desktop PC. The spreadsheet featured here is my own invention. No parts of it were borrowed or copied from other sources.

Coming next week: stay tuned for a follow-up post where I detail the steps for performing an overseas remittance with Bitcoin, and show how to use my spreadsheet to calculate how much money you will save by avoiding the traditional banking system altogether.

Sort:  

@cryptomancer always love to see others posting financial education on steemit, we are a small portion of the current community.

Been running similar spreadsheets for years keeping an eye on budget and net worth. Good post!

Thanks, glad you liked it!

I've had several bouts of financial planning, first one I've done for a couple of years several years ago, and to great success, even without really understanding what I was doing (as in, most of the planning involved slashing the unnecessary expenses, without actual plan for the expendures).

Even with great results once the supporting factor for it was gone (that is the girlfriend I had at the time), I kept going for a bit, and then stopped.

Another go I had after getting YNAB on Steam, which was great. Still is, although they've moved to yearly subscription model.
However, what is best about them is those videos and free step by step lessons they offer on how to actually go about planning your finances. While I am still sometimes spending a bit more than I make, things are much more manageable now, thanks to the lessons and the software.

They've broken the process into a four simple rules, which are brilliant stuff. Perhaps even something that you can modify your way of doing things, perhaps not, but it might be useful to other readers.
It goes like this:

  • give every dollar a job — that is plan on putting all the income somewhere, liabilities, savings, etc.
  • save for the rainy day — this one is about breaking up big yearly, biyearly etc payments into smaller pieces, as well as saving a bit for unexpected expenses.
  • roll with the punches — be flexible about the budget, when required.
  • live on the last months paycheck — going with the things above, after some months you'll have a buffer, that allows you to sleep better, in case things go wrong. Also really good for "paws feed the wolf" people (is there an English phrase for that?), like freelancers and enterpreneurs.

Anyhow here is a link to their method, which is explained much better than I did. That thing (the method, not the software, although it is handy) is really helping me, and perhaps it might help someone else out. :-D

Wonderful tips, thanks for sharing this resource! But can you double check the link? I get a "404 Not Found" error when I click on it. I think my spreadsheet nicely complements these rules, which could serve as an overall guiding framework.

Not completely sure what you mean by "paws feed the wolf" people. Maybe the phrase you're looking for is living hand to mouth ?

Fixed, it seems that steemit is forcing https if you don't specify. And I am talking about people that have as much money as the ammount effort they put in. That is wolves have to run huge distances to stay fed, thus the Russian saying that "paws feed the wolf".

Link works now, thanks. Paws feed the wolf sounds a little bit different from living hand to mouth. It's a good expression just as a direct translation from the Russian I think, and makes me think of the vast natural beauty of your country. Steemit itself is a good platform for people like that.

Yep, it is another grove, another steppe where some sweet sweet food can be found. And I am really greatful for that, even if not every hunt brings back the meat. :-D

@cryptomancer Upvoted 100%
I love this post specially the title!
SO TRUE MONEY CAN BE MANAGED! Not the other way around!
You use columns, I use jars and differently, too.
I wish you all the best financially.
I wonder if you're growing geese, too and I mean this figuratively :)

Thanks for the kind words. Managing your money instead of letting it manage you is one step on the long road to achieving a successful life! Here's wishing you all the best as well.

Coin Marketplace

STEEM 0.19
TRX 0.12
JST 0.027
BTC 64886.88
ETH 3516.64
USDT 1.00
SBD 2.37