Mortgage

There is something that you enter after college called the "real world". Many of us put it off for as long as possible, but it finds most of us eventually. If the "real world" has any strict definition I would say that shouldering an eight to five job and a mortgage is it. Just like my little friend here I will be managing these two responsibilities come November.

In order to get a grip on what a mortgage means I fell back on math. I didn't have to fall far having given a class of high school students a budget planning project that centered around paying off a large loan this past school year. The project provided a real world example of a recursive formula. I looked back on this project in order to set up an excel document to model my own mortgage. Anyone who would like to do the same, or who would like to simply learn some essential excel tools, will find this useful.

Begin by opening up an excel document and writing some labels. Label a column near the top "30 year mortgage". Beneath that column in consecutive rows type: interest rate, compounding period, monthly payment, initial total loan or U_0 (U sub zero), total payment, and amount remaining after 30 years. Fill in the corresponding numbers for these labels two or three columns to the right.

Example
interest rate: .065
compounding period: 12 (this is how many times per year the loan is compounded. 12 reflects compounding monthly)
monthly payment: 800
initial total loan: 125000
total payment: not yet known
amount remaining after 30 years: not yet known (hopefully zero)

Note my interest rate looks phenomenal. It is not the case that I am lucky enough to have a .065 percent interest rate. Instead, I am expressing a 6.5 percent interest rate as a decimal. Since 6.5 percent is the same as 6.5 divided by 100, the actual decimal I want is .065. Just take your interest rate as a percent and move the decimal place two spaces to the left.

Defining variables

Next we would like to define some variables. That is to say, we would like to use a word such as "rate" to refer to the number .065. You will see why this is useful shortly.

Select the cell in the excel sheet that contains the number .065, then go to the "Insert" pull down menu at the top of the page. On the insert menu go to "name" then "Define". Finally, type in the word that you would like to use to refer to the interest rate. I chose "irate". You too can choose a word that expresses how you feel.

Repeat the process of defining variables for the compounding period, monthly payment, and initial total loan.

Next move down a couple rows then label three adjacent columns as loan remaining, pure interest, and 12 month interest. Below the "loan remaining" column type an equals sign followed by the name of the variable for the total initial loan. When you press enter the value of that variable will appear in the cell. This won't work if you don't enter the equals sign first.

In the next row down in the same column type equals, then click on the cell in the previous row in which you entered the total initial loan variable. Then type asterisk, open parenthesis, the number one, plus, interest rate variable, forward slash, compounding period, close parenthesis, minus, payment variable.

Your cell should look something like this before you press enter:
=B10*(1+iRate/compound)-payment
When you press enter a value will appear instead. This value is the total loan that remains to be payed after you have made the first month's payment.

=B10*(1+iRate/compound)-payment
The above is the recursive formula for paying off a loan. The interpretation goes like this: take the amount still remaining in the loan (B10, previous cell), multiply (asterisk) it by one plus the interest rate divided by the compounding period, then subtract this month's payment. (1+iRate/compound) means we still have to pay all of the remaining loan (1) plus the extra interest this month (iRate/compound).

The formula is recursive because it is calculated using the previous value, which is retreived from cell B10.

Filling

Next we will use an essential excel trick. Select the cell in which you just entered the recursive formula =B10*(1+iRate/compound)-payment. Hold shift and press the down arrow, or scoll down and then click with your mouse, until you move down 360 rows. (If you do not have a 30 year mortgage then you might move down some other number of rows. 360 comes from 12 months per year multiplied times 30 years.) With these 360 rows selected click on the Edit menu at the top of the screen, then select "Fill", and "Down".

This will fill each selected cell with the recursive formula, but instead of just copying
"=B10*(1+iRate/compound)-payment" into each cell excel copies smartly so that B10 changes to be the cell right before whatever cell is being copied into. This means that the cell below B10 will contain
=B11*(1+iRate/compound)-payment followed by
=B12*(1+iRate/compound)-payment
=B13*(1+iRate/compound)-payment

and so on. Now we have a truly recursive formula.

A similar process is repeated in the next column beneath "pure interest", but for this column we only want to know the amount of the monthly payment that is solely going towards paying off interest. We only need part of the recursive formula to achieve this.

My first cell in this column looks like this:
=B10*(iRate/compound)
Then I repeated the fill down procedure 360 rows down to see how the amount that I will pay purely in interest will drop each month.

Functions

The last essential excel tool, after defining variables and filling, that I'll show here is functions. Excel has a variety of built in functions only two of which I use in my mortgage calculator.

In the cell beneath the "12 month interest" column type equals followed by the word "sum" and an open parenthesis. Excel will prompt you for some input. Click on the first "pure interest" cell, hold shift, then click on the cell 12 rows down. Finally type close parenthesis. The cell ought to look something like this: =SUM(C10:C21)

This tells excel to do a summation, to add up all the values in the cells from C10 through C21. It also tells me how much I will pay in interest during my first year paying off the mortgage.

All excel functions take the general form of word followed by parentheses.

The next function is super-useful. (Thanks to this website for bringing it to my attention). It is a monthly payment calculator! Select a blank cell off to the side and type "=PMT(". Next type your interest rate as a percent followed by a percentage sign, forward slash, the compounding period, comma, the number of months (in the case of a 30 year mortgage compounded monthly this will be 12*30=360), comma, and the initial loan amount followed by a close parenthesis.

Something like this:
=PMT(6.5%/12,360,125000)

When you press enter it will give you the amount you need to pay each month to pay off this loan in the given number of months! You can then take this value and enter it back in to the monthly payment cell.

An example of a mortgage calculator excel sheet that you can use and modify yourself can be found here. It includes a calculation of a 15 year as well as a 30 year mortgage.

Other tags this item is listed under include: smartamusement,