VB & VBA in a Nutshell: The Language

NPer Function

Named Arguments

Yes

Syntax

NPer(rate, pmt, pv [, fv [, due]])

rate

Use: Required

Data Type: Double

The interest rate per period.

pmt

Use: Required

Data Type: Double

The payment to be made each period.

pv

Use: Required

Data Type: Double

The present value of the series of future payments or receipts.

fv

Use: Optional

Data Type: Double

The future value of the series of payments or receipts. If omitted, the default value is 0.

due

Use: Optional

Data Type: Variant

A value indicating when payments are due. indicates that payments are due at the end of the payment period; 1 indicates that payments are due at the end of the period. If omitted, the default value is 0.

Return Value

A Double indicating the number of payments.

Description

Determines the number of payment periods for an annuity based on fixed periodic payments and a fixed interest rate.

Rules at a Glance

• rate is a percentage expressed as a decimal. For example, a monthly interest rate of 1% is expressed as 0.01.

• For pv and fv, cash paid out is represented by negative numbers; cash received is represented by positive numbers.

Example

Typically, the amount of time required to repay credit card debt is never explicitly stated. The following program uses the NPer function to determine how much time is required to repay credit card debt.

Private Sub HowLongToPay() On Error GoTo ErrHandler Dim dblRate As Double, dblPV As Double Dim dblPmt As Double Dim lngNPer As Long dblPV = InputBox("Enter the Credit Card balance: ...