Formula for Calculating Interest Payments

Document Sample
Formula for Calculating Interest Payments Powered By Docstoc
					1)                PMT means periodic payment (same amount each period)
       PMT function calculates the period payment for a loan (For the Borrower or the
      Lender). The Amount of each PMT must be the same and the time between each
2)                                   PMT must be the same.
         Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash
3)                              coming into the wallet is positive.
      For the borrow the PV is positive, the PMT is negative, and the FV is negative. For
4)         the Lender the PV is negative, the PMT is positive, and the FV is positive.
       Be consistent with your unit of time! If you are calculating monthly payment, you
     need monthly interest rate and total number of months! (The period can be monthly,
5)                            quarterly, yearly or any other length).
          =PMT(rate = period rate, nper = total number of periods, pv means amount
        invested or lent out today, fv means amount received after all the periods have
      elapsed or amount paid after all the periods have elapsed, type refers to the PMT:
6)                PMT at end of period = 0, PMT at beginning of period = 1)
          =FV(rate = period rate, nper = total number of periods, pmt means periodic
     payment, pv means amount invested or lent out today, type refers to the PMT: PMT
7)                    at end of period = 0, PMT at beginning of period = 1)

      Borrower Point of View: At an Annual Interest Rate of 6.50% the monthly
 1                               PMT paid = $0.00
     Price of Car                  34,799.00 Annual Interest Rate             6.50%
     Down Payment                  10,000.00 Monthly Interest Rate
     Loan Amount                              Years for Loan                       5
     PMT end of period                        Total Months
     Monthly Payment                     0.00 Periods per Year                    12
     PMT begin of period                      Type, 0 = End, 1 = Beg               1

       Lender Point of view: At an Annual Interest Rate of 6.50% the monthly
 2                             PMT received = $0.00
     Price of Car                  34,799.00 Annual Interest Rate             6.50%
     Down Payment                  10,000.00 Monthly Interest Rate            0.54%
     Loan Amount                  -24,799.00 Years for Loan                        5
     Monthly Payment                         Total Months                         60
     Monthly Payment                  485.22 Periods per Year                     12

   At an Annual Interest Rate of 5.25% and a balloon payment of $5,000.00 at
   the end of 36 months, the monthly PMT = ($1,225.21) - Borrower's Point of
 3                                   View.
   Price of Car                50,000.00 Annual Interest Rate         5.25%
   Down Payment                  5,000.00 Monthly Interest Rate       0.44%
   Loan Amount                 45,000.00 Years for Loan                    3
   Balloon Payment              -5,000.00 Total Months                    36
   Monthly Payment                        Periods per Year                12

   At an Annual Interest Rate of 8.50% and no payments during the first year,
 4               the PMT = ($67,328.25) - Borrower's Point of View.
   Loan Amount                1,000,000.00 Annual Interest Rate       8.50%
   Years payment is
   put off                                1 Period Interest Rate      2.13%
   FV after 1 year                          Years for Loan                 6
Period Payment              Total Periods      24
Period Payment   -67,328.25 Periods per Year    4   check
1087748
1)                PMT means periodic payment (same amount each period)
       PMT function calculates the period payment for a loan (For the Borrower or the
      Lender). The Amount of each PMT must be the same and the time between each
2)                                   PMT must be the same.
         Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash
3)                              coming into the wallet is positive.
      For the borrow the PV is positive, the PMT is negative, and the FV is negative. For
