2.3 Functions for Personal Finance

Learning Objectives

  1. Understand the fundamentals of loans and leases.
  2. Use the PMT function to calculate monthly mortgage payments on a house.
  3. Use the PMT function to calculate monthly lease payments for an automobile.
  4. 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 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 you to borrow this money over 10 years. Indeed, borrowing money is not free. It is important to note that to simplify this example, the payments were calculated on an annual basis. However, most loan payments are made on a monthly basis.

Amortization table for a $100,000 loan. For each year, Interest Payment plus Principal Payment is $12,950. At end of year 10, loan is paid in full.
Figure 2.29 Example of an Amortization Table

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. We will look at a demonstration of leasing a car as well as buying a home in the next section.

The PMT (Payment) Function for Loans

Data file: Continue with CH2 Personal Budget.

If you own a home, your mortgage payments are a major component of your household budget. If you are planning to buy a home, having a clear understanding of your monthly payments is critical for maintaining strong financial health. In Excel, mortgage payments are conveniently calculated through the PMT (payment) function. This function is more complex than the statistical functions covered in Section 2.2 “Statistical Functions”. With statistical functions, you are required to add only a range of cells or selected cells within the parentheses of the function, also known as the argument. With the PMT function, you must accurately define a series of arguments in order for the function to produce a reliable output. Table 2.6 lists the arguments for the PMT function. It is helpful to review the key loan and lease terms in Table 2.5 before reviewing the PMT function arguments.

Table 2.6 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] The argument letters stand for future value. The brackets around the argument indicate that it is 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] 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.

By default, the result of the PMT function in Excel is shown as a negative number. This is because it represents an outgoing payment. When making a mortgage or car payment, you are paying money out of your pocket or bank account. Depending on the type of work that you do, your employer may want you to leave your payments negative or they may ask you to format them as positive numbers. In the following assignments, the payments calculated using the PMT function will be made positive to make them easier to work with. To do this, when defining the PV argument (amount of money borrowed) in the PMT dialog box, a minus sign must precede the cell location or value (see the PV argument in Figure 2.32).

We will use the PMT function in the Personal Budget workbook to calculate the monthly mortgage payments for a house. These calculations will be made in the Mortgage Payments worksheet and then displayed in the Budget Summary worksheet through a cell reference link. So far we have demonstrated several methods for adding functions to a worksheet. The following steps explain a new method using the Insert Function command for adding the PMT function:

  1. Click the Mortgage Payments worksheet tab.
  2. Click cell B5.
  3. Click the Formulas tab on the Ribbon.
  4. Click the Insert Function button (see Figure 2.30). This opens the Insert Function dialog box, which can be used for searching all functions in Excel.
  5. In the “Search for a function:” input box at the top of the Insert Function dialog box, type mortgage payments (see Figure 2.31). Note that the current description in the “Search for a function:” input box will already be highlighted. You can begin typing and the description will be replaced with your entry.
  6. 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 (see Figure 2.31).
  7. Click the PMT option in the “Select a function:” box in the lower half of the Insert Function dialog box.
  8. Click the OK button at the lower right side of the Insert Function dialog box. This will open the Function Arguments dialog box.
    Mortgage Payments worksheet. Formulas tab shows Insert Function button. The PMT function will be added to cell B:5 to calculate monthly mortgage payments.
    Figure 2.30 Mortgage Payments Worksheet
    Insert Function Dialog Box. Enter description of what you need to calculate. Go button retrieves recommended functions. Scroll through list of recommended functions, descriptions of selected function displayed at bottom. OK button begins building selected function.
    Figure 2.31 Insert Function Dialog Box

     

  9. Click the Collapse Dialog button next to the Rate argument in the Function Arguments dialog box. This will be the first argument defined for the function (see Figure 2.32)
  10. Click cell B3 on the worksheet. This is the rate being charged on the loan.
  11. Type a forward slash / for division.
  12. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to divide the rate, which is stated in annual terms, by 12. This converts the annual rate to a monthly rate.
  13. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Rate argument is now defined.
  14. Click the Collapse Dialog button next to the Nper argument in the Function Arguments dialog box. This is the second argument we define in the function.
  15. Click cell B4 on the worksheet. This is the term or the amount of time we have to repay the loan.
  16. Type an asterisk * for multiplication.
  17. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to multiply the terms of the loan by 12. This converts the terms of the loan from years to months.
  18. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Nper argument is now defined.
  19. Click the Collapse Dialog button next to the Pv argument in the Function Arguments dialog box. This is the third argument we will define in the function.
  20. Type a minus sign . When defining the Pv argument of the PMT function, any cell location or value must be preceded with a minus sign.
  21. Click cell B2 on the worksheet. This is the principal of the loan.
  22. Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.
  23. Click the OK button at the bottom of the Function Arguments dialog box. The function will now be placed into the worksheet. Since we are not paying any lump sums of money at the end of the loan, there is no need to define the Fv argument. Also, we will assume that the monthly mortgage payments will be made at the end of each month. Therefore, there is no need to define the Type argument.

