Tips for selecting financial functions
Some financial functions are used to solve time value of money (TVM) problems — that is, problems that involve cash flows over time at specific interest rates. They can involve regular cash flows and time intervals or irregular cash flows and time intervals.
Financial functions can also be used to solve everyday financial questions.
The topics below explain which function to use to solve various types of financial problems.
Regular cash flows and time intervals
Use the financial functions listed below to solve time value of money problems that have regular periodic cash flows (all payments are of a constant amount, at constant intervals, and have fixed interest rates). These functions are interrelated.
Function and its purpose | Arguments used by the function |
|---|---|
FV (future-value): Use to determine the future value of a series of cash flows (what it is worth at a future point in time), considering the other factors such as interest rate. | periodic-rate, num-periods, payment, present-value, when-due |
NPER (num-periods): Use to determine the number of periods it would take to repay a loan or the number of periods you might receive an annuity, considering the other factors such as interest rate. | periodic-rate, payment, present-value, future-value, when-due |
PMT (payment): Use to determine the amount of the payment that would be required on a loan or received on an annuity, considering the other factors such as interest rate. | periodic-rate, num-periods, present-value, future-value, when-due |
PV (present-value): Use to determine the present value of a series of cash flows (what it is worth today), considering the other factors such as interest rate. | periodic-rate, num-periods, payment, future-value, when-due |
RATE (periodic-rate): Use to determine the periodic interest rate for a loan or annuity, based on the other factors such as the number of periods in the loan or annuity. | num-periods, payment, present-value, future-value, when-due, estimate |
Irregular cash flows and time intervals
Use the financial functions listed below to solve time value of money problems that have irregular, fixed-periodic cash flows—that is, cash flows occur at regular time intervals but the amounts vary (are not the same each period) or cash flows have irregular time intervals (cash flows do not occur at regular time intervals such as "each month").
Function and its purpose | Arguments used by the function |
|---|---|
IRR: Use to determine a periodic rate such that the net present value of a series of potentially irregular cash flows that occur at regular time intervals is equal to 0. This is commonly called the internal rate of return. | flows-range, estimate flows-range is a specified collection of cash flows that may implicitly include a payment, a present-value and a future-value. |
MIRR: Use to determine a periodic rate such that the net present value of a series of potentially irregular cash flows that occur at regular time intervals is equal to 0. This is commonly called the modified internal rate of return. MIRR differs from IRR in that it permits positive and negative cash flows to be discounted at a different rate. | flows-range, finance-rate, reinvest-rate flows-range is a specified collection of cash flows that may implicitly include a payment, a present-value and a future-value. finance-rate and reinvest-rate are specific cases of periodic-rate. |
NPV: Use to determine the present value of a series of potentially irregular cash flows that occur at regular time intervals. This is commonly called the net present value. | periodic-rate, cash-flow, cash-flow… cash-flow, cash-flow… is a specified series of one or more cash flows that may implicitly include a payment, present-value and future-value. |
XIRR: Use to determine the internal rate of return for an investment that is based on a series of irregularly spaced cash flows. | payments, dates, guess payments is a specified collection of cash flows with corresponding dates. guess is an estimate of the internal rate of return. |
XNPV: Use to determine the present value of an investment or annuity based on a series of irregularly spaced cash flows and at a discount interest rate. | discount, payments, dates payments is a specified collection of cash flows with corresponding dates, to which the discount will be applied. |
Savings
Use any of the functions listed below for problems involving savings.
To determine this | Use this function |
|---|---|
The effective interest rate on an investment or savings account that pays interest periodically | |
How much a CD will be worth at maturity (note that payment will be 0) | |
The nominal rate of interest on a CD where the issuer has advertised the "effective rate" | |
How many years it will take to save a specific amount, given monthly deposits to a savings account (note that present-value will be the amount deposited at the beginning, and could be 0) | |
How much to save each month to reach a savings goal in a given number of years (note that present-value will be the amount deposited at the beginning, and could be 0) |
Loans
Use any of the functions listed below for problems involving loans.
To determine this | Use this function |
|---|---|
The amount of interest paid during any period of the loan (for example, the third year or months 9-12) | |
The amount of principal paid during any period of the loan (for example, the third year or months 9-12) | |
The amount of interest included in any payment period of the loan (for example, the 36th loan payment) | |
The amount of principal included in any payment period of the loan (for example, the 36th loan payment |
Bond investments
Use any of the functions listed below for problems involving bond investments.
To determine this | Use this function |
|---|---|
The amount of interest that has accrued or been paid since a bond’s purchase issuance date for a bond that pays periodic interest | |
The amount of interest that has accrued or been paid since a bond’s purchase issuance date for a bond that pays interest only at maturity | |
The weighted average of the present value of a bond’s cash flows, expressed as a period of time | |
The weighted average of the present value of a bond’s cash flows, expressed as a percentage change in price for a 1% change in yield | |
The number of coupon payments between the time a bond is purchased and its maturity | |
The annual discount rate for a bond that is sold at a discount to its redemption value and pays no interest (often known as a zero coupon bond) | |
The effective annual interest rate for a bond that pays interest only at its maturity (no periodic payments, but the bond does have a coupon rate) | |
The expected purchase price of a bond that pays periodic interest | |
The expected purchase price of a bond sold at a discount and that does not pay interest | |
The expected purchase price of a bond that pays interest only at maturity) | |
The amount received on a bond that pays interest only at its maturity (no periodic payments, but the bond does have a coupon rate), including interest | |
The effective annual interest rate of a bond that pays periodic interest | |
The effective annual interest rate of a bond sold at a discount and that does not pay interest | |
The effective annual interest rate of a bond that pays interest only at maturity |
Depreciation
Use any of the functions listed below for problems involving depreciation.
To determine this | Use this function |
|---|---|
The periodic amount of depreciation of an asset using the fixed-declining balance method | |
The periodic depreciation of an asset using a declining balance method such as "double-declining balance" | |
The periodic depreciation of an asset using the straight-line method | |
The periodic depreciation of an asset using the sum-of-the-years-digits method | |
The total depreciation over a given period for an asset depreciated using a declining balance method |