Recession

There's one thing about the recession (and the economy in general) that really bugs me: Debt. Not personal debt so much, but debt owed by governmental bodies.

People complain about how we are becoming a nation of spenders, without saving any money. Where do you think this habit came from? Watching our government! The federal government as well as every single state (as far as I know) owes money to somebody, and is paying interest on those loans.

But, you say, how else would things get done?

Like they get done in the households of people who know how to save! If I want to buy a computer, I save money until I can afford it, and then I purchase it. The same thing goes for jewelry and virtually everything else less than the price of a car.

Loans exist to satisfy the impatience of people. I don't want to wait for 4 years saving money to purchase a new car, when I can purchase it today and pay for it over the next 6 years (notice the time difference there). This is especially true for houses; I don't want to save my money for 20 years to purchase a house, when I can buy it now, move in, and enjoy it while paying for the next 30 years.

But why are governments impatient? Sure that road needs to get paved today, but why not put money aside so that when it needs repaving in 20 years, we don't need to take out a new loan for it?

My problem with governmental debt really boils down to this: It is a black hole. If you borrow money to do something today, then you will need to not only pay back more than what you borrowed, you will also need to borrow more to do that same thing next year (or in five years, or ten years, ...).

So why don't governments save money? Why not tax a little more than you need, invest, and make money, rather than tax less, take out a loan, and lose money (which you will need to replace by taxing more in the future)?

Because elected officials want to lower taxes now, and do not care about the future. I would rather that my taxes went up by 5% today, the government stopped taking out loans, paid off their debt, and was able to drop taxes by 10% in ten years, than have my taxes increase by 1% each year out to the foreseeable future.

So here's my advice to government officials: Once we get out of the current recession, STOP TAKING LOANS! Pay off all of the debt, save some money, and lower taxes as the interest payments start going the OTHER way. It'll be a good model for your citizens, too.

High Finance Using Excel

After making that last post, I realized that I might be using a few Excel features that other people don't know about. So now I'm going to explain how that mortgage calculator works.

Basic Excel Features

What is that equal-sign for?
Honestly, if you don't know this, you should be reading a real Excel tutorial. But basically, it means that Excel needs to compute the value for that cell instead of just displaying what you type into it.
Example 1: In cell A1, type the number 5. In cell B1 type "=A1" (without the quotes; never type the quotes unless I say specifically that you should). When you switch out of cell B1, it will display the value of cell A1.
Example 2: In cells A1 through A5, type the numbers 1 through 5. In cell B1, type "=sum(A1:A5)". When you switch out of cell B1, it will display the sum of all of the cells between A1 and A5, which should add to 15.

What are those dollar-signs doing in there?
If you place a dollar-sign ($) before a row or column identifier, that identifier will not change when you copy/paste the cell it is in.
Example: Set cell A1 to "=B1". Set cell B1 through B5 to the numbers 1 through 5. Set cells D1 through D5 to the numbers 6 through 10. Copy cell A1, and paste it into cell A2. Notice how the reference updated and now it is showing the value of cell B2? Now, copy A1 again and paste it into cell C3. See how both references changed and now it's pointing at D3?
The dollar signs prevent that. Set cell A3 to "=$B4". Copy cell A3 and paste it into cell C4. The reference to column B remained the same because the dollar-sign was there, but the row reference changed. Set cell A4 to "=B$2" and copy it into cell C5. It should now reflect the value of D2.

Function Descriptions

Date: The date function takes a year, month, and day number and makes them into a real date reference. So calling "date(2001, 1, 1)" should give you January 1, 2001.

Year/Month/Day: These functions extract portions of an existing date, allowing you to do some calendar math. I used them to increment months in the table of mortgage payments. By doing "date(year(B2), month(B2) + 1, day(B2))" in cell B3, I was able to have cell B3 be one month ahead of cell B2. Copying/pasting this down the B column continued incrementing the months, and Excel was smart enough to automatically roll over the years when the month got too large.

Sum: Pretty obvious; it takes one or more cells or cell ranges and adds all of the values together.

IPmt: Computes the amount of a periodic loan payment that goes towards paying the interest on the loan, rather than paying down the principle. You use it like this:
ipmt(rate, period, number of periods, present value [, future value]) (the stuff in [] is optional).

• rate is the interest rate per period. If you are working with monthly payments, it will be the annual interest rate divided by 12.
• period is the current period of the loan. This actually isn't very useful, because if you want to reduce your principle by pre-paying at some point, using this will cause a mis-calculation. I recommend always using the value "1" here.
• numer of periods is the length of the loan. If you are working with monthly payments, this is the number of months (so multiply years by 12).
• present value is the present value of the loan if you are using period = 1; if you are using period = current period, it is the original loan balance.
• future value is the target value for the loan (in case you only wanted to pay off part of it). Defaults to 0, which is what pretty much everybody wants.

The function returns how much of the current payment will go towards interest instead of paying down the principle. It returns a negative number when you use it like this because that is the cash-flow model Excel uses. The model follows the perspective of the person paying the loan, so negative numbers are cash flowing away from you.

Pmt: Computes the periodic payment amount necessary to pay off a loan in a certain amount of time. Usage:
pmt(rate, number of periods, present value [, future value [, type]])

