Lastname Firstname e04 Loans Start 1. Excel Excel Chapter ...

CHAPTER 4 Excel

More Skills 12 Use the Payment (PMT) Function

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.

Use Excel Functions and Tables | Microsoft Excel Chapter 4 From Skills for Success with Microsoft? Excel 2010 Comprehensive

More Skills: Skill 12 | Page 1 of 5 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

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.

Function Arguments dialog box

PMT function

PMT function arguments

Description of active argument

Figure 1

Use Excel Functions and Tables | Microsoft Excel Chapter 4 From Skills for Success with Microsoft? Excel 2010 Comprehensive

More Skills: Skill 12 | Page 2 of 5 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

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 F to 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*12 to calculate the number of monthly payments in the loan--60.

6. Press F to move to the Pv argument box, type B5 and 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.

Argument values separated by commas Rate Nper Pv

Payment amount

Figure 2

Use Excel Functions and Tables | Microsoft Excel Chapter 4 From Skills for Success with Microsoft? Excel 2010 Comprehensive

More Skills: Skill 12 | Page 3 of 5 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

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/12 and then press F. In the Nper argument box, type B14*12 and then press F. In the Pv argument box, type -B13 and then look at the value under the Type box, 56096.23783. Click OK, and then compare your screen with Figure 3.

PMT function in cell B16 and

formula bar

Figure 3

Use Excel Functions and Tables | Microsoft Excel Chapter 4 From Skills for Success with Microsoft? Excel 2010 Comprehensive

More Skills: Skill 12 | Page 4 of 5 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

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.

PMT function in cell B24 and

formula bar

Figure 4

11. Save the workbook. Print or submit the file as directed by your instructor. Exit Excel. You have completed More Skills 12

Use Excel Functions and Tables | Microsoft Excel Chapter 4 From Skills for Success with Microsoft? Excel 2010 Comprehensive

More Skills: Skill 12 | Page 5 of 5 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download