Regular compound interest is (basically) the way most loans and savings accounts work, including home mortgages. Here, we’re going to use a spreadsheet to calculate a home mortgage payment estimator (and even a full payment schedule) using the principles of compound interest.

Fire up your spreadsheet and enter the following information into cells:
In A1, enter Simple Mortgage Calculator
In A3, enter Amount Borrowed
In A4, enter Length (in Years)
In A5, enter Interest Rate
In A7, enter Monthly Payment
In A8, enter Number of Payments
In A9, enter Total Interest
And in A10, enter Total Loan Cost

That’s a lot of labels. We’re going to use the first three numbers to calculate the last four, so enter some dummy values in B3, B4, and B5. I used $180,000, 30, and 8.00%. Then we begin setting up the calculations:

The piece in B7 looks really strange. This is what should be typed in:

=-PMT(B5/12;B4*12;B3)

If you’re using Excel instead of OpenOffice, it will look just a bit different:

=-PMT(B5/12,B4*12,B3)

This PMT function is a part of the spreadsheet that calculates how much a monthly payment will be for you. The interest on each payment will be 1/12th of the annual rate (hence the B5/12 part) but you’ll be making 12 payments a year (hence the B4*12 part). The number that appears here is the actual cost of a monthly payment given the terms you set up in B3, B4, and B5.

The other three numbers are fairly straightforward:
To calculate the number of payments (in cell B8), enter =B4*12
To calculate the total interest paid over the loan’s lifetime (in cell B9), enter =(B7*B8)-B3
To calculate the total cost of the loan (in cell B10), enter =B7*B8

In the end, you’ll get something that looks like this:

You can change the first three numbers however you want to look at various different possibilities. Want to make it even cooler? In cell A13, write Month 1, then click and drag the black square down from that cell until you can see month 360. Then, in B12 through E12, add these labels: Starting Balance, Payment, Interest Paid, Principal Paid, and Ending Balance. Yep, we’re going to see what exactly each payment is doing.

To set up the numbers, you’re going to have to enter some more formulas into the first couple rows of this little table, but after that, you can just drag down to fill in the rest.

In B13, in order to set the starting balance for the first month, you want it equal to what you entered above, so enter =B3
In C13, you’ll want it to always equal the payment calculated above, so enter =$B$7
In D13, you want to see how much interest you paid that month, which would be 1/12th of the annual interest (set permanently in B5) on the current balance, so enter =B13*$B$5/12
In E13, you’ll want the amount of principal paid this month, which is just the difference between the total payment and the interest paid, so enter =C13-D13
In F13, you’ll want to calculate the balance at the end of the month, which is the starting balance minus the principal paid, so enter =B13-E13
And finally, in B14, you just want the same starting value as the ending value from last month, so enter =F13

You can click on the cells B14, C13, D13, E13, and F13 and drag the black square in the lower right of each one all the way down until it lines up with Month 360. Don’t worry about weird values until you’ve done all five columns.

When you’re done, you’ve got a full mortgage amortization calculator - and you understand completely how the math works! Be sure to save this file for later - it’s useful!

Now that you’ve assembled your own mortgage calculator, you can see that the possibilities of using spreadsheets to do personal finance calculations are endless. Good luck!

Previously, we discussed how compound interest works on a year-by-year basis, but in the real world, interest is usually compounded more often than that. For many purposes, monthly compounding is used, so let’s look at monthly compounding. Fire up your spreadsheet and enter a few labels:
In A1, enter Monthly Compound Interest Example
In A3, enter Amount
In A4, enter Annual Interest Rate
In A6, enter Monthly Interest Rate
In B3, enter $20,000
In B4, enter 5.00%

You’ll end up with something that looks like this.

You’ll notice that in B6, I’m about to enter =B4/12 … what does that mean? That’s simply how to figure 1/12th of the annual rate of interest, or the piece of the annual interest that happens in a single month. When you enter that formula in the cell, you’ll see the number zero. Don’t worry, just click on that cell, go up to the Format menu above, choose the Cells option on that list, then in the popup box choose Percent and have it show two decimal places. After you do that, you’ll see a value of 0.42%, which is the actual monthly interest rate if the annual rate is 5%.

Now, if you’re doing annual compounding, a calculation is very easy. In cell A8, write If annual, interest is: and then in B8, enter =B3*B4 … you’ll see that with annual compounding, you’ll earn $1,000 in a year.

