Number of months, Interest Only (IO) repayments. | 24 | Enter your student number digit by digit in cells F2:M2 | 4 | 4 | 3 | 3 | 4 | 4 | 4 | 3 | First/Given Name | |||
Number of years, n, with Principal & Interest (P&I) repayments | 14 | sum of first four digits of student number | Family Name | |||||||||||
Number of months, n*m of P&I repayments | 168 | |||||||||||||
Interest rate p.a (compounding monthly) % | 5.40% | |||||||||||||
Interest rate per month, i/m, % | 0.450% | this is formatted as a percentage. i.e 5.40% per annum divided by 12 is 0.054/12 per month and displays as 0.450% in cell C6 | ||||||||||||
Loan amount | $6,00,000 | |||||||||||||
Repayment from month 25 to end of loan period (Amount to be a negative) | DO NOT Round any amounts in the Amortisation Schedule.Use format commands to display as currency. | |||||||||||||
Loan Amortisation Schedule | ||||||||||||||
Mth | Balance beginning of month | Interest | Repayment | Balance end of month | ||||||||||
1 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
2 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
3 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
4 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
5 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
6 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
7 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
8 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
9 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
10 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
11 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
12 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
13 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
14 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
15 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
16 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
17 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
18 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
19 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
20 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
21 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
22 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
23 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
24 | $6,00,000.00 | $2,700.00 | $0.00 | $6,00,000.00 | ||||||||||
25 | $6,00,000.00 | $2,700.00 | -$5,097.59 | $5,97,602.41 | ||||||||||
26 | $5,97,602.41 | $2,689.21 | -$5,097.59 | $5,95,194.03 | ||||||||||
27 | $5,95,194.03 | $2,678.37 | -$5,097.59 | $5,92,774.82 | ||||||||||
28 | $5,92,774.82 | $2,667.49 | -$5,097.59 | $5,90,344.72 | ||||||||||
29 | $5,90,344.72 | $2,656.55 | -$5,097.59 | $5,87,903.68 | ||||||||||
30 | $5,87,903.68 | $2,645.57 | -$5,097.59 | $5,85,451.66 | ||||||||||
31 | $5,85,451.66 | $2,634.53 | -$5,097.59 | $5,82,988.60 | ||||||||||
32 | $5,82,988.60 | $2,623.45 | -$5,097.59 | $5,80,514.46 | ||||||||||
33 | $5,80,514.46 | $2,612.32 | -$5,097.59 | $5,78,029.19 | ||||||||||
34 | $5,78,029.19 | $2,601.13 | -$5,097.59 | $5,75,532.73 | ||||||||||
35 | $5,75,532.73 | $2,589.90 | -$5,097.59 | $5,73,025.04 | ||||||||||
36 | $5,73,025.04 | $2,578.61 | -$5,097.59 | $5,70,506.06 | ||||||||||
37 | $5,70,506.06 | $2,567.28 | -$5,097.59 | $5,67,975.75 | ||||||||||
38 | $5,67,975.75 | $2,555.89 | -$5,097.59 | $5,65,434.05 | ||||||||||
39 | $5,65,434.05 | $2,544.45 | -$5,097.59 | $5,62,880.92 | ||||||||||
40 | $5,62,880.92 | $2,532.96 | -$5,097.59 | $5,60,316.29 | ||||||||||
41 | $5,60,316.29 | $2,521.42 | -$5,097.59 | $5,57,740.13 | ||||||||||
42 | $5,57,740.13 | $2,509.83 | -$5,097.59 | $5,55,152.37 | ||||||||||
43 | $5,55,152.37 | $2,498.19 | -$5,097.59 | $5,52,552.96 | ||||||||||
44 | $5,52,552.96 | $2,486.49 | -$5,097.59 | $5,49,941.86 | ||||||||||
45 | $5,49,941.86 | $2,474.74 | -$5,097.59 | $5,47,319.01 | ||||||||||
46 | $5,47,319.01 | $2,462.94 | -$5,097.59 | $5,44,684.36 | ||||||||||
47 | $5,44,684.36 | $2,451.08 | -$5,097.59 | $5,42,037.85 | ||||||||||
48 | $5,42,037.85 | $2,439.17 | -$5,097.59 | $5,39,379.43 | ||||||||||
49 | $5,39,379.43 | $2,427.21 | -$5,097.59 | $5,36,709.05 | ||||||||||
50 | $5,36,709.05 | $2,415.19 | -$5,097.59 | $5,34,026.65 | ||||||||||
51 | $5,34,026.65 | $2,403.12 | -$5,097.59 | $5,31,332.18 | ||||||||||
52 | $5,31,332.18 | $2,390.99 | -$5,097.59 | $5,28,625.59 | ||||||||||
53 | $5,28,625.59 | $2,378.82 | -$5,097.59 | $5,25,906.82 | ||||||||||
54 | $5,25,906.82 | $2,366.58 | -$5,097.59 | $5,23,175.81 | ||||||||||
55 | $5,23,175.81 | $2,354.29 | -$5,097.59 | $5,20,432.51 | ||||||||||
56 | $5,20,432.51 | $2,341.95 | -$5,097.59 | $5,17,676.87 | ||||||||||
57 | $5,17,676.87 | $2,329.55 | -$5,097.59 | $5,14,908.83 | ||||||||||
58 | $5,14,908.83 | $2,317.09 | -$5,097.59 | $5,12,128.33 | ||||||||||
59 | $5,12,128.33 | $2,304.58 | -$5,097.59 | $5,09,335.32 | ||||||||||
60 | $5,09,335.32 | $2,292.01 | -$5,097.59 | $5,06,529.74 | ||||||||||
61 | $5,06,529.74 | $2,279.38 | -$5,097.59 | $5,03,711.53 | ||||||||||
62 | $5,03,711.53 | $2,266.70 | -$5,097.59 | $5,00,880.64 | ||||||||||
63 | $5,00,880.64 | $2,253.96 | -$5,097.59 | $4,98,037.02 | ||||||||||
64 | $4,98,037.02 | $2,241.17 | -$5,097.59 | $4,95,180.60 | ||||||||||
65 | $4,95,180.60 | $2,228.31 | -$5,097.59 | $4,92,311.32 | ||||||||||
66 | $4,92,311.32 | $2,215.40 | -$5,097.59 | $4,89,429.13 | ||||||||||
67 | $4,89,429.13 | $2,202.43 | -$5,097.59 | $4,86,533.97 | ||||||||||
68 | $4,86,533.97 | $2,189.40 | -$5,097.59 | $4,83,625.79 | ||||||||||
69 | $4,83,625.79 | $2,176.32 | -$5,097.59 | $4,80,704.52 | ||||||||||
70 | $4,80,704.52 | $2,163.17 | -$5,097.59 | $4,77,770.10 | ||||||||||
71 | $4,77,770.10 | $2,149.97 | -$5,097.59 | $4,74,822.47 | ||||||||||
72 | $4,74,822.47 | $2,136.70 | -$5,097.59 | $4,71,861.59 | ||||||||||
73 | $4,71,861.59 | $2,123.38 | -$5,097.59 | $4,68,887.37 | ||||||||||
74 | $4,68,887.37 | $2,109.99 | -$5,097.59 | $4,65,899.78 | ||||||||||
75 | $4,65,899.78 | $2,096.55 | -$5,097.59 | $4,62,898.74 | ||||||||||
76 | $4,62,898.74 | $2,083.04 | -$5,097.59 | $4,59,884.20 | ||||||||||
77 | $4,59,884.20 | $2,069.48 | -$5,097.59 | $4,56,856.09 | ||||||||||
78 | $4,56,856.09 | $2,055.85 | -$5,097.59 | $4,53,814.35 | ||||||||||
79 | $4,53,814.35 | $2,042.16 | -$5,097.59 | $4,50,758.92 | ||||||||||
80 | $4,50,758.92 | $2,028.42 | -$5,097.59 | $4,47,689.75 | ||||||||||
81 | $4,47,689.75 | $2,014.60 | -$5,097.59 | $4,44,606.77 | ||||||||||
82 | $4,44,606.77 | $2,000.73 | -$5,097.59 | $4,41,509.91 | ||||||||||
83 | $4,41,509.91 | $1,986.79 | -$5,097.59 | $4,38,399.11 | ||||||||||
84 | $4,38,399.11 | $1,972.80 | -$5,097.59 | $4,35,274.32 | ||||||||||
85 | $4,35,274.32 | $1,958.73 | -$5,097.59 | $4,32,135.47 | ||||||||||
86 | $4,32,135.47 | $1,944.61 | -$5,097.59 | $4,28,982.49 | ||||||||||
87 | $4,28,982.49 | $1,930.42 | -$5,097.59 | $4,25,815.32 | ||||||||||
88 | $4,25,815.32 | $1,916.17 | -$5,097.59 | $4,22,633.90 | ||||||||||
89 | $4,22,633.90 | $1,901.85 | -$5,097.59 | $4,19,438.16 | ||||||||||
90 | $4,19,438.16 | $1,887.47 | -$5,097.59 | $4,16,228.05 | ||||||||||
91 | $4,16,228.05 | $1,873.03 | -$5,097.59 | $4,13,003.48 | ||||||||||
92 | $4,13,003.48 | $1,858.52 | -$5,097.59 | $4,09,764.41 | ||||||||||
93 | $4,09,764.41 | $1,843.94 | -$5,097.59 | $4,06,510.76 | ||||||||||
94 | $4,06,510.76 | $1,829.30 | -$5,097.59 | $4,03,242.47 | ||||||||||
95 | $4,03,242.47 | $1,814.59 | -$5,097.59 | $3,99,959.47 | ||||||||||
96 | $3,99,959.47 | $1,799.82 | -$5,097.59 | $3,96,661.70 | ||||||||||
97 | $3,96,661.70 | $1,784.98 | -$5,097.59 | $3,93,349.09 | ||||||||||
98 | $3,93,349.09 | $1,770.07 | -$5,097.59 | $3,90,021.57 | ||||||||||
99 | $3,90,021.57 | $1,755.10 | -$5,097.59 | $3,86,679.08 | ||||||||||
100 | $3,86,679.08 | $1,740.06 | -$5,097.59 | $3,83,321.55 | ||||||||||
101 | $3,83,321.55 | $1,724.95 | -$5,097.59 | $3,79,948.91 | ||||||||||
102 | $3,79,948.91 | $1,709.77 | -$5,097.59 | $3,76,561.09 | ||||||||||
103 | $3,76,561.09 | $1,694.52 | -$5,097.59 | $3,73,158.03 | ||||||||||
104 | $3,73,158.03 | $1,679.21 | -$5,097.59 | $3,69,739.65 | ||||||||||
105 | $3,69,739.65 | $1,663.83 | -$5,097.59 | $3,66,305.89 | ||||||||||
106 | $3,66,305.89 | $1,648.38 | -$5,097.59 | $3,62,856.68 | ||||||||||
107 | $3,62,856.68 | $1,632.86 | -$5,097.59 | $3,59,391.94 | ||||||||||
108 | $3,59,391.94 | $1,617.26 | -$5,097.59 | $3,55,911.62 | ||||||||||
109 | $3,55,911.62 | $1,601.60 | -$5,097.59 | $3,52,415.63 | ||||||||||
110 | $3,52,415.63 | $1,585.87 | -$5,097.59 | $3,48,903.91 | ||||||||||
111 | $3,48,903.91 | $1,570.07 | -$5,097.59 | $3,45,376.39 | ||||||||||
112 | $3,45,376.39 | $1,554.19 | -$5,097.59 | $3,41,833.00 | ||||||||||
113 | $3,41,833.00 | $1,538.25 | -$5,097.59 | $3,38,273.65 | ||||||||||
114 | $3,38,273.65 | $1,522.23 | -$5,097.59 | $3,34,698.30 | ||||||||||
115 | $3,34,698.30 | $1,506.14 | -$5,097.59 | $3,31,106.85 | ||||||||||
116 | $3,31,106.85 | $1,489.98 | -$5,097.59 | $3,27,499.24 | ||||||||||
117 | $3,27,499.24 | $1,473.75 | -$5,097.59 | $3,23,875.40 | ||||||||||
118 | $3,23,875.40 | $1,457.44 | -$5,097.59 | $3,20,235.25 | ||||||||||
119 | $3,20,235.25 | $1,441.06 | -$5,097.59 | $3,16,578.72 | ||||||||||
120 | $3,16,578.72 | $1,424.60 | -$5,097.59 | $3,12,905.74 | ||||||||||
121 | $3,12,905.74 | $1,408.08 | -$5,097.59 | $3,09,216.22 | ||||||||||
122 | $3,09,216.22 | $1,391.47 | -$5,097.59 | $3,05,510.11 | ||||||||||
123 | $3,05,510.11 | $1,374.80 | -$5,097.59 | $3,01,787.31 | ||||||||||
124 | $3,01,787.31 | $1,358.04 | -$5,097.59 | $2,98,047.77 | ||||||||||
125 | $2,98,047.77 | $1,341.21 | -$5,097.59 | $2,94,291.40 | ||||||||||
126 | $2,94,291.40 | $1,324.31 | -$5,097.59 | $2,90,518.12 | ||||||||||
127 | $2,90,518.12 | $1,307.33 | -$5,097.59 | $2,86,727.86 | ||||||||||
128 | $2,86,727.86 | $1,290.28 | -$5,097.59 | $2,82,920.55 | ||||||||||
129 | $2,82,920.55 | $1,273.14 | -$5,097.59 | $2,79,096.10 | ||||||||||
130 | $2,79,096.10 | $1,255.93 | -$5,097.59 | $2,75,254.44 | ||||||||||
131 | $2,75,254.44 | $1,238.65 | -$5,097.59 | $2,71,395.50 | ||||||||||
132 | $2,71,395.50 | $1,221.28 | -$5,097.59 | $2,67,519.19 | ||||||||||
133 | $2,67,519.19 | $1,203.84 | -$5,097.59 | $2,63,625.44 | ||||||||||
134 | $2,63,625.44 | $1,186.31 | -$5,097.59 | $2,59,714.17 | ||||||||||
135 | $2,59,714.17 | $1,168.71 | -$5,097.59 | $2,55,785.29 | ||||||||||
136 | $2,55,785.29 | $1,151.03 | -$5,097.59 | $2,51,838.74 | ||||||||||
137 | $2,51,838.74 | $1,133.27 | -$5,097.59 | $2,47,874.42 | ||||||||||
138 | $2,47,874.42 | $1,115.43 | -$5,097.59 | $2,43,892.27 | ||||||||||
139 | $2,43,892.27 | $1,097.52 | -$5,097.59 | $2,39,892.19 | ||||||||||
140 | $2,39,892.19 | $1,079.51 | -$5,097.59 | $2,35,874.12 | ||||||||||
141 | $2,35,874.12 | $1,061.43 | -$5,097.59 | $2,31,837.96 | ||||||||||
142 | $2,31,837.96 | $1,043.27 | -$5,097.59 | $2,27,783.65 | ||||||||||
143 | $2,27,783.65 | $1,025.03 | -$5,097.59 | $2,23,711.08 | ||||||||||
144 | $2,23,711.08 | $1,006.70 | -$5,097.59 | $2,19,620.19 | ||||||||||
145 | $2,19,620.19 | $988.29 | -$5,097.59 | $2,15,510.90 | ||||||||||
146 | $2,15,510.90 | $969.80 | -$5,097.59 | $2,11,383.11 | ||||||||||
147 | $2,11,383.11 | $951.22 | -$5,097.59 | $2,07,236.74 | ||||||||||
148 | $2,07,236.74 | $932.57 | -$5,097.59 | $2,03,071.72 | ||||||||||
149 | $2,03,071.72 | $913.82 | -$5,097.59 | $1,98,887.95 | ||||||||||
150 | $1,98,887.95 | $895.00 | -$5,097.59 | $1,94,685.36 | ||||||||||
151 | $1,94,685.36 | $876.08 | -$5,097.59 | $1,90,463.86 | ||||||||||
152 | $1,90,463.86 | $857.09 | -$5,097.59 | $1,86,223.35 | ||||||||||
153 | $1,86,223.35 | $838.01 | -$5,097.59 | $1,81,963.77 | ||||||||||
154 | $1,81,963.77 | $818.84 | -$5,097.59 | $1,77,685.02 | ||||||||||
155 | $1,77,685.02 | $799.58 | -$5,097.59 | $1,73,387.01 | ||||||||||
156 | $1,73,387.01 | $780.24 | -$5,097.59 | $1,69,069.67 | ||||||||||
157 | $1,69,069.67 | $760.81 | -$5,097.59 | $1,64,732.89 | ||||||||||
158 | $1,64,732.89 | $741.30 | -$5,097.59 | $1,60,376.60 | ||||||||||
159 | $1,60,376.60 | $721.69 | -$5,097.59 | $1,56,000.71 | ||||||||||
160 | $1,56,000.71 | $702.00 | -$5,097.59 | $1,51,605.12 | ||||||||||
161 | $1,51,605.12 | $682.22 | -$5,097.59 | $1,47,189.75 | ||||||||||
162 | $1,47,189.75 | $662.35 | -$5,097.59 | $1,42,754.52 | ||||||||||
163 | $1,42,754.52 | $642.40 | -$5,097.59 | $1,38,299.33 | ||||||||||
164 | $1,38,299.33 | $622.35 | -$5,097.59 | $1,33,824.08 | ||||||||||
165 | $1,33,824.08 | $602.21 | -$5,097.59 | $1,29,328.70 | ||||||||||
166 | $1,29,328.70 | $581.98 | -$5,097.59 | $1,24,813.09 | ||||||||||
167 | $1,24,813.09 | $561.66 | -$5,097.59 | $1,20,277.16 | ||||||||||
168 | $1,20,277.16 | $541.25 | -$5,097.59 | $1,15,720.82 | ||||||||||
169 | $1,15,720.82 | $520.74 | -$5,097.59 | $1,11,143.98 | ||||||||||
170 | $1,11,143.98 | $500.15 | -$5,097.59 | $1,06,546.54 | ||||||||||
171 | $1,06,546.54 | $479.46 | -$5,097.59 | $1,01,928.41 | ||||||||||
172 | $1,01,928.41 | $458.68 | -$5,097.59 | $97,289.50 | ||||||||||
173 | $97,289.50 | $437.80 | -$5,097.59 | $92,629.71 | ||||||||||
174 | $92,629.71 | $416.83 | -$5,097.59 | $87,948.96 | ||||||||||
175 | $87,948.96 | $395.77 | -$5,097.59 | $83,247.14 | ||||||||||
176 | $83,247.14 | $374.61 | -$5,097.59 | $78,524.16 | ||||||||||
177 | $78,524.16 | $353.36 | -$5,097.59 | $73,779.93 | ||||||||||
178 | $73,779.93 | $332.01 | -$5,097.59 | $69,014.35 | ||||||||||
179 | $69,014.35 | $310.56 | -$5,097.59 | $64,227.33 | ||||||||||
180 | $64,227.33 | $289.02 | -$5,097.59 | $59,418.76 | ||||||||||
181 | $59,418.76 | $267.38 | -$5,097.59 | $54,588.56 | ||||||||||
182 | $54,588.56 | $245.65 | -$5,097.59 | $49,736.62 | ||||||||||
183 | $49,736.62 | $223.81 | -$5,097.59 | $44,862.84 | ||||||||||
184 | $44,862.84 | $201.88 | -$5,097.59 | $39,967.14 | ||||||||||
185 | $39,967.14 | $179.85 | -$5,097.59 | $35,049.40 | ||||||||||
186 | $35,049.40 | $157.72 | -$5,097.59 | $30,109.53 | ||||||||||
187 | $30,109.53 | $135.49 | -$5,097.59 | $25,147.44 | ||||||||||
188 | $25,147.44 | $113.16 | -$5,097.59 | $20,163.01 | ||||||||||
189 | $20,163.01 | $90.73 | -$5,097.59 | $15,156.16 | ||||||||||
190 | $15,156.16 | $68.20 | -$5,097.59 | $10,126.77 | ||||||||||
191 | $10,126.77 | $45.57 | -$5,097.59 | $5,074.75 | ||||||||||
192 | $5,074.75 | $22.84 | -$5,097.59 | $0.00 | ||||||||||
Use of Excel fx functions: | ||||||||||||||
Based on your amortisation schedule, calculate answers to Q1 to Q4 in the yellow highlighted cells below (using excel fx TVM functions and referencing the Amortisation Schedule cells) | ||||||||||||||
Answer each question using one of the fx formula(s) from among =PV, =FV, =PMT, =RATE, =NPER, =IPMT, =PPMT, =CUMIPMT, =CUMPRINC. Do NOT round but format as dollars and cents. | ||||||||||||||
Total Loan cost (all repayments) | $8,90,725.68 | Answer must be positive (greater than 0) | Useful clue:Total loan cost is just the total of all amounts paid over the whole loan term ignoring time value of money. Don’t use =SUM() as it is not in the list of eligible formulas above. You can use one of the eligle formulas in conjunction with a multiply or divide or plus or minus. | |||||||||||
The amount of the loan outstanding at the end of month 50. | $5,26,520.35 | Answer must be positive (greater than 0) | Useful clue:You won’t use =SUM(). Use an eligible formula from among those in red above. | |||||||||||
Calculate the interst paid in month 80. | $2,028 | Answer must be positive (greater than 0). Must be a whole number. | Useful clue: You need to use an eligible formula in red above. But pay attention that the “per” should be 80 minus 24. Because P&I repayment only starts from month 25 onwards. | |||||||||||
The monthly loan repayment if there was no interest-only period and you agreed to make Principal and Interest repayments commencing from the end of first month rather than month 25 (using same total loan term being equal to 24 plus contents of cell C4). | $4,673.63 | Answer must be positive (greater than 0) | Useful clue: You need to modify the repayment based on total loan period of C4 plus 24. | C4 Amortisation Schedule contents are here, n*m: | 168 |