Tutorial 2: Working with the PMT Function

The PMT function is a financial function that you can use to calculate periodic payments for loans based on constant payments and a consistent interest rate. For example, you can use this function to calculate car, mortgage, or any other amortized loan payments.

The syntax for a PMT function requires three arguments, each of which must be preceded by =PMT and enclosed within parentheses:

  • Interest Rate (Rate): the rate at which the money is being borrowed.
  • Pay Period (Nper): the total number of payment periods in an annuity.
  • Amount Borrowed (Pv): the present value or the total amount of the loan.

Optional information includes:

  • Future Value (Fv): your goal for the loan, usually set at 0 (default).
  • Type: whether your loan payments occur at the beginning (1) or the end (0 or default) of each payment period.

It is critical that all argument information be expressed in comparable units. For example, if you are computing monthly payments on a four-year loan based on a 12 percent annual interest rate, use 12%/12 for Rate and 4*12 for Nper. If you make annual payments on the same loan, use 12% for Rate and 4 for Nper. Although you can do the division and multiplication yourself, it’s easier to let Excel take care of it. If you use a cell reference for any of these arguments, they also must be of comparable unit. For example, if you intend to use the formula above, the % symbol must be in the cell containing the interest rate.

The payment returned by PMT includes principal and interest only. These calculations do not include any taxes, reserve payments, or fees associated with loans.

When you open the Formula Palette, you will see a Collapse Dialog button at the end of each argument box. Use these buttons to collapse the dialog box temporarily, to help you select cells and ranges in the worksheet as arguments. When done, click the toggled Collapse Dialog box button in the collapsed dialog box.

 

PMT-Funtion-Window

 

To work with arguments using the PMT function:

First select the cell where you want to enter the function.

  1. On the Formulas tab, click the Insert Function button.
  2. In the Function category list box, select Financial.
  3. In the Function name list box, select PMT.
  4. Choose OK.
  5. In the Formula Palette, enter the following arguments:
    1. For Rate, the interest rate.
    2. For Nper, the number of payment periods.
    3. For Pv, the amount of the loan.
    4. For Fv, the goal amount, or omit it. (optional)
    5. For Type, 1 or 0, or omit it. (optional)
  6. Choose OK

Note: Arguments may be numbers, formulas, or cell references. You can enter arguments by typing them or by selecting them with the mouse in the worksheet.

Tutorial 1: Working with Excel Page Breaks

When printing multi-page spreadsheets in Excel, you may have come across page breaks which appear to be in the wrong place. For example, the default page break may force a particular row onto the next page. These breaks are set automatically by Excel and can be seen as horizontal and vertical dashed lines in Normal View.

In these situations, manual page breaks can be set to override the defaults and give you more control over how the spreadsheets looks when printed.

In Excel 2010 and later, setting manual page breaks is easier than ever. Using the Page Break Preview button shown below, you can add and remove page breaks as well as drag page breaks to new locations.

 

excel-page-breaks

 

To access Page Break Preview:

  1. On the Workbook Views group of the Views tab, choose Page Break Preview.

To exit Page Break Preview:

  1. On the Workbook Views group of the Views tab, choose Normal.

To add a page break(s) in Page Break Preview:

  1. Right-click a cell below and to the right of the new page break(s).
  2. From the shortcut menu, choose Insert Page Break.

To remove page break(s) in Page Break Preview:

  1. Right-click the cell below and to the right of the page break(s).
  2. From the shortcut menu, choose Remove Page Break.

To move page breaks in Page Break Preview:

  1. Drag the page break to its new location.

Note: You can only drag page breaks when the double-headed arrow mouse pointer appears.