If you’re doing monthly compounding, though, it’s a little different. To see this in action, you’re going to have to set up some more labels:
In A10, enter If monthly:
In B10, enter Balance
In C10, enter Interest
In A11, enter Month 1, then click on the cell and drag the little black square in the lower right of the cell downwards until you can see all the months up to Month 12.
In B23, enter interest is:

You should have something that looks like this:

Now, the math. For the first month, the balance is the same as the amount, so enter =B3 in cell B11. How much interest will that earn in a month? Enter =B11*$B$6 in cell C11 to find out; for our example, it’s $83.33. If you were to multiply this amount by 12, you’d find that it is $1,000, which is the same as the annual interest earnings.

But things change at the start of the next month: it compounds. In B12, enter =B11+C11 … and then click on that cell and drag the black square down until it’s lined up with Month 12. Do the same in the C column, starting with cell C11. You’ll notice that each month, the interest earned is a little higher.

So how much did you actually earn in the year using monthly compounding? In cell C23, enter =SUM(C11:C22) … this formula basically says add up everything between C11 and C22. The total in our example is $1,023.24.

Right now, you’re seeing the difference between APR and APY. APR is the annual percentage listed above: 5.00%. However, APY is not equal to that. If you want to figure out the APY here, type APY in cell B25, and in cell C25, enter =C23/B3 … and then reformat the cell to be a percentage, as mentioned above. You’ll get a value of 5.12%.

Whenever a bank mentions an interest rate to you, they’ll give you the APR when they’re lending you money but give you the APY when you deposit money with them. To the uneducated, it makes the offer seem better, because almost everyone outside of the financial industry uses these values interchangeably. Thus, when you see a savings account with a 5.05% APY, the actual interest rate they’re giving you is lower; you can just earn a 5.05% overall return if you don’t touch the money at all.

Next time, we’ll take a look at mortgages and build a simple mortgage calculator.

Several readers have written me excitedly asking how exactly I do some of the calculations on this site and how compound interest works. Usually, I point them at various online calculators, but the truth of the matter is that a basic understanding of how a spreadsheet works and how compound interest works makes it possible to calculate almost every personal finance situation that you want.

I originally wrote this all as one post, but I’ve since split it up into several, which will be posted in order throughout the weekend. If you found this piece enlightening, check back at the end of the weekend and there will be plenty of interesting things to try out!

Also, be aware that this first entry is intentionally very simple so that people who are unfamiliar with spreadsheets can follow along. If you know the basics of using a spreadsheet and the very basic idea of compound interest, this will probably seem very basic for you. That’s fine; tune in later and we’ll be kicking things up a notch.

Anyway, in the interest of saving money, I’m going to use OpenOffice Calc for these examples. For most things such as this, it is functionally identical to Microsoft Excel, so if you have a copy of Microsoft Office, you can basically follow along with these examples. I attempted to use Google Spreadsheet for examples, but it was quite frustrating to use for repetitive calculations. OpenOffice Calc is free, open source software; you can get it for free at OpenOffice.org.

Got a spreadsheet now? Let’s get started.

What is interest? Interest is the “rent” paid to borrow money. When you put money in the bank, you are essentially lending your money to the bank so that they can use it for various investments. In exchange for that, they pay you a small amount of interest. Similarly, when you use a credit card, you are borrowing money from a bank, and for their investment in you, you must repay that investment with interest.

What is compound interest? Compound interest means simply that rather than directly giving the interest back to you, it is instead added directly to the amount of money you loaned or deposited. After that interest has been added to your balance, your balance is now higher, and thus the new balance earns more interest than before.

Let’s get started and see how this works. Fire up OpenOffice Calc (or Excel). You’ll see the following screen:

This is the default screen for OpenOffice. If you’ve ever used Excel or any other spreadsheet, this should feel familiar; if you haven’t, play around for a bit. You’ll find out that each little rectangle can be filled with information. This is really cool because we can make those rectangles do automatic calculations for us really easily, and those calculations can really illustrate what’s going on with our money.

In the first four cells of column A, I’ve entered a few labels. These will be used to describe the data we’ll put in column B to the right. You’ll see that cell A4 is highlighted (it’s called A4 because it’s in column A and row 4 - look to the left of the row to see the number and to the top of the column to see the letter) and in the lower left there’s a little black square. If you click on that little black square and drag downwards, you’ll see that the spreadsheet will automatically fill in more years for you. Nifty! This auto-fill feature saves a ton of time for doing repetitive calculations as we’ll see in a bit.

