Question

Your friend wanted to buy a two-bedroom apartment in St. Lucia that is priced at $560,000. Assess different finance options as shown in below:(Here is only one option, because this is not a complete question)

Floating-rate Mortgage Option: ABC Bank offers a mortgage with a down payment of $100,000 (paid on the day of purchase of the apartment) and the balance financed by a 3.79% p.a. fixed interest (compounded quarterly) mortgage with a term of 20 years with quarterly payments (first payment is paid on the day of purchase of the apartment). Unfortunately, this fixed interest rate will last only two years and then the rate will be variable for the remainder of the mortgage. ABC Bank estimates that the variable rate will be 5.38% p.a. (compounded quarterly) at the beginning of year 3. For your calculation, this variable rate is assumed to remain constant over the remaining life of the mortgage. Application fees for this loan are $2,000, which must be paid in cash on the date of purchase.

List a table with time and quarterly payments on the option. (you should give the calculation process and formula)

Answer 1

Following information are given in the question

Price of the apartment	$560,000	Given in Question
Down-payment	$100,000	Given in Question
Fixed Interest (per annum) till year 2	3.79%	Given in Question
Term (years)	20	Given in Question
Payments	Quarterly	Given in Question
Variable rate from Year 3	5.38%	Given in Question
Application fees	$2,000	Given in Question
Quarterly payment (fixed rate) till year 2	$8,228	Refer below (1)
Principal outstanding at the end of year 2	$423,826	Refer below (2)
Quarterly payment (variable rate) from year 3	$9,304	Refer below (3)

(1) Quarterly payment (fixed rate) till year 2 =pmt(rate,nper,pv) (Excel function)

rate = 3.79%/4 (since interest are paid quarterly)

nper = 20 years * 4 quarters = 80 quarters

pv = Price of the apartment - Down payment = $560,000-$100,000 = $460,000

=pmt(3.79%/4,80,-460000) = $8228

(2) Principal outstanding at the end of year 2 =fv(rate,nper,pmt,-pv)

rate = 3.79%/4 (since interest are paid quarterly)

nper = variable interest rate is effective beginning of 3rd year and 1st payment on fixed rate is made immediately. Hence there are 9 quarters in between (1 immediately, 4 quarters in year 1 and 4 quarters in year 2) = 9

pmt = $8228 (as above)

Price of the apartment - Down payment = $560,000-$100,000 = $460,000

=fv(3.79%/4,9,8228,-460000) = $423,826

(3) Quarterly payment (variable rate) from year 3 =pmt(rate,nper,pv) (Excel function)

rate = 5.38%/4 (since interest are paid quarterly)

nper = Total quarters = 20 years * 4 = 80 quarters; Quarters paid on fixed rate (as above) = 9; Quarter to be paid on variable rate = 80-9 = 71

pv = Principal outstanding at the end of year 2 = 423,826

=pmt(5.38%/4,71,-423826) = $9304

List a table with time and quarterly payments on the option.

Initial payment immediately:

Application fees = $2,000

Downpayment = $100,000

Mortgage Amortisation

