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

value.

●

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.

=PV(12%,10,1200,0,0)

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.