Get documents about "Excel Skills UK Mortgage Calculator
Document Sample
Excel Skills UK | Mortgage Calculator Template
Instructions
www.excel-skills.co.uk
Mortgage calculators are sometimes also referred to as home loan calculators or mortgage calculators. The aim of this free Excel
template is to enable users to calculate monthly mortgage repayments, determine the affordability of a home loan, calculate the
interest savings that result from increased mortgage instalments and measure the sensitivity of mortgage repayments to changes in
interest rates. After using this template, you will also gain a better understanding of home loan amortization and specifically the
timing of capital repayments on a mortgage.
The following sheets are included in this template:
MortgageCalculator Sheet
All the calculations in this template are based on the values that are entered in the input cells from cell B4 to B9 on the
MortgageCalculator sheet (except for the net disposable income calculation). Input guidance is displayed below the selected input
cell. We have also added data validation to all input cells to ensure that only valid user input is accepted.
Calculation Results:
The monthly mortgage repayment amount is calculated from the mortgage principle amount (cell B4), mortgage period (cell B6)
and the annual interest rate (cell B5).
The total interest paid over the entire mortgage period is the total amount of interest that will have to be paid over the entire
mortgage period.
The total mortgage repayment over the mortgage period is the sum of all the monthly mortgage repayment amounts. This amount
consists of all interest charges and capital repayments.
The monthly net disposable Income is calculated on the NetDisposable sheet - refer to this sheet for more information on the
calculation.
The maximum mortgage qualification amount is calculated based on the net disposable income, annual interest rate and mortgage
period. It represents an estimate of the maximum mortgage amount that applicants can qualify for based on their combined monthly
net disposable income. There are a number of other factors that financial institutions will consider when determining the maximum
mortgage qualification amount - our calculation should therefore only be seen as an estimate which cannot be guaranteed.
The minimum required net disposable income is the minimum net disposable income that is required in order to qualify for the
mortgage principle amount that is entered in cell B4.
The interest rate safety percentage indicates the percentage by which interest rates have to increase before the monthly net
disposable income would not be sufficient to cover the required monthly mortgage repayments.
The increased instalment calculations in row 21 to 26 are based on the additional monthly mortgage repayment that is entered in
cell B7. The assumption is made that the entire additional mortgage repayments are deducted from the outstanding capital
balance, thereby resulting in a shorter mortgage repayment period. Note that the present value of the interest saving is calculated
by discounting the monthly interest savings by the average annual inflation rate over the entire mortgage period. It therefore
represents the value of future interest savings in today's monetary terms.
The interest rate sensitivity calculation measures the effect that changes in the mortgage interest rate have on monthly mortgage
repayments. The interest rate sensitivity percentage that is entered in cell B8 is used for this purpose.
The capital repayment chart is a visual display of the timing of capital repayments over the entire mortgage period.
The increased instalment interest saving chart is a visual display of the interest savings that result from effecting increased monthly
mortgage repayments.
NetDisposable Sheet
This sheet includes a detailed calculation of the monthly net disposable income. All values should be entered as positive values.
Refer to the guidance that has been included from row 38 downwards for more information on the input that is required in each
input cell.
AnnualAmort Sheet
This sheet includes an annual amortization table that is based on the mortgage input values that are entered in cell B4 to B6 on the
MortgageCalculator sheet. We recommend that you pay special attention to the outstanding capital percentage in column G as it
indicates how the capital will be repaid over the entire mortgage period. You'll notice that during the first few years of the mortgage
repayment period, the monthly mortgage repayments consist almost entirely of interest.
MonthAmort Sheet
This sheet includes a monthly amortization table that is based on the mortgage input values that are entered in cell B4 to B6 on the
MortgageCalculator sheet.
Help
If you experience any difficulty while using this template, please e-mail us at support@excel-skills.co.uk for assistance.
Page 1 of 11
Mortgage Calculator www.excel-skills.co.uk Capital Repayment
120,000 Capital Repayment
Input Variables
Mortgage Amount 100,000.00 100,000
Annual Interest Rate 3.5%
Mortgage Period in Years 25 80,000
Additional Monthly Mortgage Repayment 200.00
Annual Interest Rate Sensitivity 5.5% 60,000
Average Annual Inflation Rate 3.0%
40,000
Calculation Results
20,000
Monthly Mortgage Repayment 500.62
Total Interest over Mortgage Period 50,187.07 0
Total Mortgage Repayment over Mortgage Period 150,187.07 Years 1 4 7 10 13 16 19 22 25 28
Net Disposable Income 750
Maximum Mortgage Qualification Amount 149,813.16 Increased Instalment - Interest Saving Amortization
Minimum Required Net Disposable Income 501 Increased Instalment
60,000
Interest Rate Safety 4.17%
50,000
Increased Instalment Repayment Amount 700.62
Total Adjusted Interest over Mortgage Period 29,510.37 40,000
Adjusted Mortgage Repayment Period (in months) 184.85
Adjusted Mortgage Repayment Period (in years) 15.40 30,000
Interest Saving 20,676.71
Present Value of Interest Saving 13,744.32 20,000
10,000
Monthly Mortgage Repayment @ 3.5% 500.62
Monthly Mortgage Repayment @ 5.5% 614.09
0
Monthly Difference 113.46
Years 1 4 7 10 13 16 19 22 25 28
Page 2 of 11
Net Disposable Income Calculation www.excel-skills.co.uk
Remuneration Operational Expenses Financing Expenses
Gross Salary 750 1 Rent Paid - 18 Mortgage Repayments - 42
Subsidies Received - 2 Water, Electricity & Gas - 19 Personal Loan Instalments - 43
Commission Received - 3 Council Tax - 20 Financing - Motor Vehicles - 44
Total Allowances - 4 Repairs & Maintenance - 21 Financing - Computers - 45
Other - 5 Levies Paid - 22 Financing - Furniture - 46
Total Gross Remuneration 750 Telephone / Mobile Phone / Internet - 23 Credit Card Repayments - 47
Deductions Insurance - Short Term - 24 Bank Charges - 48
Pension - 6 Insurance - Life - 25 Other - 49
Medical Aid - 7 Medical Costs - 26 Total Financing Expenses -
Retirement Annuity - 8 Investments - Retirement Annuities - 27
Income Tax (PAYE) - 9 Investments - Other - 28
National Insurance (NI) - 10 Donations - 29
Other Tax Deductions - 11 Education - 30
Other Deductions - 12 Fuel & Vehicle Maintenance - 31
Total Deductions - Television Rental - 32
Net Remuneration 750 13 Memberships - 33
Subscriptions - 34
Other Income Domestic Wages - 35
Dividend Income - 14 Gardening - 36
Rental Income - 15 Groceries - 37
Maintenance - 16 Clothing - 38
Monthly Pension - 17 Entertainment - 39
Total Other Income - Maintenance Payments - 40
Other - 41
Total Operational Expenses -
Net Disposable Income Calculation
Net Remuneration 750
Add: Other Income -
Less: Operational Expenses -
Less: Financing Expenses -
Net Disposable Income 750
Input Field Guidance
1 Enter the combined monthly gross household salaries
2 Enter total of all subsidies received as part of remuneration, e.g.. housing subsidy
3 Enter the average monthly commission received
4 Enter the total monthly allowances received as part of remuneration, e.g.. travel allowance, mobile phone allowance, etc.
5 Enter the total of any other remuneration received
6 Enter the total monthly pension fund deductions
7 Enter the total monthly medical aid deductions
8 Enter the total monthly retirement annuity contributions
9 Enter the total monthly income tax deducted
10 Enter the total NI deducted
Page 3 of 11
Net Disposable Income Calculation www.excel-skills.co.uk
11 Enter the total of any other income tax deductions
12 Enter the total of all other deductions, e.g.. funeral plan
13 This amount represents the monthly combined net remuneration
14 Enter the average monthly dividend income
15 Enter the monthly average rental income received from existing buy to let properties
16 Enter monthly maintenance amount received from a previous spouse
17 Enter the total monthly pension received
18 Enter the total monthly rent paid. If a primary residence is being acquired and a property is currently being rented, the rent amount should be excluded from the calculation
19 Enter the monthly average for water, electricity and gas payments
20 Enter the monthly council tax paid on properties owned
21 Enter the average monthly repairs and maintenance on existing properties
22 Enter the monthly levy payable to a body corporate for properties that form part of a complex
23 Enter the average monthly telephone, mobile phone and internet expense
24 Enter the current monthly insurance premium
25 Enter the current monthly life insurance premium
26 Enter the average monthly medical costs that are not covered by a medical aid
27 Enter the total monthly premiums associated with retirement annuities
28 Enter the average monthly payments relating to other investments
29 Enter the average monthly donations amount
30 Enter the average monthly education cost, e.g.. school fees
31 Enter the average monthly vehicle fuel and maintenance costs
32 Enter the monthly subscription fee applicable to television / cable rentals
33 Enter the monthly average membership cost, e.g.. gym membership
34 Enter the total monthly subscription fees applicable to newspapers, magazines, etc.
35 Enter the total monthly domestic wages, e.g.. housekeeper wages, gardener wages, etc.
36 Enter the average monthly cost associated with gardening services / landscaping
37 Enter the average monthly grocery spend
38 Enter the average monthly clothing spend
39 Enter the average monthly cost associated with entertainment, take-outs and restaurants
40 Enter a monthly total for maintenance payments relating to estranged spouses and dependents
41 Enter total monthly cost of any other expense items that do not form part of any of the other categories
42 Enter a monthly total for existing mortgage repayments
43 Enter a monthly total for personal loan repayments
44 Enter a monthly total for motor finance repayments
45 Enter a monthly total for computer finance repayments
46 Enter a monthly total for furniture finance repayments
47 Enter a monthly total for credit card repayments
48 Enter the average monthly combined bank charges
49 Enter a total for any other financing payments that do not form part of the other financing cost categories
Note: If you're experiencing any difficulty in completing this spreadsheet, we recommend using our Personal Finance template to analyze your monthly household expenses.
Page 4 of 11
Annual Amortization Table www.excel-skills.co.uk
Opening Loan Interest Closing % Capital
Years Balance Repayment Charged Capital Repaid Balance Outstanding
1 100,000.00 6,007.48 3,459.38 2,548.10 97,451.90 97.5%
2 97,451.90 6,007.48 3,368.75 2,638.73 94,813.17 94.8%
3 94,813.17 6,007.48 3,274.90 2,732.58 92,080.59 92.1%
4 92,080.59 6,007.48 3,177.71 2,829.77 89,250.82 89.3%
5 89,250.82 6,007.48 3,077.07 2,930.42 86,320.40 86.3%
6 86,320.40 6,007.48 2,972.84 3,034.64 83,285.76 83.3%
7 83,285.76 6,007.48 2,864.91 3,142.58 80,143.19 80.1%
8 80,143.19 6,007.48 2,753.14 3,254.35 76,888.84 76.9%
9 76,888.84 6,007.48 2,637.39 3,370.09 73,518.74 73.5%
10 73,518.74 6,007.48 2,517.52 3,489.96 70,028.79 70.0%
11 70,028.79 6,007.48 2,393.40 3,614.09 66,414.70 66.4%
12 66,414.70 6,007.48 2,264.86 3,742.63 62,672.07 62.7%
13 62,672.07 6,007.48 2,131.74 3,875.74 58,796.33 58.8%
14 58,796.33 6,007.48 1,993.89 4,013.59 54,782.74 54.8%
15 54,782.74 6,007.48 1,851.14 4,156.34 50,626.40 50.6%
16 50,626.40 6,007.48 1,703.31 4,304.17 46,322.23 46.3%
17 46,322.23 6,007.48 1,550.23 4,457.26 41,864.98 41.9%
18 41,864.98 6,007.48 1,391.70 4,615.79 37,249.19 37.2%
19 37,249.19 6,007.48 1,227.53 4,779.96 32,469.24 32.5%
20 32,469.24 6,007.48 1,057.52 4,949.96 27,519.27 27.5%
21 27,519.27 6,007.48 881.46 5,126.02 22,393.25 22.4%
22 22,393.25 6,007.48 699.15 5,308.34 17,084.92 17.1%
23 17,084.92 6,007.48 510.35 5,497.14 11,587.78 11.6%
24 11,587.78 6,007.48 314.83 5,692.65 5,895.12 5.9%
25 5,895.12 6,007.48 112.36 5,895.12 - 0.0%
26 - - - - - 0.0%
27 - - - - - 0.0%
28 - - - - - 0.0%
29 - - - - - 0.0%
30 - - - - - 0.0%
Page 5 of 11
Monthly Amortization Table www.excel-skills.co.uk
Repayment Opening Loan Interest Closing % Capital
Number Balance Repayment Charged Capital Repaid Balance Outstanding
1 100,000.00 500.62 291.67 208.96 99,791.04 99.79%
2 99,791.04 500.62 291.06 209.57 99,581.48 99.58%
3 99,581.48 500.62 290.45 210.18 99,371.30 99.37%
4 99,371.30 500.62 289.83 210.79 99,160.51 99.16%
5 99,160.51 500.62 289.22 211.41 98,949.10 98.95%
6 98,949.10 500.62 288.60 212.02 98,737.08 98.74%
7 98,737.08 500.62 287.98 212.64 98,524.44 98.52%
8 98,524.44 500.62 287.36 213.26 98,311.18 98.31%
9 98,311.18 500.62 286.74 213.88 98,097.30 98.10%
10 98,097.30 500.62 286.12 214.51 97,882.79 97.88%
11 97,882.79 500.62 285.49 215.13 97,667.66 97.67%
12 97,667.66 500.62 284.86 215.76 97,451.90 97.45%
13 97,451.90 500.62 284.23 216.39 97,235.51 97.24%
14 97,235.51 500.62 283.60 217.02 97,018.49 97.02%
15 97,018.49 500.62 282.97 217.65 96,800.84 96.80%
16 96,800.84 500.62 282.34 218.29 96,582.55 96.58%
17 96,582.55 500.62 281.70 218.92 96,363.63 96.36%
18 96,363.63 500.62 281.06 219.56 96,144.06 96.14%
19 96,144.06 500.62 280.42 220.20 95,923.86 95.92%
20 95,923.86 500.62 279.78 220.85 95,703.01 95.70%
21 95,703.01 500.62 279.13 221.49 95,481.52 95.48%
22 95,481.52 500.62 278.49 222.14 95,259.39 95.26%
23 95,259.39 500.62 277.84 222.78 95,036.60 95.04%
24 95,036.60 500.62 277.19 223.43 94,813.17 94.81%
25 94,813.17 500.62 276.54 224.09 94,589.09 94.59%
26 94,589.09 500.62 275.88 224.74 94,364.35 94.36%
27 94,364.35 500.62 275.23 225.39 94,138.95 94.14%
28 94,138.95 500.62 274.57 226.05 93,912.90 93.91%
29 93,912.90 500.62 273.91 226.71 93,686.19 93.69%
30 93,686.19 500.62 273.25 227.37 93,458.82 93.46%
31 93,458.82 500.62 272.59 228.04 93,230.78 93.23%
32 93,230.78 500.62 271.92 228.70 93,002.08 93.00%
33 93,002.08 500.62 271.26 229.37 92,772.71 92.77%
34 92,772.71 500.62 270.59 230.04 92,542.68 92.54%
35 92,542.68 500.62 269.92 230.71 92,311.97 92.31%
36 92,311.97 500.62 269.24 231.38 92,080.59 92.08%
37 92,080.59 500.62 268.57 232.06 91,848.53 91.85%
38 91,848.53 500.62 267.89 232.73 91,615.80 91.62%
39 91,615.80 500.62 267.21 233.41 91,382.39 91.38%
40 91,382.39 500.62 266.53 234.09 91,148.30 91.15%
41 91,148.30 500.62 265.85 234.77 90,913.53 90.91%
42 90,913.53 500.62 265.16 235.46 90,678.07 90.68%
43 90,678.07 500.62 264.48 236.15 90,441.92 90.44%
44 90,441.92 500.62 263.79 236.83 90,205.09 90.21%
45 90,205.09 500.62 263.10 237.53 89,967.56 89.97%
46 89,967.56 500.62 262.41 238.22 89,729.34 89.73%
47 89,729.34 500.62 261.71 238.91 89,490.43 89.49%
48 89,490.43 500.62 261.01 239.61 89,250.82 89.25%
49 89,250.82 500.62 260.31 240.31 89,010.51 89.01%
50 89,010.51 500.62 259.61 241.01 88,769.50 88.77%
51 88,769.50 500.62 258.91 241.71 88,527.79 88.53%
52 88,527.79 500.62 258.21 242.42 88,285.37 88.29%
53 88,285.37 500.62 257.50 243.12 88,042.25 88.04%
54 88,042.25 500.62 256.79 243.83 87,798.41 87.80%
55 87,798.41 500.62 256.08 244.54 87,553.87 87.55%
56 87,553.87 500.62 255.37 245.26 87,308.61 87.31%
57 87,308.61 500.62 254.65 245.97 87,062.64 87.06%
58 87,062.64 500.62 253.93 246.69 86,815.95 86.82%
59 86,815.95 500.62 253.21 247.41 86,568.54 86.57%
60 86,568.54 500.62 252.49 248.13 86,320.40 86.32%
61 86,320.40 500.62 251.77 248.86 86,071.55 86.07%
62 86,071.55 500.62 251.04 249.58 85,821.97 85.82%
Page 6 of 11
Monthly Amortization Table www.excel-skills.co.uk
Repayment Opening Loan Interest Closing % Capital
Number Balance Repayment Charged Capital Repaid Balance Outstanding
63 85,821.97 500.62 250.31 250.31 85,571.66 85.57%
64 85,571.66 500.62 249.58 251.04 85,320.62 85.32%
65 85,320.62 500.62 248.85 251.77 85,068.85 85.07%
66 85,068.85 500.62 248.12 252.51 84,816.34 84.82%
67 84,816.34 500.62 247.38 253.24 84,563.10 84.56%
68 84,563.10 500.62 246.64 253.98 84,309.12 84.31%
69 84,309.12 500.62 245.90 254.72 84,054.39 84.05%
70 84,054.39 500.62 245.16 255.46 83,798.93 83.80%
71 83,798.93 500.62 244.41 256.21 83,542.72 83.54%
72 83,542.72 500.62 243.67 256.96 83,285.76 83.29%
73 83,285.76 500.62 242.92 257.71 83,028.05 83.03%
74 83,028.05 500.62 242.17 258.46 82,769.60 82.77%
75 82,769.60 500.62 241.41 259.21 82,510.38 82.51%
76 82,510.38 500.62 240.66 259.97 82,250.42 82.25%
77 82,250.42 500.62 239.90 260.73 81,989.69 81.99%
78 81,989.69 500.62 239.14 261.49 81,728.20 81.73%
79 81,728.20 500.62 238.37 262.25 81,465.95 81.47%
80 81,465.95 500.62 237.61 263.01 81,202.94 81.20%
81 81,202.94 500.62 236.84 263.78 80,939.16 80.94%
82 80,939.16 500.62 236.07 264.55 80,674.61 80.67%
83 80,674.61 500.62 235.30 265.32 80,409.28 80.41%
84 80,409.28 500.62 234.53 266.10 80,143.19 80.14%
85 80,143.19 500.62 233.75 266.87 79,876.31 79.88%
86 79,876.31 500.62 232.97 267.65 79,608.66 79.61%
87 79,608.66 500.62 232.19 268.43 79,340.23 79.34%
88 79,340.23 500.62 231.41 269.21 79,071.02 79.07%
89 79,071.02 500.62 230.62 270.00 78,801.02 78.80%
90 78,801.02 500.62 229.84 270.79 78,530.23 78.53%
91 78,530.23 500.62 229.05 271.58 78,258.65 78.26%
92 78,258.65 500.62 228.25 272.37 77,986.28 77.99%
93 77,986.28 500.62 227.46 273.16 77,713.12 77.71%
94 77,713.12 500.62 226.66 273.96 77,439.16 77.44%
95 77,439.16 500.62 225.86 274.76 77,164.40 77.16%
96 77,164.40 500.62 225.06 275.56 76,888.84 76.89%
97 76,888.84 500.62 224.26 276.36 76,612.47 76.61%
98 76,612.47 500.62 223.45 277.17 76,335.30 76.34%
99 76,335.30 500.62 222.64 277.98 76,057.33 76.06%
100 76,057.33 500.62 221.83 278.79 75,778.54 75.78%
101 75,778.54 500.62 221.02 279.60 75,498.93 75.50%
102 75,498.93 500.62 220.21 280.42 75,218.51 75.22%
103 75,218.51 500.62 219.39 281.24 74,937.28 74.94%
104 74,937.28 500.62 218.57 282.06 74,655.22 74.66%
105 74,655.22 500.62 217.74 282.88 74,372.34 74.37%
106 74,372.34 500.62 216.92 283.70 74,088.64 74.09%
107 74,088.64 500.62 216.09 284.53 73,804.11 73.80%
108 73,804.11 500.62 215.26 285.36 73,518.74 73.52%
109 73,518.74 500.62 214.43 286.19 73,232.55 73.23%
110 73,232.55 500.62 213.59 287.03 72,945.52 72.95%
111 72,945.52 500.62 212.76 287.87 72,657.66 72.66%
112 72,657.66 500.62 211.92 288.71 72,368.95 72.37%
113 72,368.95 500.62 211.08 289.55 72,079.40 72.08%
114 72,079.40 500.62 210.23 290.39 71,789.01 71.79%
115 71,789.01 500.62 209.38 291.24 71,497.77 71.50%
116 71,497.77 500.62 208.54 292.09 71,205.68 71.21%
117 71,205.68 500.62 207.68 292.94 70,912.74 70.91%
118 70,912.74 500.62 206.83 293.79 70,618.95 70.62%
119 70,618.95 500.62 205.97 294.65 70,324.30 70.32%
120 70,324.30 500.62 205.11 295.51 70,028.79 70.03%
121 70,028.79 500.62 204.25 296.37 69,732.41 69.73%
122 69,732.41 500.62 203.39 297.24 69,435.18 69.44%
123 69,435.18 500.62 202.52 298.10 69,137.07 69.14%
124 69,137.07 500.62 201.65 298.97 68,838.10 68.84%
Page 7 of 11
Monthly Amortization Table www.excel-skills.co.uk
Repayment Opening Loan Interest Closing % Capital
Number Balance Repayment Charged Capital Repaid Balance Outstanding
125 68,838.10 500.62 200.78 299.85 68,538.25 68.54%
126 68,538.25 500.62 199.90 300.72 68,237.53 68.24%
127 68,237.53 500.62 199.03 301.60 67,935.93 67.94%
128 67,935.93 500.62 198.15 302.48 67,633.46 67.63%
129 67,633.46 500.62 197.26 303.36 67,330.10 67.33%
130 67,330.10 500.62 196.38 304.24 67,025.85 67.03%
131 67,025.85 500.62 195.49 305.13 66,720.72 66.72%
132 66,720.72 500.62 194.60 306.02 66,414.70 66.41%
133 66,414.70 500.62 193.71 306.91 66,107.79 66.11%
134 66,107.79 500.62 192.81 307.81 65,799.98 65.80%
135 65,799.98 500.62 191.92 308.71 65,491.27 65.49%
136 65,491.27 500.62 191.02 309.61 65,181.66 65.18%
137 65,181.66 500.62 190.11 310.51 64,871.15 64.87%
138 64,871.15 500.62 189.21 311.42 64,559.74 64.56%
139 64,559.74 500.62 188.30 312.32 64,247.41 64.25%
140 64,247.41 500.62 187.39 313.24 63,934.18 63.93%
141 63,934.18 500.62 186.47 314.15 63,620.03 63.62%
142 63,620.03 500.62 185.56 315.07 63,304.96 63.30%
143 63,304.96 500.62 184.64 315.98 62,988.98 62.99%
144 62,988.98 500.62 183.72 316.91 62,672.07 62.67%
145 62,672.07 500.62 182.79 317.83 62,354.24 62.35%
146 62,354.24 500.62 181.87 318.76 62,035.49 62.04%
147 62,035.49 500.62 180.94 319.69 61,715.80 61.72%
148 61,715.80 500.62 180.00 320.62 61,395.18 61.40%
149 61,395.18 500.62 179.07 321.55 61,073.63 61.07%
150 61,073.63 500.62 178.13 322.49 60,751.13 60.75%
151 60,751.13 500.62 177.19 323.43 60,427.70 60.43%
152 60,427.70 500.62 176.25 324.38 60,103.33 60.10%
153 60,103.33 500.62 175.30 325.32 59,778.00 59.78%
154 59,778.00 500.62 174.35 326.27 59,451.73 59.45%
155 59,451.73 500.62 173.40 327.22 59,124.51 59.12%
156 59,124.51 500.62 172.45 328.18 58,796.33 58.80%
157 58,796.33 500.62 171.49 329.13 58,467.20 58.47%
158 58,467.20 500.62 170.53 330.09 58,137.10 58.14%
159 58,137.10 500.62 169.57 331.06 57,806.05 57.81%
160 57,806.05 500.62 168.60 332.02 57,474.02 57.47%
161 57,474.02 500.62 167.63 332.99 57,141.03 57.14%
162 57,141.03 500.62 166.66 333.96 56,807.07 56.81%
163 56,807.07 500.62 165.69 334.94 56,472.13 56.47%
164 56,472.13 500.62 164.71 335.91 56,136.22 56.14%
165 56,136.22 500.62 163.73 336.89 55,799.33 55.80%
166 55,799.33 500.62 162.75 337.88 55,461.45 55.46%
167 55,461.45 500.62 161.76 338.86 55,122.59 55.12%
168 55,122.59 500.62 160.77 339.85 54,782.74 54.78%
169 54,782.74 500.62 159.78 340.84 54,441.90 54.44%
170 54,441.90 500.62 158.79 341.83 54,100.07 54.10%
171 54,100.07 500.62 157.79 342.83 53,757.24 53.76%
172 53,757.24 500.62 156.79 343.83 53,413.40 53.41%
173 53,413.40 500.62 155.79 344.83 53,068.57 53.07%
174 53,068.57 500.62 154.78 345.84 52,722.73 52.72%
175 52,722.73 500.62 153.77 346.85 52,375.88 52.38%
176 52,375.88 500.62 152.76 347.86 52,028.02 52.03%
177 52,028.02 500.62 151.75 348.88 51,679.14 51.68%
178 51,679.14 500.62 150.73 349.89 51,329.25 51.33%
179 51,329.25 500.62 149.71 350.91 50,978.34 50.98%
180 50,978.34 500.62 148.69 351.94 50,626.40 50.63%
181 50,626.40 500.62 147.66 352.96 50,273.44 50.27%
182 50,273.44 500.62 146.63 353.99 49,919.45 49.92%
183 49,919.45 500.62 145.60 355.03 49,564.42 49.56%
184 49,564.42 500.62 144.56 356.06 49,208.36 49.21%
185 49,208.36 500.62 143.52 357.10 48,851.26 48.85%
186 48,851.26 500.62 142.48 358.14 48,493.12 48.49%
Page 8 of 11
Monthly Amortization Table www.excel-skills.co.uk
Repayment Opening Loan Interest Closing % Capital
Number Balance Repayment Charged Capital Repaid Balance Outstanding
187 48,493.12 500.62 141.44 359.19 48,133.94 48.13%
188 48,133.94 500.62 140.39 360.23 47,773.70 47.77%
189 47,773.70 500.62 139.34 361.28 47,412.42 47.41%
190 47,412.42 500.62 138.29 362.34 47,050.08 47.05%
191 47,050.08 500.62 137.23 363.39 46,686.69 46.69%
192 46,686.69 500.62 136.17 364.45 46,322.23 46.32%
193 46,322.23 500.62 135.11 365.52 45,956.72 45.96%
194 45,956.72 500.62 134.04 366.58 45,590.13 45.59%
195 45,590.13 500.62 132.97 367.65 45,222.48 45.22%
196 45,222.48 500.62 131.90 368.72 44,853.76 44.85%
197 44,853.76 500.62 130.82 369.80 44,483.96 44.48%
198 44,483.96 500.62 129.74 370.88 44,113.08 44.11%
199 44,113.08 500.62 128.66 371.96 43,741.12 43.74%
200 43,741.12 500.62 127.58 373.05 43,368.07 43.37%
201 43,368.07 500.62 126.49 374.13 42,993.94 42.99%
202 42,993.94 500.62 125.40 375.22 42,618.71 42.62%
203 42,618.71 500.62 124.30 376.32 42,242.39 42.24%
204 42,242.39 500.62 123.21 377.42 41,864.98 41.86%
205 41,864.98 500.62 122.11 378.52 41,486.46 41.49%
206 41,486.46 500.62 121.00 379.62 41,106.84 41.11%
207 41,106.84 500.62 119.89 380.73 40,726.11 40.73%
208 40,726.11 500.62 118.78 381.84 40,344.27 40.34%
209 40,344.27 500.62 117.67 382.95 39,961.32 39.96%
210 39,961.32 500.62 116.55 384.07 39,577.25 39.58%
211 39,577.25 500.62 115.43 385.19 39,192.06 39.19%
212 39,192.06 500.62 114.31 386.31 38,805.75 38.81%
213 38,805.75 500.62 113.18 387.44 38,418.31 38.42%
214 38,418.31 500.62 112.05 388.57 38,029.74 38.03%
215 38,029.74 500.62 110.92 389.70 37,640.03 37.64%
216 37,640.03 500.62 109.78 390.84 37,249.19 37.25%
217 37,249.19 500.62 108.64 391.98 36,857.21 36.86%
218 36,857.21 500.62 107.50 393.12 36,464.09 36.46%
219 36,464.09 500.62 106.35 394.27 36,069.82 36.07%
220 36,069.82 500.62 105.20 395.42 35,674.40 35.67%
221 35,674.40 500.62 104.05 396.57 35,277.82 35.28%
222 35,277.82 500.62 102.89 397.73 34,880.09 34.88%
223 34,880.09 500.62 101.73 398.89 34,481.21 34.48%
224 34,481.21 500.62 100.57 400.05 34,081.15 34.08%
225 34,081.15 500.62 99.40 401.22 33,679.93 33.68%
226 33,679.93 500.62 98.23 402.39 33,277.54 33.28%
227 33,277.54 500.62 97.06 403.56 32,873.98 32.87%
228 32,873.98 500.62 95.88 404.74 32,469.24 32.47%
229 32,469.24 500.62 94.70 405.92 32,063.31 32.06%
230 32,063.31 500.62 93.52 407.11 31,656.21 31.66%
231 31,656.21 500.62 92.33 408.29 31,247.92 31.25%
232 31,247.92 500.62 91.14 409.48 30,838.43 30.84%
233 30,838.43 500.62 89.95 410.68 30,427.75 30.43%
234 30,427.75 500.62 88.75 411.88 30,015.88 30.02%
235 30,015.88 500.62 87.55 413.08 29,602.80 29.60%
236 29,602.80 500.62 86.34 414.28 29,188.52 29.19%
237 29,188.52 500.62 85.13 415.49 28,773.03 28.77%
238 28,773.03 500.62 83.92 416.70 28,356.33 28.36%
239 28,356.33 500.62 82.71 417.92 27,938.41 27.94%
240 27,938.41 500.62 81.49 419.14 27,519.27 27.52%
241 27,519.27 500.62 80.26 420.36 27,098.91 27.10%
242 27,098.91 500.62 79.04 421.59 26,677.33 26.68%
243 26,677.33 500.62 77.81 422.81 26,254.51 26.25%
244 26,254.51 500.62 76.58 424.05 25,830.46 25.83%
245 25,830.46 500.62 75.34 425.28 25,405.18 25.41%
246 25,405.18 500.62 74.10 426.53 24,978.66 24.98%
247 24,978.66 500.62 72.85 427.77 24,550.89 24.55%
248 24,550.89 500.62 71.61 429.02 24,121.87 24.12%
Page 9 of 11
Monthly Amortization Table www.excel-skills.co.uk
Repayment Opening Loan Interest Closing % Capital
Number Balance Repayment Charged Capital Repaid Balance Outstanding
249 24,121.87 500.62 70.36 430.27 23,691.60 23.69%
250 23,691.60 500.62 69.10 431.52 23,260.08 23.26%
251 23,260.08 500.62 67.84 432.78 22,827.30 22.83%
252 22,827.30 500.62 66.58 434.04 22,393.25 22.39%
253 22,393.25 500.62 65.31 435.31 21,957.94 21.96%
254 21,957.94 500.62 64.04 436.58 21,521.36 21.52%
255 21,521.36 500.62 62.77 437.85 21,083.51 21.08%
256 21,083.51 500.62 61.49 439.13 20,644.38 20.64%
257 20,644.38 500.62 60.21 440.41 20,203.97 20.20%
258 20,203.97 500.62 58.93 441.70 19,762.27 19.76%
259 19,762.27 500.62 57.64 442.98 19,319.29 19.32%
260 19,319.29 500.62 56.35 444.28 18,875.01 18.88%
261 18,875.01 500.62 55.05 445.57 18,429.44 18.43%
262 18,429.44 500.62 53.75 446.87 17,982.57 17.98%
263 17,982.57 500.62 52.45 448.17 17,534.40 17.53%
264 17,534.40 500.62 51.14 449.48 17,084.92 17.08%
265 17,084.92 500.62 49.83 450.79 16,634.12 16.63%
266 16,634.12 500.62 48.52 452.11 16,182.02 16.18%
267 16,182.02 500.62 47.20 453.43 15,728.59 15.73%
268 15,728.59 500.62 45.88 454.75 15,273.84 15.27%
269 15,273.84 500.62 44.55 456.07 14,817.77 14.82%
270 14,817.77 500.62 43.22 457.41 14,360.36 14.36%
271 14,360.36 500.62 41.88 458.74 13,901.62 13.90%
272 13,901.62 500.62 40.55 460.08 13,441.55 13.44%
273 13,441.55 500.62 39.20 461.42 12,980.13 12.98%
274 12,980.13 500.62 37.86 462.76 12,517.36 12.52%
275 12,517.36 500.62 36.51 464.11 12,053.25 12.05%
276 12,053.25 500.62 35.16 465.47 11,587.78 11.59%
277 11,587.78 500.62 33.80 466.83 11,120.95 11.12%
278 11,120.95 500.62 32.44 468.19 10,652.77 10.65%
279 10,652.77 500.62 31.07 469.55 10,183.21 10.18%
280 10,183.21 500.62 29.70 470.92 9,712.29 9.71%
281 9,712.29 500.62 28.33 472.30 9,239.99 9.24%
282 9,239.99 500.62 26.95 473.67 8,766.32 8.77%
283 8,766.32 500.62 25.57 475.06 8,291.26 8.29%
284 8,291.26 500.62 24.18 476.44 7,814.82 7.81%
285 7,814.82 500.62 22.79 477.83 7,336.99 7.34%
286 7,336.99 500.62 21.40 479.22 6,857.77 6.86%
287 6,857.77 500.62 20.00 480.62 6,377.15 6.38%
288 6,377.15 500.62 18.60 482.02 5,895.12 5.90%
289 5,895.12 500.62 17.19 483.43 5,411.69 5.41%
290 5,411.69 500.62 15.78 484.84 4,926.86 4.93%
291 4,926.86 500.62 14.37 486.25 4,440.60 4.44%
292 4,440.60 500.62 12.95 487.67 3,952.93 3.95%
293 3,952.93 500.62 11.53 489.09 3,463.84 3.46%
294 3,463.84 500.62 10.10 490.52 2,973.32 2.97%
295 2,973.32 500.62 8.67 491.95 2,481.36 2.48%
296 2,481.36 500.62 7.24 493.39 1,987.98 1.99%
297 1,987.98 500.62 5.80 494.83 1,493.15 1.49%
298 1,493.15 500.62 4.36 496.27 996.88 1.00%
299 996.88 500.62 2.91 497.72 499.17 0.50%
300 499.17 500.62 1.46 499.17 - 0.00%
301 - - - - - 0.00%
302 - - - - - 0.00%
303 - - - - - 0.00%
304 - - - - - 0.00%
305 - - - - - 0.00%
306 - - - - - 0.00%
307 - - - - - 0.00%
308 - - - - - 0.00%
309 - - - - - 0.00%
310 - - - - - 0.00%
Page 10 of 11
Monthly Amortization Table www.excel-skills.co.uk
Repayment Opening Loan Interest Closing % Capital
Number Balance Repayment Charged Capital Repaid Balance Outstanding
311 - - - - - 0.00%
312 - - - - - 0.00%
313 - - - - - 0.00%
314 - - - - - 0.00%
315 - - - - - 0.00%
316 - - - - - 0.00%
317 - - - - - 0.00%
318 - - - - - 0.00%
319 - - - - - 0.00%
320 - - - - - 0.00%
321 - - - - - 0.00%
322 - - - - - 0.00%
323 - - - - - 0.00%
324 - - - - - 0.00%
325 - - - - - 0.00%
326 - - - - - 0.00%
327 - - - - - 0.00%
328 - - - - - 0.00%
329 - - - - - 0.00%
330 - - - - - 0.00%
331 - - - - - 0.00%
332 - - - - - 0.00%
333 - - - - - 0.00%
334 - - - - - 0.00%
335 - - - - - 0.00%
336 - - - - - 0.00%
337 - - - - - 0.00%
338 - - - - - 0.00%
339 - - - - - 0.00%
340 - - - - - 0.00%
341 - - - - - 0.00%
342 - - - - - 0.00%
343 - - - - - 0.00%
344 - - - - - 0.00%
345 - - - - - 0.00%
346 - - - - - 0.00%
347 - - - - - 0.00%
348 - - - - - 0.00%
349 - - - - - 0.00%
350 - - - - - 0.00%
351 - - - - - 0.00%
352 - - - - - 0.00%
353 - - - - - 0.00%
354 - - - - - 0.00%
355 - - - - - 0.00%
356 - - - - - 0.00%
357 - - - - - 0.00%
358 - - - - - 0.00%
359 - - - - - 0.00%
360 - - - - - 0.00%
50,187.07
Page 11 of 11
