Home > Category: How To...

Viewing the 'How To...' Category

How To Track Savings Subaccounts

January 20th, 2013 at 04:00 pm

Petunia asked me some questions about my savings methods, and I thought she might find my method useful.

I found this idea on a personal finance blog many moons ago. I don't remember what blog. I would much prefer to link the clear and concise instructions I read many years ago. Big Grin But I will try my best.

One banking benefit that I really don't give a flip about is subaccounts. My spreadsheet method is an alternative to relying on bank functionality.

I suppose I do it this way is so that I can completely divorce *where* my money is from *what* it is. My banking strategy is basically to find the consistently highest interest rates with the least hassle and fees. My savings strategy is to save x for a and to save y for b. Combining the two strategies gives me a headache. Using a spreadsheet just keeps it separate.

At current I basically have 3 savings accounts. I always keep a token amount in my old account when I find a better interest rate and move to a new account. So I will in general always have two online savings accounts. I will close the lowest performing account when I find a better interest rate elsewhere, so there is always generally two accounts. I also have a savings account at my primary credit union, linked to my checking account. I always keep about $1,000 in there. Give or take, it doesn't have to be exact. (My checking account is little more than a conduit, I always run it to $0. The $1,000 account means I don't literally only have $0 at my disposal. You could call it a baby emergency fund. The interest rate sucks, but I have instant access to it. Though frankly, the primary reason I have/use the account is so that I don't transfer money back and forth to other accounts very frequently. You will see what I mean...).

FIRST, you do not need any fancy software to track subaccounts. I use openoffice. You can download it for free. (It is virtually identical to Excel - which just cost a LOT more). Or if you have any other spreadsheet software at your disposal...

I will start with the main sheet and then drill down:

My sub-accounts are: Short-term cash, mid-term cash, medical fund, and cash Efund. (Your subaccounts can be anything and everything. Vacation Funds, down payment funds, debt payoff funds, car replacement funds, however you organize things).

The totals for the subaccounts are being pulled from other spreadsheets. I have one "sheet" for each subaccount.

This first sheet reconciles the total in each subaccount with the grand total of all my savings accounts. **I just manually enter the bank balances at the end of the month.** I check that it is balanced about once every month (after the end of the month). If the difference between both totals is -zero-, then there is success!!

**You do not have to limit yourself to cash. If you have bonds, mutual funds, stocks, whatever, same method can be used.**

Okay, now for the subaccounts.

Let's start with an easy one:

CASH EFUND. This balance has been $5k *forever.* I don't have to do much with this.

My medical savings is the same kind of thing.

The ones I update regularly are the short-term and the mid-term, as I save a set amount monthly towards these savings accounts:

As I mentioned before, the totals just automatically flow through to my first subaccount Total sheet.

I generally enter the income and the outflow to these subaccounts as I make the transfers or payments. Since I generally charge everything, I usually do all this ahead of time before I actually need the money or do the actual transfers. BUT this should still work fine for a more cash method. You don't want to make 10 transfers every month from your bank account (there are legal limits and penalties for that kind of thing!). But, the money I transfer every month generally serves as a buffer until I finalize it all at the end of the month. If I have plenty of money in my checking account I might do my regular "to savings transfers" at the beginning of the month, and figure out what I need to transfer back, later. BUT, if not, I just leave it all to figure at the end of the month. Many months the two figures (to savings/from savings) are kind of a wash. I most often do just one transfer a month, and I will explain that at the end.

**I do think using an electronic check register, like Quicken, is essential with the way I do things. I often just move things around so that my checking balance is always positive. This is far more complicated to do with pen and paper. In a really spendy month I can easily rejigger in Quicken - change a payment date here and there.**

Anyway, so I jot it all down as the info becomes available, but put an "x" next to each transaction when I literally record it in Quicken (or you could do the same when you record it into whatever kind of check register you use - paper or electronic). When I record it in Quicken is not necessarily when transfers are made. This just means, I didn't forget anything in my check register. If everything has an "x", everything will easily reconcile, UNLESS I messed up a spreadsheet formula. Which is usually what throws me off once in a blue moon.

Okay, so now that you see the nitty gritty details for each account, let's go back to the summary.

Got it?


Two more notes:

This is what my $1,000 linked savings account (with crappy interest) generally looks like:

I am not a stickler on the $1,000. It's just if the amount starts to get to be more than $1,000 at the end of every single month, then I know I have some money to transfer to my higher interest savings. Likewise, if the account balance is $5,000, but I know I have $4,000 in bills coming up - I just leave the $5,000 in there.


** I do not do 10+ transfers per month to/from my savings accounts!**

I generally do one transfer per month.

What I do is add up all the money I need to transfer back out of savings (adding up all the little things in my spreadsheets), and put the total in my Quicken register (check register). That could be one total for 10 transactions.

In this example I needed to transfer $1,665 for several different insurances and expenses. This my checking account register:

It's really this simple. I need to transfer $1900 to savings, but I need to pull out $1,665 for expenses. SO, the net is $235.

What I actually do is just transfer $235 to savings. The End!

{Looking at the savings register above, before this last check register, you will note I did just *one* 12/30 transfer for a heck of a lot of transactions. There were like 8 transactions - all I did was transfer $570 to savings at the end of the month}.

**Quicken is a little finicky about this. What I do is delete all savings transfers when they download, and hit the "reconcile" button. I find if I do it this way, and manually reconcile the savings transfers, it works beautifully. If you try to finalize the reconciliation automatically, during the download process - which is quite easy to do by accident - then things can get messy. & it won't let you go back and mark off the savings transfers to make it balance. But after a while you know better NOT to do that. You will learn quickly once you figure out how to make it work. The trick is to choose to reconcile *before* closing off the "accept/match transactions" screen.**

