Mortgage Illustration
Mortgage Illustration for (enter name): Mr & Mrs Example
Enter mortgage scheme name: Example Scheme
Enter estimated property value: £135,000
Loan required: £100,000 Loan-to-Value (LTV) is 74.1%
Enter R for repayment, M for mixed: r Capital repayment mortgage £0 i or blank for Interest-only
Balance of equity: £35,000 This is your deposit, if loan is for house purchase
Fees Enter y if fee/cost is added to loan, else n
Valuation fee: £0 n Notes
Application fee: £100 n
Mortgage Indemnity Guarantee premium: £0 y
Legal fees (mortgage only): £0 n
Other mortgage related fees: £0 n
Term and Interest rates
Total advance (including any fees added to the loan): £100,000
Full term in years: 25 years
Cashback: £0 payable with advance
Initial interest rate: 3.500 % pa fixed/variable?
Number of rests per annum: 12 (ie Monthly rests)
Initial period: 24 months
Estimated nominal interest rate after initial period: 6.000 % pa fixed/variable?
Step two period - if any: more months
Final estimated nominal interest rate: % pa fixed/variable?
Monthly Cost Summary
Period: - Initial 24 mths Next 0 mths Final 276 mths Notes - if any
Initial monthly payments: £500.62 £634.16
Compulsory insurance - if any: £0.00 £0.00 £0.00
Enter life Insurance premiums - if any: £0.00 £0.00 £0.00
Enter savings scheme payments - if any: £0.00 £0.00 £0.00
Enter buildings & contents Insurance - estimate: £0.00 £0.00 £0.00
Enter any other premiums if applicable: £0.00 £0.00 £0.00
Total monthly costs: £500.62 £0.00 £634.16
Early fixed redemption fee - if any: £50 £50.00 total redemption fee.
Typical overall APR measured over 25 years: 5.7% Calculated in accordance with the rule changes in April 2000.
Total charge for credit over 25 years: £87,142
Total amount payable over 25 years: £187,142
Internal Rate of Return (IRR) measured over 6 years: 5.187%
The APR is measured over the full term. The IRR (Internal Rate of Return) is like the APR but measured over a more realistic life,
typically six years. To change it, change assumed mortgage life in the "Comparator" tab
Notes
Scheme: Example Scheme
Introducer
Today's date: 3-Dec-11
Mortgage Payment Schedule
This schedule reflects data entered for scheme A on the Loan Comparator sheet.
The first seven years are shown month by month, then annual thereafter.
Initial Advance £100,000 Repayment Mortgage
Full Term: 25
Final Loan: £0
Repayment rests pa: 12 Every year
Year Month Nominal Monthly Interest Capital Debt at
interest % pa payment element element month end
1 1 3.500% £500.62 £291.67 £208.96 £99,791.04
2 3.500% £500.62 £291.06 £209.57 £99,581.48
3 3.500% £500.62 £290.45 £210.18 £99,371.30
4 3.500% £500.62 £289.83 £210.79 £99,160.51
5 3.500% £500.62 £289.22 £211.41 £98,949.10
6 3.500% £500.62 £288.60 £212.02 £98,737.08
7 3.500% £500.62 £287.98 £212.64 £98,524.44
8 3.500% £500.62 £287.36 £213.26 £98,311.18
9 3.500% £500.62 £286.74 £213.88 £98,097.30
10 3.500% £500.62 £286.12 £214.51 £97,882.79
11 3.500% £500.62 £285.49 £215.13 £97,667.66
12 3.500% £500.62 £284.86 £215.76 £97,451.90
2 13 3.500% £500.62 £284.23 £216.39 £97,235.51
14 3.500% £500.62 £283.60 £217.02 £97,018.49
15 3.500% £500.62 £282.97 £217.65 £96,800.84
16 3.500% £500.62 £282.34 £218.29 £96,582.55
17 3.500% £500.62 £281.70 £218.92 £96,363.63
18 3.500% £500.62 £281.06 £219.56 £96,144.06
19 3.500% £500.62 £280.42 £220.20 £95,923.86
20 3.500% £500.62 £279.78 £220.85 £95,703.01
21 3.500% £500.62 £279.13 £221.49 £95,481.52
22 3.500% £500.62 £278.49 £222.14 £95,259.39
23 3.500% £500.62 £277.84 £222.78 £95,036.60
24 3.500% £500.62 £277.19 £223.43 £94,813.17
3 25 6.000% £634.16 £474.07 £160.09 £94,653.08
26 6.000% £634.16 £473.27 £160.89 £94,492.19
27 6.000% £634.16 £472.46 £161.69 £94,330.50
28 6.000% £634.16 £471.65 £162.50 £94,167.99
29 6.000% £634.16 £470.84 £163.32 £94,004.68
30 6.000% £634.16 £470.02 £164.13 £93,840.55
31 6.000% £634.16 £469.20 £164.95 £93,675.59
32 6.000% £634.16 £468.38 £165.78 £93,509.82
33 6.000% £634.16 £467.55 £166.61 £93,343.21
34 6.000% £634.16 £466.72 £167.44 £93,175.77
35 6.000% £634.16 £465.88 £168.28 £93,007.50
36 6.000% £634.16 £465.04 £169.12 £92,838.38
4 37 6.000% £634.16 £464.19 £169.96 £92,668.41
38 6.000% £634.16 £463.34 £170.81 £92,497.60
39 6.000% £634.16 £462.49 £171.67 £92,325.93
40 6.000% £634.16 £461.63 £172.53 £92,153.41
41 6.000% £634.16 £460.77 £173.39 £91,980.02
42 6.000% £634.16 £459.90 £174.26 £91,805.77
43 6.000% £634.16 £459.03 £175.13 £91,630.64
44 6.000% £634.16 £458.15 £176.00 £91,454.64
45 6.000% £634.16 £457.27 £176.88 £91,277.75
46 6.000% £634.16 £456.39 £177.77 £91,099.99
47 6.000% £634.16 £455.50 £178.66 £90,921.33
48 6.000% £634.16 £454.61 £179.55 £90,741.78
5 49 6.000% £634.16 £453.71 £180.45 £90,561.34
50 6.000% £634.16 £452.81 £181.35 £90,379.99
51 6.000% £634.16 £451.90 £182.26 £90,197.73
52 6.000% £634.16 £450.99 £183.17 £90,014.57
53 6.000% £634.16 £450.07 £184.08 £89,830.49
54 6.000% £634.16 £449.15 £185.00 £89,645.48
Year Month Nominal Monthly Interest Capital Debt at
interest % pa payment element element month end
55 6.000% £634.16 £448.23 £185.93 £89,459.55
56 6.000% £634.16 £447.30 £186.86 £89,272.70
57 6.000% £634.16 £446.36 £187.79 £89,084.91
58 6.000% £634.16 £445.42 £188.73 £88,896.17
59 6.000% £634.16 £444.48 £189.67 £88,706.50
60 6.000% £634.16 £443.53 £190.62 £88,515.88
6 61 6.000% £634.16 £442.58 £191.58 £88,324.30
62 6.000% £634.16 £441.62 £192.53 £88,131.77
63 6.000% £634.16 £440.66 £193.50 £87,938.27
64 6.000% £634.16 £439.69 £194.46 £87,743.81
65 6.000% £634.16 £438.72 £195.44 £87,548.37
66 6.000% £634.16 £437.74 £196.41 £87,351.96
67 6.000% £634.16 £436.76 £197.40 £87,154.56
68 6.000% £634.16 £435.77 £198.38 £86,956.18
69 6.000% £634.16 £434.78 £199.37 £86,756.81
70 6.000% £634.16 £433.78 £200.37 £86,556.43
71 6.000% £634.16 £432.78 £201.37 £86,355.06
72 6.000% £634.16 £431.78 £202.38 £86,152.68
7 73 6.000% £634.16 £430.76 £203.39 £85,949.29
74 6.000% £634.16 £429.75 £204.41 £85,744.88
75 6.000% £634.16 £428.72 £205.43 £85,539.45
76 6.000% £634.16 £427.70 £206.46 £85,332.99
77 6.000% £634.16 £426.66 £207.49 £85,125.50
78 6.000% £634.16 £425.63 £208.53 £84,916.97
79 6.000% £634.16 £424.58 £209.57 £84,707.40
80 6.000% £634.16 £423.54 £210.62 £84,496.79
81 6.000% £634.16 £422.48 £211.67 £84,285.11
82 6.000% £634.16 £421.43 £212.73 £84,072.38
83 6.000% £634.16 £420.36 £213.79 £83,858.59
84 6.000% £634.16 £419.29 £214.86 £83,643.73
Year Nominal Monthly Interest Capital Debt
only interest % pa payment element element at year
First month only end
8 96 6.000% £634.16 £418.22 £215.94 £80,980.03
9 108 6.000% £634.16 £404.90 £229.26 £78,152.04
10 120 6.000% £634.16 £390.76 £243.40 £75,149.62
11 132 6.000% £634.16 £375.75 £258.41 £71,962.03
12 144 6.000% £634.16 £359.81 £274.35 £68,577.83
13 156 6.000% £634.16 £342.89 £291.27 £64,984.90
14 168 6.000% £634.16 £324.92 £309.23 £61,170.36
15 180 6.000% £634.16 £305.85 £328.30 £57,120.55
16 192 6.000% £634.16 £285.60 £348.55 £52,820.96
17 204 6.000% £634.16 £264.10 £370.05 £48,256.18
18 216 6.000% £634.16 £241.28 £392.87 £43,409.86
19 228 6.000% £634.16 £217.05 £417.11 £38,264.62
20 240 6.000% £634.16 £191.32 £442.83 £32,802.04
21 252 6.000% £634.16 £164.01 £470.15 £27,002.53
22 264 6.000% £634.16 £135.01 £499.14 £20,845.33
23 276 6.000% £634.16 £104.23 £529.93 £14,308.36
24 288 6.000% £634.16 £71.54 £562.61 £7,368.21
25 300 6.000% £634.16 £36.84 £597.31 £0.00
26 312 6.000% £0.00 £0.00 £0.00 £0.00
27 324 6.000% £0.00 £0.00 £0.00 £0.00
28 336 6.000% £0.00 £0.00 £0.00 £0.00
29 348 6.000% £0.00 £0.00 £0.00 £0.00
30 360 6.000% £0.00 £0.00 £0.00 £0.00
31 372 6.000% £0.00 £0.00 £0.00 £0.00
32 384 6.000% £0.00 £0.00 £0.00 £0.00
33 396 6.000% £0.00 £0.00 £0.00 £0.00
34 408 6.000% £0.00 £0.00 £0.00 £0.00
35 420 6.000% £0.00 £0.00 £0.00 £0.00
Loan Comparator - calculate and compare two mortgage schemes
Scheme A figures entered in 'Illustration'. Enter figures for scheme B to compare it with scheme A: adjust blue figures if required.
Use View, Zoom for best screen layout See notes below
Change any blue figure
Loan required £100,000
Full term in years 25 the maximum period the mortgage can run
Assumed mortgage life in years 6 period for IRR calculation. 6 yrs typical. If full term, IRR = APR
R for repayment, M for mixed r Capital repayment mortgage £0 i or blank for Interest-only
Scheme A Example Scheme Scheme B Enter scheme name
Initial nominal interest rate pa 3.500 % pa with 12 rests pa 5.000 % pa with 1 rests pa
Rests per year (1 to 365) 12 pa (repayment loans only) 1 pa (repayment loans only)
Some loans have an initial period when the interest rate changes. If so, enter the term & rates below otherwise leave blank.
Initial rate changing after 24 months months
To a new interest rate of 6.000 % pa % pa
Changing again after a further 0 more months more months
To a final interest rate of 0.000 % pa % pa
Enter any initial costs Enter y if fee/cost is added to loan, else n
Valuation fee £0 n y
Application fee £100 n £100 n
Mortgage Indemnity Guarantee £0 y (If relevant) n (If relevant)
Legal fees (mortgage only) £0 n n
Other mortgage related costs £0 n n
Enter any initial refunds
Cashback £0
Other refunds £0
Enter any regular costs pm
Eg compulsory insurance pm £0.00 per month per month
Enter any redemption fees (Total £50 after 6 yrs, £0 at term end) (Total £50 after 6 yrs, £0 at term end)
Max term for redemption fee to apply 6 years (no fees thereafter) 6 years (no fees thereafter)
Fixed amount redemption fee £50 after 6 years £50 after 6 years
Redemption fee as months 0 months @ 6.000 % pa 0 months @ 5.000 % pa
Redemption fee as percentage 0 % of final capital owing of 0 % of final capital owing of
£86,153 in 6 years time £85,748 in 6 years time
Monthly payments & IRR over 6 years
Initial advance (including costs) £100,000 the actual amount lent £100,000 the actual amount lent
Initial mortgage payment £500.62 per month for 24 months £591.27 per month for 300 months
Mortgage payment interim stage per month for 0 more months per month for 0 more months
Mortgage payment final stage £634.16 per month thereafter per month thereafter
Total mortgage pmts over 6 yrs £42,454 capital repaid £13,797 £42,571 capital repaid £14,202
Total payments + costs over 6 yrs £42,604 Including cashbacks & all costs £42,671 Including cashbacks & all costs
Internal Rate of Return (IRR pa) 5.1872% <--Scheme A better 5.2057%
New (Apr 2000) APR is: - 5.7% measured over 25 years 5.2% measured over 25 years
Extra cashback to = other scheme -£86.55 -0.09 % of the loan requested £85.43 0.09 % of the loan requested
Equivalent to monthly payment of £1.40 per month for 72 months -£1.38 per month for 72 months
Summary: In 'cash now' terms, Scheme A is better by £85.
Consumer Credit Act requirements
Total Charge for Credit - TCC: - £87,142 £77,481
Total Amount Payable - TAP: - £187,142 £177,481
With this spreadsheet you can easily calculate payments and compare the true value-for-money for almost any mortgage scheme.
Some loans start with a special interest rate, which changes after a period, and sometimes there are two rate changes.
The best way to compare different loan schedules is by using the Internal Rate of Return - the IRR. This is a single, annual interest
rate that reflects every payment made on the loan, including fees and costs, regardless of how the payments are calculated.
It accurately reflects cashflow and its timing. A mortgage scheme with a lower IRR is better value-for-money. The total amount
paid is not as relevant, since timing is ignored. The new APR introduced in April 2000 is now calculated in the same way as
the IRR but is measured over the full term. The IRR calculated here can be measured over any period, accepting that most
mortgages are redeemed well before their full term - 6 years being typical.
You can enter figures for just one scheme in the "Illustration" tab, or two to compare results. The IRR, APR and the projected payments are illustrated.
A mixed loan (m) is where part is repayment and part is interest-only: if so, remember to enter the endowment part.
Note: A lower IRR implies better value-for-money, but there may be others factors about the mortgage which affect the final choice.
The 'Extra cashback' figures measure the scheme differences as a present value: entering this figure under 'initial refunds' will make
the IRR of each scheme identical. Alternatively, adding the equivalent monthly payment to 'regular costs' has the same effect.
If there are other subjective factors about a mortgage scheme you need to consider, such as flexible payments or fixed rates, then
you can use the extra cashback figure to more easily evaluate their worth.
It is possible for a scheme to have a higher APR than another scheme but with a lower IRR. This is because the IRR is usually
measured over a shorter term and so the effect of any early discount or cashback is enhanced.