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 |