Chapter 6
Section 6.1 – 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. 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.
Table 6.1 – 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 must repay a loan |
A lease is a contract in which you, the lessee, use an asset such as a car or a piece of equipment and you agree to make regular payments to the owner or the lessor. When you lease a car, the manufacturer or a leasing company retains ownership of the vehicle and you agree to make regular payments for a specific period. The amount of money you pay depends on the price of the car, the terms of the lease contract, and the car’s expected residual value at the end of the lease. The calculation of lease payments is like the calculation of loan payments. However, when you lease a car, you pay only the value of the car that is used.
For example, suppose you are leasing a car that is priced at $25,000. The lease contract is for 4 years at an interest rate of 5%. The residual value of the car is $10,000. This means the car will lose $15,000 of its value over 4 years. Another way to state this is that the car will depreciate $15,000. A lease will be structured so that you pay this $15