Excel Loan Amortization Schedule with Additional Principle by lxn21430

VIEWS: 79 PAGES: 23

More Info
									Chapter 6 Homework
   Pg. 268 ST-2; ST-4
    Pg. 270 6-6; 6-10
      Pg. 271 6-12
      Pg. 274 6-25
             Pg. 268 ST-2
In the introduction to this chapter we asked
whether you would prefer to invest $5,500 today
and receive either $7,020 in five years or $8,126
in eight years. You should now be able to
determine which investment alternative is better.
 a. Based only on the return you would earn from each
    investment, which is better?
 b. Can you think of any factors other than the
    expected return that might be important to consider
    when choosing between the two investment
    alternatives?
                      Pg. 268 ST-2a
           0      1     2         3    4     5      6     7         8

Option A -5,500                            7, 020
Option B -5,500                                                   8,126
    FVn = PV(1+i)n

    Option A                          Option B
    7,020         = 5,500(1+i)5       8,126       = 5,500(1+i)8
    7,020/5,500   = (1+i)5            8,126/5,500 = (1+i)8
    1.2764        = (1+i)5            1.4775      = FVIFi,8
    1.27641/5     = (1+i)                    Table Lookup
    1.0500        = (1+i)             1.4775      = FVIF5%,8
    i             = 5%
             Pg. 268 ST-2b
Other Factors to Consider:
  – Risk of 8-year investment vs. 5-year
  – Timing
  – Rate Expectations
                   Pg. 268 ST-4
      Assume that it is now January 1, 2000,
      and you will need $1,000 on January 1,
      2004. Your bank compounds interest at
      an eight percent annual rate.
‘00     8%   ‘01        ‘02       ‘03      ‘04

                                         1,000
‘00          ‘01
                 Pg. 268 ST-4 ‘03
                       ‘02                         ‘04
      8%
           PV=?                                   1,000

 a.   How much must you deposit on January 1, 2001, to
      have a balance of $1,000 on January 1, 2004?

                     PV=FV[1/(1+i)n]

      PV    = 1,000[1/(1+0.08)3]
      PV    = 1,000[0.7938]
      PV    = 793.83
‘00          ‘01
                 Pg. 268 ST-4 ‘03
                       ‘02                         ‘04
      8%
            PMT         PMT          PMT           PMT
                                                  1,000
 b. If you want to make equal payments to each January
    1 from 2001 through 2004 to accumulate the $1,000,
    how large must each of the four payments be?

                  FVAn = PMT(FVIFAi,n)

      1,000        = PMT(4.5061)
      1,000/4.5061 = 221.92
‘00             ‘01
                    Pg. 268 ST-4 ‘03
                          ‘02                                     ‘04
       8%
                750                                               FV
 c.   If your father were to offer either to make the payments
      calculated in part (b) ($221.92) or to give you a lump sum of
      $750 on January 1, 2001, which would you choose?



                 Input:    3     8   -750    0
                          N I/Y PV          PMT    FV
               Output:                            944.78

      You should take the payments of $221.92 (FV = $1,000) instead
      of the $750 on 1/1/01 which will only be worth $944.78.
‘00           ‘01
                  Pg. 268 ST-4 ‘03
                        ‘02                         ‘04
      ?%
              750                                 1,000

 d. If you have on $750 on January 1, 2001, what interest
    rate, compounded annually, would you have to earn
    to have the necessary $1,000 on January 1, 2004?
                     FV = PV(1+i)n
      1,000       = 750(1+i)3
      1,000/750   = (1+i)3
      1.33331/3   = (1+i)
      1.1006      = (1+i)
      i           =10.06%
‘00          ‘01
                 Pg. 268 ST-4 ‘03
                       ‘02                           ‘04
      ?%
           -186.29        -186.29        -186.29    -186.29
                                                   1,000.00
 e.   Suppose you can deposit $186.29 on each January 1
      from 2001 through 2004, but you still need $1,000 on
      January 1, 2004. What interest rate, with annual
      compounding, must you seek out to achieve your
      goal? Input:                       1,000
                      4            0 -186.29
                      N I/Y       PV PMT       FV
            Output:       19.99
‘00            ‘01
                   Pg. 268 ST-4 ‘03
                         ‘02                                       ‘04
      8%
               -400    PMT      PMT        PMT      PMT      PMT   PMT
                                                                   1,000
 f.   To help you reach your $1,000 goal, your father offers to give
      you $400 on January 1, 2001. You will get a part-time job and
      make six additional payment of equal amounts each six months
      thereafter. If all of this money is deposited in a bank that pays
      eight percent, compounded semiannually, how large must each
      of the six payments be?
      Part 1 Input:       6     4     400    0
                         N I/Y PV           PMT      FV
            Output:                                 506.13
      Part 2 Input:       6     4      0            493.87
                         N I/Y PV           PMT      FV
              Output:                       74.46
‘00            ‘01
                   Pg. 268 ST-4 ‘03
                         ‘02                         ‘04
      8%



 g. What is the effective annual rate being paid
    by the bank in part f?
 Effective Annual Rate = EAR = [1+(isimple/m)]m-1
      Where: m = # of compounding periods per year

      EAR          = [1+(.08/2)]2-1
      EAR          = 8.16%
               Pg. 270 6-6
Find the present values of the following cash
flow streams under the following conditions:
Year         Cash Stream A      Cash Stream B
  1             $100               $300
  2              400                400
  3              400                400
  4              400                400
  5              500                100
                    Pg. 270 6-6