So there you have it. What appears to be a little bit complicated actually just makes my life VERY simple.


For the spreadsheet uneducated, I found this. I am sure there are also lots of useful internet and library resources. My spreadsheets that I have shared are VERY basic.

Text is and Link is

Text is and Link is

I also use spreadsheets to track our net worth, and to track all things financial. ALSO is really great for loan amortizations. Etc., etc.

Mortgage Loan Amortization Tips

November 15th, 2006 at 09:13 pm

I didn't have much to blab about myself today so I figured I would be helpful instead. & give some tips on figuring out your mortgage, or any loan, and how maybe you can pay it off early.

Anyway, a while back I went through some amortization tables and played with how much in extra payments, that I Can afford anyway, I should make on my mortgage to get the most benefit. Basically, what is the minimum amount I Can pay that will shorten the loan the most? For me, paying an extra 11% was the best. I am not sure how it differs on different variables. But another $100/month was doable and maybe shortened it a few years. An extra $150 shortened it 6 or 7 years. $200 only shortened 8 years. I said why bother then $150 is like the sweet spot. I like round numbers so I didn't dig much deeper than that.

I did the extra $150 thing for a while, but that has been out the window since my last maternity leave. We took a big hit on our cash and I am waiting to have $10k in the bank to resume payments again. I am guessing next summer though so not much longer. I then updated my amortization schedule with some future thinking. $150 extra/month for the next 2 years. After that I assume with raises and dh bringing in some money I could really afford $200/month extra for the following 2 years. At that point the youngest will be in 1st grade and odds are dh will have at least a part-time job. So I assume $500/month extra at that point. There is a big difference between my attitude now with kind of struggling to get ahead, vs. when dh can bring home a significant check and our goal then will be to pay off the mortgage as fast as we can. With this plan we will payy off the mortgage in 2022, or Big Monkey's first year in college. That's pretty much how I came up with the $500/month extra amount.

The beauty of making early mortgage payments is not only you are paying down your principal, but you are being charged less interest over time as a result. My current plan has $100k in early payments but also saves $85k in interest over the life of the loan. Hey that is $85k I Rather not spend if I do not need to. Even just sticking with the $150/month saved $65k/month interest. So that is really the sweet spot. That would take 4 more years to pay though, but still shaves 7 years off the original loan.

OF course, there is always the argument that if you have money in the bank earning more, that it is best not to pay off low-interest debt. For now my $215k loan at 5.75% is way beyond my measly few thousand in the bank. But certainly something I will re-evaluate as our means change. I am trying to find that balance where we are saving, paying down the mortgage, and maxing our retirement contributions. IF we can do all 3, great. If not, that is where priorities have to be made. But I know money going to my mortgage every month is money well spent and will not go anywhere else once it is gone. A forced savings plan in a way. Trying to balance all 3 is a challenge, but I have never been a fan of debt, and I decide not to be a fan of my mortgage either. No doubt needed it to get into a house in our region in our lifetime, BUT doesn't mean I won't try my best to pay it off long before it is due. An extra $150/month is pretty simple and doable and saves a lot of interest.

Anyway, I have always felt at an advantage because when I applied for a loan and as I study my loans, I have lots of tools at my fingertips as an accountant. But I Decided I would look for some free tools onnline and share, so you can evaluate your loans and do the same. The same principles should apply to auto loans and such.

First I will go through the basics for the really ambitious, and then share the tools for those who just want to know the bottom line.

How amortization works is you take your current loan balance and multiply that by the interest rate. Divide that by 12 (12 months) and that is your interest payment for the month. Take your total monthly payment and subtract the interest portion, the rest is applied to principal. Subtract the principal paid from the beginning loan balance and that is your new loan balance. You can set up columns in excel to calculate your amortization. Maybe a column with date (month and year), monthly payment, principal portion, interest portion, and balance. The sum of the principal and interest portion should always equal your payment for the month. Put in your calculations for a month or 2 and just copy it down the spreadheet until the remaining balance is 0. IT is also a good idea to add a column for extra payments, then you can throw in extra payments evenly for the life of the loan, or just times when you think you can swing it. You can see the length of the loan shorten as you play with it, and you can get to a point where you are happy.

This is the hard way and I wouldn't recommend it overall, but it sure gives you an understanding of how it all works out.

I actually found this website which had a pretty good amortization tool:

Text is and Link is

The top part of the calculator you put in all of your variables. This is good if you are evaluating a new loan - it will calculate your payment. On the bottom portion you can add extra payments too. The resulting amortization table is really informative - says how much you save in interest with prepayments, etc. You can play with it and see what your sweet spot is.

However, on the above tool, though great, you can't customize to make extra payments here and there, and change amounts over time, etc. What works great for that is an excel amortization template. Here is a free one, you will have to scroll down to the "loan amortization schedule":
Text is and Link is

You just enter the basic loan info, and it will calculate the monthly payment for you. & in the spreadsheet you can change the additional payments line by line if you like. I understand that this template works in open office as well. I haven't tried, but read it does.

And beyond all that read this super cool article I found.
Text is and Link is

I don't know anyone, besides my accounting friends anyway, who even knows or cares about all this stuff. But I figured if I shared here it might actually be appreciated. Wink

& just FYI - this is all in regards to fixed rate mortgages. It does not work the same on ARMS - don't even ask - I have no clue how those work but I always hear scary words like "negative amortization" and such when it comes to other products. I am not sure how much you can prepay principal on some of those ARMs. I tried to find some articles on the subject but it just confused me more...