Using the PMT Function - Exercise

Contact Us or call 1-877-932-8228
Using the PMT Function - Exercise

Using the PMT Function

Duration: 15 to 25 minutes.

In this exercise, you will...

  1. Open Functions.xlsx from your Excel2013.2/Exercises folder and go to the sheet named "PMT".
  2. Calculate the payments for Loans 1, 2, 3, and 4.
  3. Assume you purchased a house for $240,000 and took out a 30-year mortgage for the whole amount with an interest rate of 6%. What is your payment? Enter the formula in cell B9.
  4. Assume you purchased a car for $29,000 and took out a loan for the whole amount with an interest rate of 9%. You are to pay off $20,000 of the loan in 4 years. Payments are to be made at the beginning of each period. What is your payment? Enter the formula in cell B10.

Solution:

  1. Loan 1:
    1. Formula: "=PMT(C2/12,D2,B2,E2)"
    2. Solution:
      1. On the FORMULAS tab, in the Function Library group, click the Insert Function command:
      2. In the Insert Function dialog box, select PMT and click OK:
      3. In the Function Arguments dialog box, enter the following values and click OK:
        1. Rate: C2/12
        2. Nper: D2
        3. Pv: B2
        4. Fv: E2
        5. Type: Leave blank.
  2. Loan 2:
    1. Formula: "=PMT(C3/12,D3,B3)"
    2. Solution:
      1. On the FORMULAS tab, in the Function Library group, click the Insert Function command.
      2. In the Insert Function dialog box, select PMT and click OK.
      3. In the Function Arguments dialog box, enter the following values and click OK:
        1. Rate: C3/12
        2. Nper: D3
        3. Pv: B3
        4. Fv: Leave blank.
        5. Type: Leave blank.
  3. Loan 3:
    1. Formula: "=PMT(C4/12,D4,B4)"
    2. Solution:
      1. On the FORMULAS tab, in the Function Library group, click the Insert Function command.
      2. In the Insert Function dialog box, select PMT and click OK.
      3. In the Function Arguments dialog box, enter the following values and click OK:
        1. Rate: C4/12
        2. Nper: D4
        3. Pv: B4
        4. Fv: Leave blank.
        5. Type: Leave blank.
  4. Loan 4:
    1. Formula: "=PMT(C5/12,D5,B5,E5,1)"
    2. Solution:
      1. On the FORMULAS tab, in the Function Library group, click the Insert Function command.
      2. In the Insert Function dialog box, select PMT and click OK.
      3. In the Function Arguments dialog box, enter the following values and click OK:
        1. Rate: C5/12
        2. Nper: D5
        3. Pv: B5
        4. Fv: E5
        5. Type: 1.
  5. Assume you purchased a house for $240,000 and took out a 30-year mortgage for the whole amount with an interest rate of 6%. What is your payment?
    1. The formula is: =PMT(0.06/12,360,240000)
    2. To solve this using the Insert Function command:
      1. On the FORMULAS tab, in the Function Library group, click the Insert Function command.
      2. In the Insert Function dialog box, select PMT and click OK.
      3. In the Function Arguments dialog box, enter the following values and click OK:
        1. Rate: 0.06/12
        2. Nper: 360
        3. Pv: 240000
        4. Fv: Leave blank.
        5. Type: Leave blank.
  6. Assume you purchased a car for $29,000 and took out a loan for the whole amount with an interest rate of 9%. You are to pay off $20,000 of the loan in 4 years. Payments are to be made at the beginning of each period. What is your payment?
    1. Formula: =PMT(0.09/12,48,29000,9000,1)
    2. To solve this using the Insert Function command:
      1. On the FORMULAS tab, in the Function Library group, click the Insert Function command.
      2. In the Insert Function dialog box, select PMT and click OK.
      3. In the Function Arguments dialog box, enter the following values and click OK:
        1. Rate: 0.09/12
        2. Nper: 48
        3. Pv: 29000
        4. Fv: 9000
        5. Type: 1.
Next