Microsoft Office Tutorials and References
In Depth Information
The Basic Excel Financial Functions
Financial function arguments
The five basic Excel financial functions have many common arguments. The arguments and their
meanings are listed here:
● rate: The interest rate, expressed as a percentage, that is paid on a loan or used to
discount future cash flows.
The period that the interest rate covers must be the same period used for nper and pmt.
● nper: The number of periods. This could be the number of payments on a loan or the
number of periods that money is kept in a savings account.
The number of periods must be expressed in the same terms as rate and pmt. A 30-year
mortgage with monthly payments, for instance, would have an nper of 360.
pmt: The amount of each payment. For these financial functions, the payments must be
the same amount and made at regular intervals. The payment amount is normally made up
of both principal and interest.
fv: The future value. This is the last cash flow that settles the transaction. In many cases,
the payments settle the transaction (for example, pay off the loan), so there is no future
pv: The present value. This is the first cash flow that starts the transaction, such as
borrowing money on a loan or putting money into a savings account.
If the transaction is made up of just payments, there may not be a present value.
type: Whether the payments are made in arrears (0 or default) or in advance (1).
guess: An approximation of the result. When computing an interest rate, Excel must
perform many iterations to get the answer. You can help Excel by specifying a guess
argument that you expect to be close to the actual result.
The example in this section computes the present value of a series of future receipts, sometimes
called an annuity. You get one payment of $1,200 each year for ten years. The value of those
payments right now is $6,780.27.
In other words, if the payer offered you more than $6,800 right now (so he wouldn’t have to
make the payments to you in the future), you would take it. If he offered you less, you would
pass and wait for the regular payments.
The file basic financial formulas.xlsx on the companion CD-ROM contains all
the examples in this section.