Amortization Table
The amortization table at the end of this worksheet calculates the principal and interest payments, ending balance, and cumulative interest for any 84 consecutive payment periods of a loan. u To use the table, change the values in the Inital Data section of the worksheet.
u If you increase the term of the loan or the number of payments, you will need to add more payment periods
to the table. Select cells A75:G77, then drag the Fill handle (+) into the cells below the table.
u Most formulas on this worksheet are contained in defined names. To see the names and formulas,
choose Name from the Insert menu, and then choose Define. Select a name from the list ('Amortization Table'!Interest, for example).
Initial Data
LOAN DATA Loan amount: Annual interest rate: Term in years: Payments per year: First payment due: PERIODIC PAYMENT Entered payment: Calculated payment: CALCULATIONS Use payment of: $310,850.00 6.625% 30 12 7/1/2003 TABLE DATA Table starts at date: 7/1/2003 or at payment number: 1
$1,990.41 $1,990.41
The table uses the calculated periodic payment amount, unless you enter a value for "Entered payment." Beginning balance at payment 1: Cumulative interest prior to payment 1: $310,850.00 $0.00
1st payment in table: 1
Table
No. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Payment Date 7/1/2003 8/1/2003 9/1/2003 10/1/2003 11/1/2003 12/1/2003 1/1/2004 2/1/2004 3/1/2004 4/1/2004 5/1/2004 6/1/2004 7/1/2004 8/1/2004 9/1/2004 10/1/2004 11/1/2004 12/1/2004 1/1/2005 2/1/2005 3/1/2005 4/1/2005 5/1/2005 6/1/2005 Beginning Balance 310,850.00 310,575.74 310,299.97 310,022.68 309,743.86 309,463.50 309,181.59 308,898.12 308,613.09 308,326.48 308,038.30 307,748.52 307,457.14 307,164.15 306,869.55 306,573.32 306,275.45 305,975.94 305,674.77 305,371.95 305,067.45 304,761.27 304,453.40 304,143.83 Interest 1,716.15 1,714.64 1,713.11 1,711.58 1,710.04 1,708.50 1,706.94 1,705.38 1,703.80 1,702.22 1,700.63 1,699.03 1,697.42 1,695.80 1,694.18 1,692.54 1,690.90 1,689.24 1,687.58 1,685.91 1,684.23 1,682.54 1,680.84 1,679.13 Principal 274.26 275.77 277.29 278.82 280.36 281.91 283.47 285.03 286.61 288.19 289.78 291.38 292.99 294.60 296.23 297.87 299.51 301.16 302.83 304.50 306.18 307.87 309.57 311.28 Ending Balance 310,575.74 310,299.97 310,022.68 309,743.86 309,463.50 309,181.59 308,898.12 308,613.09 308,326.48 308,038.30 307,748.52 307,457.14 307,164.15 306,869.55 306,573.32 306,275.45 305,975.94 305,674.77 305,371.95 305,067.45 304,761.27 304,453.40 304,143.83 303,832.55 Cumulative Interest 1,716.15 3,430.79 5,143.90 6,855.49 8,565.53 10,274.03 11,980.97 13,686.34 15,390.14 17,092.36 18,792.99 20,492.02 22,189.44 23,885.24 25,579.42 27,271.96 28,962.85 30,652.09 32,339.67 34,025.58 35,709.81 37,392.34 39,073.18 40,752.31
Page
No. 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
Payment Date 7/1/2005 8/1/2005 9/1/2005 10/1/2005 11/1/2005 12/1/2005 1/1/2006 2/1/2006 3/1/2006 4/1/2006 5/1/2006 6/1/2006 7/1/2006 8/1/2006 9/1/2006 10/1/2006 11/1/2006 12/1/2006 1/1/2007 2/1/2007 3/1/2007 4/1/2007 5/1/2007 6/1/2007
Beginning Balance 303,832.55 303,519.55 303,204.83 302,888.36 302,570.15 302,250.18 301,928.45 301,604.94 301,279.64 300,952.55 300,623.65 300,292.94 299,960.40 299,626.03 299,289.81 298,951.73 298,611.78 298,269.96 297,926.26 297,580.65 297,233.14 296,883.70 296,532.34 296,179.04
Interest 1,677.41 1,675.68 1,673.94 1,672.20 1,670.44 1,668.67 1,666.90 1,665.11 1,663.31 1,661.51 1,659.69 1,657.87 1,656.03 1,654.19 1,652.33 1,650.46 1,648.59 1,646.70 1,644.80 1,642.89 1,640.97 1,639.05 1,637.11 1,635.16
Principal 313.00 314.73 316.46 318.21 319.97 321.73 323.51 325.30 327.09 328.90 330.71 332.54 334.38 336.22 338.08 339.94 341.82 343.71 345.61 347.51 349.43 351.36 353.30 355.25
Ending Balance 303,519.55 303,204.83 302,888.36 302,570.15 302,250.18 301,928.45 301,604.94 301,279.64 300,952.55 300,623.65 300,292.94 299,960.40 299,626.03 299,289.81 298,951.73 298,611.78 298,269.96 297,926.26 297,580.65 297,233.14 296,883.70 296,532.34 296,179.04 295,823.79
Cumulative Interest 42,429.72 44,105.40 45,779.34 47,451.54 49,121.98 50,790.65 52,457.55 54,122.66 55,785.97 57,447.48 59,107.17 60,765.04 62,421.07 64,075.26 65,727.59 67,378.05 69,026.63 70,673.33 72,318.13 73,961.03 75,602.00 77,241.05 78,878.15 80,513.31
Page