VIEWS: 353 PAGES: 4 CATEGORY: Business POSTED ON: 6/30/2010 Public Domain
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