Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
Syntax
PV(rate,nper,pmt,fv,type)
Rate is the interest rate per period. For example, if you obtain an automobile loan at a 10 percent annual interest rate and make monthly payments, your interest rate per month is 10%/12, or 0.83%. You would enter 10%/12, or 0.83%, or 0.0083, into the formula as the rate.
Nper is the total number of payment periods in an annuity. For example, if you get a four-year car loan and make monthly payments, your loan has 4*12 (or 48) periods. You would enter 48 into the formula for nper.
Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. For example, the monthly payments on a $10,000, four-year car loan at 12 percent are $263.33. You would enter -263.33 into the formula as the pmt. If pmt is omitted, you must include the fv argument.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). For example, if you want to save $50,000 to pay for a special project in 18 years, then $50,000 is the future value. You could then make a conservative guess at an interest rate and determine how much you must save each month. If fv is omitted, you must include the pmt argument.
Type is the number 0 or 1 and indicates when payments are due.
|
Set type equal to |
If payments are due |
|
0 or omitted |
At the end of the period |
|
1 |
At the beginning of the period |
Remarks
- Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
- The following functions apply to annuities:
|
CUMIPMT |
PPMT |
|
CUMPRINC |
PV |
|
FV |
RATE |
|
FVSCHEDULE |
XIRR |
|
IPMT |
XNPV |
|
PMT |
|
An annuity is a series of constant cash payments made over a continuous period. For example, a car loan or a mortgage is an annuity. For more information, see the description for each annuity function.- In annuity functions, cash you pay out, such as a deposit to savings, is represented by a negative number; cash you receive, such as a dividend check, is represented by a positive number. For example, a $1,000 deposit to the bank would be represented by the argument -1000 if you are the depositor and by the argument 1000 if you are the bank.
- Microsoft Excel solves for one financial argument in terms of the others. If rate is not 0, then:
If rate is 0, then:
(pmt * nper) + pv + fv = 0
Example
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
The result is negative because it represents money that you would pay, an outgoing cash flow. If you are asked to pay (60,000) for the annuity, you would determine this would not be a good investment because the present value of the annuity (59,777.15) is less than what you are asked to pay.
Note The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.
RATE
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Syntax
RATE(nper,pmt,pv,fv,type,guess)
For a complete description of the arguments nper, pmt, pv, fv, and type, see PV.
Nper is the total number of payment periods in an annuity.
Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
Pv is the present value— the total amount that a series of future payments is worth now.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
Type is the number 0 or 1 and indicates when payments are due.
|
Set type equal to |
If payments are due |
|
0 or omitted |
At the end of the period |
|
1 |
At the beginning of the period |
Guess is your guess for what the rate will be.
- If you omit guess, it is assumed to be 10 percent.
- If RATE does not converge, try different values for guess. RATE usually converges if guess is between 0 and 1.
Remark
Make sure that you are consistent about the units you use for specifying guess and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for guess and 4*12 for nper. If you make annual payments on the same loan, use 12% for guess and 4 for nper.
Example
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
Note The number of years of the loan is multiplied by 12 to get the number of months.
SLN
Returns the straight-line depreciation of an asset for one period.
Syntax
SLN(cost,salvage,life)
Cost is the initial cost of the asset.
Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset).
Life is the number of periods over which the asset is depreciated (sometimes called the useful life of the asset).
Example
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
SYD
Returns the sum-of-years' digits depreciation of an asset for a specified period.
Syntax
SYD(cost,salvage,life,per)
Cost is the initial cost of the asset.
Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset).
Life is the number of periods over which the asset is depreciated (sometimes called the useful life of the asset).
Per is the period and must use the same units as life.
Remark
- SYD is calculated as follows:
Example
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
VDB
Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining balance method or some other method you specify. VDB stands for variable declining balance.
Syntax
VDB(cost,salvage,life,start_period,end_period,factor,no_switch)
Cost is the initial cost of the asset.
Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset).
Life is the number of periods over which the asset is depreciated (sometimes called the useful life of the asset).
Start_period is the starting period for which you want to calculate the depreciation. Start_period must use the same units as life.
End_period is the ending period for which you want to calculate the depreciation. End_period must use the same units as life.
Factor is the rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method). Change factor if you do not want to use the double-declining balance method. For a description of the double-declining balance method, see DDB.
No_switch is a logical value specifying whether to switch to straight-line depreciation when depreciation is greater than the declining balance calculation.
- If no_switch is TRUE, Microsoft Excel does not switch to straight-line depreciation even when the depreciation is greater than the declining balance calculation.
- If no_switch is FALSE or omitted, Excel switches to straight-line depreciation when depreciation is greater than the declining balance calculation.
All arguments except no_switch must be positive numbers.
Example
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
Note The results are rounded to two decimal places
MIRR
Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash.
Syntax
MIRR(values,finance_rate,reinvest_rate)
Values is an array or a reference to cells that contain numbers. These numbers represent a series of payments (negative values) and income (positive values) occurring at regular periods.
- Values must contain at least one positive value and one negative value to calculate the modified internal rate of return. Otherwise, MIRR returns the #DIV/0! error value.
- If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
Finance_rate is the interest rate you pay on the money used in the cash flows.
Reinvest_rate is the interest rate you receive on the cash flows as you reinvest them.
Remarks
- MIRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want and with the correct signs (positive values for cash received, negative values for cash paid).
- If n is the number of cash flows in values, frate is the finance_rate, and rrate is the reinvest_rate, then the formula for MIRR is:
Example
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
NPER
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
Syntax
NPER(rate, pmt, pv, fv, type)
For a more complete description of the arguments in NPER and for more information about annuity functions, see PV.
Rate is the interest rate per period.
Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes.
Pv is the present value, or the lump-sum amount that a series of future payments is worth right now.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
Type is the number 0 or 1 and indicates when payments are due.
|
Set type equal to |
If payments are due |
|
0 or omitted |
At the end of the period |
|
1 |
At the beginning of the period |
Example
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
NPV
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
Syntax
NPV(rate,value1,value2, ...)
Rate is the rate of discount over the length of one period.
Value1, value2, ... are 1 to 29 arguments representing the payments and income.
- Value1, value2, ... must be equally spaced in time and occur at the end of each period.
- NPV uses the order of value1, value2, ... to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
- Arguments that are numbers, empty cells, logical values, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.
- If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
Remarks
- The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments. For more information, see the examples below.
- If n is the number of cash flows in the list of values, the formula for NPV is:
- NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment. For information about annuities and financial functions, see PV.
- NPV is also related to the IRR function (internal rate of return). IRR is the rate for which NPV equals zero: NPV(IRR(...), ...) = 0.
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
In the preceding example, you include the initial $10,000 cost as one of the values, because the payment occurs at the end of the first period.
Example 2
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
In the preceding example, you don't include the initial $40,000 cost as one of the values, because the payment occurs at the beginning of the first period.
PMT
Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax
PMT(rate,nper,pv,fv,type)
For a more complete description of the arguments in PMT, see the PV function.
Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type is the number 0 (zero) or 1 and indicates when payments are due.
|
Set type equal to |
If payments are due |
|
0 or omitted |
At the end of the period |
|
1 |
At the beginning of the period |
Remarks
- The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
- Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.
To find the total amount paid over the duration of the loan, multiply the returned PMT value by nper.
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
Example 2
You can use PMT to determine payments to annuities other than loans.
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
Note The interest rate is divided by 12 to get a monthly rate. The number of years the money is paid out is multiplied by 12 to get the number of payments.
PPMT
Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
Syntax
PPMT(rate,per,nper,pv,fv,type)
For a more complete description of the arguments in PPMT, see PV.
Rate is the interest rate per period.
Per specifies the period and must be in the range 1 to nper.
Nper is the total number of payment periods in an annuity.
Pv is the present value— the total amount that a series of future payments is worth now.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type is the number 0 or 1 and indicates when payments are due.
|
Set type equal to |
If payments are due |
|
0 or omitted |
At the end of the period |
|
1 |
At the beginning of the period |
Remark
Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
Example 1
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
Note The interest rate is divided by 12 to get a monthly rate. The number of years the money is paid out is multiplied by 12 to get the number of payments.
Example 2
The example may be easier to understand if you copy it to a blank worksheet.
- Create a blank workbook or worksheet.
- Select the example in the Help topic. Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
DB
Returns the depreciation of an asset for a specified period using the fixed-declining balance method. Syntax DB(cost,salvage,life,period,month) Cost is the initial cost of the asset. Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset). Life is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset). Period is the period for which you want to calculate the depreciation. Period must use the same units as life. Month is the number of months in the first year. If month is omitted, it is assumed to be 12. Remarks
(cost - total depreciation from prior periods) * rate where: rate = 1 - ((salvage / cost) ^ (1 / life)), rounded to three decimal places
cost * rate * month / 12
((cost - total depreciation from prior periods) * rate * (12 - month)) / 12 Example 1 The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
DDB Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. Syntax DDB(cost,salvage,life,period,factor) Cost is the initial cost of the asset. Salvage is the value at the end of the depreciation (sometimes called the salvage value of the asset). Life is the number of periods over which the asset is being depreciated (sometimes called the useful life of the asset). Period is the period for which you want to calculate the depreciation. Period must use the same units as life. Factor is the rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the double-declining balance method). Important All five arguments must be positive numbers. Remarks
((cost-salvage) - total depreciation from prior periods) * (factor/life)
Example The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
Note The results are rounded to two decimal places.
FV
Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Syntax FV(rate,nper,pmt,pv,type) For a more complete description of the arguments in FV and for more information on annuity functions, see PV. Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
Example 1 The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
Note The annual interest rate is divided by 12 because it is compounded monthly. Example 2 The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
Note The annual interest rate is divided by 12 because it is compounded monthly. Example 3 The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
Note The annual interest rate is divided by 12 because it is compounded monthly. Example 4 The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
Note The annual interest rate is divided by 12 because it is compounded monthly.
IPMT Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. For a more complete description of the arguments in IPMT and for more information about annuity functions, see PV. Syntax IPMT(rate,per,nper,pv,fv,type) Rate is the interest rate per period. Per is the period for which you want to find the interest and must be in the range 1 to nper. Nper is the total number of payment periods in an annuity. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
Example The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
Note The interest rate is divided by 12 to get a monthly rate. The years the money is paid out is multiplied by 12 to get the number of payments.
IRR Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods. Syntax IRR(values,guess) Values is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
Guess is a number that you guess is close to the result of IRR.
Remarks IRR is closely related to NPV, the net present value function. The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value. The following formula demonstrates how NPV and IRR are related: NPV(IRR(B1:B6),B1:B6) equals 3.60E-08 [Within the accuracy of the IRR calculation, the value 3.60E-08 is effectively 0 (zero).] Example The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
ISPMT Calculates the interest paid during a specific period of an investment. This function is provided for compatibility with Lotus 1-2-3. Syntax ISPMT(rate,per,nper,pv) Rate is the interest rate for the investment. Per is the period for which you want to find the interest, and must be between 1 and nper. Nper is the total number of payment periods for the investment. Pv is the present value of the investment. For a loan, pv is the loan amount. Remarks
Example The example may be easier to understand if you copy it to a blank worksheet.
Selecting an example from Help
Note The interest rate is divided by 12 to get a monthly rate. The number of years the money is paid out is multiplied by 12 to get the number of payments. |
.: Weblog Themes By Pichak :.