PPMT

Returns the portion of the periodic payment which is repaid capital for a fixed rate loan or annuity.

Syntax:

PPMT(rate, period, numperiods, presentvalue, futurevalue, type)

rate: the interest rate per period.

period: the period of the payment whose repaid capital portion is to be calculated, numbered from 1.

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

presentvalue: the initial sum borrowed or invested.

futurevalue: 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).

With a fixed rate loan, where you make a constant payment each period to pay off the loan over the term, some of each period payment is interest on the outstanding capital, and some is a repayment of capital. Over time (as you pay off capital), the interest becomes less and the capital repayment becomes more.

PPMT returns the capital repaid in the payment of a specified period. IPMT returns the interest paid in the payment of that period. Together they add up to the actual payment, given by PMT. PPMT is calculated as PMT - IPMT.

When payments are made at the end of each period, the relevant IPMT interest arises during that period.

When payments are made at the start of each period, the relevant IPMT interest arises during the preceding period.

By convention, money that you receive is positive, and money you pay is negative. For a loan where you receive a lump sum at the start, presentvalue is positive. For an investment where you pay a lump sum at the start, presentvalue is negative.

Example:

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

returns -207.75 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. In the 12th month you make your usual monthly repayment, of which 207.75 is repayment of capital.

Application:

Car Loan

Let's say you've taken out a fixed-rate car loan with the following terms:

Loan Amount (Present Value, pv): $25,000
Annual Interest Rate: 6%
Loan Term (nper): 5 years
Payment Frequency: Monthly

We can use the PPMT function to determine how much of each monthly payment goes towards the principal.

The syntax for the PPMT function is:

PPMT(rate, per, nper, pv, [fv], [type])

Where:

rate: The interest rate per period. Since payments are monthly, we'll use 6% / 12.
per: The specific period you want to calculate the principal payment for (e.g., payment 1, payment 2, etc.).
nper: The total number of payment periods. For a 5-year loan with monthly payments, this is 5 * 12 = 60.
pv: The present value or the loan amount, which is $25,000.
fv: Optional. The future value, which is usually 0 for a loan that will be paid off completely.
type: Optional. 0 for payments at the end of the period (most common for loans) or 1 for the beginning.

Before we can calculate the principal payments, we first need to determine the total monthly payment using the PMT function, which comes out to -$483.32.

Now, let's use the PPMT function to create an amortization schedule for the first few months of the loan.

Calculations:

Month 1: The principal portion is calculated as =PPMT(6%/12, 1, 60, 25000). This returns -$358.32.
Month 2: The principal portion is calculated as =PPMT(6%/12, 2, 60, 25000). This returns -$360.11.
Month 60 (Last Month): The principal portion is calculated as =PPMT(6%/12, 60, 60, 25000). This returns -$480.92.

The negative value indicates that this is an outgoing payment.

Amortization Table

As you can see from the table below, with each successive payment, the amount of money going toward the principal increases, while the amount of money going toward interest decreases. This is a key characteristic of fixed-rate, amortizing loans.

	Month	Total Payment	Principal (PPMT)	Interest (IPMT)	Ending Balance
	A	B	C	D	E
1	0				$25,000.00
2	1	-$483.32	-$358.32	-$125.00	$24,641.68
3	2	-$483.32	-$360.11	-$123.21	$24,281.57
4	3	-$483.32	-$361.91	-$121.41	$23,919.66
5	4	-$483.32	-$363.72	-$119.60	$23,555.93
6	60	-$483.32	-$480.92	-$2.40	$23,075.02