Microsoft Office Tutorials and References

In Depth Information

**Working with Financial Functions**

function and some of the other ﬁ nancial functions often used to develop budgets. These

ﬁ nancial functions are the same as those widely used in business and accounting to

perform various ﬁ nancial calculations, such as depreciation of an asset, the amount of

interest paid on an investment, and the present value of an investment.

Figure 3-36

Financial functions for loans and investments

Function

Description

FV(
rate
,
nper
,
pmt
, [
pv=0] [,
type
=0])

Calculates the future value of an investment, where
rate
is the interest

rate per period,
nper
is the total number of periods,
pmt
is the payment in

each period,
pv is the present value of the investment, and
type
indicates

whether payments should be made at the end of the period (0) or the

beginning of the period (1)

PMT(
rate
,
nper
,
pv, [
fv=0] [,
type
=0])

Calculates the payments required each period on a loan or investment,

where
fv is the future value of the investment

IPMT(
rate
,
per
,
nper
,
pv, [
fv=0] [,
type
=0])

Calculates the amount of a loan payment devoted to paying the loan

interest, where
per
is the number of the payment period

PPMT(
rate
,
per
,
nper
,
pv, [
fv=0] [,
type
=0])

Calculates the amount of a loan payment devoted to paying off the principal

of a loan

PV(
rate
,
nper
,
pmt
, [
fv=0] [,
type
=0])

Calculates the present value of a loan or investment based on periodic,

constant payments

NPER(
rate
,
pmt
,
pv, [
fv=0] [,
type
=0])

Calculates the number of periods required to pay off a loan or investment

RATE(
nper
,
pmt
,
pv, [
fv=0] [,
type
=0])

Calculates the interest rate of a loan or investment based on periodic,

constant payments

The cost of a loan to the borrower is largely based on three factors: the principal, the

interest, and the time required to pay back the loan.
Principal
is the amount of money

being loaned, and
interest
is the amount added to the principal by the lender. You can

think of interest as a kind of “user fee” because the borrower is paying for the right to

use the lender’s money for a period of time. A few years ago, Diane and Glenn borrowed

money to buy a second car and are still repaying the bank for the principal and interest

on that loan. On the other hand, Diane and Glenn have also deposited money in their

main savings account and receive interest payments from the bank in return.

Interest is calculated either as simple interest or as compound interest. In
simple

interest
, the interest is equal to a percentage of principal for each period that the money

has been lent. For example, if Diane and Glenn deposit $1,000 at a simple interest rate

of 5 percent, they will receive $50 in interest payments each year. After one year their

investment will be worth $1,050, after two years it will be worth $1,100, and so forth.

With
compound interest
, the interest is applied not only to the principal but also to

any accrued interest. If Diane and Glenn deposit $1,000 in a bank at 5 percent annual

interest compounded every year, they will earn $50 in the ﬁ rst year, raising the value of

the account to $1,050. If they leave that money in the bank for another year, the interest

payment in the second year rises to 5 percent of $1,050 or $52.50, resulting in a total

value of $1,102.50. So they earn more money the second year because they are

receiving interest on their interest.

Compound interest payments are divided into the period of time in which the interest

is applied. For example, an 8 percent annual interest rate compounded monthly results

in 12 interest payments per year with the interest each month equal to 1/12 of 8 percent,

or about 0.67 percent per month.

Another factor in calculating the cost of a loan is the length of time required to pay it

back. The longer it takes to pay back a loan, the more the loan costs because the

borrower is paying interest over a longer period of time. To save money, loans should be

paid back quickly and in full.