2.3 Functions for Personal Finance
Learning Objectives
- Understand the fundamentals of loans and leases.
- Use the PMT function to calculate monthly mortgage payments on a house.
- Use the PMT function to calculate monthly lease payments for an automobile.
- Learn how to summarize data in a workbook by using worksheet links to create a summary worksheet.
In this section, we continue to develop the Personal Budget workbook. Notable items that are missing from the Budget Detail worksheet are the payments you might make for a car or a home. This section demonstrates Excel functions used to calculate lease payments for a car and to calculate mortgage payments for a house.
The Fundamentals of Loans and Leases
One of the functions we will add to the Personal Budget workbook is the PMT function. This function calculates the payments required for a loan or a lease. However, before demonstrating this function, it is important to cover a few fundamental concepts on loans and leases.
A loan is a contractual agreement in which money is borrowed from a lender and paid back over a specific period of time. The amount of money that is borrowed from the lender is called the principal of the loan. The borrower is usually required to pay the principal of the loan plus interest. When you borrow money to buy a house, the loan is referred to as a mortgage. This is because the house being purchased also serves as collateral to ensure payment. In other words, the bank can take possession of your house if you fail to make loan payments. As shown in Table 2.5, there are several key terms related to loans and leases.
Table 2.5 Key Terms for Loans and Leases
| Term | Definition |
| Collateral | Any item of value that is used to secure a loan to ensure payments to the lender |
| Down Payment | The amount of cash paid toward the purchase of a house. If you are paying 20% down, you are paying 20% of the cost of the house in cash and are borrowing the rest from a lender. |
| Interest Rate | The interest that is charged to the borrower as a cost for borrowing money |
| Mortgage | A loan where property is put up for collateral |
| Principal | The amount of money that has been borrowed |
| Residual Value | The estimated selling price of a vehicle at a future point in time |
| Terms | The amount of time you have to repay a loan |
Figure 2.29 shows an example of an amortization table for a loan. A lender is required by law to provide borrowers with an amortization table when a loan contract is offered. The table in the figure shows how the payments of a loan would work if you borrowed $100,000 from a lender and agreed to pay it back over 10 years at an interest rate of 5%. You will notice that each time you make a payment, you are paying the bank an interest fee plus some of the loan principal. Each year the amount of interest paid to the bank decreases and the amount of money used to pay off the principal increases. This is because the bank is charging you interest on the amount o