Question

Part B

Your firm borrows $1m to buy a warehouse. The loan is a 30-year mortgage at 6% per year with monthly repayments without any balloon payment. Create an amortization table, but print out only the 30 rows of monthly payments for the anniversary months, i.e., 12, 24, 36, … , 348, and 360. The 6 needed columns are: No. of month, Beginning balance, Monthly payment, Interest, Principal reduction, Ending balance. Except for first column, all columns are to be in currency format.

Answer:

Print the 30 selected anniversary months of the amortization table in 1 page.

Estimate:

i. Percent of interest of monthly payment in first, 180^th, and last months.

Ans.: ________%; _________%; __________% (2 decimal places)

ii. Percent of equity accrued midway in the 30-year mortgage.

Ans.: _________% (2 decimal places)

Answer 1

Monthly payment	PV/PVAF At .5% for 360 months	1000000/166.7916	5995.506
PVAF at .5% for 360 months	1-(1+r)^-n/r	1-(1.005)^-360/.5%	166.7916
No. of months	beginning balance	Monthly payment	Interest	Principal reduction	Ending balance
1	1000000	5995.506	5000	995.5057	999004.5
12	988771.5	5995.506	4943.858	1051.648	987719.9
24	975798.9	5995.506	4878.994	1116.511	974682.3
36	962026.1	5995.506	4810.13	1185.375	960840.7
48	947403.8	5995.506	4737.019	1258.487	946145.3
60	931879.6	5995.506	4659.398	1336.107	930543.5
72	915398	5995.506	4576.99	1418.516	913979.5
84	897899.8	5995.506	4489.499	1506.007	896393.8
96	879322.4	5995.506	4396.612	1598.894	877723.5
120	838659.4	5995.506	4193.297	1802.209	836857.2
132	816428.1	5995.506	4082.141	1913.365	814514.8
144	792825.7	5995.506	3964.128	2031.377	790794.3
156	767767.5	5995.506	3838.838	2156.668	765610.8
168	741163.8	5995.506	3705.819	2289.687	738874.1
180	712919.2	5995.506	3564.596	2430.91	710488.3
192	682932.6	5995.506	3414.663	2580.843	680351.7
204	651096.4	5995.506	3255.482	2740.024	648356.4
216	617296.7	5995.506	3086.484	2909.022	614387.7
228	581412.3	5995.506	2907.061	3088.444	578323.8
240	543314.6	5995.506	2716.573	3278.933	540035.7
252	502867.1	5995.506	2514.336	3481.17	499385.9
264	459924.9	5995.506	2299.625	3695.881	456229
276	414334.1	5995.506	2071.671	3923.835	410410.3
288	365931.4	5995.506	1829.657	4165.849	361765.6
300	314543.3	5995.506	1572.717	4422.789	310120.6
312	259985.8	5995.506	1299.929	4695.577	255290.2
324	202063.2	5995.506	1010.316	4985.19	197078
336	140568.1	5995.506	702.8403	5292.665	135275.4
348	75280.05	5995.506	376.4003	5619.105	69660.95
360	5965.229	5995.506	29.82615	5965.68	0
Month	Monthly payment	Interest amount	Interest as % of total monthly payment
1	5995.506	5000	83.40%
180	5995.506	3564.596	59.45%
360	5995.506	29.82615	0.50%
% of equity accrued widway of in the 30th year of mortgage
No. of months	beginning balance	Monthly payment	Interest	Principal reduction	Ending balance
180	712919.2	5995.506	3564.596	2430.91	710488.3
% of equity accrued widway of in the 30th year of mortgage	710488.3/1000000		71.05%