4)         the Lender the PV is negative, the PMT is positive, and the FV is positive.
       Be consistent with your unit of time! If you are calculating monthly payment, you
     need monthly interest rate and total number of months! (The period can be monthly,
5)                            quarterly, yearly or any other length).
          =PMT(rate = period rate, nper = total number of periods, pv means amount
        invested or lent out today, fv means amount received after all the periods have
      elapsed or amount paid after all the periods have elapsed, type refers to the PMT:
6)                PMT at end of period = 0, PMT at beginning of period = 1)
          =FV(rate = period rate, nper = total number of periods, pmt means periodic
     payment, pv means amount invested or lent out today, type refers to the PMT: PMT
7)                    at end of period = 0, PMT at beginning of period = 1)

      Borrower Point of View: At an Annual Interest Rate of 6.50% the monthly
 1                             PMT paid = ($485.22)
     Price of Car                  34,799.00   Annual Interest Rate           6.50%
     Down Payment                  10,000.00   Monthly Interest Rate          0.54%
     Loan Amount                   24,799.00   Years for Loan                      5
     PMT end of period               -485.22   Total Months                       60
     Monthly Payment                 -485.22   Periods per Year                   12
     PMT begin of period             -482.61   Type, 0 = End, 1 = Beg              1

       Lender Point of view: At an Annual Interest Rate of 6.50% the monthly
 2                            PMT received = $485.22
     Price of Car                  34,799.00   Annual Interest Rate           6.50%
     Down Payment                  10,000.00   Monthly Interest Rate          0.54%
     Loan Amount                  -24,799.00   Years for Loan                      5
     Monthly Payment                  485.22   Total Months                       60
     Monthly Payment                  485.22   Periods per Year                   12

   At an Annual Interest Rate of 5.25% and a balloon payment of $5,000.00 at
   the end of 36 months, the monthly PMT = ($1,225.21) - Borrower's Point of
 3                                   View.
   Price of Car                50,000.00 Annual Interest Rate         5.25%
   Down Payment                  5,000.00 Monthly Interest Rate       0.44%
   Loan Amount                 45,000.00 Years for Loan                    3
   Balloon Payment              -5,000.00 Total Months                    36
   Monthly Payment              -1,225.21 Periods per Year                12

   At an Annual Interest Rate of 8.50% and no payments during the first year,
 4               the PMT = ($67,328.25) - Borrower's Point of View.
   Loan Amount                1,000,000.00 Annual Interest Rate       8.50%
   Years payment is
   put off                            1.00 Period Interest Rate       2.13%
   FV after 1 year            1,087,747.96 Years for Loan                  6
Period Payment   -67,328.25 Total Periods      24
Period Payment   -67,328.25 Periods per Year    4   check
1087748
     =RATE(nper = total number of periods, pmt means periodic
   payment, pv means amount invested or lent out today, fv means
    amount received after all the periods have elapsed or amount
    paid after all the periods have elapsed, type refers to the pmt:
1)     pmt at end of period = 0, pmt at beginning of period = 1)
      Be consistent with your unit of time! If you are calculating
      monthly payment, you need monthly interest rate and total
   number of months! (The period can be monthly, quarterly, yearly
2)                         or any other length).
3)          Remember, RATE returns the period rate!!!!!!
     Cash Flow matters in Finance. Cash going out of the wallet is negative.
4)                 Cash coming into the wallet is positive.
   For the borrow the PV is positive, the PMT is negative, and the
   FV is negative. For the Lender the PV is negative, the PMT is
5)                 positive, and the FV is positive.

      If you pay points, then you do not get to use all the
        cash you borrowed. Therefore, the Rate is really
     based on the cash you receive, not the face value of
                            the loan.
     Item   House       Annual Rate                     5.00%
     Price   $200,000 Monthly Rate                     .00417
     Down           $0 Years                                15
     Loan    $200,000 Months                               180
     Points        0.02 Periods Per Year                    12
                        Type (0 = End, 1 =
     PMT      -1,581.59 Begin)                               0
                        Adjusted Annual
                        Rate                                                   0.053081
     =RATE(nper = total number of periods, pmt means periodic
   payment, pv means amount invested or lent out today, fv means
    amount received after all the periods have elapsed or amount
    paid after all the periods have elapsed, type refers to the pmt:
1)     pmt at end of period = 0, pmt at beginning of period = 1)
      Be consistent with your unit of time! If you are calculating
      monthly payment, you need monthly interest rate and total
   number of months! (The period can be monthly, quarterly, yearly
2)                         or any other length).
3)          Remember, RATE returns the period rate!!!!!!
     Cash Flow matters in Finance. Cash going out of the wallet is negative.
4)                 Cash coming into the wallet is positive.
   For the borrow the PV is positive, the PMT is negative, and the
   FV is negative. For the Lender the PV is negative, the PMT is
