Syntax

PPmt(Rate,Per,NPer,Pv,Fv,Due)

Description

Calculates the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Comments

An annuity is a series of fixed payments made to an insurance company or other investment company over a period of time. Examples of annuities are mortgages and monthly savings plans.

The PPmt function requires the following parameters:

Parameter

Description

Rate

Double representing the interest rate per period.

Per

Double representing the number of payment periods. Per can be no less than 1 and no greater than NPer.

NPer

Double representing the total number of payments in your annuity.

Pv

Double representing the present value of your annuity. In the case of a loan, the present value would be the amount of the loan.

Fv

Double representing the future value of your annuity. In the case of a loan, the future value would be 0.

Due

Integer indicating when payments are due. If this parameter is 0, then payments are due at the end of each period; if it is 1, then payments are due at the start of each period.

Rate and NPer must be in the same units to calculate correctly. If Rate is expressed in months, then NPer must also be expressed in months.

Negative values represent payments paid out, whereas positive values represent payments received.

Example

This example calculates the principal paid during each year on a loan of $1,000.00 with an annual rate of 10% for a period of 10 years. The result is displayed as a table containing the following information: payment, principal payment, principal balance.

Const crlf = Chr$(13) + Chr$(10)

  pay = Pmt(.1,10,1000.00,0,1)
  msg1 = "Amortization table for 1,000" & crlf & "at 10% annually for"
  msg1 = msg1 & " 10 years: " & crlf & crlf
  bal = 1000.00
  For per = 1 to 10
    prn = PPmt(.1,per,10,1000,0,0)
    bal = bal + prn
    msg1 = msg1 & Format(pay,"Currency") & " " & Format$(Prn,"Currency")
    msg1 = msg1 & " " & Format(bal,"Currency") & crlf
  Next per
  MsgBox msg1
End Sub

See Also

IPmt (function); NPer (function); PPmt (function); Rate (function).

P