Case 1 Analysis Tables

Analysis Table 1: Case 1 — Endowment Mortgage.

Analysis Table 2: Case 1 — Repayment Mortgage.

Analysis Table 3: Case 1 — An 18 Year Repayment Mortgage.

Note! Print the Tables separately. The Tables are not included when printing the Chapter.

Analysis Table 1

May 1991		Case 1: Endowment Mortgage				£35,000 Borrowed over 20 Years @ 11.85% p.a.						Analysis Table 1
A	B	C	D	E	F	G	H	I	J	K	L
Year	Gross Cash Flow Out	Interest Paid	Premium Paid	Interest Applicable for Tax Relief	Tax Relief on Interest	Tax Relief on Premium	Net Cash Flow Out	@ y% Internal Rate of Return				Tax Relief at 29% p.a.
								@13%	@13.5%	@13.15%	13.158%
								npv	npv	npv	npv
1	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	3664.81	3648.66	3659.95	3659.69	FV = P( 1 + i )n +£12,770 = £35,000 ( 1.1185 )20 +£12,770 i.e. FV = £341,461.00
2	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	3243.19	3214.68	3234.60	3234.14
3	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	2870.08	2832.32	2858.68	2858.07
4	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	2539.89	2495.43	2526.45	2525.74
5	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	2247.69	2198.62	2232.83	2232.05
6	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	1989.11	1937.11	1973.34	1972.50
7	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	1760.27	1706.70	1744.00	1743.14
8	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	1557.76	1503.70	1541.32	1540.45
9	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	1378.55	1324.85	1362.19	1361.33
10	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	1219.96	1167.27	1203.88	1203.03
11	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	1079.61	1028.43	1063.97	1063.14
12	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	955.41	906.11	940.32	939.52
13	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	845.49	798.33	831.04	830.27
14	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	748.22	703.38	734.46	733.73
15	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	662.14	619.71	649.10	648.41
16	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	585.97	546.00	573.66	573.01
17	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	518.56	481.06	506.99	506.38
18	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	458.90	423.84	448.07	447.50
19	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	406.11	373.43	396.00	395.47
20	5103.90	4147.50	956.40	3200.00	928.00	34.67	4141.23	359.39	329.01	349.98	349.48
					NPV = ?npv			29,091.11	28,238.64	28,830.83	28,817.05
					yields FV			335,219.41	355,435.67	341,152.31	341,471.75
											OK

Analysis Table 2

May 1991		Case 1: Endowment Mortgage				£35,000 Borrowed over 20 Years @ 11.85% p.a.						Analysis Table 2
A	B	C	D	E	F	G	H	I	J	K	L
Year	Gross Cash Flow Out	Interest Paid	Principal Repaid	Interest Applicable for Tax Relief	Tax Relief on Interest	Tax Relief on Premium	Net Cash Flow Out	@ x% Internal Rate of Return				Tax Relief at 29% p.a.
								@ 12%	@13%	@13.2%	13.18%
								npv	npv	npv	npv
1	4789.73	4147.50	494.27	3200.00	928.00	5.36	3856.37	3443.19	3412.72	3406.69	3407.29	FV = P( 1 + i )n = £35,000 ( 1.1185 )20 i.e. FV = £328,691.00
2	4789.73	4088.93	552.84	3200.00	928.00	5.36	3856.37	3074.27	3020.10	3009.44	3010.50
3	4789.73	4023.42	618.35	3200.00	928.00	5.36	3856.37	2744.89	2672.66	2658.52	2659.93
4	4789.73	3950.14	691.63	3160.11	916.43	5.36	3867.94	2458.15	2372.28	2355.56	2357.22
5	4789.73	3868.18	773.59	3094.54	897.42	5.36	3886.95	2205.56	2109.68	2091.11	2092.96
6	4789.73	3776.51	865.26	3021.21	876.15	5.36	3908.22	1980.03	1877.19	1857.38	1859.35
7	4789.73	3673.98	967.79	2939.18	852.36	5.36	3932.01	1778.64	1671.34	1650.78	1652.82
8	4789.73	3559.30	1082.47	2847.44	825.76	5.36	3958.61	1598.82	1489.07	1468.15	1470.23
9	4789.73	3431.03	1210.74	2744.82	796.00	5.36	3988.37	1438.25	1327.67	1306.71	1308.78
10	4789.73	3287.55	1354.22	2630.04	762.71	5.36	4021.66	1294.87	1184.73	1163.97	1166.03
11	4789.73	3127.08	1514.69	2501.66	725.48	5.36	4058.89	1166.83	1058.14	1037.76	1039.78
12	4789.73	2947.59	1694.18	2358.07	683.84	5.36	4100.53	1052.50	946.02	926.15	928.12
13	4789.73	2746.83	1894.94	2197.46	637.26	5.36	4147.11	950.41	846.69	827.45	829.35
14	4789.73	2522.28	2119.49	2017.82	585.17	5.36	4199.20	859.24	758.70	740.14	741.98
15	4789.73	2271.12	2370.65	1816.90	526.90	5.36	4257.47	777.82	680.73	662.91	664.67
16	4789.73	1990.19	2651.58	1592.15	461.72	5.36	4322.65	705.12	611.64	594.58	596.26
17	4789.73	1675.98	2965.79	1340.78	388.83	5.36	4395.54	640.19	550.40	534.10	535.71
18	4789.73	1324.54	3317.23	1059.63	307.29	5.36	4477.08	582.20	496.12	480.57	482.10
19	4789.73	931.44	3710.33	745.15	216.09	5.36	4568.28	530.41	447.98	433.18	434.64
20	4789.73	491.77	4150.00	393.42	114.09	5.36	4670.28	484.15	405.30	391.21	392.6
			Total		13,357.50	NPV = ?npv		29,765.54	27,939.16	27,596.36	27,630.32
		Arithmetic Average (per month)			£55.66	yields FV		287,127.12	321,945.39	329,443.04	328,684.87
											OK