We want to do some compound interest calculation, so we need to enter a balance (put this in cell B1, to the right of where we labeled “Balance”) and an interest rate (in B2, just to the right of the “Interest Rate” label). You should be sure to put a percent sign at the end of the interest rate value so the program knows you’re talking about a percentage and you might want to also put a dollar sign in front of the balance amount so the program knows you’re talking about dollars. When you’re done, things will look something like this:

Notice that in cell B4, I’ve also written =B1+B1*B2 … what does that mean? I’m telling the program that in this cell I want to see the value in B1 (the balance) plus the value in B1 multiplied by the value in B2 (the interest). This is the basic interest calculation: we want to see the original balance (B1) plus the interest earned a year (B1*B2). If you type in =B1+B1*B2 into that cell and hit enter, you’ll see the result of the calculation: $10,500.00 in this case.

Here’s the neat part, and this will hopefully begin to show you the power of a spreadsheet. Go back up to B1 and change the balance to something different. As soon as you do that, the calculated balance in B4 automatically changes! You can change the interest rate in B2 as well. Throw in all kinds of numbers to see what happens.

Now we’re going to see what happens in year two. This time, in cell B5, we’re going to enter the formula =B4+B4*$B$2 … that’s a bit different than before. Just bear with me for a minute. When you enter that formula, you’ll see that it takes the value in B4 and uses that as the initial balance to calculate the second year’s worth of compound interest, giving you a new balance of $11,025.00 in this example.

Now, click on B5 and then click and drag down on that black square in the lower right, all the way down to the label for Year 20. When you let go of the mouse button, you’ll see that the calculation for all of the years is done. This is why we did the $B$2 part; the program knew to automatically change the balance each time, but we wanted to tell it to always point back to B2 for the interest value no matter what. You can jump back up to the top and play with the interest rate and the balance to see compound interest at work. You can keep dragging down for as far as you wish, but after a while you start looking at numbers that aren’t really feasible for your lifetime.

If you want to see how much interest is actually earned each year with compound interest, just start another column off to the right as pictured above. In the Year 1 row in cell C4, enter the formula =B4-B1 to see how much interest was earned in year one…

… and then enter =B5-B4 in cell C5 to calculate how much is earned in year two. Once you’ve done year two, click on cell C5 and then drag that black square in the lower right of the cell down again to automatically calculate the earnings for all of the other years. See how that money grows? That’s the power of compound interest.

Next, we’ll tackle the idea of how monthly compound interest works, learn what the difference is between APRs and APYs, and see how that affects you.

While doing some calculations for my income taxes, I stumbled across the statement from my checking account for February 2006, just before my financial meltdown. Amused, I spent some time really looking at the staement when I noticed that there were several ATM fees on the bill and a few sales tax dings on those withdrawals. I added these fees and taxes up and gasped. The total was $30.28.

Why did you have all of those fees? Admittedly, my fee count was higher than most, but that was mostly due to traveling. I used several out-of-network ATMs that month, mostly to have cash on hand for incidentals, and the fees really began to add up.

The worst ones? I would withdraw $20, pay a $1.50 fee from that ATM’s bank, pay a $2 fee from my own bank, and pay a $0.14 sales tax on that fee. Total money lost on a $20 cash withdrawal? $3.64, or an 18.2% fee. That’s worse than buying something on a credit card and leaving it there for months.

Here are five tips for avoiding a pile of unnecessary ATM fees.

Use your ATM card (where you can) directly for purchases. Most ATM cards today are also check cards, meaning they work as credit cards almost everywhere. Instead of using an ATM card and suffering those fees, use your check card for the purchase directly and avoid the fees. The vast majority of banks provide this for free, so you should use it.

If you have other options to pay besides using your pocket cash, use it. I generally reserve cash itself for situations where that is the only solution (tips, paying the pizza guy, and such). Otherwise, I try to use my cards as much as possible. If I use a credit card for something, I make sure to pay off the balance ASAP.

Know your network. Most ATM cards have a network logo on the back so you can easily identify whether or not a given ATM machine can be used without fees. Know that logo so you can quickly tell if a given machine will work for you. If not, strongly consider other ways of making the purchase that you wish to make.

