Figure 1 Amortization table for a 15 year mortgage at 4% annual interest. Note rows have been hidden. Select the rows to be hidden then use: home > cells > format > hide
Mortgages
Excel is quite good at keeping track of money.
Large purchases such as a home are usually financed with a mortgage. If a lender provides say $100,000.00, each month the borrower makes a payment. The lender expects to make money on the loan by charging interest. A portion of the monthly payment includes interest, the remainder of the payment each month goes to pay off the loan. For example, consider the spreadsheet shown in Figure 1. $100,000.00 is borrowed at 4% interest. 4% is the annual percent rate (APR). 4% of $100,000.00 is $4,000.00 per year.
However, interest is calculated each month. The monthly interest rate is 4/12 or one third of a percent per month. The first month when $100,000.00is due, the interest is $100,000.00 * .00333 = $333.33.
If all that is paid is the interest, the loan will never be repaid. The payment is increased above the interest due and the extra money goes to reduce the amount owed. (The principal) In this example $406.35 in addition to the interest is paid in the first month to reduce the amount owed. The total payment is $739.69. Note the one cent round off error.
In the second month the amount owed is less and therefore the interest is less. Since the payment is the same each month, each month there will be less interest due and more of the payment goes to reduce the principal.
Figure 1 Amortization table for a 15 year mortgage at 4% annual interest. Note rows have been hidden.
Select the rows to be hidden then use: home > cells > format > hide
A formula for the amount of the payment is,
where i is the periodic interest (Here the period is one month.), n is the number of periods. In this example there are 12*15 periods. That is, there are 180 months in 15 years. P is the original amount borrowed.
The formulas used to create the amortization table are shown in Figure 2.
Figure 2. Excel formulas used to create the amortization table are shown.
The total amount of the payments over the 15 year term is 180*$739.69 = $133,144.20. That's $33,144.20 more than was borrowed. The interest paid on the loan was $33,144.20.
If the term of the load were 30 years, the interest would have been much more.
Excel has a number of built-in functions useful for financial calculations. For example, PMT
Excel help page