5)                 positive, and the FV is positive.

      If you pay points, then you do not get to use all the
        cash you borrowed. Therefore, the Rate is really
     based on the cash you receive, not the face value of
                            the loan.
     Item   House       Annual Rate                     5.00%
     Price   $200,000 Monthly Rate                     .00417
     Down           $0 Years                                15
     Loan    $200,000 Months                               180
     Points        0.02 Periods Per Year                    12
                        Type (0 = End, 1 =
     PMT      -1,581.59 Begin)                               0
                        Adjusted Annual
                        Rate                        0.053081                   0.053081
     Effective Interest Rate is always higher than APR or Nominal Rate when the compounding periods per year
1)                      are greater than 1. Why? Because you are earning interest on interest.

       =EFFECT(nominal_rate means APR or Nominal Rate (APR = NOMINAL RATE = period interest rate *
2)     number of compounding periods in 1 year), npery means "number of compounding periods in 1 year")
       Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the wallet is
3)                                                    positive.
      For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender the PV is
4)                             negative, the PMT is positive, and the FV is positive.

   Annual Interest Rate =
 1 APR = Nominal Rate                                 0.085      check
   Period Rate                                                              100 Amount put in bank on day 1
   Periods per Year                                       12                    =FV(C7,C8,,-E7)
   Effective Annual Rate                                                        =(1+C7)^C8*E7
   Effective Annual Rate                                                        =E7*(1+C10)
     Effective Interest Rate is always higher than APR or Nominal Rate when the compounding periods per year
1)                      are greater than 1. Why? Because you are earning interest on interest.

       =EFFECT(nominal_rate means APR or Nominal Rate (APR = NOMINAL RATE = period interest rate *
2)     number of compounding periods in 1 year), npery means "number of compounding periods in 1 year")
       Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the wallet is
3)                                                    positive.
      For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender the PV is
4)                             negative, the PMT is positive, and the FV is positive.

   Annual Interest Rate =
 1 APR = Nominal Rate                               0.085        check
   Period Rate                                  0.007083                   100   Amount put in bank on day 1
   Periods per Year                                    12          108.8390906   =FV(C7,C8,,-E7)
   Effective Annual Rate         0.08839090589263550000            108.8390906   =(1+C7)^C8*E7
   Effective Annual Rate         0.08839090589263550000            108.8390906   =E7*(1+C10)
        APR = Annual Percentage Rate = NOMINAL RATE = period interest rate * number of
1)          compounding periods in 1 year. This is the rate usually calculated by the bank.
     Effective Interest Rate is always higher than APR or Nominal Rate when the compounding
    periods per year are greater than . Why? Because you are earning interest on interest - The
   Effective Interest Rate tells you in percentage terms what the rate really is (You could multiply it
2)                            plus 1 by the principal and get Future Value)

3)      =EFFECT(APR or Nominal Rate, npery means "number of compounding periods in 1 year")

4) =NOMINAL(Effective Interest Rate), npery means "number of compounding periods in 1 year")
   =FV(rate = period rate, nper = total number of periods, pmt means periodic payment, pv means
     amount invested or lent out today, type refers to the PMT: PMT at end of period = 0, PMT at
5)                                      beginning of period = 1)
    Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the
6)                                          wallet is positive.
   For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender
7)                  the PV is negative, the PMT is positive, and the FV is positive.
   Be consistent with your unit of time! If you are calculating monthly payment, you need monthly
    interest rate and total number of months! (The period can be monthly, quarterly, yearly or any
8)                                            other length).

    Savings Plan that compounds interest 365 times a year, but you put money in 12
1                                   times a year.
  Monthly PMT =                                        -250
  x = years                                              25
  n for account is =                                    365
  APR = i =                                            0.08
  Type= 0 or 1 ==>                                        0
                                                                                            check
     Solve for EAR first =                                                                  0.0832776
     n for PMT =                                              12
     Then from EAR, find APR (i) ==>
     Then from APR (i), find period Rate ==>
     Solve for Future Value =




