Documentation/Calc Functions/NPER

    From The Document Foundation Wiki
    Other languages:

    Function name:

    NPER

    Category:

    Financial Analysis

    Summary:

    Calculates the number of payment periods for an investment or loan. NPER calculates the number of periods needed, so that the value today (Present Value), followed by a regular payment at the end of each period, results in a specified value at the end of the term (Future Value).

    NPER assumes an investment or loan with the following general characteristics:

    • An initial lump sum.
    • A number of regular payments/deposits.
    • A final sum.
    • A constant interest rate.

    Some of these characteristics can be ignored by setting relevant function arguments to zero.

    Syntax:

    NPER(Rate; PMT; PV[; [FV] [; Type]])

    Returns:

    Returns a real number, which is the number of payment periods for the specified investment or loan.

    Arguments:

    Rate is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the periodic interest rate. It is common for interest rates to be quoted annually and, where necessary, care should be taken to convert such values to be compatible with the length of your payment period.

    PMT is a real number, or a reference to a cell containing that number, which is the constant payment or deposit made in each period.

    PV is a real number, or a reference to a cell containing that number, which is the present value of the investment or loan. PV must be in the same currency units as PMT.

    FV is a real number, or a reference to a cell containing that number, which is the future value of the cash balance desired at the end of the term. If FV is omitted, the function uses the value 0.0. FV must be in the same currency units as PMT and PV.

    Type is a logical value, or a reference to a cell containing a logical value, which determines whether the due date is at the beginning or end of a payment period. If Type is set to 0 or FALSE, or is omitted, then the function assumes the end of the payment period. If Type is set to any other value, then the function assumes the start of the payment period.

    • If any argument is non-numeric, then NPER reports a #VALUE! error.
    • If both PV and FV are set to 0.0, then NPER returns the value 0.
    • NPER does not impose any range checks on the values of the Rate, PMT, PV, and FV arguments. As a consequence, it is possible to generate sets of arguments that lead NPER to return meaningless negative values.

    Additional details:

    • In common with several of Calc’s financial functions, NPER uses a general model to describe both loans and annuities. This model assumes that an initial lump sum is borrowed or invested, a number of regular payments are made or received, and a final sum is paid or received. A constant interest rate is applied and compounded throughout the term of the investment. This model is expressed by the following equations:
    In the case of a zero interest rate:
    [math]\displaystyle{ PV ~+~ PMT \times n~+~FV~=~0 }[/math]
    In the case of a non-zero interest rate:
    [math]\displaystyle{ PV \times (1+r)^{n} ~+~ PMT \times \frac{\left ( \left ( 1+r \right )^{n} -1\right )}{r}\times \left ( 1+ \left (t \times r \right ) \right )~+~FV~=~0 }[/math]
    where:
    FV = future value
    n = number of payment periods
    PMT = periodic payment value
    PV = present value
    r = periodic interest rate
    t = due date flag (0 when the due date is at the end of each payment period; 1 when the due date is at the beginning of each payment period).
    • NPER assumes the convention that money received is positive, while money paid out is negative.
    • The value returned by NPER may have a fractional part, which can result from solving the relevant equation. Non-integer values may not be meaningful in real life, when an integer number of payment periods would be expected.

    Examples:

    Note:

    1. The formatting of the return values given in the following examples might assume the English (USA) locale.
    2. The return values are quoted to one decimal place, since further granularity is not useful for this function.
    Formula Description Returns
    =NPER(A1; A2; A3; A4)

    where cell A1 contains the formula =8% / 12, cell A2 contains the value 500, cell A3 contains the value −35,019.37, and cell A4 contains the value 20,000.

    Here the function calculates the duration in months of an investment that initially costs 35,019.37 currency units, but pays the investor 500 currency units every month and an extra sum of 20,000 currency units at the end of the investment period. The annual interest rate is assumed to be 8% and the periodic payments are made at the end of each month (this is the default setting for the omitted Type argument). Note that payments to the investor have a positive value, while payments by the investor are negative. The value returned is 48.0 and so the investment period is 48 months or 4 years.

    48.0
    =NPER(8% / 12; 500; −35155.91; 20000; 1)

    Here the function calculates the duration in months of the same investment as the previous example, except that the non-zero value for the Type argument implies that the periodic payments of 500 currency units occur at the beginning of each month (rather than at the end). The value returned is again 48.0 and so the investment period is 48 months or 4 years.

    48.0
    =NPER(7%; 1500; −15891.02)

    Here the function calculates the duration in years of an annuity which costs 15,891.02 currency units initially but pays the investor 1500 currency units at the end of each year. A constant annual interest rate of 7% is assumed. The FV and Type arguments are omitted and assume their default values of 0.0 and 0 respectively. The value returned is 20.0 and so the investment period is 20 years.

    20.0
    =NPER(5.5% / 12; −300; 15705.85)

    Here the function calculates the duration in months of a loan, assuming a constant annual interest rate of 5.5%. The loaned amount is 15,705.85 currency units and it is to be repaid at the rate of 300 currency units each month. The FV and Type arguments are again omitted and assume their default values of 0.0 and 0 respectively. The value returned is 60.0 and so the investment period is 60 months or 5 years.

    60.0
    =NPER(7.5% / 12; −100; −3785.21; 60000)

    An investment is required that will provide a cash sum of 60,000 currency units in 18 years time. The investment costs 3,785.21 currency units initially and 100 currency units are to be invested at the end of each month, with an assumed annual integration rate of 7.5%. The Type argument is omitted and assumes its default value of 0. The value returned is 216.0 and so the investment period is 216 months or 18 years.

    216.0

    Related LibreOffice functions:

    FV

    PMT

    PV

    RATE

    ODF standard:

    Section 6.12.29, part 2

    Related (or similar) Excel functions:

    NPER