Question

Consider a loan of 75,000 repayable by equal quarterly instalments over 15 years calculated at an interest rate of 8.80% per annum convertible quarterly. From the lenders perspective, set a cash flow model in tabular form which covers all the scheduled repayments in the 15 years, allowing for tax at 30% on the interest component of the loan. Calculate the amount of each quarterly payment and calculate the after tax internal rate of return achieved by the lender on its investment over this loan to an accuracy of 0.01%, assuming that the borrower follows the loan schedule and there is no default.

Answer 1

	Loan Amount	$75,000
	Annual Interest	8.80%
	Quarterly interest	2.20%	(8.8/4)
	Number of quarterly periods	60	(15*4)
	SCHEDULE OF RECEIPT OF PAYMENTS
	For Interest Payment Use IPMT function of excel with Rate=2.2%, Per =Period of payment,Nper=60, PV=75000
	For Principle Payment Use PPMT function of excel with Rate=2.2%, Per =Period of payment,Nper=60, PV=75001
		N	A	B	C=B*(1-0.3)	D=C+E	E	F
		Quarterly	Beginning	Interest	After tax	Total	Principle	Ending
		Period	Balance	Payment	Interest	Cash flow	Repayment	Balance
		0				($75,000)		$75,000
		1	$75,000.00	$1,650.00	$1,155.00	$1,768.33	$613.33	$74,386.67
		2	$74,386.67	$1,636.51	$1,145.55	$1,772.38	$626.82	$73,759.85
		3	$73,759.85	$1,622.72	$1,135.90	$1,776.51	$640.61	$73,119.23
		4	$73,119.23	$1,608.62	$1,126.04	$1,780.74	$654.71	$72,464.53
		5	$72,464.53	$1,594.22	$1,115.95	$1,785.06	$669.11	$71,795.42
		6	$71,795.42	$1,579.50	$1,105.65	$1,789.48	$683.83	$71,111.59
		7	$71,111.59	$1,564.45	$1,095.12	$1,793.99	$698.87	$70,412.71
		8	$70,412.71	$1,549.08	$1,084.36	$1,798.61	$714.25	$69,698.46
		9	$69,698.46	$1,533.37	$1,073.36	$1,803.32	$729.96	$68,968.50
		10	$68,968.50	$1,517.31	$1,062.11	$1,808.14	$746.02	$68,222.47
		11	$68,222.47	$1,500.89	$1,050.63	$1,813.06	$762.44	$67,460.04
		12	$67,460.04	$1,484.12	$1,038.88	$1,818.09	$779.21	$66,680.83
		13	$66,680.83	$1,466.98	$1,026.88	$1,823.24	$796.35	$65,884.48
		14	$65,884.48	$1,449.46	$1,014.62	$1,828.49	$813.87	$65,070.61
		15	$65,070.61	$1,431.55	$1,002.09	$1,833.86	$831.78	$64,238.83
		16	$64,238.83	$1,413.25	$989.28	$1,839.35	$850.08	$63,388.75
		17	$63,388.75	$1,394.55	$976.19	$1,844.96	$868.78	$62,519.98
		18	$62,519.98	$1,375.44	$962.81	$1,850.70	$887.89	$61,632.09
		19	$61,632.09	$1,355.91	$949.13	$1,856.56	$907.42	$60,724.66
		20	$60,724.66	$1,335.94	$935.16	$1,862.55	$927.39	$59,797.28
		21	$59,797.28	$1,315.54	$920.88	$1,868.67	$947.79	$58,849.49
		22	$58,849.49	$1,294.69	$906.28	$1,874.92	$968.64	$57,880.85
		23	$57,880.85	$1,273.38	$891.37	$1,881.32	$989.95	$56,890.89
		24	$56,890.89	$1,251.60	$876.12	$1,887.85	$1,011.73	$55,879.16
		25	$55,879.16	$1,229.34	$860.54	$1,894.53	$1,033.99	$54,845.18
		26	$54,845.18	$1,206.59	$844.62	$1,901.35	$1,056.74	$53,788.44
		27	$53,788.44	$1,183.35	$828.34	$1,908.33	$1,079.98	$52,708.46
		28	$52,708.46	$1,159.59	$811.71	$1,915.45	$1,103.74	$51,604.71
		29	$51,604.71	$1,135.30	$794.71	$1,922.74	$1,128.03	$50,476.69
		30	$50,476.69	$1,110.49	$777.34	$1,930.18	$1,152.84	$49,323.84
		31	$49,323.84	$1,085.12	$759.59	$1,937.79	$1,178.21	$48,145.64
		32	$48,145.64	$1,059.20	$741.44	$1,945.57	$1,204.13	$46,941.51
		33	$46,941.51	$1,032.71	$722.90	$1,953.52	$1,230.62	$45,710.89
		34	$45,710.89	$1,005.64	$703.95	$1,961.64	$1,257.69	$44,453.20
		35	$44,453.20	$977.97	$684.58	$1,969.94	$1,285.36	$43,167.85
		36	$43,167.85	$949.69	$664.78	$1,978.42	$1,313.64	$41,854.21
		37	$41,854.21	$920.79	$644.55	$1,987.09	$1,342.54	$40,511.67
		38	$40,511.67	$891.26	$623.88	$1,995.95	$1,372.07	$39,139.60
		39	$39,139.60	$861.07	$602.75	$2,005.01	$1,402.26	$37,737.34
		40	$37,737.34	$830.22	$581.16	$2,014.26	$1,433.11	$36,304.23
		41	$36,304.23	$798.69	$559.09	$2,023.72	$1,464.64	$34,839.59
		42	$34,839.59	$766.47	$536.53	$2,033.39	$1,496.86	$33,342.74
		43	$33,342.74	$733.54	$513.48	$2,043.27	$1,529.79	$31,812.95
		44	$31,812.95	$699.88	$489.92	$2,053.36	$1,563.45	$30,249.50
		45	$30,249.50	$665.49	$465.84	$2,063.68	$1,597.84	$28,651.66
		46	$28,651.66	$630.34	$441.24	$2,074.23	$1,632.99	$27,018.67
		47	$27,018.67	$594.41	$416.09	$2,085.01	$1,668.92	$25,349.75
		48	$25,349.75	$557.69	$390.39	$2,096.02	$1,705.64	$23,644.11
		49	$23,644.11	$520.17	$364.12	$2,107.28	$1,743.16	$21,900.95
		50	$21,900.95	$481.82	$337.27	$2,118.78	$1,781.51	$20,119.44
		51	$20,119.44	$442.63	$309.84	$2,130.54	$1,820.70	$18,298.74
		52	$18,298.74	$402.57	$281.80	$2,142.56	$1,860.76	$16,437.98
		53	$16,437.98	$361.64	$253.14	$2,154.84	$1,901.69	$14,536.29
		54	$14,536.29	$319.80	$223.86	$2,167.39	$1,943.53	$12,592.76
		55	$12,592.76	$277.04	$193.93	$2,180.22	$1,986.29	$10,606.47
		56	$10,606.47	$233.34	$163.34	$2,193.33	$2,029.99	$8,576.48
		57	$8,576.48	$188.68	$132.08	$2,206.73	$2,074.65	$6,501.83
		58	$6,501.83	$143.04	$100.13	$2,220.42	$2,120.29	$4,381.54
		59	$4,381.54	$96.39	$67.48	$2,234.41	$2,166.94	$2,214.61
		60	$2,214.61	$48.72	$34.10	$2,248.71	$2,214.61	($0.00)
	Year	Annual Cash Flow
	0	($75,000)
	1	$7,097.97	(Sum of Quarter 1,2,3 and 4 )
	2	$7,167.14	(Sum of Quarter 5,6,7,8 )
	3	$7,242.61
	4	$7,324.95
	5	$7,414.77
	6	$7,512.76
	7	$7,619.66
	8	$7,736.28
	9	$7,863.51
	10	$8,002.32
	11	$8,153.74
	12	$8,318.94
	13	$8,499.16
	14	$8,695.77
	15	$8,910.27
	INTERNAL RATE OF RETURN(IRR)	5.96%	(Using Excel IRR function over the cash flow)