Loan Amortization Schedules

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.) DATA: PARTIAL AMORTIZATION DATA: 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): Loan payment (PMT ): Total number of payments (n ): Payment number 0 1 2 3 4 5 6 7 8 9 10 11 12 Interest portion -Principal portion -- 0.000000% Original principal (PV ): Nominal (annual) rate of Compoundings per year Interest rate per compou Payments per year: Compoundings per paym Interest rate per paymen Loan payment (PMT ): Total number of paymen Payment number 0 1 2 | Payment -$0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Principal balance $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. GENERAL INSTRUCTION 1. Copy the preceding DA 2. Cells with a yellow bac 3. Cells with a blue backg Example 14.2B from the text is solved below to illustrate the use of this template. 1. Enter values for PV, j, m , Payments per year, 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 decimal equivalent form. 2. Enter formulas in the two blue cells of the DATA section to calculate i and c . The 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 Example 14.2C from the 1. Enter values for PV, j, m DATA section. Enter do interest rates in their de 2. Enter formulas in the tw formula for calculating i 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 DATA: Example 14.2C DATA: 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): Loan payment (PMT ): Total number of payments (n ): Payment number 0 1 2 3 4 5 6 7 Interest portion -$4,732.25 $4,260.75 $3,733.46 $3,143.80 $2,484.37 $1,746.93 $922.25 Principal portion -$3,985.44 $4,456.94 $4,984.23 $5,573.89 $6,233.32 $6,970.76 $7,795.42 $40,000.00 11.500% 2 5.750% 1 2.00000 11.830625% $8,717.69 7.000000 Principal balance $40,000.00 $36,014.56 $31,557.62 $26,573.39 $20,999.50 $14,766.18 $7,795.42 $0.00 Original principal (PV ): Nominal (annual) rate of Compoundings per year Interest rate per compou Payments per year: Compoundings per paym Interest rate per paymen Loan payment (PMT ): Total number of paymen Payment number 0 1 2 | 26 27 28 | 54 55 56 Payment -$8,717.69 $8,717.69 $8,717.69 $8,717.69 $8,717.69 $8,717.69 $8,717.67 4. Enter a formula in C65 to calculate the interest for one payment interval on the balance in E64. In preparation for copying the formula down the column, the absolute reference must be used for the location of i 2 (that is, E$58). (Ideally, 5. 6. 7. 8. you should use the ROUND function to round the interest to the nearest cent.) Enter a formula in D65 to calculate the principal portion of the payment. Enter a formula in E65 to calculate the new balance. Copy the three formulas in C65, D65, and E65 down their respective columns. When the last regular payment would result in a negative balance (as in B71), this final payment cell must be programmed to equal the preceding balance plus the current period's interest. 4. Enter the serial number the yellow cells of the P 5. Calculate and enter the balance column. 5. Enter a formula in J65 t balance in L64. In prep 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): Loan payment (PMT ): Total number of payments (n ): Interest portion -Principal portion -- 0.000000% Payment -$0.00 $0.00 | -$0.00 $0.00 | -$0.00 $0.00 Principal balance $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 ): 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): Loan payment (PMT ): Total number of payments (n ): Interest portion -$96.25 $94.90 | -$57.16 $55.48 | -$3.89 $1.74 Principal portion -$153.75 $155.10 | -$192.84 $194.52 | -$246.11 $198.63 $11,000.00 10.500% 12 0.875% 12 1.00000 0.875000% $250.00 55.800772 Principal balance $11,000.00 $10,846.25 $10,691.15 | $6,533.07 $6,340.23 $6,145.71 | $444.74 $198.63 $0.00 Payment -$250.00 $250.00 | -$250.00 $250.00 | -$250.00 $200.37 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