Course contents · What-if analysis

PMT and data tables

Open the practice in the trainer →

A data table (Data → What-If Analysis → Data Table) shows how the result of a formula changes for different input values.

The PMT function

=PMT(rate, nper, pv)

Rate and periods must use the same units: for monthly payments divide the annual rate by 12. PMT returns a negative number (money going out), so the loan amount is often entered with a minus: =PMT(B3/12,B4,-B2).

Two-variable table

Scenario from the manual: a mortgage, interest rates from 5% to 20% in rows and terms from 60 to 480 months in columns. In Excel this is done with the Data Table tool; here — by hand, with one formula using mixed references:

=PMT($A9/12, B$8, -$B$2)

The rate column $A is locked and the term row $8 is locked — the formula can be copied across the whole table.

Tasks in the trainer

  1. In B5 calculate the monthly payment (the result should be positive).
  2. Fill the table B9:F12 with a single mixed-reference formula: the payment for the rate in column A and the term in row 8.
  3. In D2 calculate the total of all payments (payment × term).
  4. In D3 — the interest paid over the loan (total paid − loan amount).
  5. One-variable table: in G9:G12 calculate the payment for the rates in column A and the term in B4.

Solve the tasks in the trainer →

Check yourself

  1. Annual rate 12%, monthly payments. What goes in the first argument of PMT?
  2. Which reference locks only the row?