Keyboard Shortcuts

Insert Function

  • Hold the SHIFT key while pressing the F3 key.

Function Arguments Dialog Box

  • After the equal sign = and function name are typed into cell a location, hold down the CTRL key and press the letter A on your keyboard.

Figure 2.32 shows the completed Function Arguments dialog box for the PMT function. 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.

Function Arguments dialog box for PMT function shows values for Rate and Nper, function output, and definition of selected argument. Help on function link at bottom.
Figure 2.32 Function Arguments Dialog Box for the PMT Function

Integrity Check

Comparable Arguments for PMT Function

When using functions such as PMT, 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.

 

Figure 2.33 shows the final appearance of the Mortgage Payments worksheet after the PMT function is added. The result of the function in cell B5 will be displayed in the Budget Summary worksheet.

Mortgage Payments worksheet after PMT function is added. Cell B5 shows function output.
Figure 2.33 Mortgage Payments Worksheet with the PMT Function

The PMT (Payment) Function for Leases

In addition to calculating the mortgage payments for a home, the PMT function will be used in the Personal Budget workbook to calculate the lease payments for a car. The details for the lease payments are found in the Car Lease Payments worksheet. Similar to the statistical functions, we can either type the PMT function directly into a cell or use the Insert Function button. However, you must know the definitions for each argument of the function and understand how these arguments need to be defined based on your objective. The terms for loans and leases are in Table 2.5, and the definitions for the arguments of the PMT function are in Table 2.6. The following steps explain how the PMT function is added to the Personal Budget workbook to calculate the lease payments for a car:

  1. Click cell B6 in the Car Lease Payments worksheet.
  2. Click the Formulas tab on the Ribbon.
  3. Click the Financial button in the Function Library group. This opens the Financial Function drop down list.
  4. Scroll down and click on the PMT Function in the drop down list. This will open the PMT Function Arguments dialog box. See Figure 2.34.
  5. Click the Collapse Dialog button next to the Rate argument in the PMT Function Arguments dialog box. This will be the first argument defined for the monthly car lease payment.
  6. Click cell B4. This is the interest rate being charged for the lease.
  7. Type the forward slash / for division.
  8. Type the number 12. Since our goal is to calculate the monthly lease payments, we divide the interest rate by 12 to convert the annual rate to a monthly rate.
  9. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Rate argument is now defined.
  10. Click the Collapse Dialog button next to the Nper argument in the Function Arguments dialog box. This is the second argument we define in the function.
  11. Click cell B5. This is the term or the length of time for the lease contract. Since the term is already expressed in months, we can just reference cell B5 and move to the next argument. If the term was defined in years, instead of months, we would need to multiply the terms (years) of the loan by 12 to convert the years to months.
  12. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Nper argument is now defined.
  13. Click the Collapse Dialog button next to the Pv argument in the Function Arguments dialog box. This is the third argument we will define in the function.
  14. Type a minus sign . Remember that cell locations or values used to define the Pv argument must be preceded with a minus sign.
  15. Click cell B2 on the worksheet, which is the price of the car.
  16. Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.
  17. Click the Collapse Dialog button next to the Fv argument in the Function Arguments dialog box. This is the fourth argument we will define in the function.
  18. Click cell B3 on the worksheet. This is the residual value of the car. Note that cell location and values used to define the [Fv] argument are NOT preceded by a minus sign.
  19. Press the ENTER key on your keyboard. You will now see the Rate, Nper, Pv and Fv arguments defined for the function.
  20. Click the Collapse Dialog button next to the Type argument in the Function Arguments dialog box. This is the fifth and last argument we will define in the function.
  21. Type the number 1. We will assume that the lease payments will be due at the beginning of each month. For payments made at the beginning of the period, a 1 will be entered in the Type argument box. For payments made at the end of the period, a 0 will be entered in the Type argument box.
  22. Press the ENTER key. You will now see the Rate, Nper, Pv, Fv and Type arguments defined for the function. See Figure 2.34.
  23. Click the OK button at the bottom of the Function Arguments dialog box. The function will now be placed into the worksheet.