• rate is the interest rate (per period) of the loan; if you are working with monthly payments, this will be the annual interest rate divided by 12.
• number of periods is the length of the loan. Again, if you are working with mothly payments, it will be the number of years in the loan multiplied by 12.
• present value is the size of the loan.
• future value is not generally needed, but if you only wanted to pay down the loan to a certain amount in that time, you would type that in here; you probably want this to be 0, which is the default.
• type specifies when payments are due; 0 (default) means payments are due at the end of each period, 1 means payments are due at the beginning of each period.

The function returns how much each payment will be. Since payments are cash flowing away from you, the return value will be negative for this kind of usage. (It could be used to determine monthly income if you lent money to someone else if present value was negative, meaning that the loan moved money away from you.)

These are some of the other functions related to finance that I didn't use:

• ISPmt: Calculate interest paid during a period of a loan given the interest rate, period to calculate up to, number of periods, and present loan value.
• Rate: Calculate the interest rate of a loan given the length, periodic payment, and size of the loan.
• NPer: Calculate the length of a loan given the interest rate, periodic payment, present value, and optionally the future value and payment type (beginning/end of each period).
• PPmt: Counterpart to IPmt; calculates the amount of principle paid down by a periodic payment given the interest rate, period, number of periods, present value, and optionally the future value and payment type.

Hope this was useful, and happy spreadsheeting!

Mortgages

My wife and I just bought a house on Friday. It's our first house, after living in two different apartments, so we are very excited about finally paying for something that will hold a value. We both feel like we're dumping money into a black hole by paying rent, whereas with a house, each mortgage payment is adding more and more to the portion of the house that we own, instead of the bank.
So today at work, I decided I'd like to see what the payments and whatnot will look like. Like any good computer guy, I built up a nice Excel spreadsheet to list everything out. Turns out the whole mortgage thing is a little frightening! We'll be paying for this house through about 2038, and we'll end up shelling out about 2.25x the purchase price!
Obviously, this got me thinking of ways to pay it off a little early, and so I decided to make the spreadsheet easy for anyone to use. Assuming I can figure out how to post it in here somewhere, I'll release it for everyone to take advantage of...
Update: So it seems Blogspot is unable to accept files other than images/video, nor can I place it into a CDATA section within a text area. So until I find a good hosting service, you'll just have to type this stuff in yourself. Here is how I set up my spreadsheet:

Excel Mortgage Calculator

Across the top starting at cell A1, create bold, centered column headers:

• Year
• Month
• Payment
• Interest Paid
• Principle Paid
• Remaining Balance
• Paid So Far

On the next row, fill in a few of the cells like this:

• 1
• =$J$4
• (Empty)
• (Empty)
• (Empty)
• =$J$1
• (Empty)

On the next row, fill in the cells like this:

• (Empty)
• =DATE(YEAR(B2), MONTH(B2) + 1, DAY(B2))
• =$J$6
• =-IPMT($J$2 / 12, 1, $J$3 * 12, F2)
• =C3-D3
• =F2 - E3
• =SUM($C$3:C3)

Now, copy that last row and paste it into every row through 362. As a shortcut, after copying the row, you can select that whole huge block of cells and paste; they'll all fill in properly. After filling those in, write in the year numbers down column A, one every 12 cells (so cell A2 has "1", A14 has "2", and so on down to "31" in cell A362).
Next, create a small table over on the right side by creating bold labels down column I like this:

• Starting Balance
• Interest Rate
• Term (years)
• Starting Date
• (Empty)
• Monthly Payment

Values for those labels you just created go in column J. For now, just enter "30" for the term, and "=-PMT($J$2 / 12, $J$3 * 12, $J$1)" for the monthly payment.
You're almost done now. Everything would work, but it wouldn't be very legible, so let's do some quick formatting:

• Click on the "B" column to select the whole thing, go to the menu item "Format | Cells", choose "Date" as the category and "Mar-01" as the type.
• Columns C through G get the "Currency" category with negative numbers marked as red and in parenthesis.
• Cells J1 and J6 get the same Currency formatting as columns C-G.
• Cell J2 gets formatting as a Percentage with 3 decimal places.
• Cell J4 gets the same Date formatting as column B.
• Feel free to change the column widths however you like.

To Use

Type in your mortgage terms in the little table at the right. It defaults to 30 years because that is common and it prevents Excel from filling all of the cells with error codes, but you can change the value however you need. The length of the table won't change if you change the term, though, so you may need to copy/paste some new rows or delete/ignore existing rows.
The monthly payment will automatically be calculated based on the values you set above; if it is not the same as your real mortgage, it is probably due to extra fees charged by your bank (such as mortgage insurance), which have nothing to do with your loan payments and are only tacked on for convenience.
Now the table should be listing out how your mortgage will act as you pay it off. To pre-pay portions of it, just change the payment for the month to see how things change. For example, you can save quite a bit of time by paying an extra $1000 per year or by paying as if it was a 20-year loan whenever possible.
I hope this helps all of those other new home-buyers like myself figure out how all this financing stuff works. Please feel free to leave comments on how you like the spreadsheet. I'm sorry I wasn't able to post the original.
2013-04-22 Update: This spreadsheet is completely compatible with OpenOffice and LibreOffice, which is what I use now.
2016-02-28 Update: I have since switched to Google Docs, which means I can make a template so anybody can copy and edit without needing to trust a downloaded MS Office file. Just go here and choose Make a Copy from the File menu. I've changed the formatting a little from what I described above.