a. i = 8%
 Stream A:                      Stream B:
  8%    Rate
                                 CF
 Year    FV PVIFi,n    PV       CFo=            0.00
  1     $100 0.9259    $92.59   C01=          300.00
  2     $400 0.8573   $342.94   F01=            1.00
  3     $400 0.7938   $317.53   C02=          400.00
  4     $400 0.7350   $294.01   F02=            3.00
  5     $300 0.6806   $204.17   C03=          100.00
                                F03=            1.00
             PVA    $1,251.25
                                 CPT   NPV
                                I =              8.00
                                NPV CPT      1,300.32
                    Pg. 270 6-6
b. i = 0%
 Stream A:                      Stream B:
  0%    Rate
                                 CF
 Year    FV PVIFi,n    PV       CFo=            0.00
  1     $100 1.0000   $100.00   C01=          300.00
  2     $400 1.0000   $400.00   F01=            1.00
  3     $400 1.0000   $400.00   C02=          400.00
  4     $400 1.0000   $400.00   F02=            3.00
  5     $300 1.0000   $300.00   C03=          100.00
                                F03=            1.00
             PVA    $1,600.00
                                 CPT   NPV
                                I =              0.00
                                NPV CPT      1,600.00
                Pg. 270 6-10a
Find the future values of the following
ordinary annuities:
  a.   FV of $400 each six months for five years at a simple
       rate of 12 percent, compounded semiannually.

               Input:   10   6   0   -400
                        N I/Y PV     PMT    FV
             Output:                    5,272.32
                Pg. 270 6-10b
Find the future values of the following ordinary
annuities:
   b. FV of $200 each three months for five years at a
      simple rate of 12 percent, compounded quarterly.

               Input:   20   3   0   -200
                        N I/Y PV     PMT    FV
             Output:                    5,374.07
                 Pg. 270 6-10c
Find the future values of the following ordinary
annuities:
   c. The annuities described in parts (a) and (b) have the
      same amount of money paid into them during the five-
      year period and both earn interest at the same simple
      rate, yet the annuity in part (b) ears $101.76 more than
      the one in part (a) over the five years. Why does this
      occur?

      The first payment is made 3 months earlier in (b) than
      in (a) and the effect of more frequent compounding
      both contribute to the higher earnings.
                  Pg. 271 6-12
Lorkay Seidens Inc. just borrowed $25,000. The
loan is to be repaid in equal installments at the end of
each of the next five years, and the interest rate is ten
percent.
   a. Set up an amortization schedule for the loan.
   b. How large must each annual payment be if the loan is
      for $50,000? Assume that the interest rate remains at
      ten percent and that the loan is paid off over five
      years.
                   Pg. 271 6-12a
        a. 10% Interest                      Loan Amount  25,000
           Year Payment        Interest       Principal  Balance
            1    ($6,594.94)   ($2,500.00)      ($4,094.94)   $20,905.06
            2    ($6,594.94)   ($2,090.51)      ($4,504.43)   $16,400.63
            3    ($6,594.94)   ($1,640.06)      ($4,954.87)   $11,445.76
            4    ($6,594.94)   ($1,144.58)      ($5,450.36)    $5,995.40
            5    ($6,594.94)     ($599.54)      ($5,995.40)        $0.00
                 ($32,974.69) ($7,974.69)      ($25,000.00)
In Excel:
   Payment = PMT(rate,Nper,PV,FV,Type)
   Interest = IPMT(rate,Per,Nper,PV,FV)
   Principle = PPMT(rate,Per,Nper,PV,FV,Type)
   Balance = Loan Amount -Principal
                   Pg. 271 6-12b
        a. 10% Interest                      Loan Amount  50,000
           Year Payment        Interest       Principal  Balance
            1   ($13,189.87)   ($5,000.00)      ($8,189.87)   $41,810.13
            2   ($13,189.87)   ($4,181.01)      ($9,008.86)   $32,801.26
            3   ($13,189.87)   ($3,280.13)      ($9,909.75)   $22,891.52
            4   ($13,189.87)   ($2,289.15)     ($10,900.72)   $11,990.79
            5   ($13,189.87)   ($1,199.08)     ($11,990.79)        $0.00
                 ($65,949.37) ($15,949.37)     ($50,000.00)

In Excel:
   Payment = PMT(rate,Nper,PV,FV,Type)
   Interest = IPMT(rate,Per,Nper,PV,FV)
   Principle = PPMT(rate,Per,Nper,PV,FV,Type)
   Balance = Loan Amount -Principal
                 Pg. 271 6-12c
c.   How large must each payment be if the loan is for $50,000, the
     interest rate is ten percent, and the loan is paid off in equal
     installments at the end of each of the next ten years? This loan
     is for the same amount as the loan in part (b), but the payments
     are spread out over twice as many periods. Why are these
     payments not half as large as the payments on the loan in part
     (b)?
               Input:    10    10 50,000          0
                         N I/Y PV          PMT   FV
              Output:                      8,137.27

     Because the payments are spread out over a longer period of
     time, more interest must be paid. The total interest paid on the
     10-year loan is $31,373, while the total for the 5-year loan is
     $15,949, however the same principal ($50,000) is repaid over a
     longer period of time so that the total payment per year is not
     doubled.
                       Pg. 274 6-25
While Steve Bouchard was a student at the University of Florida, he
borrowed $12,000 in student loans at an annual interest rate of nine
percent. If Steve repays $1,500 per year, how long, to the nearest year,
will it take him to repay the loan?
                       PVAn = PMT(PVIFAi,n)
                    Input:           9 12,000 1,500   0
                              N    I/Y PV PMT         FV
                  Output: 14.77

								
To top