| 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.
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: 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). 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: 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). 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: Example: =MIRR(0.1,0.15,-5000,2000,4000,-2000, 5000) returns 19.66% (when using the Percent format). Calculates the number of periods of an investment involving constant cash flows. Format: =NPER(rate,pmt,pv,fv,type) Arguments: 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. 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: 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. 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: 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. 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: 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%. 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: 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. Related topic |
| Table of contents | Index |