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

VIEWS: 353 PAGES: 4

More Info
									                                           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


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

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