2                                   MoneyTreeLoaning will:
       Allow you to write a check that has a date 25 days in the future for $250 and will
                      give you $200 today (they cash check in 25 days).
                                   What is the APR and EAR?
     Days in Future =                                         25
     Check Amount =                                          250               FV
     You get Today =                                         200               PV
     25 day rate is =
     Days in Year =                                          365
     # of 25 day periods in 1 year =
     APR =
EAR =                                      <== correct because math formula does not truncate to an inte

EAR =                                      <== Incorrect because the EFFECT function truncates npery to a


Excel Help:
 Npery is truncated to an integer.
 If either argument is nonnumeric, EFFECT returns the #VALUE! error value.
 If nominal_rate ≤ 0 or if npery < 1, EFFECT returns the #NUM! error value.
 EFFECT is calculated as follows:
oes not truncate to an integer

nction truncates npery to an integer
        APR = Annual Percentage Rate = NOMINAL RATE = period interest rate * number of
1)          compounding periods in 1 year. This is the rate usually calculated by the bank.
     Effective Interest Rate is always higher than APR or Nominal Rate when the compounding
    periods per year are greater than . Why? Because you are earning interest on interest - The
   Effective Interest Rate tells you in percentage terms what the rate really is (You could multiply it
2)                            plus 1 by the principal and get Future Value)

3)      =EFFECT(APR or Nominal Rate, npery means "number of compounding periods in 1 year")

4) =NOMINAL(Effective Interest Rate), npery means "number of compounding periods in 1 year")
   =FV(rate = period rate, nper = total number of periods, pmt means periodic payment, pv means
     amount invested or lent out today, type refers to the PMT: PMT at end of period = 0, PMT at
5)                                      beginning of period = 1)
    Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the
6)                                          wallet is positive.
   For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender
7)                  the PV is negative, the PMT is positive, and the FV is positive.
   Be consistent with your unit of time! If you are calculating monthly payment, you need monthly
    interest rate and total number of months! (The period can be monthly, quarterly, yearly or any
8)                                            other length).

    Savings Plan that compounds interest 365 times a year, but you put money in 12
1                                   times a year.
  Monthly PMT =                                        -250
  x = years                                              25
  n for account is =                                    365
  APR = i =                                            0.08
  Type= 0 or 1 ==>                                        0
                                                                                            check
     Solve for EAR first =                        0.083277572                               0.0832776
     n for PMT =                                            12
     Then from EAR, find APR (i) ==>              0.080258436
     Then from APR (i), find period Rate ==>      0.006688203
     Solve for Future Value =                     $238,757.59


       If we have a Savings Plan that compounds interest 365 times a year, but we put
           $250.00 in only 12 times a year, the Future Value would be $238,757.59.


2                                 MoneyTreeLoaning will:
      Allow you to write a check that has a date 25 days in the future for $250 and will
                      give you $200 today (they cash check in 25 days).
                                 What is the APR and EAR?
     Days in Future =                                        25
     Check Amount =                                         250               FV
     You get Today =                                        200               PV
     25 day rate is =                                      0.25
     Days in Year =                                         365
# of 25 day periods in 1 year =        14.60
APR =                                365.00%
EAR =                               2499.48% <== correct because math formula does not truncate to an inte

EAR =                                #REF!    <== Incorrect because the EFFECT function truncates npery to a


Excel Help:
  Npery is truncated to an integer.
  If either argument is nonnumeric, EFFECT returns the #VALUE! error value.
  If nominal_rate ≤ 0 or if npery < 1, EFFECT returns the #NUM! error value.
  EFFECT is calculated as follows:
oes not truncate to an integer

nction truncates npery to an integer
      NPER function calculates the = total number of periods = Years*Number of
        compounding periods per year. Example 30 year loan compounded 12
1)                   times a year ==> 12*30 = 360 = Total periods.
          =NPER(rate = period Rate, pmt means periodic payment, pv means
       amount invested or lent out today, fv means amount received after all the
        periods have elapsed or amount paid after all the periods have elapsed,
     type refers to the pmt: pmt at end of period = 0, pmt at beginning of period =
2)                                           1)
      Be consistent with your unit of time! If you are calculating monthly payment,
     you need monthly interest rate and total number of months! (The period can
3)                  be monthly, quarterly, yearly or any other length).
        Cash Flow matters in Finance. Cash going out of the wallet is negative.
