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!