Functions for Personal Finance

Introduction:

Excel functions can be used for financial calculations. There are many built-in functions that 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.

Learning:

The Fundamentals of Loans and Leases

The PMT 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. Below are several key terms related to loans and leases.

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

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 of time. 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 similar to 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,000 in depreciation. However, the interest charges will be based on the purchase price of $25,000.

The PMT (Payment) Function for Loans

In Excel, mortgage payments are conveniently calculated through the PMT (payment) function. THE PMT functions calculates the payment for a loan based on constant payments and a constant interest rate. This function is more complex than some of the functions you’ve already studied. With functions such as SUM, MAX, MIN, or AVERAGE, the arguments for those functions only required the range of cells or selected cells within the parentheses of the function. With the PMT function, however, you must accurately define a series of arguments in order for the function to produce a reliable output.

The syntax for the PMT function is

=PMT(rate,nper,PV,[FV],[Type])

The order of the arguments is critical and must be in consistent units. For example, you can’t have some arguments in months and others in years. It is imperative to have all arguments in the same unit of measure. If you are in calculating monthly payments, so be sure that all of your arguments are in monthly terms.

Arguments for the PMT Function

Argument	Definition
Rate	This is the interest rate the lender is charging the borrower. The interest rate is usually quoted in annual terms, so you have to divide this rate by 12 if you are calculating monthly payments.
Nper	The argument letters stand for number of periods. This is the term of the loan, which is the amount of time you have to repay the bank. This is usually quoted in years, so you have to multiply the years by 12 if you are calculating monthly payments.
Pv	The argument letters stand for present value. This is the principal of the loan or the amount of money that is borrowed. When defining this argument, a minus sign must precede the cell location or value. For leases, this argument is used for the price of the item being leased.
[Fv] Optional	The argument letters stand for future value. The brackets around the argument indicate that it is OPTIONAL and not always necessary to define it. It is used if there is a lump-sum payment that will be made at the end of the loan terms. This is also used for the residual value of a lease. If it is not defined, Excel will assume that it is zero.
[Type] Optional	This argument can be defined with either a 1 or a 0. The number 1 is used if payments are made at the beginning of each period. A 0 is used if payments are made at the end of each period. The argument is in brackets because it does not have to be defined if payments are made at the end of each period. Excel assumes that this argument is 0 if it is not defined.

Are you wondering why [fv] and [type] are in square brackets? The brackets mean they’re optional. If you don’t include values for fv and type in your formula, Excel assumes your balance will be $0 at the end of the loan, and that your payments are due at the end of the period.

Example of the PMT Function

To most effectively setup your spreadsheet to calculate the payment of a loan, have values that will be used in the PMT function in separate cells. See below, the actual values for the loan amount, the interest rate, and terms of the loan are in cells B2, B3, and B4. The PMT function will be entered into cell B5.

Creating the PMT function

The easiest way to create the formula for the PMT function, particularly when you are first getting used to using the function is to use the Insert Function command. The Insert Function command can be found on the left side of the ribbon of the Formulas Tab.

The Insert Function dialog box will launch. In the “Search for a function:” input box at the top of the Insert Function dialog box, begin typing the description you are interested in, in this case “mortgage payment” Click the Go button in the upper right side of the Insert Function dialog box. This adds all the Excel functions that match your description in the “Select a function:” box in the lower half of the Insert Function dialog box.

Click the PMT option in the “Select a function:” box in the lower half of the Insert Function dialog box.

Click the OK button at the lower right side of the Insert Function dialog box. This will open the Function Arguments dialog box.

The Functions Arguments dialog box allows the user to setup each of the arguments, in the correct order. Each of the arguments for our example are in cell locations. Use the appropriate cell references. Remember: when using functions such as PMT or FV, make sure the arguments are defined in comparable terms. For example, if you are calculating the monthly payments of a loan, make sure both the Rate and Nper argument are expressed in terms of months. The function will produce an erroneous result if one argument is expressed in years while the other is expressed in months. Notice that the dialog box shows the values for the Rate and Nper arguments. The Rate is divided by 12 to convert the annual interest rate to a monthly interest rate. The Nper argument is multiplied by 12 to convert the terms of the loan from years to months. Finally, the dialog box provides you with a definition for each argument. The definition appears when you click in the input box for the argument.

 The PMT Function for Leases

In addition to calculating the mortgage payments for a home, the PMT function can be used to calculate the lease payments for a car. 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 that is used. 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.

This is when the FV argument is used. The FV is the residual value.

This example shows the result of the PMT function when leasing a car worth $20,000 for 48 months. The car will have a residual value of $12,000 at the conclusion of the lease. The interest rate for the lease period is 3.0%. The formula is entered in cell B6, and will use the PMT function. Remember the sequence of the arguments is critical. The first argument is the Rate. The value in cell B4 is 3.0% but this a yearly value. Because the monthly lease payment is being calculated, the rate must be in months. Therefore, the first argument would be B4/12. The next arguments is nper which is in cell B5 – note this is already in month units. The next argument is PV, which is in B2. Because this is a lease and there is residual value, the FV argument will be used. The residual (FV) is in B3. The final argument of type is chosen to be 1. The monthly payments for this lease are $206.56.

SKILL REFRESHER: PMT FUNCTION

Type an equal sign (=).

Type the letters PMT followed by an open parenthesis, or double click the function name from the function list.

Define the Rate argument with a cell location that contains the rate being charged by the lender for the loan or lease.

Define the Nper argument with a cell location that contains the amount of time to repay the loan or lease.

Define the Pv argument with a cell location that contains the principal of the loan or the price of the item being leased. Cell locations or values used for this argument must be preceded by a minus sign.

Define the [Fv] argument with a cell location that contains the residual value of the item being leased or the lump sum payment for a loan.

Define the [Type] argument with a 1 if payments are made at the beginning of each period or 0 if payments are made at the end of each period.

Type a closing parenthesis ()).

Press the ENTER key.

Summary:

PMT Function is a financial function that can be used to calculate payments for loans and leases. The syntax is =PMT(rate, nper, PV, [FV], [Type])

• The Rate argument equals the rate being charged by the lender for the loan or lease.
• The Nper argument equals the amount of time to repay the loan or lease.
• The Pv argument equals the principal of the loan or the price of the item being leased. Cell locations or values used for this argument must be preceded by a minus sign.
• The [Fv] argument equals the residual value of the item being leased or the lump sum payment for a loan. This is optional, and assumed to be zero if not used.
• The [Type] argument with a 1 if payments are made at the beginning of each period or 0 if payments are made at the end of each period.

Sources:

How to Use Microsoft Excel: The Careers in Practice Series by Saylor Academy. Published 2012.