PRDV006: Spreadsheets II: Formatting and Functions

In this section, we continue to develop the Personal Budget workbook. Notable items missing from the Budget Detail worksheet are the payments you might make for a car or a home. In addition, you may want to set and track a savings goal. We demonstrate Excel functions used to calculate lease payments for a car, calculate mortgage payments for a house, and project future savings based on regular contributions and an average rate of return. We also discuss the scenario capabilities of Excel once the Personal Budget workbook is complete.

Here, we add the PMT function to the Personal Budget workbook. 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 where money is borrowed from a lender and paid back over a specific period. The money borrowed from the lender is the principal of the loan. The borrower is usually required to pay the loan's principal plus interest. We call the loan a mortgage when you borrow money to buy a house. This is because the purchased home 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. (see Table 1)

Figure 1 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 loan payments 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%. Notice that each time you make a payment, you pay the bank an interest fee plus some of the loan principal.

Each year, the interest paid to the bank decreases, and the money used to pay off the principal increases. This is because the bank charges you interest on the amount of principal that has not been paid. As you pay off the principal, the interest rate is applied to a lower number, which reduces your interest charges. Finally, the figure shows that the sum of the values in the Interest Payment column is $29,505. This is how much it costs to borrow this money over 10 years. Indeed, borrowing money is not free. It is important to note that the payments were calculated annually to simplify this example. Most loan payments are made every month.

Figure 1 Example of an Amortization Table

A lease is a contract where you (the lessee) use an asset, such as a car or 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 car's price, the lease contract terms, and the car’s expected residual value at the end of the lease. The calculation of lease payments is similar to the calculation of loan payments.

However, when you lease a car, you pay only the value of the car. For example, suppose you are leasing a car priced at $25,000. The lease contract is for four 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 four 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,000 in depreciation. However, the interest charges will be based on the purchase price of $25,000. In the next section, we will look at a demonstration of leasing a car and buying a home.

 This text was adapted by Saylor Academy under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License without attribution as requested by the work's original creator or licensor.