Business and financial functions

FV (Future Value)

    Calculates the future value of an investment and a series of added payments, based on a given interest rate and a given number of periods.

    Format: =FV(rate,nper,pmt,pv,type)

    Arguments:
    rate: interest rate per period
    nper: number of periods
    pmt: added payment to be made per period; enter as a negative number
    pv (optional): present value of investment; enter as a negative number
    type (optional): type of payment scheme. 0 means payments are due at the end of the period; 1 means payments are due at the beginning of the period.

    Example: =FV(5%,10,-300,-3000,0) calculates the value in 10 years of a $3000 initial investment, with an added payment of $300 at the end of each year, at an annual rate of 5%. The future value in this example is $8660.05 (when using the Currency number format with a precision of 2).

Top

IRR (Internal Rate of Return)

    Calculates an approximate internal rate of return on an investment and series of cash flows.

    Note: This function is not available in databases.

    Format: =IRR(range,guess)

    Arguments:
    range: a range of cells containing future cash flow amounts in the order they are paid out or received. The first cell in the range should contain the initial investment, represented as a negative number.
    guess (optional): your estimated internal rate of return (a numeric expression). If you don't enter a guess, the default rate is 10 percent. After 20 guesses, this function returns a #NUM! error. Then you can try another value.

    Example: In a spreadsheet, cells C1 to C11 contain the following values: -220000, 0, 25000, 40000, 40000, 40000, 40000, 40000, 40000, 40000, 40000. The data shows that a projected expenditure of $220,000 increases revenues by $25,000 the second year and $40,000 for each of the next eight years.

    =IRR(C1...C11) returns 7.87% (when using the Percent format).

Top

MIRR (Modified Internal Rate of Return)

    Calculates the modified internal rate of return of a series of cash flows, given safe and risk investment rates.

    Format: =MIRR(safe,risk,values,...)

    Arguments:
    safe: rate returned by the investment that finances the negative cash flows.
    risk: rate at which the positive cash flows can be reinvested.
    values: future cash flows. Ranges can be used for values.

    Example: =MIRR(0.1,0.15,-5000,2000,4000,-2000, 5000) returns 19.66% (when using the Percent format).

Top

NPER (Number of Periods)

    Calculates the number of periods of an investment involving constant cash flows.

    Format: =NPER(rate,pmt,pv,fv,type)

    Arguments:
    rate: interest rate per period
    pmt: payment to be made per period
    pv: present value of the investment
    fv (optional): future value of the investment or cash value remaining after final payment. If omitted, fv is assumed to be 0.
    type (optional): type of payment scheme. 0 (default) means payments are due at the end of the period; 1 means payments are due at the beginning of the period.

    Example: =NPER(10%/12,-250,10000) returns 48.86 months (when using a Fixed number format with a precision of 2). It shows that a $10,000 purchase, at a 10% interest rate (over 12 months) with monthly payments of $250 and nothing down, requires 49 monthly payments.

    Example: =NPER(9%/12,-100,90000) returns an error because $90,000 will never be paid off with monthly payments of $100 and a 9% interest rate.

Top

NPV (Net Present Value)

    Calculates the net present value of an investment based on a series of future cash flows, assuming a fixed interest rate.

    Format: =NPV(interest rate,payment1,payment2,...)

    Arguments:
    interest rate: periodic interest rate
    payment values: a series of values, such as payments or income. Must be entered in chronological order. Ranges can be used for values.

    Example: =NPV(10%,-5000,0,10000,10000,10000) returns $16007.04 (when using the Currency format). It shows that the NPV of a $5,000 investment at 10% interest, followed by an income flow of $0, $10,000, $10,000, and $10,000, is $16,007.04 after four years.

Top

PMT (Payment)

    Calculates payments, given the interest rate, number of periods, preset value, future value, and type of payment.

    Format: =PMT(rate,nper,pv,fv,type)

    Arguments:
    rate: interest rate per period
    nper: number of periods
    pv: present value of the investment
    fv (optional): future value of the investment or cash value remaining after final payment. If omitted, fv is assumed to be 0.
    type (optional): type of payment scheme. 0 (default) means payments are due at the end of the period; 1 means payments are due at the beginning of the period.

    Example: =PMT(10%/12,36,10000,0) returns -$322.67 (when using the Currency format). It shows that a $12,000 purchase minus a $2,000 downpayment (or $10,000) over 36 months at a 10% interest rate requires monthly payments of $322.67.

Top

PV (Present Value)

    Calculates the present value of an investment, which is the current value of a series of future payments, based on a specific periodic interest rate over a given number of periods for a given payment amount.

    Format: =PV(rate,nper,pmt,fv,type)

    Arguments:
    rate: interest rate per period
    nper: number of periods
    pmt: payment to be made per period
    fv (optional): future value of the investment or cash value remaining after final payment. If omitted, fv is assumed to be 0.
    type (optional): type of payment scheme. 0 (default) means payments are due at the end of the period; 1 means payments are due at the beginning of the period.

    Example: =PV(10%,5,-100,5000) returns -2725.5279 (using a Fixed number format with a precision of 4). It shows that to accumulate $5,000 in five years, when making payments of $100 at the end of each year, you would have to invest $2,725.53 now, at an interest rate of 10%.

Top

RATE

    Calculates the interest rate required for a present value to grow to a specified future value when compounded over a given term. If either the present value or the term is 0, the function returns an error (#ERROR!).

    Format: =RATE(fv,pv,term)

    Arguments:
    fv: future value of the investment
    pv: present value of the investment
    term: number of payments (must be a positive number)

    Example: =RATE(20000,5000,10*12) returns 0.0116 (when using a Fixed number format with a precision of 4). It shows that a monthly interest rate of 1.16% (or 13.9% annually) is required for $5,000 to grow to $20,000 in 10 years. The number of years is multiplied by 12 to find the number of monthly payments.

Top

Related topic

 


Table of contents | Index