Keep some pocket money at all times. Don’t expect to spend this money unless you have no other choice. I usually keep a pair of twenties (and a few smaller bills) folded up in a back pocket of my wallet for such events; it’s generally out of sight and out of mind until a vital situation comes up.

Keep your head on straight when it comes to finances. If, after all this, you still feel the need to use an out-of-network ATM, use the ten second rule and consider whether or not the item you’re considering buying is really necessary. Could you live without it (and also without that extra ATM fee)? The answer is probably yes.

Recently, I’ve received emails from people asking me what I think of various checking accounts, such as ING’s Electric Orange or EverBank. I realized that time and time again I used the same criteria to compare various checking and savings accounts, and thus I thought I would share these criteria with you.

I rank these criteria in the order I value them; thus, a winner in the first two criteria will overcome another bank that wins in everything else, for example. If you want to “score” banks, give 4 points to the first criteria’s winner, 3 to the second, 2 to the third, and 1 to the fourth.

1. FDIC insured accounts. If a bank’s accounts are not FDIC insured, I won’t bank there, period. FDIC insurance guarantees you up to $100,000 of your money back in the event of a collapse of your bank. Most young people aren’t aware that banks are businesses and they indeed do go out of business, so this insurance means that the federal government is insuring you against the event of a bank collapse. As a depositor, this is essential, because in the event of another savings and loan crisis, you have a huge risk of losing everything. Thankfully, almost all banks today are FDIC insured. If you don’t know, check with them.

2. Fees. Fees trump almost everything else about an account, and it doesn’t take much at all to make one bank a huge clear winner over another bank. Here are some common bank fees to look out for; if you’re comparing accounts, make sure you know what these fees are going to be for each account and also have a good idea of how often you’ll be dinged for them:

Overdraft fees
ATM fees
Out-of-network ATM fees
Transaction fees
Maintenance fees
Online banking fees
Online bill pay fees
Minimum balance fees

Before you even consider opening a new checking or savings account, be aware of all of the fees you may be charged with. This is especially true if you’re being tempted to jump to a different account because of a higher interest rate, because if they charge more fees, you’ll end up losing ground overall.

3. Customer service. Many people undervalue this, particularly for savings accounts, but customer service makes a huge amount of difference when you’re trying to execute transactions. Are transactions easy to execute? Are they immediate, or is there a delay? Is it easy to talk to a customer service representative on the phone? Customer service is a huge advantage that brick and mortar banks generally have over online banks, as you can simply stop by a branch and have your questions answered and issues resolved, which is particularly important if you have documents to sign, change to sort, or other such service needs.

4. Interest rates. Interest rates matter, but they are completely trumped by fees and partially trumped by customer service. Take, for example, EverBank. Their checking account interest rate of 6.01% APY is stellar. However, they have no ATMs of their own, which means you get dinged with a $1-$3 ATM fee every time you withdraw cash. They will reimburse you up to $6 each month to cover part of this, but this recurring fee basically eliminates the interest rate advantage that their accounts offer in my situation. If you are in a situation where you never use an ATM at all, then this fee is negligible, but it’s easy to see why fees trump interest rates.

If you’re considering switching, be sure to compare all of your options using the above criteria. More than anything, though, don’t overvalue the interest rate. You should be quite willing to accept multiple points lower in interest rate in order to minimize fees and maximize customer service.

If you haven’t heard the news, HSBC Direct is running a promotion from January 29 to April 30, 2007. During that promotion, all new deposits earn 6.00% APY through April 30, 2007. Naturally, given my curious self, I couldn’t help but wonder how good of a deal this is, so I used a pretty standard situation as a model to estimate how much that’s actually worth to an investor.

Let’s say that on January 29, 2007, Bill opens an account with HSBC Direct and deposits $100 in it. Bill wants to build an emergency fund, so he schedules a $100 deposit each week. I’m also going to simplify HSBC’s method of making interest payments just a bit, and say that they deposit all of the interest from the previous month into the account on the 22nd of each month, which is roughly accurate.

On May 1, Bill wakes up and checks his account. With the old rate of 5.05% APY, Bill would have earned $8.87 in interest over the period. With the new introductory rate, Bill earns $10.53 over the period, meaning the introductory rate earns Bill an extra $1.66 in interest. That’s not bad, considering the relatively small amounts that Bill’s working with.