Figure 2.34 shows how the the completed Function Arguments dialog box for the PMT function car lease should appear before pressing the OK button.

Function Arguments dialog box for PMT function car lease showing formula result.
Figure 2-34 Function Arguments Dialog Box for the PMT Lease Function

Figure 2.35 shows the result of the PMT function for the car lease. The monthly payments for this lease are $206.56. This monthly payment will be displayed in the Budget Summary worksheet.

Car Lease Payments worksheet. PMT function output in cell B6.
Figure 2.35 Results of the PMT Function in the Car Lease Payments Worksheet

Skill Refresher

PMT Function

  1. Type an equal sign =.
  2. Type the letters PMT followed by an open parenthesis, or double click the function name from the function list.
  3. Define the Rate argument with a cell location that contains the rate being charged by the lender for the loan or lease. If the interest rate given is an annual rate, divide it by 12 to convert it to a monthly rate.
  4. Define the Nper argument with a cell location that contains the amount of time to repay the loan or lease. If the amount of time is in years, multiply it by 12 to convert it to number of months.
  5. 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.
  6. 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.
  7. 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.
  8. Type a closing parenthesis ).
  9. Press the ENTER key.

Linking Worksheets (Creating a Summary Worksheet)

So far we have used cell references in formulas and functions, which allow Excel to produce new outputs when the values in the cell references are changed. Cell references can also be used to display values or the outputs of formulas and functions in cell locations on other worksheets. This is how data will be displayed on the Budget Summary worksheet in the Personal Budget workbook. Outputs from the formulas and functions that were entered into the Budget Detail, Mortgage Payments, and Car Lease Payments worksheets will be displayed on the Budget Summary worksheet through the use of cell references. The following steps explain how this is accomplished:

  1. Click cell C3 in the Budget Summary worksheet.
  2. Type an equal sign =.
  3. Click the Budget Detail worksheet tab.
  4. Click cell D12 on the Budget Detail worksheet.
  5. Press the ENTER key on your keyboard. The output of the SUM function in cell D12 on the Budget Detail worksheet will be displayed in cell C3 on the Budget Summary worksheet.

Figure 2.36 shows how the cell reference appears in the Budget Summary worksheet. Notice that the cell reference D12 is preceded by the Budget Detail worksheet name enclosed in apostrophes followed by an exclamation point (‘Budget Detail’!) This indicates that the value displayed in the cell is referencing a cell location in the Budget Detail worksheet.

Function ='BudgetDetail'!D12 in cell C3 indicates cell reference from Budget Detail worksheet. Value $17,950 displayed in C3 is Total Annual Spend from D12 in Budget Detail worksheet.
Figure 2.36 Cell Reference Showing the Total Expenses in the Budget Summary Worksheet

As shown in Figure 2.36, the Budget Summary worksheet is designed to show the expense budget for the mortgage payments and the auto lease payments. However, you will recall that we used the PMT function to calculate the monthly payments. In the Budget Summary worksheet, we need to show the total annual payments. As a result, we will create a formula that references cell locations in the Mortgage Payments and Car Lease Payments worksheets. The following steps explain how this is accomplished:

  1. Click cell C4 in the Budget Summary worksheet.
  2. Type an equal sign =.
  3. Click the Mortgage Payments worksheet tab.
  4. Click cell B5 in the Mortgage Payments worksheet.
  5. Type an asterisk * for multiplication.
  6. Type the number 12. This multiplies the monthly payments by 12 to calculate the total payments required for the year. The formula in the formula bar should read: =’Mortgage Payments’!B5*12
  7. Press the ENTER key on your keyboard. The value of multiplying the monthly mortgage payments by 12 is now displayed on the Budget Summary worksheet.
  8. Click cell C5 on the Budget Summary worksheet.
  9. Type an equal sign =.
  10. Click the Car Lease Payments worksheet tab.
  11. Click cell B6 in the Car Lease Payments worksheet.
  12. Type an asterisk * for multiplication.
  13. Type the number 12. This multiplies the monthly lease payments by 12 to calculate the total payments required for the year.
  14. Press the ENTER key on your keyboard. The value of multiplying the monthly lease payments by 12 is now displayed on the Budget Summary worksheet.

