Question

Please solve it on excel

Q.1

I charge a 6% nominal interest rate on a loan when inflation is 3%. By how much does the nominal rate need to rise for me to see a real interest rate of 5%?

Q.2

Carlson is buying a motel. The purchase price is $1,000,000. The bank is willing to finance 70% of the purchase price for 30 years in monthly installments at 7.5% per annum. He wishes to sell the property after exactly six years.

a. What is his monthly loan payment?

b. What will his loan balance be at the time of sale?

c. If he plans to sell after 7 years and 2 months, what will be his loan balance?

Answer 1

Q1

Calculation of Nominal interest rate when inflation is 3% and desired real interest rate of 5% = 1.03*1.05 - 1 = 8.15%

To see a real rate of 5% nominal interest rate must rise by (8.15-5) = 3.15%

Q2

Amount of loan = 1000000 * 0.70 = 700000

Monthly loan payment = 700000 / PVAF 0.625%,360months

Monthly loan payment = 700000 / 143.01 = 4894.76

	Opening	Interest @ 0.625% per month	closing
1	700,000	4,375	4,895	699,480
2	699,480	4,372	4,895	698,957
3	698,957	4,368	4,895	698,431
4	698,431	4,365	4,895	697,901
5	697,901	4,362	4,895	697,369
6	697,369	4,359	4,895	696,832
7	696,832	4,355	4,895	696,293
8	696,293	4,352	4,895	695,750
9	695,750	4,348	4,895	695,203
10	695,203	4,345	4,895	694,654
11	694,654	4,342	4,895	694,101
12	694,101	4,338	4,895	693,544
13	693,544	4,335	4,895	692,984
14	692,984	4,331	4,895	692,420
15	692,420	4,328	4,895	691,853
16	691,853	4,324	4,895	691,282
17	691,282	4,321	4,895	690,708
18	690,708	4,317	4,895	690,130
19	690,130	4,313	4,895	689,549
20	689,549	4,310	4,895	688,964
21	688,964	4,306	4,895	688,375
22	688,375	4,302	4,895	687,783
23	687,783	4,299	4,895	687,187
24	687,187	4,295	4,895	686,587
25	686,587	4,291	4,895	685,983
26	685,983	4,287	4,895	685,376
27	685,376	4,284	4,895	684,765
28	684,765	4,280	4,895	684,150
29	684,150	4,276	4,895	683,531
30	683,531	4,272	4,895	682,908
31	682,908	4,268	4,895	682,281
32	682,281	4,264	4,895	681,651
33	681,651	4,260	4,895	681,017
34	681,017	4,256	4,895	680,378
35	680,378	4,252	4,895	679,736
36	679,736	4,248	4,895	679,089
37	679,089	4,244	4,895	678,439
38	678,439	4,240	4,895	677,784
39	677,784	4,236	4,895	677,126
40	677,126	4,232	4,895	676,463
41	676,463	4,228	4,895	675,796
42	675,796	4,224	4,895	675,125
43	675,125	4,220	4,895	674,450
44	674,450	4,215	4,895	673,770
45	673,770	4,211	4,895	673,087
46	673,087	4,207	4,895	672,399
47	672,399	4,202	4,895	671,707
48	671,707	4,198	4,895	671,010
49	671,010	4,194	4,895	670,309
50	670,309	4,189	4,895	669,604
51	669,604	4,185	4,895	668,894
52	668,894	4,181	4,895	668,180
53	668,180	4,176	4,895	667,461
54	667,461	4,172	4,895	666,738
55	666,738	4,167	4,895	666,010
56	666,010	4,163	4,895	665,278
57	665,278	4,158	4,895	664,541
58	664,541	4,153	4,895	663,800
59	663,800	4,149	4,895	663,054
60	663,054	4,144	4,895	662,303
61	662,303	4,139	4,895	661,548
62	661,548	4,135	4,895	660,788
63	660,788	4,130	4,895	660,023
64	660,023	4,125	4,895	659,253
65	659,253	4,120	4,895	658,479
66	658,479	4,115	4,895	657,700
67	657,700	4,111	4,895	656,916
68	656,916	4,106	4,895	656,127
69	656,127	4,101	4,895	655,333
70	655,333	4,096	4,895	654,534
71	654,534	4,091	4,895	653,730
72	653,730	4,086	4,895	652,921

