Interest Rate Formula - Excel by pza38721

VIEWS: 244 PAGES: 4

More Info
									                                   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
                                                the last payment is made.

         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
                                                the last payment is made.

          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
                                                 the last payment is made

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
                                               the last payment is made.

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

								
To top