Year	Quarters	Payment	Opening Principal outstanding	Quarterly Payments	Interest Paid	Princpal Paid	Closing Principal outstanding
0	0 (Now)	1	$460,000	$8,228	$4,359	$3,869	$456,131
1	1	2	$456,131	$8,228	$4,322	$3,906	$452,225
1	2	3	$452,225	$8,228	$4,285	$3,943	$448,281
1	3	4	$448,281	$8,228	$4,247	$3,980	$444,301
1	4	5	$444,301	$8,228	$4,210	$4,018	$440,283
2	5	6	$440,283	$8,228	$4,172	$4,056	$436,227
2	6	7	$436,227	$8,228	$4,133	$4,095	$432,132
2	7	8	$432,132	$8,228	$4,094	$4,133	$427,999
2	8	9	$427,999	$8,228	$4,055	$4,173	$423,826
3	9	10	$423,826	$9,304	$5,700	$3,603	$420,223
3	10	11	$420,223	$9,304	$5,652	$3,652	$416,571
3	11	12	$416,571	$9,304	$5,603	$3,701	$412,870
3	12	13	$412,870	$9,304	$5,553	$3,751	$409,120
4	13	14	$409,120	$9,304	$5,503	$3,801	$405,319
4	14	15	$405,319	$9,304	$5,452	$3,852	$401,467
4	15	16	$401,467	$9,304	$5,400	$3,904	$397,563
4	16	17	$397,563	$9,304	$5,347	$3,956	$393,606
5	17	18	$393,606	$9,304	$5,294	$4,010	$389,597
5	18	19	$389,597	$9,304	$5,240	$4,064	$385,533
5	19	20	$385,533	$9,304	$5,185	$4,118	$381,415
5	20	21	$381,415	$9,304	$5,130	$4,174	$377,241
6	21	22	$377,241	$9,304	$5,074	$4,230	$373,011
6	22	23	$373,011	$9,304	$5,017	$4,287	$368,725
6	23	24	$368,725	$9,304	$4,959	$4,344	$364,380
6	24	25	$364,380	$9,304	$4,901	$4,403	$359,978
7	25	26	$359,978	$9,304	$4,842	$4,462	$355,516
7	26	27	$355,516	$9,304	$4,782	$4,522	$350,994
7	27	28	$350,994	$9,304	$4,721	$4,583	$346,411
7	28	29	$346,411	$9,304	$4,659	$4,644	$341,766
8	29	30	$341,766	$9,304	$4,597	$4,707	$337,060
8	30	31	$337,060	$9,304	$4,533	$4,770	$332,289
8	31	32	$332,289	$9,304	$4,469	$4,834	$327,455
8	32	33	$327,455	$9,304	$4,404	$4,899	$322,556
9	33	34	$322,556	$9,304	$4,338	$4,965	$317,590
9	34	35	$317,590	$9,304	$4,272	$5,032	$312,558
9	35	36	$312,558	$9,304	$4,204	$5,100	$307,458
9	36	37	$307,458	$9,304	$4,135	$5,168	$302,290
10	37	38	$302,290	$9,304	$4,066	$5,238	$297,052
10	38	39	$297,052	$9,304	$3,995	$5,308	$291,744
10	39	40	$291,744	$9,304	$3,924	$5,380	$286,364
10	40	41	$286,364	$9,304	$3,852	$5,452	$280,912
11	41	42	$280,912	$9,304	$3,778	$5,525	$275,387
11	42	43	$275,387	$9,304	$3,704	$5,600	$269,787
11	43	44	$269,787	$9,304	$3,629	$5,675	$264,112
11	44	45	$264,112	$9,304	$3,552	$5,751	$258,361
12	45	46	$258,361	$9,304	$3,475	$5,829	$252,532
12	46	47	$252,532	$9,304	$3,397	$5,907	$246,625
12	47	48	$246,625	$9,304	$3,317	$5,987	$240,638
12	48	49	$240,638	$9,304	$3,237	$6,067	$234,571
13	49	50	$234,571	$9,304	$3,155	$6,149	$228,423
13	50	51	$228,423	$9,304	$3,072	$6,231	$222,191
13	51	52	$222,191	$9,304	$2,988	$6,315	$215,876
13	52	53	$215,876	$9,304	$2,904	$6,400	$209,476
14	53	54	$209,476	$9,304	$2,817	$6,486	$202,990
14	54	55	$202,990	$9,304	$2,730	$6,573	$196,416
14	55	56	$196,416	$9,304	$2,642	$6,662	$189,754
14	56	57	$189,754	$9,304	$2,552	$6,751	$183,003
15	57	58	$183,003	$9,304	$2,461	$6,842	$176,161
15	58	59	$176,161	$9,304	$2,369	$6,934	$169,226
15	59	60	$169,226	$9,304	$2,276	$7,028	$162,199
15	60	61	$162,199	$9,304	$2,182	$7,122	$155,077
16	61	62	$155,077	$9,304	$2,086	$7,218	$147,859
16	62	63	$147,859	$9,304	$1,989	$7,315	$140,544
16	63	64	$140,544	$9,304	$1,890	$7,413	$133,130
16	64	65	$133,130	$9,304	$1,791	$7,513	$125,617
17	65	66	$125,617	$9,304	$1,690	$7,614	$118,003
17	66	67	$118,003	$9,304	$1,587	$7,717	$110,287
17	67	68	$110,287	$9,304	$1,483	$7,820	$102,466
17	68	69	$102,466	$9,304	$1,378	$7,925	$94,541
18	69	70	$94,541	$9,304	$1,272	$8,032	$86,509
18	70	71	$86,509	$9,304	$1,164	$8,140	$78,369
18	71	72	$78,369	$9,304	$1,054	$8,250	$70,119
18	72	73	$70,119	$9,304	$943	$8,361	$61,759
19	73	74	$61,759	$9,304	$831	$8,473	$53,286
19	74	75	$53,286	$9,304	$717	$8,587	$44,699
19	75	76	$44,699	$9,304	$601	$8,702	$35,996
19	76	77	$35,996	$9,304	$484	$8,820	$27,177
20	77	78	$27,177	$9,304	$366	$8,938	$18,239
20	78	79	$18,239	$9,304	$245	$9,058	$9,180
20	79	80	$9,180	$9,304	$123	$9,180	($0)

Notes:

1. Opening Principal Outstanding =

Payment 1 = Mortgage Loan value

Subsequent payments / quarters = closing principal outstanding of previous payment / quarter

2. Quarterly payments =

As computed above, $8228 for fixed interest rates for 9 payments / quarters (till year 2)

$9304 for variable interest rates from 10th payment (year 3)

3. Interest paid = Opening principal outstanding * fixed interest rate/4 ( for 9 payments / quarters (till year 2))

Interest paid = Opening principal outstanding * variable interest rate/4 ( from 10th payments / quarter (from year 3))

4. Principal paid = Quarterly payments - Interest paid

5. Closing Principal outstanding = Opening Principal outstanding - Principal paid