Loan balance after 6 years (72 month) = 652921

	Opening	Interest @ 0.625% per month	closing
1	700,000	4,375	4,895	699,480
2	699,480	4,372	4,895	698,957
3	698,957	4,368	4,895	698,431
4	698,431	4,365	4,895	697,901
5	697,901	4,362	4,895	697,369
6	697,369	4,359	4,895	696,832
7	696,832	4,355	4,895	696,293
8	696,293	4,352	4,895	695,750
9	695,750	4,348	4,895	695,203
10	695,203	4,345	4,895	694,654
11	694,654	4,342	4,895	694,101
12	694,101	4,338	4,895	693,544
13	693,544	4,335	4,895	692,984
14	692,984	4,331	4,895	692,420
15	692,420	4,328	4,895	691,853
16	691,853	4,324	4,895	691,282
17	691,282	4,321	4,895	690,708
18	690,708	4,317	4,895	690,130
19	690,130	4,313	4,895	689,549
20	689,549	4,310	4,895	688,964
21	688,964	4,306	4,895	688,375
22	688,375	4,302	4,895	687,783
23	687,783	4,299	4,895	687,187
24	687,187	4,295	4,895	686,587
25	686,587	4,291	4,895	685,983
26	685,983	4,287	4,895	685,376
27	685,376	4,284	4,895	684,765
28	684,765	4,280	4,895	684,150
29	684,150	4,276	4,895	683,531
30	683,531	4,272	4,895	682,908
31	682,908	4,268	4,895	682,281
32	682,281	4,264	4,895	681,651
33	681,651	4,260	4,895	681,017
34	681,017	4,256	4,895	680,378
35	680,378	4,252	4,895	679,736
36	679,736	4,248	4,895	679,089
37	679,089	4,244	4,895	678,439
38	678,439	4,240	4,895	677,784
39	677,784	4,236	4,895	677,126
40	677,126	4,232	4,895	676,463
41	676,463	4,228	4,895	675,796
42	675,796	4,224	4,895	675,125
43	675,125	4,220	4,895	674,450
44	674,450	4,215	4,895	673,770
45	673,770	4,211	4,895	673,087
46	673,087	4,207	4,895	672,399
47	672,399	4,202	4,895	671,707
48	671,707	4,198	4,895	671,010
49	671,010	4,194	4,895	670,309
50	670,309	4,189	4,895	669,604
51	669,604	4,185	4,895	668,894
52	668,894	4,181	4,895	668,180
53	668,180	4,176	4,895	667,461
54	667,461	4,172	4,895	666,738
55	666,738	4,167	4,895	666,010
56	666,010	4,163	4,895	665,278
57	665,278	4,158	4,895	664,541
58	664,541	4,153	4,895	663,800
59	663,800	4,149	4,895	663,054
60	663,054	4,144	4,895	662,303
61	662,303	4,139	4,895	661,548
62	661,548	4,135	4,895	660,788
63	660,788	4,130	4,895	660,023
64	660,023	4,125	4,895	659,253
65	659,253	4,120	4,895	658,479
66	658,479	4,115	4,895	657,700
67	657,700	4,111	4,895	656,916
68	656,916	4,106	4,895	656,127
69	656,127	4,101	4,895	655,333
70	655,333	4,096	4,895	654,534
71	654,534	4,091	4,895	653,730
72	653,730	4,086	4,895	652,921
73	652,921	4,081	4,895	652,107
74	652,107	4,076	4,895	651,288
75	651,288	4,071	4,895	650,463
76	650,463	4,065	4,895	649,634
77	649,634	4,060	4,895	648,800
78	648,800	4,055	4,895	647,960
79	647,960	4,050	4,895	647,115
80	647,115	4,044	4,895	646,264
81	646,264	4,039	4,895	645,409
82	645,409	4,034	4,895	644,548
83	644,548	4,028	4,895	643,682
84	643,682	4,023	4,895	642,810
85	642,810	4,018	4,895	641,933
86	641,933	4,012	4,895	641,050

Loan balance aftere 7 Years and 2 months (86months) = 641,050