Brown2e_Ch09 Spread Sheets.xls
Amortization Table
A simple amortization table covering 300 payment periods of a loan.
1) To use the table, simply change any of the values in the “inital data” area of the worksheet.
2) To print the table, just choose “Print” from the “File” menu. The print area is already defined.
Initial Data
LOAN DATA TABLE DATA
Loan amount: $100,000,000.00 Table starts at date:
Annual interest rate: 7.00% or at payment number: 1
Term in years: 25
Payments per year: 1
PERIODIC PAYMENT
CALCULATIONS
Use payment of: $8,581,051.72 Beginning balance at payment 1: 100,000,000.00
1st payment in table: 1 Cumulative interest prior to payment 1: 0.00
Table
Payment Beginning Ending Cumulative
No. Date Balance Interest Principal Balance Interest
18/1/2005 100,000,000.00 7,000,000.00 1,581,051.72 98,418,948.28 7,000,000.00
28/1/2006 98,418,948.28 6,889,326.38 1,691,725.34 96,727,222.94 13,889,326.38
38/1/2007 96,727,222.94 6,770,905.61 1,810,146.12 94,917,076.82 20,660,231.98
48/1/2008 94,917,076.82 6,644,195.38 1,936,856.34 92,980,220.47 27,304,427.36
58/1/2009 92,980,220.47 6,508,615.43 2,072,436.29 90,907,784.19 33,813,042.80
68/1/2010 90,907,784.19 6,363,544.89 2,217,506.83 88,690,277.36 40,176,587.69
78/1/2011 88,690,277.36 6,208,319.41 2,372,732.31 86,317,545.05 46,384,907.10
88/1/2012 86,317,545.05 6,042,228.15 2,538,823.57 83,778,721.48 52,427,135.26
98/1/2013 83,778,721.48 5,864,510.50 2,716,541.22 81,062,180.26 58,291,645.76
10 8/1/2014 81,062,180.26 5,674,352.62 2,906,699.10 78,155,481.16 63,965,998.38
11 8/1/2015 78,155,481.16 5,470,883.68 3,110,168.04 75,045,313.12 69,436,882.06
12 8/1/2016 75,045,313.12 5,253,171.92 3,327,879.80 71,717,433.31 74,690,053.98
13 8/1/2017 71,717,433.31 5,020,220.33 3,560,831.39 68,156,601.92 79,710,274.31
14 8/1/2018 68,156,601.92 4,770,962.13 3,810,089.59 64,346,512.34 84,481,236.44
15 8/1/2019 64,346,512.34 4,504,255.86 4,076,795.86 60,269,716.48 88,985,492.31
16 8/1/2020 60,269,716.48 4,218,880.15 4,362,171.57 55,907,544.91 93,204,372.46
17 8/1/2021 55,907,544.91 3,913,528.14 4,667,523.58 51,240,021.33 97,117,900.60
18 8/1/2022 51,240,021.33 3,586,801.49 4,994,250.23 46,245,771.10 100,704,702.10 Using Excel’s PMT Function
19 8/1/2023 46,245,771.10 3,237,203.98 5,343,847.75 40,901,923.36 103,941,906.07 $8,581,051.72
21 8/1/2025 35,184,006.27 2,462,880.44 6,118,171.28 29,065,834.99 109,267,921.15
22 8/1/2026 29,065,834.99 2,034,608.45 6,546,443.27 22,519,391.71 111,302,529.60
23 8/1/2027 22,519,391.71 1,576,357.42 7,004,694.30 15,514,697.41 112,878,887.02
25 8/1/2029 8,019,674.51 561,377.22 8,019,674.51 0.00 114,526,293.05 214,526,293.05
Page 1
Brown2e_Ch09 Spread Sheets.xls
Page 2
Brown2e_Ch09 Spread Sheets.xls
Page 3
Brown2e_Ch09 Spread Sheets.xls
Page 4
Brown2e_Ch09 Spread Sheets.xls
Amortization Table
A simple amortization table covering 300 payment periods of a loan.
1) To use the table, simply change any of the values in the “inital data” area of the worksheet.
2) To print the table, just choose “Print” from the “File” menu. The print area is already defined.
Initial Data
LOAN DATA TABLE DATA
Loan amount: $100,000,000.00 Table starts at date:
Annual interest rate: 5.00% or at payment number: 1
Term in years: 25
PERIODIC PAYMENT
Table
Payment Beginning Ending Cumulative
No. Date Balance Interest Principal Balance Interest
1 6/14/2010 100,000,000.00 5,000,000.00 2,095,245.73 97,904,754.27 5,000,000.00
2 6/14/2011 97,904,754.27 4,895,237.71 2,200,008.02 95,704,746.25 9,895,237.71
3 6/14/2012 95,704,746.25 4,785,237.31 2,310,008.42 93,394,737.84 14,680,475.03
4 6/14/2013 93,394,737.84 4,669,736.89 2,425,508.84 90,969,229.00 19,350,211.92
5 6/14/2014 90,969,229.00 4,548,461.45 2,546,784.28 88,422,444.72 23,898,673.37
6 6/14/2015 88,422,444.72 4,421,122.24 2,674,123.49 85,748,321.22 28,319,795.60
7 6/14/2016 85,748,321.22 4,287,416.06 2,807,829.67 82,940,491.56 32,607,211.67
8 6/14/2017 82,940,491.56 4,147,024.58 2,948,221.15 79,992,270.40 36,754,236.24
9 6/14/2018 79,992,270.40 3,999,613.52 3,095,632.21 76,896,638.19 40,753,849.76
10 6/14/2019 76,896,638.19 3,844,831.91 3,250,413.82 73,646,224.37 44,598,681.67
11 6/14/2020 73,646,224.37 3,682,311.22 3,412,934.51 70,233,289.86 48,280,992.89
12 6/14/2021 70,233,289.86 3,511,664.49 3,583,581.24 66,649,708.63 51,792,657.38
13 6/14/2022 66,649,708.63 3,332,485.43 3,762,760.30 62,886,948.33 55,125,142.82
14 6/14/2023 62,886,948.33 3,144,347.42 3,950,898.31 58,936,050.01 58,269,490.23
15 6/14/2024 58,936,050.01 2,946,802.50 4,148,443.23 54,787,606.78 61,216,292.73
16 6/14/2025 54,787,606.78 2,739,380.34 4,355,865.39 50,431,741.39 63,955,673.07
17 6/14/2026 50,431,741.39 2,521,587.07 4,573,658.66 45,858,082.73 66,477,260.14
18 6/14/2027 45,858,082.73 2,292,904.14 4,802,341.59 41,055,741.14 68,770,164.28 Using Excel’s PMT Function
19 6/14/2028 41,055,741.14 2,052,787.06 5,042,458.67 36,013,282.47 70,822,951.34 $7,095,245.73
20 6/14/2029 36,013,282.47 1,800,664.12 5,294,581.61 30,718,700.86 72,623,615.46 $177,381,143.25
21 6/14/2030 30,718,700.86 1,535,935.04 5,559,310.69 25,159,390.17 74,159,550.50
22 6/14/2031 25,159,390.17 1,257,969.51 5,837,276.22 19,322,113.95 75,417,520.01
23 6/14/2032 19,322,113.95 966,105.70 6,129,140.03 13,192,973.92 76,383,625.71
24 6/14/2033 13,192,973.92 659,648.70 6,435,597.03 6,757,376.89 77,043,274.40 Total Payments from the Table to the left
25 6/14/2034 6,757,376.89 337,868.84 6,757,376.89 0.00 77,381,143.25 177,381,143.25
Page 5
Brown2e_Ch09 Spread Sheets.xls
Page 6
Brown2e_Ch09 Spread Sheets.xls
Page 7
Brown2e_Ch09 Spread Sheets.xls
Page 8
Brown2e_Ch09 Spread Sheets.xls
Page 9
County Prop
Tax
County Sales Tax City Sales Tax
Combined from the
0.5% add-on to State
sales tax
Hotel Tax
The public’s portion of the $350 million is $210 million. Revenues $475,000,000 $69,000,000 $64,000,000 $133,000,000 $3,300,000
Additional Rate 0.010 Make it 0.503748199% instead of 0.5% –> 2,660,000,000.00 <– Add 2% more on to the current 12% (of which 2% goes to General Fund)
Incremental annual source $4,750,000 $9,970,209 $3,300,000
Sacramento NBA Arena
Range of Sources
Additional rate increase –> 0.003748199
Annual Total
Sacramento County Property Tax Special Fee $4,750,000 (1)
Revenue Bond (backed by three above sources) $18,020,209 $450,505,215 (4)
Brown2e_Ch09 Spread Sheets.xls
Amortization Table
A simple amortization table covering 300 payment periods of a loan.
1) To use the table, simply change any of the values in the “inital data” area of the worksheet.
2) To print the table, just choose “Print” from the “File” menu. The print area is already defined.
Initial Data
LOAN DATA TABLE DATA
Loan amount: $210,000,000 Table starts at date:
Annual interest rate: 7.00% or at payment number: 1
Term in years: 25
PERIODIC PAYMENT
Payment Beginning Ending Cumulative
No. Date Balance Interest Principal Balance Interest
1 1/1/2011 210,000,000.00 14,700,000.00 3,320,208.62 206,679,791.38 14,700,000.00
2 1/1/2012 206,679,791.38 14,467,585.40 3,552,623.22 203,127,168.16 29,167,585.40
3 1/1/2013 203,127,168.16 14,218,901.77 3,801,306.84 199,325,861.32 43,386,487.17
4 1/1/2014 199,325,861.32 13,952,810.29 4,067,398.32 195,258,463.00 57,339,297.46
5 1/1/2015 195,258,463.00 13,668,092.41 4,352,116.21 190,906,346.79 71,007,389.87
6 1/1/2016 190,906,346.79 13,363,444.28 4,656,764.34 186,249,582.45 84,370,834.15
7 1/1/2017 186,249,582.45 13,037,470.77 4,982,737.85 181,266,844.60 97,408,304.92
10 1/1/2020 170,230,578.55 11,916,140.50 6,104,068.12 164,126,510.43 134,328,596.60
11 1/1/2021 164,126,510.43 11,488,855.73 6,531,352.89 157,595,157.55 145,817,452.33
12 1/1/2022 157,595,157.55 11,031,661.03 6,988,547.59 150,606,609.96 156,849,113.35
13 1/1/2023 150,606,609.96 10,542,462.70 7,477,745.92 143,128,864.04 167,391,576.05
14 1/1/2024 143,128,864.04 10,019,020.48 8,001,188.13 135,127,675.90 177,410,596.53
15 1/1/2025 135,127,675.90 9,458,937.31 8,561,271.30 126,566,404.60 186,869,533.85
16 1/1/2026 126,566,404.60 8,859,648.32 9,160,560.29 117,405,844.31 195,729,182.17
17 1/1/2027 117,405,844.31 8,218,409.10 9,801,799.51 107,604,044.79 203,947,591.27
18 1/1/2028 107,604,044.79 7,532,283.14 10,487,925.48 97,116,119.31 211,479,874.41
19 1/1/2029 97,116,119.31 6,798,128.35 11,222,080.26 85,894,039.05 218,278,002.76
20 1/1/2030 85,894,039.05 6,012,582.73 12,007,625.88 73,886,413.16 224,290,585.49
21 1/1/2031 73,886,413.16 5,172,048.92 12,848,159.69 61,038,253.47 229,462,634.41
22 1/1/2032 61,038,253.47 4,272,677.74 13,747,530.87 47,290,722.60 233,735,312.16
23 1/1/2033 47,290,722.60 3,310,350.58 14,709,858.03 32,580,864.56 237,045,662.74
24 1/1/2034 32,580,864.56 2,280,660.52 15,739,548.10 16,841,316.46 239,326,323.26 Total Payments
25 1/1/2035 16,841,316.46 1,178,892.15 16,841,316.46 0.00 240,505,215.41 450,505,215.41
Page 11
Brown2e_Ch09 Spread Sheets.xls
Page 12
Brown2e_Ch09 Spread Sheets.xls
Page 13
Brown2e_Ch09 Spread Sheets.xls
Page 14
Brown2e_Ch09 Spread Sheets.xls
Page 15