PMT

 The PMT function in Excel is used to calculate the periodic payment for a loan or investment based on constant payments and a constant interest rate. Below is an expanded explanation of the PMT function, including examples, data, and formulas.

---

Syntax of the PMT Function

Copy

=PMT(rate, nper, pv, [fv], [type])

• rate: The interest rate for each period.

• nper: The total number of payment periods.

• pv: The present value (loan amount or principal).

• [fv]: (Optional) The future value or cash balance after the last payment. Default is 0.

• [type]: (Optional) When payments are due:

  • 0 = End of the period (default).

  • 1 = Beginning of the period.

---

Example 1: Loan Payment Calculation

Data:

• Loan Amount (pv): $50,000

• Annual Interest Rate: 5%

• Loan Term: 10 years

• Payments per Year: 12 (monthly payments)

Formula:

Copy

=PMT(5%/12, 10*12, 50000)

Explanation:

• rate: 5%/12 (monthly interest rate)

• nper: 10*12 (total number of monthly payments)

• pv: 50000 (loan amount)

• fv: Omitted (defaults to 0)

• type: Omitted (defaults to 0, payments at the end of the period)

Result:

The monthly payment is -$530.33 (negative because it represents an outgoing payment).

---

Example 2: Investment Goal Calculation

Data:

• Future Value (fv): $100,000

• Annual Interest Rate: 6%

• Investment Term: 20 years

• Contributions per Year: 12 (monthly contributions)

Formula:

Copy

=PMT(6%/12, 20*12, 0, 100000)

Explanation:

• rate: 6%/12 (monthly interest rate)

• nper: 20*12 (total number of monthly contributions)

• pv: 0 (no initial investment)

• fv: 100000 (desired future value)

• type: Omitted (defaults to 0, contributions at the end of the period)

Result:

The monthly contribution required is -$216.31.

---

Example 3: Loan Payment with Beginning-of-Period Payments

Data:

• Loan Amount (pv): $30,000

• Annual Interest Rate: 4%

• Loan Term: 5 years

• Payments per Year: 12 (monthly payments)

• Payments at the beginning of the period.

Formula:

Copy

=PMT(4%/12, 5*12, 30000, 0, 1)

Explanation:

• rate: 4%/12 (monthly interest rate)

• nper: 5*12 (total number of monthly payments)

• pv: 30000 (loan amount)

• fv: 0 (default)

• type: 1 (payments at the beginning of the period)

Result:

The monthly payment is -$552.50.

---

Example 4: Loan Payment with Future Value

Data:

• Loan Amount (pv): $20,000

• Annual Interest Rate: 3%

• Loan Term: 8 years

• Payments per Year: 12 (monthly payments)

• Future Value (fv): $5,000 (balloon payment at the end)

Formula:

Copy

=PMT(3%/12, 8*12, 20000, 5000)

Explanation:

• rate: 3%/12 (monthly interest rate)

• nper: 8*12 (total number of monthly payments)

• pv: 20000 (loan amount)

• fv: 5000 (balloon payment at the end)

• type: Omitted (defaults to 0, payments at the end of the period)

Result:

The monthly payment is -$230.29.

---

Key Notes:

1. Negative Result: The PMT function returns a negative value because it represents an outgoing payment. Use a negative pv or multiply the result by -1 to make it positive.

2. Units Consistency: Ensure rate and nper are in the same units (e.g., monthly rate and monthly periods).

3. Optional Arguments: If fv or type are omitted, they default to 0.

---

Summary Table

Example	Loan/Investment	Rate	Term (Years)	Payments/Year	PV	FV	Type	Formula	Result
1	Loan	5%	10	12	$50,000	0	0	=PMT(5%/12, 10*12, 50000)	-$530.33
2	Investment	6%	20	12	0	$100,000	0	=PMT(6%/12, 20*12, 0, 100000)	-$216.31
3	Loan	4%	5	12	$30,000	0	1	=PMT(4%/12, 5*12, 30000, 0, 1)	-$552.50
4	Loan	3%	8	12	$20,000	$5,000	0	=PMT(3%/12, 8*12, 20000, 5000)	-$230.29

---

This expanded explanation should help you understand and use the PMT function effectively in Excel!