Documentation/Calc Functions/PV

    From The Document Foundation Wiki
    Other languages:

    Function name:

    PV

    Category:

    Financial Analysis

    Summary:

    Calculates the present value of an investment or loan. The finance term "present value" refers to the value today of an amount that is to be received at some future date, assuming a specific rate of return.

    PV 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:

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

    Returns:

    Returns a real number, which is the present value of the specified investment or loan. The number returned is in the same currency unit as the specified future value and period payment, and by default is formatted in a locale-dependent currency format.

    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.

    NPER is a real number, or a reference to a cell containing that number, which is the number of payment periods in the term.

    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.

    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.

    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 PV reports a #VALUE! error.
    • PV does not impose any range checks on the values of the Rate, NPER, PMT, and FV arguments.

    Additional details:

    • In common with several of Calc’s financial functions, PV 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).
    • PV assumes the convention that money received is positive, while money paid out is negative.
    • For more information about the concept of present value, visit Wikipedia's Present value page.

    Examples:

    Note that the formatting of the return values given in the following examples assume the English (USA) locale.

    Formula Description Returns
    =PV(A1; A2; A3; A4) where cell A1 contains the formula =8%/12, cell A2 contains the value 48, cell A3 contains the value 500, and cell A4 contains the value 20000. Here the function calculates the present value of an investment that pays the investor 500 currency units every month and an extra sum of 20,000 currency units at the end of the four-year 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). The value returned is -35,019.37 currency units and this is the amount that should be paid to purchase the investment today. This value can be compared to the actual cost of the investment to evaluate whether it is good value for money. Note that payments to the investor have a positive value, while payments by the investor are negative. Note also the present value of -35,019.37 currency units leads to future payments of 44,000 currency units and the difference between these two is due to interest accrued during the life of the investment. -$35,019.37
    =PV(8%/12; 48; 500; 20000; 1) Here the function calculates the present value 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 -35,155.91 currency units and this is the amount that should be paid to purchase the investment today. Note that the value returned in this case is slightly greater than that of the previous example and this is expected because the value of each periodic payment has less time to accrue interest in this scenario. -$35,155.91
    =PV(7%; 20; 1500) Here the function calculates the present value of an annuity which pays the investor 1,500 currency units at the end of each year, for the next 20 years. 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. PV returns the value -15,891.02 which is the present value of the annuity – if the actual cost is higher, then it is probably not a good investment. -$15,891.02
    =PV(5.5%/12; 60; −300) Here the function calculates the present value of a loan, assuming a constant annual interest rate of 5.5%. The loan is to be repaid at the rate of 300 currency units each month for 5 years. The FV and Type arguments are again omitted and assume their default values of 0.0 and 0 respectively. PV returns the value 15,705.85, which is the present value of the loan – if the lender is offering a smaller lump sum for these terms, then it may be better to look for a different provider. $15,705.85
    =PV(7.5%/12; 18*12; −100; 60000) An investment is required that will provide a cash sum of 60,000 currency units in 18 years time. 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. PV returns the value -3,785.21. Hence an initial sum of 3,785.21 currency units should be invested at the start of the investment to ensure that the desired amount is available after 18 years. -$3,785.21

    Related LibreOffice functions:

    FV

    NPER

    PMT

    RATE

    ODF standard:

    Section 6.12.41, part 2

    Related (or similar) Excel functions:

    PV