However, the best way to leverage this feature is to deposit a large amount into the account. Let’s say Jill deposits $50,000 into her HSBC account next Monday morning, February 5. She heard about the interest rate and wants to capitalize on it. On May 1, Jill checks her balance. With the old 5.05% APY, Jill earned $576.38 on her money, but with the introductory 6.00% APY, she earned $684.45 in interest, a difference of $108.08.

What’s the drawback? There are two big drawbacks with this offer: it only affects new deposits (meaning that money already in your HSBC account gets the old 5.05% APY - and by new they mean anything over your balance on January 29, so a withdrawal and redeposit won’t help) and it ends on April 30. For me, it’s not enough to make HSBC my primary savings account for my emergency fund, but as a person who uses HSBC for a small savings fund, I quite like the extra money.

However, if you’ve never tried out an online savings account before, this is the most lucrative offer I’ve heard of, and it’s from one of the leaders in the field, backed by the FDIC and by one of the largest banks around. Just visit http://www.hsbcdirect.com/ and see what you think.

Recently, CNNMoney.com posted an article containing 35 outrageous fees, along with tips on avoiding them. Unfortunately, due to the terrible layout of the article (you’re forced to jump from page to page in a very awkward fashion), it’s almost impossible to see all of the fees and also jump to the one you want to read.

I did the footwork for you, and here’s the list of Money Magazine’s 35 Outrageous Fees. Each one is linked to the appropriate page on the CNN/Money site so that you can jump straight to the information about that fee. After the list is a bit of general commentary.

1. Airlines: Paper tickets
2. Airlines: Talking to a person
3. Changing your flight
4. Airlines: Reserving an aisle seat
5. Overpacking
6. Car rental: insurance
7. Car rental: excise taxes
8. Hotel fees: Internet connection fee
9. Hotel fees: Resort fee
10. Hotel fees: Automatic gratuities
11. Hotel fees: Package delivery fees
12. Hotel fees: Mini-bar restocking
13. Phone surcharges: Carrier cost recovery fee
14. Phone surcharges: AT&T’s tax-related surcharge
15. Phone surcharges: Property tax surcharge
16. Phone surcharges: Activation fee
17. Phone surcharges: Cancellation penalty
18. Gift card activation fee
19. Junk closing costs
20. Title insurance
21. Biweekly payment fee
22. ATM fees
23. Monthly service fee
24. Bad deposit fee
25. Foreign currency fee
26. Credit card late fees
27. Balance transfer fee
28. Over-limit fee
29. Same day payment fee
30. Mutual fund fees: Index funds
31. Mutual fund fees: Large U.S. stock funds
32. Mutual fund fees: Target-date retirement funds
33. Variable annuity expenses
34. Brokerage transfer fees
35. 529 expenses

It’s a good list, but most of these “outrageous” fees boil down to consumer irresponsibility, unfortunately. Many of these are due to violations of clearly-stated policies by companies; just follow their policies and use some common sense and you’ll skip right over many of these fees. However, there is one fee that really irritates me that isn’t listed here: a fee to deposit loose change into your savings/checking account.

I’m on the verge of taking the plunge and switching to ING’s Electric Orange checking account, but one thing has kept me from switching over: the hassle of moving all of my deposits and transfers to a new bank.

Given that, I’ve prepared a plan for moving to a new checking account that should pave the way to making the transition substantially easier than it was the last time I attempted this feat. Here’s what I plan to do.

1. Open the new checking account. The first step is the most obvious one. Open the account and get the information you need: account number and routing number. Order checks if you need them. In other words, be prepared.

2. Make a list and check it twice. Make a detailed list of all automated withdrawals and deposits from your current primary checking account. The best way to do this is to simply watch the account for a period of two to three months so that you pick up as many of these as possible.

3. Balance your checkbook. Make sure you’ve accounted for everything outstanding so there are no nasty surprises during the transition. Figure out what you have in the old account down to the cent so that you can avoid overdraft dangers.

4. Switch over all deposits and withdrawals at once. I find this is easiest to do by switching over the deposits a bit earlier than the withdrawals, so that there is money already in the new account when deposits begin to be set up. I’m also incredibly careful about such things.

5. Leave the old account open for a while with a balance in it to catch any missing deposits or withdrawals. Even though it might feel like the balance in the old account is just sitting there wasting time, it’s actually there to protect you against your own poor memory. Just be patient and give it several months; you might surprise yourself.

6. Close the old account. Be sure to leave a correct address behind. You might also want to end other services at that bank, such as a safety deposit box.