Question

On the third tab build the full amortization table for a 30 year Constant Payment Mortgage (CPM) Loan with a 4.5% interest rate compounded monthly. The initial loan amount should be $2,500,000. in excel

Answer 1

	P =	Regular Payments
	PV =	Loan Amount
	r =	rate of interest
	n =	no of periods
	P =	r (PV)
		1 - (1 + r )-n
	P =	(4.5%/12)*2500000
		1 - (1 / (1 + 4.5%/12)^360))
	P =	9375
		0.740104346
	P =	$ 12667.13
	Beginning Balance	Interest	Principal	Ending Balance
1	$2,500,000.00	$9,375.00	$3,292.13	$2,496,707.87
2	$2,496,707.87	$9,362.65	$3,304.48	$2,493,403.39
3	$2,493,403.39	$9,350.26	$3,316.87	$2,490,086.52
4	$2,490,086.52	$9,337.82	$3,329.31	$2,486,757.21
5	$2,486,757.21	$9,325.34	$3,341.79	$2,483,415.42
6	$2,483,415.42	$9,312.81	$3,354.32	$2,480,061.09
7	$2,480,061.09	$9,300.23	$3,366.90	$2,476,694.19
8	$2,476,694.19	$9,287.60	$3,379.53	$2,473,314.66
9	$2,473,314.66	$9,274.93	$3,392.20	$2,469,922.46
10	$2,469,922.46	$9,262.21	$3,404.92	$2,466,517.53
11	$2,466,517.53	$9,249.44	$3,417.69	$2,463,099.84
12	$2,463,099.84	$9,236.62	$3,430.51	$2,459,669.33
Year #1 End
13	$2,459,669.33	$9,223.76	$3,443.37	$2,456,225.96
14	$2,456,225.96	$9,210.85	$3,456.29	$2,452,769.67
15	$2,452,769.67	$9,197.89	$3,469.25	$2,449,300.43
16	$2,449,300.43	$9,184.88	$3,482.26	$2,445,818.17
17	$2,445,818.17	$9,171.82	$3,495.31	$2,442,322.86
18	$2,442,322.86	$9,158.71	$3,508.42	$2,438,814.44
19	$2,438,814.44	$9,145.55	$3,521.58	$2,435,292.86
20	$2,435,292.86	$9,132.35	$3,534.78	$2,431,758.07
21	$2,431,758.07	$9,119.09	$3,548.04	$2,428,210.03
22	$2,428,210.03	$9,105.79	$3,561.35	$2,424,648.69
23	$2,424,648.69	$9,092.43	$3,574.70	$2,421,073.99
24	$2,421,073.99	$9,079.03	$3,588.11	$2,417,485.88
Year #2 End
25	$2,417,485.88	$9,065.57	$3,601.56	$2,413,884.32
26	$2,413,884.32	$9,052.07	$3,615.07	$2,410,269.25
27	$2,410,269.25	$9,038.51	$3,628.62	$2,406,640.63
28	$2,406,640.63	$9,024.90	$3,642.23	$2,402,998.40
29	$2,402,998.40	$9,011.24	$3,655.89	$2,399,342.51
30	$2,399,342.51	$8,997.53	$3,669.60	$2,395,672.91
31	$2,395,672.91	$8,983.77	$3,683.36	$2,391,989.55
32	$2,391,989.55	$8,969.96	$3,697.17	$2,388,292.38
33	$2,388,292.38	$8,956.10	$3,711.04	$2,384,581.35
34	$2,384,581.35	$8,942.18	$3,724.95	$2,380,856.39
35	$2,380,856.39	$8,928.21	$3,738.92	$2,377,117.47
36	$2,377,117.47	$8,914.19	$3,752.94	$2,373,364.53
Year #3 End
37	$2,373,364.53	$8,900.12	$3,767.02	$2,369,597.51
38	$2,369,597.51	$8,885.99	$3,781.14	$2,365,816.37
39	$2,365,816.37	$8,871.81	$3,795.32	$2,362,021.05
40	$2,362,021.05	$8,857.58	$3,809.55	$2,358,211.50
41	$2,358,211.50	$8,843.29	$3,823.84	$2,354,387.66
42	$2,354,387.66	$8,828.95	$3,838.18	$2,350,549.48
43	$2,350,549.48	$8,814.56	$3,852.57	$2,346,696.91
44	$2,346,696.91	$8,800.11	$3,867.02	$2,342,829.89
45	$2,342,829.89	$8,785.61	$3,881.52	$2,338,948.37
46	$2,338,948.37	$8,771.06	$3,896.08	$2,335,052.29
47	$2,335,052.29	$8,756.45	$3,910.69	$2,331,141.60
48	$2,331,141.60	$8,741.78	$3,925.35	$2,327,216.25
Year #4 End
49	$2,327,216.25	$8,727.06	$3,940.07	$2,323,276.18
50	$2,323,276.18	$8,712.29	$3,954.85	$2,319,321.33
51	$2,319,321.33	$8,697.45	$3,969.68	$2,315,351.65
52	$2,315,351.65	$8,682.57	$3,984.56	$2,311,367.09
53	$2,311,367.09	$8,667.63	$3,999.51	$2,307,367.58
54	$2,307,367.58	$8,652.63	$4,014.50	$2,303,353.08
55	$2,303,353.08	$8,637.57	$4,029.56	$2,299,323.52
56	$2,299,323.52	$8,622.46	$4,044.67	$2,295,278.85
57	$2,295,278.85	$8,607.30	$4,059.84	$2,291,219.02
58	$2,291,219.02	$8,592.07	$4,075.06	$2,287,143.95
59	$2,287,143.95	$8,576.79	$4,090.34	$2,283,053.61
60	$2,283,053.61	$8,561.45	$4,105.68	$2,278,947.93
Year #5 End

Due to length constraint only this much data can be shown.