4)                       Cash coming into the wallet is positive.
         For the borrow the PV is positive, the PMT is negative, and the FV is
     negative. For the Lender the PV is negative, the PMT is positive, and the FV
5)                                      is positive.


       How long to pay off your credit Card if you pay only the
                     minimum PMT required?
     Balance = PV =                  2,000.00
     APR = i =                         18.00%               Type
     n=                                     12                       0
     Minimum Monthly PMT =               41.00
     n*x = NPER function =
     x = n*x/n = years
     Words:
      NPER function calculates the = total number of periods = Years*Number of
        compounding periods per year. Example 30 year loan compounded 12
1)                   times a year ==> 12*30 = 360 = Total periods.
          =NPER(rate = period Rate, pmt means periodic payment, pv means
       amount invested or lent out today, fv means amount received after all the
        periods have elapsed or amount paid after all the periods have elapsed,
     type refers to the pmt: pmt at end of period = 0, pmt at beginning of period =
2)                                           1)
      Be consistent with your unit of time! If you are calculating monthly payment,
     you need monthly interest rate and total number of months! (The period can
3)                  be monthly, quarterly, yearly or any other length).
        Cash Flow matters in Finance. Cash going out of the wallet is negative.
4)                       Cash coming into the wallet is positive.
         For the borrow the PV is positive, the PMT is negative, and the FV is
     negative. For the Lender the PV is negative, the PMT is positive, and the FV
5)                                      is positive.


       How long to pay off your credit Card if you pay only the
                     minimum PMT required?
     Balance = PV =                   2,000.00
     APR = i =                          18.00%             Type
     n=                                       12                    0
     Minimum Monthly PMT =                 41.00
     n*x = NPER function =          88.367992
     x = n*x/n = years              7.3639994
                                   It will take 7.36399935665702 years to pay off the credit
                                       card if we make only the minimum payment each
     Words:                                                 period.
                                            260aa84a-bb10-4c7e-8b55-cbd93bfe6d73.xls - Loan Analysis


1)                                                PMT means periodic payment (same amount each period)
     PMT function calculates the period payment for a loan (For the Borrower or the Lender). The Amount of each PMT must be the same and the
2)                                                       time between each PMT must be the same.
3)                      Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the wallet is positive.
     For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender the PV is negative, the PMT is positive, and the
4)                                                                       FV is positive.
      Be consistent with your unit of time! If you are calculating monthly payment, you need monthly interest rate and total number of months! (The
5)                                               period can be monthly, quarterly, yearly or any other length).
       =PMT(rate = period rate, nper = total number of periods, pv means amount invested or lent out today, fv means amount received after all the
     periods have elapsed or amount paid after all the periods have elapsed, type refers to the PMT: PMT at end of period = 0, PMT at beginning of
6)                                                                        period = 1)
      =RATE(nper = total number of periods, pmt means periodic payment, pv means amount invested or lent out today, fv means amount received
        after all the periods have elapsed or amount paid after all the periods have elapsed, type refers to the pmt: pmt at end of period = 0, pmt at
7)                                                                 beginning of period = 1)

                                                               Loan Comparison
             % Down                                                 Amount to       Monthly       Actual Cash       Adjusted
     Option# Payment       APR   Years Points Extra Fee              Borrow         Payment        Received           APR          PMT w Balloon
           1     5.0%      8.50%     30     1       400
           2    15.0%      8.25%     30     3       400
           3     4.5%      8.90%     30     1       450
           4    12.0%      9.00%     30     2       100
           5    15.0%      8.50%     30     2       125
           6    20.0%      8.00%     15     0       500
           7    15.0%      7.60%     15     1       750

                      Price                             430,000
           Compounding Periods per year                     12
            Balloon Payment (Optional)                  -50,000




                                                                    Page 23 of 32
                                         260aa84a-bb10-4c7e-8b55-cbd93bfe6d73.xls - Loan Analysis (an)


1)                                                PMT means periodic payment (same amount each period)
     PMT function calculates the period payment for a loan (For the Borrower or the Lender). The Amount of each PMT must be the same and the
