# Amortization Formula Loan Amortization Schedules LOAN AMORTIZATION SCHEDULES for Section by pza38721

VIEWS: 353 PAGES: 4

• pg 1
```									                                           Loan Amortization Schedules

LOAN AMORTIZATION SCHEDULES (for Section 14.2)

Scroll DOWN for FULL AMORTIZATION SCHEDULE.

Scroll RIGHT for PARTIAL AMORTIZATION SCHEDULE.

FULL AMORTIZATION SCHEDULE (Instructions begin in row 36.)                               PARTIAL AMORTIZATION

DATA:                                                                                    DATA:
Original principal (PV ):                                                                Original principal (PV ):
Nominal (annual) rate of interest (j ):                                                  Nominal (annual) rate of
Compoundings per year (m ):                                                              Compoundings per year
Interest rate per compounding interval (i ):                                             Interest rate per compou
Payments per year:                                                                       Payments per year:
Compoundings per payment interval (c ):                                                  Compoundings per paym
Interest rate per payment interval (i 2):                         0.000000%              Interest rate per paymen
Loan payment (PMT ):                                                                     Loan payment (PMT ):
Total number of payments (n ):                                                           Total number of paymen

Payment                      Interest       Principal            Principal               Payment
number       Payment         portion         portion             balance                 number
0           --               --             --                      \$0.00               0
1            \$0.00                                                                      1
2            \$0.00                                                                      2
3            \$0.00                                                                      |
4            \$0.00
5            \$0.00
6            \$0.00
7            \$0.00                                                                        |
8            \$0.00
9            \$0.00
10            \$0.00
11            \$0.00
12            \$0.00

GENERAL INSTRUCTIONS:                                                                    GENERAL INSTRUCTION
1. Copy the preceding DATA and amortization schedule to a new worksheet.                 1. Copy the preceding DA
2. Cells with a yellow background require the entry of appropriate raw data.             2. Cells with a yellow bac
3. Cells with a blue background require the entry of appropriate formulas.               3. Cells with a blue backg

Example 14.2B from the text is solved below to illustrate the use of this template.      Example 14.2C from the
1. Enter values for PV, j, m , Payments per year, and PMT in the yellow cells            1. Enter values for PV, j, m
of the DATA section. Enter dollar amounts without a dollar sign or a comma               DATA section. Enter do
separator. Enter interest rates in decimal equivalent form.                               interest rates in their de
2. Enter formulas in the two blue cells of the DATA section to calculate i and c . The   2. Enter formulas in the tw
The formula for calculating i 2 has already been entered in the DATA section.            formula for calculating i
3. The cells in the amortization schedule for the original principal and the periodic    3. The cells in the amortiz

Page 1
Loan Amortization Schedules

Example 14.2B                                                                           Example 14.2C
DATA:                                                                                   DATA:
Original principal (PV ):                                       \$40,000.00              Original principal (PV ):
Nominal (annual) rate of interest (j ):                            11.500%              Nominal (annual) rate of
Compoundings per year (m ):                                               2             Compoundings per year
Interest rate per compounding interval (i ):                         5.750%             Interest rate per compou
Payments per year:                                                        1             Payments per year:
Compoundings per payment interval (c ):                             2.00000             Compoundings per paym
Interest rate per payment interval (i 2):                       11.830625%              Interest rate per paymen
Loan payment (PMT ):                                             \$8,717.69              Loan payment (PMT ):
Total number of payments (n ):                                     7.000000             Total number of paymen

Payment                       Interest       Principal          Principal               Payment
number         Payment        portion         portion           balance                 number
0              --              --             --               \$40,000.00                0
1           \$8,717.69       \$4,732.25       \$3,985.44          \$36,014.56                1
2           \$8,717.69       \$4,260.75       \$4,456.94          \$31,557.62                2
3           \$8,717.69       \$3,733.46       \$4,984.23          \$26,573.39                |
4           \$8,717.69       \$3,143.80       \$5,573.89          \$20,999.50               26
5           \$8,717.69       \$2,484.37       \$6,233.32          \$14,766.18               27
6           \$8,717.69       \$1,746.93       \$6,970.76            \$7,795.42              28
7           \$8,717.67         \$922.25       \$7,795.42                \$0.00               |
54
4. Enter a formula in C65 to calculate the interest for one payment interval on the        55
balance in E64. In preparation for copying the formula down the column, the             56
absolute reference must be used for the location of i 2 (that is, E\$58). (Ideally,
you should use the ROUND function to round the interest to the nearest cent.)      4. Enter the serial number
5.    Enter a formula in D65 to calculate the principal portion of the payment.            the yellow cells of the P
6.    Enter a formula in E65 to calculate the new balance.                              5. Calculate and enter the
7.    Copy the three formulas in C65, D65, and E65 down their respective columns.          balance column.
8.    When the last regular payment would result in a negative balance (as in B71),     5. Enter a formula in J65 t
this final payment cell must be programmed to equal the preceding                    balance in L64. In prep
balance plus the current period's interest.                                          absolute reference mus
you should use the ROU
6. Enter a formula in K65
7. Enter a formula in L65 t
8. Copy the three formulas
respective columns.
9. When the last regular p
this final payment cell m
balance plus the curren

Page 2
Loan Amortization Schedules

PARTIAL AMORTIZATION SCHEDULE (Instructions begin in row 36.)

Original principal (PV ):
Nominal (annual) rate of interest (j ):
Compoundings per year (m ):
Interest rate per compounding interval (i ):
Payments per year:
Compoundings per payment interval (c ):
Interest rate per payment interval (i 2):                        0.000000%
Loan payment (PMT ):
Total number of payments (n ):

Interest       Principal          Principal
Payment         portion         portion           balance
--               --             --                    \$0.00
\$0.00
\$0.00
|                |               |                 |
--               --              --
\$0.00
\$0.00
|                |               |                 |
--               --              --
\$0.00
\$0.00

GENERAL INSTRUCTIONS:
1. Copy the preceding DATA and amortization schedule to a new worksheet.
2. Cells with a yellow background require the entry of appropriate raw data.
3. Cells with a blue background require the entry of appropriate formulas.

Example 14.2C from the text is solved below to illustrate the use of this template.
1. Enter values for PV, j, m , Payments per year, n , and PMT in the yellow cells of the
DATA section. Enter dollar amounts without a dollar sign or a comma separator. Enter
interest rates in their decimal equivalent.
2. Enter formulas in the two blue cells of the DATA section to calculate i and c . The
formula for calculating i 2 has already been entered in the DATA section.
3. The cells in the amortization schedule for the original principal and the periodic

Page 3
Loan Amortization Schedules

payment are already linked to the appropriate cell in the DATA section.

Example 14.2C

Original principal (PV ):                                       \$11,000.00
Nominal (annual) rate of interest (j ):                            10.500%
Compoundings per year (m ):                                               12
Interest rate per compounding interval (i ):                         0.875%
Payments per year:                                                        12
Compoundings per payment interval (c ):                             1.00000
Interest rate per payment interval (i 2):                        0.875000%
Loan payment (PMT ):                                               \$250.00
Total number of payments (n ):                                    55.800772

Interest        Principal         Principal
Payment         portion          portion          balance
--               --              --              \$11,000.00
\$250.00           \$96.25         \$153.75          \$10,846.25
\$250.00           \$94.90         \$155.10          \$10,691.15
|                |               |                |
--               --              --               \$6,533.07
\$250.00           \$57.16         \$192.84           \$6,340.23
\$250.00           \$55.48         \$194.52           \$6,145.71
|                |               |                |
--               --              --                  \$444.74
\$250.00              \$3.89       \$246.11              \$198.63
\$200.37              \$1.74       \$198.63                \$0.00

4. Enter the serial numbers for the desired intermediate and ending payments in
the yellow cells of the Payment number column.
5. Calculate and enter the two (yellow) intermediate balances in the Principal
balance column.
5. Enter a formula in J65 to calculate the interest for one payment interval on the
balance in L64. In preparation for copying the formula down the column, the
absolute reference must be used for the location of i 2 (that is, L\$58). (Ideally,
you should use the ROUND function to round the interest to the nearest cent.)
6. Enter a formula in K65 to calculate the principal portion of the payment.
7. Enter a formula in L65 to calculate the new balance.
8. Copy the three formulas in J65, K65, and L65 to the other blue cells in the
respective columns.
9. When the last regular payment would result in a negative balance (as in I74),
this final payment cell must be programmed to equal the preceding
balance plus the current period's interest.

Page 4

```
To top