Excel Loan Amortization Schedule with Additional Principle by lxn21430

VIEWS: 79 PAGES: 23

• pg 1
```									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
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