2)                                                       time between each PMT must be the same.
3)                      Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash coming into the wallet is positive.
     For the borrow the PV is positive, the PMT is negative, and the FV is negative. For the Lender the PV is negative, the PMT is positive, and the
4)                                                                       FV is positive.
      Be consistent with your unit of time! If you are calculating monthly payment, you need monthly interest rate and total number of months! (The
5)                                               period can be monthly, quarterly, yearly or any other length).
       =PMT(rate = period rate, nper = total number of periods, pv means amount invested or lent out today, fv means amount received after all the
     periods have elapsed or amount paid after all the periods have elapsed, type refers to the PMT: PMT at end of period = 0, PMT at beginning of
6)                                                                        period = 1)
      =RATE(nper = total number of periods, pmt means periodic payment, pv means amount invested or lent out today, fv means amount received
        after all the periods have elapsed or amount paid after all the periods have elapsed, type refers to the pmt: pmt at end of period = 0, pmt at
7)                                                                 beginning of period = 1)

                                                               Loan Comparison
             % Down                                                 Amount to       Monthly       Actual Cash       Adjusted
     Option# Payment       APR   Years Points Extra Fee              Borrow         Payment        Received           APR          PMT w Balloon
           1     5.0%      8.50%     30     1       400              408,500.00      -3,141.01       404,015.00      8.620%               -3,110.72
           2    15.0%      8.25%     30     3       400              365,500.00      -2,745.88       354,135.00      8.591%               -2,714.00
           3     4.5%      8.90%     30     1       450              410,650.00      -3,274.68       406,093.50      9.024%               -3,246.79
           4    12.0%      9.00%     30     2       100              378,400.00      -3,044.69       370,732.00      9.231%               -3,017.38
           5    15.0%      8.50%     30     2       125              365,500.00      -2,810.38       358,065.00      8.724%               -2,780.09
           6    20.0%      8.00%     15     0       500              344,000.00      -3,287.44       343,500.00      8.024%               -3,142.95
           7    15.0%      7.60%     15     1       750              365,500.00      -3,409.03       361,095.00      7.799%               -3,259.35

                      Price                             430,000
           Compounding Periods per year                     12
            Balloon Payment (Optional)                  -50,000




                                                                    Page 24 of 32
        Preset Value = How much future cash flows are worth
       today. Think of it as interest going backwards; if we put
       money in the bank today (present value) it will be worth
      some future value amount in the future: Present Value is
      the Opposite! We want to receive some cash amounts in
        the future, what amount do we have to put in the bank
1)                               today?

    PV function calculates present Value when the cash flows
    are the same and are separated by regular time periods.
    NPV function lets us calculate the present value when the
   amounts are not the same. XNPV function lets us calculate
   the present value when the amounts are not the same and
2)                 the times are not the same.
                                                                     When an asset has an annuity cash flow pattern, you can use
                                                                       Capital Investment Decision. An annuity has equal paymen
3)                    =PV( rate, nper, fv, type)                                                       intervals.

    =NPV( rate, CF1, CF2…. (as range or cells separated by           Calculates the net present value for a series of cash flows tha
4) commas). NOTE: You cannot include Cash Flow at time 0.                                      time between each cash flow)
                                                                     Returns the net present value for a schedule of cash flows tha
      =XNPV(rate, values, dates) NOTE: you can include Cash            periodic. To calculate the net present value for a series of c
5)                        Flow at time 0.                                                     periodic, use the NPV function.

   Cash Flow matters in Finance. Cash going out of the wallet
6)     is negative. Cash coming into the wallet is positive.
   For the borrow the PV is positive, the PMT is negative, and
    the FV is negative. For the Lender the PV is negative, the
