Ppmt Function

Purpose

Return value

Syntax

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

• rate - The interest rate per period.
• per - The given payment period.
• nper - The total number of payments for the loan.
• pv - The present value, or total value of all payments now.
• fv - [optional] The cash balance desired after last payment is made. Defaults to 0.
• type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.

Using the PPMT function

The PPMT function returns the principal portion of a payment for a given period of an investment or a loan, based on constant periodic payments and a constant interest rate. PPMT takes six arguments, four of which are required:

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

Each argument has the following meaning:

• rate - the interest rate per period. Typically, this is the annual interest rate divided by the compounding periods per year.
• per - the given payment period as a number (i.e. 1, 2, 3, etc.)
• nper - The total number of payment periods for the loan or investment.
• pv - The present value, or the principal amount of the loan or investment.
• fv - Optional. The desired future value, or a cash balance after the last payment (defaults to 0)
• type - Optional. The timing of the payment: 0 = end of period (default), 1 = beginning of the period.

Example #1 - hardcoded values

Suppose you have a 5-year loan of $10,000 with an annual interest rate of 5% and 12 compounding periods per year. You want to figure out the principal portion of the payment for period 1. You can calculate this amount with the PPMT function like this:

=PPMT(5%/12,1,60,-10000)

The inputs to PPMT are as follows:

• rate - 5%/12 = 0.00416 (annual interest rate with monthly compounding)
• per - 1 (period 1)
• nper - 60 (a 5-year loan has 60 periods)
• pv - -10,000 (the loan amount is negative to yield a positive result)
• fv - Omitted. Defaults to 0.
• type - Omitted. Defaults to 0.

The result is 147.05. This is the principal portion of the payment for period 1 of the loan. Notice we have provided the loan balance as a negative value to get a positive result from PPMT. If we provide 10,000 as a positive number, PPMT will return -147.05. In practice, the decision to use a positive or negative value for pv depends on the specific scenario.

Example #2 - worksheet formula

In the example above, all inputs to PPMT are hardcoded to make the formula easier to read. More typically, the inputs will come from cell references. The screen below shows how the same example can be transferred to a worksheet:

The formula in cell C10 is evaluated like this:

=PPMT(C5/C7,1,C6*C7,-C4)
=PPMT(0.05/12,1,5*12,-10000)
=PPMT(0.004167,1,60,-10000)
147.05

Notice that we provide (years * periods per year) for nper instead of hardcoding the number 60. We do this so that the formula will automatically adapt to a loan with a different term in years, or a loan with a different number of periods per year.

Also, notice that the PPMT function does not need the monthly payment. This is because PPMT calculates the principal portion of a payment based on the original principal (or present value), the interest rate, and the number of periods. To calculate the payment for a loan you can use the PMT function .

Notes

1. Use the IPMT function to get the interest portion of a loan payment for a given period.
2. The interest rate can be provided as a percentage like 5% or a decimal number like 0.05.
3. Be careful to provide the periodic interest rate for rate . For example, 5%/12 or 0.05/12.
4. The loan value ( pv ) can be entered as a positive value or a negative value.
5. The value for period ( per) must be in the range 1 to nper .

Purpose

Return value

Syntax

=PRICE(sd,md,rate,yld,redemption,frequency,[basis])

• sd - Settlement date of the security.
• md - Maturity date of the security.
• rate - Annual coupon rate.
• yld - Annual required rate of return.
• redemption - Redemption value per $100 face value.
• frequency - Coupon payments per year (annual = 1, semiannual = 2; quarterly = 4.
• basis - [optional] Day count basis (see below, default =0).

Using the PRICE function

The Excel PRICE function returns the price per $100 face value of a security that pays periodic interest. For example, the PRICE function can be used to determine the “clean price” of a bond (also known as the quoted price), which is the price of the bond excluding accrued interest.

In the example shown, the formula in F5 is:

=PRICE(C9,C10,C7,C8,C6,C12,C13)

with these inputs, the PRICE function returns 97.56, which indicates the value of the bond is 97.56% of the face value. To get the actual dollar value, the formula in F6 is:

=F5/100*C5

Entering dates

In Excel, dates are serial numbers . Generally, the best way to enter valid dates is to use cell references, as shown in the example. If you want to enter valid dates directly inside a function, the DATE function is the best approach.

Basis

The basis argument controls how days are counted. The PRICE function allows 5 options (0-4) and defaults to zero, which specifies US 30/360 basis . This article on Wikipedia provides a detailed explanation of available conventions.

Notes

• In Excel, dates are serial numbers .
• settlement , maturity , frequency , and basis are truncated to integers
• If settlement or maturity dates are not valid, PRICE returns #VALUE!
• If basis is out-of-range, PRICE returns #NUM!
• If maturity date is not later than settlement date, PRICE returns #NUM!