Figure 2.37 shows the results of creating formulas that reference cell locations in the Mortgage Payments and Car Lease Payments worksheets.

Function "='Car Lease Payments'!B6*12" indicates that B6 reference is from Car Lease Payments worksheet. Outputs for formulas that reference cells in Mortgage payments ($10,629) and Car Lease Payments ($2,479) appear in Personal Cash Budget worksheet.
Figure 2.37 Formulas Referencing Cells in Mortgage Payments and Car Lease Payments Worksheets

We can now add other formulas and functions to the Budget Summary worksheet that can calculate the difference between the total spend dollars vs. the total net income in cell D2. The following steps explain how this is accomplished:

  1. Click cell D6 in the Budget Summary worksheet.
  2. Type an equal sign =.
  3. Type the function name SUM followed by an open parenthesis (.
  4. Highlight the range C3:C5.
  5. Type a closing parenthesis ) and press the ENTER key on your keyboard or simply press the ENTER key to close the function. The total for all annual expenses now appears on the worksheet.
  6. Click cell D7 on the Budget Summary worksheet. You will enter a formula to calculate Net Change in Cash in this cell.
  7. Type an equal sign =.
  8. Click cell D2.
  9. Type a minus sign  and then click cell D6.
  10. Press the ENTER key on your keyboard. This formula produces an output of $1,942, indicating our income is greater than our total expenses.

Figure 2.38 shows the results of the formulas that were added to the Budget Summary worksheet. The output for the formula in cell D7 shows that the net income exceeds total planned expenses by $1,942. Overall, having your income exceed your total expenses is a good thing because it allows you to save money for future spending needs or unexpected events.

SUM function "=SUM(C3:C5) entered into cell D6 showing cell is subtracting D6 (Total Plan Spend) from D2 (Net Income). "Net Change in Cash" shows output of $1,942 in cell D7.
Figure 2.38 Formulas Added to Show Income Is Greater Than Expenses

We can now add a few formulas that calculate both the spending rate and the savings rate as a percentage of net income. These formulas require the use of absolute references, which we covered earlier in this chapter. The following steps explain how to add these formulas:

  1. Click cell E6 in the Budget Summary worksheet.
  2. Type an equal sign =.
  3. Click cell D6.
  4. Type a forward slash / for division and then click D2.
  5. Press the F4 key on your keyboard. This adds an absolute reference to cell D2.
  6. Press the ENTER key. The result of the formula shows that total expenses consume 94.1% of our net income.
  7. Click cell E6.
  8. Place the mouse pointer over the Auto Fill Handle.
  9. When the mouse pointer turns to a black plus sign, left click and drag down to cell E7. This copies and pastes the formula into cell E7.
  10. Save the CH2 Personal Budget file.
  11. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH2 Personal Budget workbook as directed by your instructor.
  12. Close the CH2 Personal Budget file before moving on to 2.4 – Preparing to Print.

Figure 2.39 shows the output of the formulas calculating the spending rate and savings rate as a percentage of net income. The absolute reference shown for cell D2 prevents the cell from changing when the formula is copied from cell E6 and pasted into cell E7. The results of the formula show that our current budget allows for a savings rate of 5.9%. This is a fairly good savings rate.

"$" in "=D7/$D$2" indicates an absolute reference was added to cell D2 function. Cell E7 in Personal Cash Budget worksheet shows savings rate is 5.9% of net income.
Figure 2.39 Calculating the Savings Rate

Key Takeaways

  • The PMT function can be used to calculate the monthly mortgage payments for a house or the monthly lease payments for a car.
  • When using the PMT function, each argument must be separated by a comma.
  • To calculate the monthly payment for a loan using the PMT function, the Rate and Nper arguments must be defined in terms of months. The Rate should be divided by 12 to convert it from an annual rate to a monthly rate. The Nper should be multiplied by 12 to convert the term of the loan from years to months.
  • The PMT function produces a negative output if the Pv argument is not preceded by a minus sign. For the purposes of this textbook, a minus sign will be entered before the PV argument in the PMT dialog box.

Attribution

Adapted by Mary Schatz from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

License

Icon for the Creative Commons Attribution 4.0 International License

Beginning Excel, First Edition Copyright © by Noreen Brown, Barbara Lave, Julie Romey, Mary Schatz, Diane Shingledecker is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.