7)            PMT is positive, and the FV is positive.
    Be consistent with your unit of time! If you are calculating
   monthly payment, you need monthly interest rate and total
   number of months! (The period can be monthly, quarterly,
8)                  yearly or any other length).



        You are considering buying a machine that will yield
      $35,000.00 net cash flow in for the next ten years. If you
       must earn a minimum return on investment of 15.00%,
 1      should we buy a machine if it costs ($165,500.00)?

     Net Cash Flow at end of each year =                 35,000.00
     Min Return (hurdle rate or Discount Rate) =              15%
     Cost =                                            -165,500.00
     Years                                                      10
     PV =
     Difference =
     NPV =                                                                   NPV =

 2                      Period                            CF                     CF
                                                   0                         -165,500.00
                                                   1    35,000.00              40,000.00
              2      35,000.00            40,000.00
              3      35,000.00            40,000.00
              4      35,000.00            35,000.00
              5      35,000.00            35,000.00
              6      35,000.00            35,000.00
              7      35,000.00            20,000.00
              8      35,000.00            10,000.00
              9      35,000.00            50,000.00
             10      35,000.00             2,500.00

3 RRR                     0.15

 Date              Year          CF      PV function XNPV algorithm XNPV
        1/1/2007            0    -500.00     -500.00       -500.00
        1/1/2008            1     200.00      173.91        173.91
        1/1/2010            3     100.00       65.75         65.73
        1/1/2011            4     100.00       57.18         57.15
        1/1/2012            5     100.00       49.72         49.70
        1/1/2013            6     100.00       43.23         43.20
                                             -110.21       -110.31

        1/1/2009
             366
h flow pattern, you can use the PV function for
n annuity has equal payments at equal time
  intervals.

 r a series of cash flows that is periodic (equal
 een each cash flow)
 schedule of cash flows that is not necessarily
esent value for a series of cash flows that is
use the NPV function.
        Preset Value = How much future cash flows are worth
       today. Think of it as interest going backwards; if we put
       money in the bank today (present value) it will be worth
      some future value amount in the future: Present Value is
      the Opposite! We want to receive some cash amounts in
        the future, what amount do we have to put in the bank
1)                               today?

    PV function calculates present Value when the cash flows
    are the same and are separated by regular time periods.
    NPV function lets us calculate the present value when the
   amounts are not the same. XNPV function lets us calculate
   the present value when the amounts are not the same and
2)                 the times are not the same.
                                                                     When an asset has an annuity cash flow pattern, you can use
                                                                       Capital Investment Decision. An annuity has equal paymen
3)                    =PV( rate, nper, fv, type)                                                       intervals.

    =NPV( rate, CF1, CF2…. (as range or cells separated by           Calculates the net present value for a series of cash flows tha
4) commas). NOTE: You cannot include Cash Flow at time 0.                                      time between each cash flow)
                                                                     Returns the net present value for a schedule of cash flows tha
      =XNPV(rate, values, dates) NOTE: you can include Cash            periodic. To calculate the net present value for a series of c
5)                        Flow at time 0.                                                     periodic, use the NPV function.

   Cash Flow matters in Finance. Cash going out of the wallet
6)     is negative. Cash coming into the wallet is positive.
   For the borrow the PV is positive, the PMT is negative, and
    the FV is negative. For the Lender the PV is negative, the
7)            PMT is positive, and the FV is positive.
    Be consistent with your unit of time! If you are calculating
   monthly payment, you need monthly interest rate and total
   number of months! (The period can be monthly, quarterly,
8)                  yearly or any other length).



        You are considering buying a machine that will yield
      $35,000.00 net cash flow in for the next ten years. If you
       must earn a minimum return on investment of 15.00%,
 1      should we buy a machine if it costs ($165,500.00)?

     Net Cash Flow at end of each year =                 35,000.00
     Min Return (hurdle rate or Discount Rate) =              15%
     Cost =                                            -165,500.00
     Years                                                      10
     PV =                                              -175,656.90
     Difference =                                        10,156.90
     NPV =                                               10,156.90           NPV =                3,991.86

 2                      Period                            CF                     CF
                                                   0                         -165,500.00
                                                   1    35,000.00              40,000.00
              2      35,000.00            40,000.00
              3      35,000.00            40,000.00
              4      35,000.00            35,000.00
              5      35,000.00            35,000.00
              6      35,000.00            35,000.00
              7      35,000.00            20,000.00
              8      35,000.00            10,000.00
              9      35,000.00            50,000.00
             10      35,000.00             2,500.00

