Business and financial functions

These functions operate on numbers and require numeric expressions as arguments. Unless otherwise stated, these functions return numeric values as their result.

  FV (Future Value)
IRR (Internal Rate of Return)
MIRR (Modified Internal Rate of Return)
NPER (Number of Periods)
NPV (Net Present Value)
PMT (Payment)
PV (Present Value)
RATE

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

  Alphabetical list of functions

 
Table of contents Index