In: Finance
The data on a loan has been collected in the Microsoft Excel
Online file below. Open...
The data on a loan has been collected in the Microsoft Excel
Online file below. Open the spreadsheet and perform the required
analysis to answer the questions below.
a. Complete an amortization schedule for a $22,000 loan to be
repaid in equal installments at the end of each of the next three
years. The interest rate is 8% compounded annually. Round all
answers to the nearest cent.
Beginning |
|
|
Repayment |
Ending |
Year |
Balance |
Payment |
Interest |
of Principal |
Balance |
1 |
$ |
$ |
$ |
$ |
$ |
2 |
$ |
$ |
$ |
$ |
$ |
3 |
$ |
$ |
$ |
$ |
$ |
b. What percentage of the payment represents interest and what
percentage represents principal for each of the three years? Round
all answers to two decimal places.
|
% Interest |
% Principal |
Year 1: |
% |
% |
Year 2: |
% |
% |
Year 3: |
% |
% |
c. Why do these percentages change over time?
- These percentages change over time because even though the
total payment is constant the amount of interest paid each year is
declining as the remaining or outstanding balance declines.
- These percentages change over time because even though the
total payment is constant the amount of interest paid each year is
increasing as the remaining or outstanding balance declines.
- These percentages change over time because even though the
total payment is constant the amount of interest paid each year is
declining as the remaining or outstanding balance increases.
- These percentages change over time because even though the
total payment is constant the amount of interest paid each year is
increasing as the remaining or outstanding balance increases.
- These percentages do not change over time; interest and
principal are each a constant percentage of the total payment.
_____IIIIIIIVV
a. Complete an amortization schedule for a $22,000 loan to be
repaid in equal installments at the end of each of the next three
years. The interest rate is 8% compounded annually. Round all
answers to the nearest cent.
|
Beginning |
|
|
Repayment |
Ending |
Year |
Balance |
Payment |
Interest |
of Principal |
Balance |
1 |
$ |
$ |
$ |
$ |
$ |
2 |
$ |
$ |
$ |
$ |
$ |
3 |
$ |
$ |
$ |
$ |
$ |
b. What percentage of the payment represents interest and what
percentage represents principal for each of the three years? Round
all answers to two decimal places.
|
% Interest |
% Principal |
Year 1: |
% |
% |
Year 2: |
% |
% |
Year 3: |
% |
% |
c. Why do these percentages change over time?
- These percentages change over time because even though the
total payment is constant the amount of interest paid each year is
declining as the remaining or outstanding balance declines.
- These percentages change over time because even though the
total payment is constant the amount of interest paid each year is
increasing as the remaining or outstanding balance declines.
- These percentages change over time because even though the
total payment is constant the amount of interest paid each year is
declining as the remaining or outstanding balance increases.
- These percentages change over time because even though the
total payment is constant the amount of interest paid each year is
increasing as the remaining or outstanding balance increases.
- These percentages do not change over time; interest and
principal are each a constant percentage of the total payment.
-
With a financial calculator, enter N = 3, I/YR = 8, PV =
-22,000, and FV = 0, and solve for PMT = $8,536.74. Then go through
the amortization procedure as described in your calculator manual
to get the entries for the amortization table.
|
Beginning |
|
|
Repayment |
Remaining |
Year |
Balance |
Payment |
Interest |
of Principal |
Balance |
1 |
$22,000.00 |
$8,536.74 |
$1,760.00 |
$6,776.74 |
$15,223.26 |
2 |
15,223.26 |
8,536.74 |
1,217.86 |
7,318.88 |
7,904.39 |
3 |
7,904.39 |
8,536.74 |
632.35 |
7,904.39 |
0.00 |
|
|
$25,610.21 |
$3,610.21 |
$22,000.00 |
|
-
|
% Interest |
% Principal |
Year 1: |
1,760.00/8,536.74 = 20.62% |
6,776.74/8,536.74 = 79.38% |
Year 2: |
1,217.86/8,536.74 = 14.27% |
7,318.88/8,536.74 = 85.73% |
Year 3: |
632.35/8,536.74 = 7.41% |
7,904.39/8,536.74 = 92.59% |
These percentages change over time because, even though the
total payment is constant, the amount of interest paid each year is
declining as the balance declines.
|