3 RRR                     0.15

 Date              Year          CF      PV function XNPV algorithm XNPV
        1/1/2007            0    -500.00     -500.00       -500.00 #NAME?
        1/1/2008            1     200.00      173.91        173.91
        1/1/2010            3     100.00       65.75         65.73
        1/1/2011            4     100.00       57.18         57.15
        1/1/2012            5     100.00       49.72         49.70
        1/1/2013            6     100.00       43.23         43.20
                                             -110.21       -110.31

        1/1/2009
             366
h flow pattern, you can use the PV function for
n annuity has equal payments at equal time
  intervals.

 r a series of cash flows that is periodic (equal
 een each cash flow)
 schedule of cash flows that is not necessarily
esent value for a series of cash flows that is
use the NPV function.
         FV function calculates the future value of a lump sum (invested at very
        beginning) or regular payments (called PMT and amount is the same for
1)                 each period and the amount is always the same).
2)             PMT means periodic payment (same amount each period)
      PMT function calculates the period payment for a loan (For the Borrower or
         the Lender). The Amount of each PMT must be the same and the time
3)                       between each PMT must be the same.
     Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash
     coming into the wallet is positive. Time period Matters in Finance: Example -
      if you are making monthly payments, then total number of periods must be
4)                               total number of months.
          For the borrow the PV is positive, the PMT is negative, and the FV is
     negative. For the Lender the PV is negative, the PMT is positive, and the FV
5)                                      is positive.

      =FV(rate = period rate, nper = total number of periods, pmt means periodic
       payment, pv means amount invested or lent out today, type refers to the
6)        PMT: PMT at end of period = 0, PMT at beginning of period = 1)

      =PMT(rate = period rate, nper = total number of periods, pv means amount
       invested or lent out today, fv means amount received after all the periods
      have elapsed or amount paid after all the periods have elapsed, type refers
7)       to the PMT: PMT at end of period = 0, PMT at beginning of period = 1)

     Monthly PMT (end)                -100.00 Amount put in
     years                                 30 Amount taken out
     Months                                   Interest
     Annual Rate                          9%                   .
     Monthly Rate
     FV

     PV
     years                                 35
     Months
     Annual Rate                       7.00%
     Monthly Rate
     Monthly PMT (begin)
     Leave to Kids                250,000.00
         FV function calculates the future value of a lump sum (invested at very
        beginning) or regular payments (called PMT and amount is the same for
1)                 each period and the amount is always the same).
2)             PMT means periodic payment (same amount each period)
      PMT function calculates the period payment for a loan (For the Borrower or
         the Lender). The Amount of each PMT must be the same and the time
3)                       between each PMT must be the same.
     Cash Flow matters in Finance. Cash going out of the wallet is negative. Cash
     coming into the wallet is positive. Time period Matters in Finance: Example -
      if you are making monthly payments, then total number of periods must be
4)                               total number of months.
          For the borrow the PV is positive, the PMT is negative, and the FV is
     negative. For the Lender the PV is negative, the PMT is positive, and the FV
5)                                      is positive.

      =FV(rate = period rate, nper = total number of periods, pmt means periodic
       payment, pv means amount invested or lent out today, type refers to the
6)        PMT: PMT at end of period = 0, PMT at beginning of period = 1)

      =PMT(rate = period rate, nper = total number of periods, pv means amount
       invested or lent out today, fv means amount received after all the periods
      have elapsed or amount paid after all the periods have elapsed, type refers
7)       to the PMT: PMT at end of period = 0, PMT at beginning of period = 1)

     Monthly PMT (end)               -100.00 Amount put in             36,000.00
     years                                30 Amount taken out         680,414.56
     Months                              360 Interest                 644,414.56
     Annual Rate                         9%                   .
     Monthly Rate                    0.750%
     FV                           183,074.35

     PV                          -183,074.35
     years                                 35
     Months                               420
     Annual Rate                       7.00%
     Monthly Rate                   0.5833%
     Monthly PMT (begin)            1,024.80
     Leave to Kids                250,000.00

				
DOCUMENT INFO
Description: Formula for Calculating Interest Payments document sample