Copyright © 2013 by Pearson Education, Inc. All rights reserved. From Skills for Success with Microsoft® Office 2010 Vol. 1, Second Edition by Kris Townsend Copyright © 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved. From Skills for Success with Microsoft® Office 2010 Vol. 1
Use Excel Functions and Tables |Microsoft Excel Chapter 4 More Skills: Skill 12 | Page 1 of 5
The PMT function calculates the payment for a loan based on constant payments and
a constant interest rate.
When you borrow money from a bank, interest is the charge for borrowing the money and is
generally a percentage of the amount borrowed. The rate is the percentage that is paid for the use of the borrowed money.
The principal or Present value (Pv) of a loan is the initial amount of the loan—the total
amount that a series of future payments is worth today.
In the PMT function, the number of time periods—number of payments—is abbreviated
nper. The value at the end of the time periods is the Future value (Fv)—the cash balance you
want to attain after the last payment is made. The future value for a loan is usually zero.
To complete this workbook, you will need the following file: e04_Loans
You will save your workbook as: Lastname_Firstname_e04_Loans
1. Start Excel. From your student data files, open e04_Loans. Save the workbook in your
Excel Chapter 4 folder as Lastname_Firstname_e04_Loans
2. Add the file name in the worksheet’s left footer, and then return to Normal view. Review the displayed data.
More Skills
12
Use the Payment (PMT) Function
3. The city has borrowed $500,000 for five years at 6% interest to build a new parking garage. Click cell B8. On the Formulas tab, in the Function Library group, click the Financial button. Scroll down the list, and then click PMT. Compare your screen with Figure 1.
The Function Arguments dialog box displays the arguments for the PMT function. Recall that arguments are the values that an Excel function uses to perform calculations or operations. When the insertion point is in an argument box, a description of that argument is provided in the Function Arguments dialog box. Here, the Rate argument is described.
Figure 1 Function Arguments dialog box PMT function PMT function arguments Description of active argument
Copyright © 2013 by Pearson Education, Inc. All rights reserved. From Skills for Success with Microsoft® Office 2010 Vol. 1, Second Edition by Kris Townsend
4. Move the Function Arguments dialog box to the right side of your screen so you can view the data in columns A and B. With the insertion point in the Rate argument box, type
B7/12
The payments on a loan are usually made monthly; however, when borrowing money, the interest rate and the number of periods are generally quoted in years. The number of periods, which is stated in years, and the annual interest rate must be changed to a monthly equivalent to calculate the monthly payment amount. Calculations like these can be made as part of the argument in a function.
Here, the annual interest rate of 6% located in cell B7 is divided by 12—the number of months in a year—which results in a monthly interest rate.
Notice that to the right of the Rate argument box the monthly interest rate of 0.005 displays—the decimal equivalent of 0.5%.
5. Press Fto move the insertion point to the Nper argument box. In the lower portion of the dialog box, notice that Nper is the total number of payments for the loan—the number of periods. Type B6*12to calculate the number of monthly payments in the loan—60.
6. Press Fto move to the Pv argument box, type B5and then compare your screen with
Figure 2.
The present value, or principal, of the loan is the amount of the loan—$500,000. In cell B8 and in the formula bar, the PMT function arguments are separated by commas.
Below the arguments, the payment amount displays.
Copyright © 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved. From Skills for Success with Microsoft® Office 2010 Vol. 1
Use Excel Functions and Tables |Microsoft Excel Chapter 4 More Skills: Skill 12 | Page 3 of 5 Figure 2 Argument values separated by commas Rate Nper Pv Payment amount 9780132840323_XLS_C04_MS12.indd 3 12/12/11 11:35 AM
7. In the Function Arguments dialog box, click OK.
The monthly payment amount, ($9,666.40), displays in cell B8. The amount displays in red and in parentheses to show that it is a negative number.
8. Click on the formula bar, and then use the arrow keys on the keyboard to position the insertion point in front of B5. Type –(minus sign) to insert a minus sign into the function, and then press J.
By placing a minus sign in the function, the monthly payment amount, $9,666.40, displays in cell B8 as a positive number.
9. Click cell B16. On the Formulas tab, in the Function Library group, click the Financial button, and then click PMT. In the Rate argument box, type B15/12and then press F. In the Nper argument box, type B14*12and then press F. In the Pv argument box, type -B13and then look at the value under the Type box, 56096.23783. Click OK, and then compare your screen with Figure 3.
Figure 3
PMT function in cell B16 and formula bar
Copyright © 2013 by Pearson Education, Inc. All rights reserved. From Skills for Success with Microsoft® Office 2010 Vol. 1, Second Edition by Kris Townsend
10.Click cell B24, and then insert the PMT function to calculate the monthly payment for the water plant construction loan. Compare your screen with Figure 4.
Copyright © 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved. From Skills for Success with Microsoft® Office 2010 Vol. 1
Use Excel Functions and Tables |Microsoft Excel Chapter 4 More Skills: Skill 12 | Page 5 of 5 Figure 4
PMT function in cell B24 and formula bar
11.Save the workbook. Print or submit the file as directed by your instructor. Exit Excel. You have completed More Skills 12
