# ExcelNutshell

Document Sample

```					                                   Excel Financial Formulas
Future Value       The FV function returns the future value of an investment based on periodic, constant
payments and a constant interest rate.
Excel Formula: FV(rate,nper,pmt,pv), where:
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.
pv: is the present value, or the lump-sum amount that a series of
future payments is worth right now.
Example: Find the ammount accumulated when \$1000 is invested for 5 years at 8% compounded
quarterly.
Solution:
Interest rate:                       0.08
Compound Periods:                        4
Number of years:                         5
rate:            0.02 = Interest rate / compound periods
nper:              20 = Number of years * compound periods
pmt:               0
pv:          -1000 (note negative sign here)
FV(rate,nper,pmt,pv) =      \$1,485.95

Present Value       The PV function 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.
Excel Formula: PV(rate, nper, pmt, fv), where
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.
fv: is the future value, or a cash balance you want to attain after

Example: Find the present value of \$1000 due after 3 years if the interest rate is 9% compounded
monthly.
Solution:
Interest rate:                           0.09
Compound Periods:                           12
Number of years:                             3
rate:            0.0075 = Interest rate / compound periods
nper:                  36 = Number of years * compound periods
pmt:                   0
fv:             1000
PV(rate, nper, pmt, fv) =         (\$764.15)
Rate               The RATE function returns the interest rate per period of an annuity. RATE is calculated by
iteration and can have zero or more solutions.
Excel Formula: RATE(nper,pmt,pv,fv), where
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.
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

Example: Find the nominal rate compounded seminnually for an investment of \$500 which amounts
to \$588.38 in three years.
Solution: Compound Periods:                        2
Number of years:                         3
nper:             6
pmt:             0
pv:          -500 (note the negative sign here)
fv:       588.38

RATE(nper,pmt,pv,fv) =                  2.75%

Payment            The PMT function calculates the payment or a loan based on constant payments and a
constant interest rate.
Excel Formula: PMT(rate,nper,pv,fv), where
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 principal.
fv: is the future value, or a cash balance you want to attain after

Periods            The NPER function teturns the number of periods for an investment based on periodic,
constant payments and a constant interest rate.
Excel Formula: NPER(rate, pmt, pv, fv), where
rate: is the interest rate for the loan.
pmt: is the payment made each period; it cannot change over the life
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

Effective Rate      You can use the FV (future value) function to compute the 'effective rate'
Example: What effective rate is equivalent to the nominal rate of 6% compounded semiannually?
Solution: Interest rate:                             0.06
Compound periods:                              2
rate:               0.03
nper:                   2
pmt:                   0
pv:                  -1 (note the negative sign here)
FV(rate,nper,pmt,pv)-1 =                 6.09% (subtract 1 from FV and format as %)

Annuities Examples
Example: Find the present value of an annuity of \$100 per month for 3 1/2 years at an interest rate of
6% compounded monthly.
Solution: We use the PV (present value) function as follows:
Interest rate:                             0.06
Compound Periods:                            12
Number of years:                            3.5
rate:               0.005
nper:                   42
pmt:                 -100 (note the negative sign here)
fv:                   0
PV(rate, nper, pmt, fv) =           \$3,779.83

Example: Find the monthly payment for a loan of \$10000 for 3 years with an interest rate of 8%
compounded monthly.
Solution: We use the PMT (payment) function as follows:

Interest rate:                            0.08
Compound Periods:                           12
Number of years:                             3
rate:        0.006666667
nper:                   36
pv:              -10000 (note the negative sign here)
fv:                   0
PMT(rate,nper,pv,fv) =            \$313.36

Example: Optional: For the previous example, find the payment if the payments are made at the
beginning of the month.
Solution: The PMT function (like most others) can take another optional input parameter (0 =
payment at end, 1 = payment at beginning of month) as follows:
rate:        0.006666667
nper:                   36
pv:              -10000 (note the negative sign)
fv:                   0
PMT(rate,nper,pv,fv,1) =            \$311.29

Example: How many months will it take to pay off a debt of \$1500 where payments of \$75 will be
made each month and the interest rate is 12% compounded monthly.
Solution: We will use the NPER (Periods) function as follows:
Interest rate:                             0.12
Compound Periods:                            12
Number of years:                               3
rate:               0.01
pmt:                  75
pv:              -1500 (note the negative sign)
fv:                  0
NPER(rate, pmt, pv, fv) =   22.42574188

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 0 posted: 10/25/2011 language: English pages: 4
xiaohuicaicai