Question

The lender offers the following terms on the commercial loan of $1,500,000: the loan amortization period is 30 years, interest rate is 7.5%, the payments are to be made monthly and the loan matures in 5 years. Prepare an annual debt service table, with beginning balance principal, interest, ending balance and total debt service rows for 5 years. Which line item is tax deductible? If the loan origination fees are 1.5% and due upon closing, what is the dollar value of the fees? Do the loan origination fee alter the annual debt service table (if so, prepare a new one).

Answer 1

r=7.5%/12 = 0.00625, n=30*12 = 360months, p=1,500,000
Monthly payment = p*r*[(1+r)^n]/{[(1+r)^n]-1} = 1500000*0.00625*[(1+0.00625)^360]/{[(1+0.00625)^360]-1} = 9375*[1.00625^360]/{[1.00625^360]-1} = 9375*9.421533905/8.421533905 = $10,488.2176

Remaining loan balance after 25years:

r=0.00625, n=25*12=300months, p=10,488.2176, PV=1,500,000; FV=ramaining balance after 25 years

FV = 1500000*[(1+0.00625)^300] - 10488.2176*{[(1+0.00625)^300]-1}/0.00625
= 1500000*[1.00625^300] - 10488.2176*{[1.00625^300]-1}/0.00625
= (1500000*6.482880448) - (10488.2176*5.482880448/0.00625)
= 9724320.672 - 9200902.914 = $523,417.7578

Monthly debt service table:

Remaining month	Beginning balance principal	Interest	Repayment	Ending balance
1	523,417.76	3,271.36	10,488.22	516,200.90
2	516,200.90	3,226.26	10,488.22	508,938.94
3	508,938.94	3,180.87	10,488.22	501,631.59
4	501,631.59	3,135.20	10,488.22	494,278.57
5	494,278.57	3,089.24	10,488.22	486,879.59
6	486,879.59	3,043.00	10,488.22	479,434.37
7	479,434.37	2,996.46	10,488.22	471,942.62
8	471,942.62	2,949.64	10,488.22	464,404.04
9	464,404.04	2,902.53	10,488.22	456,818.35
10	456,818.35	2,855.11	10,488.22	449,185.25
11	449,185.25	2,807.41	10,488.22	441,504.44
12	441,504.44	2,759.40	10,488.22	433,775.62
13	433,775.62	2,711.10	10,488.22	425,998.50
14	425,998.50	2,662.49	10,488.22	418,172.78
15	418,172.78	2,613.58	10,488.22	410,298.14
16	410,298.14	2,564.36	10,488.22	402,374.29
17	402,374.29	2,514.84	10,488.22	394,400.91
18	394,400.91	2,465.01	10,488.22	386,377.70
19	386,377.70	2,414.86	10,488.22	378,304.34
20	378,304.34	2,364.40	10,488.22	370,180.52
21	370,180.52	2,313.63	10,488.22	362,005.93
22	362,005.93	2,262.54	10,488.22	353,780.25
23	353,780.25	2,211.13	10,488.22	345,503.16
24	345,503.16	2,159.39	10,488.22	337,174.34
25	337,174.34	2,107.34	10,488.22	328,793.46
26	328,793.46	2,054.96	10,488.22	320,360.20
27	320,360.20	2,002.25	10,488.22	311,874.24
28	311,874.24	1,949.21	10,488.22	303,335.23
29	303,335.23	1,895.85	10,488.22	294,742.86
30	294,742.86	1,842.14	10,488.22	286,096.79
31	286,096.79	1,788.10	10,488.22	277,396.67
32	277,396.67	1,733.73	10,488.22	268,642.18
33	268,642.18	1,679.01	10,488.22	259,832.98
34	259,832.98	1,623.96	10,488.22	250,968.72
35	250,968.72	1,568.55	10,488.22	242,049.06
36	242,049.06	1,512.81	10,488.22	233,073.64
37	233,073.64	1,456.71	10,488.22	224,042.14
38	224,042.14	1,400.26	10,488.22	214,954.18
39	214,954.18	1,343.46	10,488.22	205,809.43
40	205,809.43	1,286.31	10,488.22	196,607.52
41	196,607.52	1,228.80	10,488.22	187,348.10
42	187,348.10	1,170.93	10,488.22	178,030.81
43	178,030.81	1,112.69	10,488.22	168,655.28
44	168,655.28	1,054.10	10,488.22	159,221.16
45	159,221.16	995.13	10,488.22	149,728.08
46	149,728.08	935.80	10,488.22	140,175.66
47	140,175.66	876.10	10,488.22	130,563.54
48	130,563.54	816.02	10,488.22	120,891.34
49	120,891.34	755.57	10,488.22	111,158.70
50	111,158.70	694.74	10,488.22	101,365.22
51	101,365.22	633.53	10,488.22	91,510.54
52	91,510.54	571.94	10,488.22	81,594.26
53	81,594.26	509.96	10,488.22	71,616.01
54	71,616.01	447.60	10,488.22	61,575.39
55	61,575.39	384.85	10,488.22	51,472.02
56	51,472.02	321.70	10,488.22	41,305.50
57	41,305.50	258.16	10,488.22	31,075.44
58	31,075.44	194.22	10,488.22	20,781.44
59	20,781.44	129.88	10,488.22	10,423.11
60	10,423.11	65.14	10,488.22	0.04

Annual debt service table:

Year	Beginning balance principal	Interest	Repayment	Ending balance
1	523,417.76	36,216.48	125,858.61	433,775.62
2	433,775.62	29,257.33	125,858.61	337,174.34
3	337,174.34	21,757.92	125,858.61	233,073.64
4	233,073.64	13,676.31	125,858.61	120,891.34
5	120,891.34	4,967.31	125,858.61	0.04

Interest line item is tax deductible.
Origination fee = 1,500,000*1.5% = $22,500
An origination fee is an upfront fee charged by a lender to process a loan application. The fee is compensation for executing the loan. The amount has to be paid in advance i.e, at the time of remitting the loan amount fee will be deducted from it. It does not alter the debt service table.