PMT

Returns the payment per period for a fixed rate loan.

Syntax:

PMT(rate, numperiods, principal, finalbalance, type)

rate: the interest rate per period.

numperiods: the total number of payment periods in the term.

principal: the initial sum borrowed.

finalbalance: the cash balance you wish to attain at the end of the term (optional - defaults to 0). With a loan, this would normally be 0.

type: when payments are made (optional - defaults to 0):

0 - at the end of each period.

1 - at the start of each period (including a payment at the start of the term).

See the examples for how this function can be used for building up savings with fixed regular payments.

Example:

PMT(5.5%/12, 12*2, 5000, 0, 0)

returns -220.48 in currency units. You take out a 2 year loan of 5000 currency units at a yearly interest rate of 5.5%, making monthly payments at the end of the month. You pay 220.48 currency units each month; it is given as negative because you pay it.

PMT(5%/12, 12*2, 0, 1000, 1)

returns -39.54 in currency units. You wish to save 1000 currency units over 2 years, making monthly payments, beginning today. You assume the rate will remain the same at 5%. Interest is compounded monthly. If you save 39.54 currency units each month, the value at the end of 2 years will be 1000 currency units.

PMT(5.5%/12, 12*2, 5000, 1000, 0)

returns -259.99 in currency units. You take out a 2 year loan of 5000 currency units at a yearly interest rate of 5.5%, making monthly payments at the end of the month. You wish to build up a lump sum of 1000 currency units, to be paid to you at the end of the term. Interest is compounded monthly.

This is a hypothetical example, which makes the unlikely assumption that the interest you are charged when in debt is the same as the interest that you get when in credit (when building up your 1000 lump sum). It illustrates the necessary sign of each parameter: 5000 is positive, because you are paid the loan sum; 1000 is positive, because you are paid the lump sum; 259.99 is negative, because you pay the monthly sum.

Application:

Car Loan

Let's say you want to buy a new car and need to take out a loan. You have a few options to consider, and you want to calculate the monthly payment for each one to see what fits your budget.

Scenario: You want to borrow $30,000 to buy a car.

Here are the details of the loan offers you are considering:

Loan Amount (PV): $30,000
Annual Interest Rate: 5.5%
Loan Term (in years): 5 years (for Option A) and 6 years (for Option B)

To use the PMT function, we need to convert the annual interest rate and loan term into monthly values.

Monthly Interest Rate (rate): Annual Interest Rate / 12 = 5.5% / 12 = 0.4583% or 0.004583
Total Number of Payments (nper): Loan Term in years * 12

Option A: 5-Year Loan

nper: 5 years * 12 months/year = 60 payments
PMT Function: =PMT(0.004583, 60, 30000)
Result: -$573.03

Option B: 6-Year Loan

nper: 6 years * 12 months/year = 72 payments
PMT Function: =PMT(0.004583, 72, 30000)
Result: -$490.13

The negative sign indicates a payment leaving your account.

Here is a table summarizing the results:

	Loan Amount	Option A (5-Year Loan)	Option A (6-Year Loan)
	A	B	C
1	Loan Amount (PV)	$30,000.00	$30,000.00
2	Annual Interest Rate	5.5%	5.5%
3	Loan Term (Years)	5 years	6 years
4	Monthly Interest Rate (rate)	0.4583%	0.4583%
5	Number of Payments (nper)	60	72
6	PMT Formula	PMT(0.004583, 60, 30000)	PMT(0.004583, 72, 30000)
7	Calculated Monthly Payment	-$573.03	-$490.13

Conclusion:

Based on the PMT function, you can see that choosing the 6-year loan term (Option B) results in a lower monthly payment, which might be more manageable for your budget. However, it's important to remember that a longer loan term also means you will pay more in total interest over the life of the loan. This is a crucial factor to consider when making a financial decision. The PMT function helps you quickly compare these options and make an informed choice.