Question

EXCEL Question 1 (Home Loan) (3marks] Construct an EXCEL spreadsheet, which projects cash flows of a home loan with yearly loan repayments, ie. the loan is paid by payments at the end of each year for n years, until the loan is completely paid off. Please use your spreadsheet to answer the following questions: (a) Given loan amount is $600,000, annual effective interest rate i is 11%, loan term is 20 years. Find the annual repayment amount. (b) Given annual effective interest rate i is 8%, loan term is 30 years. I can afford to make annual repayment amount of $72,000. How much money can I borrow? In your EXCEL spreadsheet, you are required to show the loan amount outstanding at the end of each year, after interest has been accrued and the annual repayment deducted.

Answer 1

There are two situations given here.

1. In the first situation, the principle amount of loan, the annual interest rate, and the term of loan is given. We have to find out the annual repayment amount.
2. In the second situation, the annual repayment amount amount of loan, the annual interest rate, and the term of loan is given. We have to find out the principle amount that can be borrowed.

The formula for annual repayment amount has been used to find the values of annual repayment amount in the first case and of principle amount in the second case. Calculations have been shown in the excel file images below.

a) Annual Repayment amount = ? Principle, P: Effective annual interest rate i Loan term in years, n $6,00,000.00 11% 20 Annual Repayment amount formula: A =P* r+ (1+r)n (1+r)n-1 $75,345.38 - $64,972.01 $6,00,000* 11% $75,345.38 Loan Repayment Schedule: Year Annual Repayment amount Loan Outstanding $6,00,000 - $9,345.38 (x) + (Y) Previous loan outstanding - current principle paid Annual Repayment amount - current interest amount (Y) $66,000.00 $9,345.38 $64,972.01 $10,373.37 $63,830.94 $11,514.45 $62,564.35 $12,781.03 21 $75,345.38 $75,345.38 $75,345.38 $75,345.38 $5,90,654.62 $5,80,281.24 $5,68,766.80 $5,55,985.76 $5,80,281.24 - $11,514.45 Yearly

Loan Repayment schedule (First case):

Year	Annual Repayment amount	Interest (11%)	Principle paid	Loan Outstanding
	(X) + (Y)	(X)	Annual Repayment amount - current interest amount (Y)	Previous loan outstanding - current principle paid
1	$75,345.38	$66,000.00	$9,345.38	$5,90,654.62
2	$75,345.38	$64,972.01	$10,373.37	$5,80,281.24
3	$75,345.38	$63,830.94	$11,514.45	$5,68,766.80
4	$75,345.38	$62,564.35	$12,781.03	$5,55,985.76
5	$75,345.38	$61,158.43	$14,186.95	$5,41,798.82
6	$75,345.38	$59,597.87	$15,747.51	$5,26,051.30
7	$75,345.38	$57,865.64	$17,479.74	$5,08,571.56
8	$75,345.38	$55,942.87	$19,402.51	$4,89,169.05
9	$75,345.38	$53,808.60	$21,536.79	$4,67,632.27
10	$75,345.38	$51,439.55	$23,905.83	$4,43,726.44
11	$75,345.38	$48,809.91	$26,535.47	$4,17,190.96
12	$75,345.38	$45,891.01	$29,454.38	$3,87,736.59
13	$75,345.38	$42,651.02	$32,694.36	$3,55,042.23
14	$75,345.38	$39,054.65	$36,290.74	$3,18,751.49
15	$75,345.38	$35,062.66	$40,282.72	$2,78,468.77
16	$75,345.38	$30,631.57	$44,713.82	$2,33,754.96
17	$75,345.38	$25,713.05	$49,632.34	$1,84,122.62
18	$75,345.38	$20,253.49	$55,091.89	$1,29,030.73
19	$75,345.38	$14,193.38	$61,152.00	$67,878.72
20	$75,345.38	$7,466.66	$67,878.72	$0.00

b) Amount that can be borrowed: ? Principle, P: Effective annual interest rate i Loan term in years, n Annual Repayment amount: 8% 30 $72,000.00 Annual Repayment amount formula: A = P * r+ (1+r) n (1+r)n-1 P = A* (1+r)n-1 [r*(1+r)n P = $8,10,560.40 Loan Repayment Schedule: Year Annual Repayment amount Interest (8%) Principle paid Loan Outstanding (x) + (Y) (x) = Principle outstanding * 8% (Loan outstanding - Y) (Y) = Annual Repayment amount - Interest $7,155.17 $7,727.58 $8,345.79 =$ 8,03,405.23 - $7,727.58 2 $72,000.00 $72,000.00 $72,000.00 $64,844.83 $64,272.42 $63,654.21 $8,03,405.23 $7,95,677.65 $7,87,331.86

Loan Repayment schedule (second case):

Year	Annual Repayment amount	Interest (8%)	Principle paid	Loan Outstanding
	(X) + (Y)	(X) = Principle outstanding*8%	(Y) = Annual Repayment amount - Interest	(Loan outstanding - Y)
1	$72,000.00	$64,844.83	$7,155.17	$8,03,405.23
2	$72,000.00	$64,272.42	$7,727.58	$7,95,677.65
3	$72,000.00	$63,654.21	$8,345.79	$7,87,331.86
4	$72,000.00	$62,986.55	$9,013.45	$7,78,318.41
5	$72,000.00	$62,265.47	$9,734.53	$7,68,583.89
6	$72,000.00	$61,486.71	$10,513.29	$7,58,070.60
7	$72,000.00	$60,645.65	$11,354.35	$7,46,716.24
8	$72,000.00	$59,737.30	$12,262.70	$7,34,453.54
9	$72,000.00	$58,756.28	$13,243.72	$7,21,209.83
10	$72,000.00	$57,696.79	$14,303.21	$7,06,906.61
11	$72,000.00	$56,552.53	$15,447.47	$6,91,459.14
12	$72,000.00	$55,316.73	$16,683.27	$6,74,775.87
13	$72,000.00	$53,982.07	$18,017.93	$6,56,757.94
14	$72,000.00	$52,540.64	$19,459.36	$6,37,298.58
15	$72,000.00	$50,983.89	$21,016.11	$6,16,282.47
16	$72,000.00	$49,302.60	$22,697.40	$5,93,585.06
17	$72,000.00	$47,486.81	$24,513.19	$5,69,071.87
18	$72,000.00	$45,525.75	$26,474.25	$5,42,597.62
19	$72,000.00	$43,407.81	$28,592.19	$5,14,005.43
20	$72,000.00	$41,120.43	$30,879.57	$4,83,125.86
21	$72,000.00	$38,650.07	$33,349.93	$4,49,775.93
22	$72,000.00	$35,982.07	$36,017.93	$4,13,758.00
23	$72,000.00	$33,100.64	$38,899.36	$3,74,858.64
24	$72,000.00	$29,988.69	$42,011.31	$3,32,847.34
25	$72,000.00	$26,627.79	$45,372.21	$2,87,475.12
26	$72,000.00	$22,998.01	$49,001.99	$2,38,473.13
27	$72,000.00	$19,077.85	$52,922.15	$1,85,550.98
28	$72,000.00	$14,844.08	$57,155.92	$1,28,395.06
29	$72,000.00	$10,271.60	$61,728.40	$66,666.67
30	$72,000.00	$5,333.33	$66,666.67	$0.00