Analysis Table 3

May 1991		Case 1: An 18 Year Repayment Mortgage				£35,000 Borrowed over 18 Years @ 11.85% p.a.						Analysis Table 3
A	B	C	D	E	F	G	H	I	J	K	L
Year	Gross Cash Flow Out	Interest Paid	Principal Repaid	Interest Applicable for Tax Relief	Tax Relief on Interest	Tax Relief on Premium	Net Cash Flow Out	@ x% Internal Rate of Return				Tax Relief at 29% p.a.
								@ 12%	@13%	@13.3%	13.276%
								npv	npv	npv	npv
1	4932.88	4147.50	637.42	3200.00	928.00	5.36	3999.52	3571.00	3539.40	3530.03	3530.77	FV = P( 1 + i )n = £35,000 ( 1.1185 )18 i.e. FV = £262,733.73
2	4932.88	4071.97	712.95	3200.00	928.00	5.36	3999.52	3188.39	3132.21	3115.65	3116.97
3	4932.88	3987.48	797.44	3189.98	925.09	5.36	4002.43	2848.85	2773.88	2751.91	2753.66
4	4932.88	3892.98	891.94	3114.38	903.17	5.36	4024.35	2557.55	2468.21	2442.17	2444.24
5	4932.88	3787.29	997.63	3029.83	878.65	5.36	4048.87	2297.44	2197.56	2168.62	2170.92
6	4932.88	3669.07	1115.85	2935.26	851.23	5.36	4076.29	2065.18	1957.92	1927.02	1929.47
7	4932.88	3536.84	1248.08	2829.47	820.55	5.36	4106.97	1857.78	1745.71	1713.61	1716.15
8	4932.88	3388.95	1395.97	2711.16	786.24	5.36	4141.28	1672.59	1557.78	1525.09	1527.68
9	4932.88	3223.52	1561.40	2578.82	747.86	5.36	4179.66	1507.23	1391.39	1358.54	1361.13
10	4932.88	3038.50	1746.42	2430.80	704.93	5.36	4222.59	1359.56	1243.93	1211.38	1213.95
11	4932.88	2831.55	1953.37	2265.24	656.92	5.36	4270.60	1227.70	1113.34	1081.33	1083.86
12	4932.88	2600.07	2184.85	2080.06	603.22	5.36	4324.30	1109.94	997.64	966.40	968.86
13	4932.88	2341.17	2443.75	1872.94	543.15	5.36	4384.37	1004.78	895.13	864.81	867.19
14	4932.88	2051.58	2733.34	1641.26	475.97	5.36	4451.55	910.88	804.29	774.98	777.29
15	4932.88	1727.68	3057.24	1382.14	400.82	5.36	4526.70	827.01	723.78	695.56	697.77
16	4932.88	1365.40	3419.52	1092.32	316.77	5.36	4610.75	752.11	652.40	625.31	627.43
17	4932.88	960.18	3824.74	768.14	222.76	5.36	4704.76	685.22	589.12	563.16	565.19
18	4932.88	506.95	4277.97	405.56	117.61	5.36	4809.91	625.48	533.00	508.16	510.10
						NPV = ?npv		30,068.69	28,316.69	27,823.73	27,862.63
						yields FV		231,227.20	255,537.40	263,362.37	262,726.81
											OK

Appendix 8/1

Analysis Table 1

Analysis Table 2

Analysis Table 3

Copyright © 2013, 2014 John O'Meara. All Rights Reserved.