Question

In: Finance

Create an amortization schedule in Excel for the following loan: (Hint: Show Calculations) The loan term...

Create an amortization schedule in Excel for the following loan: (Hint: Show Calculations)

The loan term is 15 years, the payments are made monthly, the loan amount is $300,000 and the interest rate is 4.00% APR. Also include the "totals" over the loan term for the "interest" payment, the "principal" payment, as well as "total" payments.

Expert Solution

Sol:

Loan value (PV) = $300,000

Periods (NPER) = 15 years, Monthly = 15 * 12 = 180

Interest rate = 4% APR, Monthly = 4 / 12 = 0.3333%

Amortization schedule

Present value	-300,000
NPER	180
Interest rate	0.3333%
Monthly payment	$2,219.06

Year	Opening balance	Monthly payment	Principal	Interest	Closing balance
1	300,000	$2,219.06	$1,219.06	1000	$298,780.94
2	$298,780.94	$2,219.06	$1,223.13	995.936	$297,557.81
3	$297,557.81	$2,219.06	$1,227.20	991.859	$296,330.60
4	$296,330.60	$2,219.06	$1,231.30	987.769	$295,099.31
5	$295,099.31	$2,219.06	$1,235.40	983.664	$293,863.91
6	$293,863.91	$2,219.06	$1,239.52	979.546	$292,624.39
7	$292,624.39	$2,219.06	$1,243.65	975.415	$291,380.74
8	$291,380.74	$2,219.06	$1,247.79	971.269	$290,132.95
9	$290,132.95	$2,219.06	$1,251.95	967.11	$288,880.99
10	$288,880.99	$2,219.06	$1,256.13	962.937	$287,624.87
11	$287,624.87	$2,219.06	$1,260.31	958.75	$286,364.55
12	$286,364.55	$2,219.06	$1,264.52	954.549	$285,100.04
13	$285,100.04	$2,219.06	$1,268.73	950.333	$283,831.31
14	$283,831.31	$2,219.06	$1,272.96	946.104	$282,558.35
15	$282,558.35	$2,219.06	$1,277.20	941.861	$281,281.15
16	$281,281.15	$2,219.06	$1,281.46	937.604	$279,999.69
17	$279,999.69	$2,219.06	$1,285.73	933.332	$278,713.95
18	$278,713.95	$2,219.06	$1,290.02	929.047	$277,423.94
19	$277,423.94	$2,219.06	$1,294.32	924.746	$276,129.62
20	$276,129.62	$2,219.06	$1,298.63	920.432	$274,830.99
21	$274,830.99	$2,219.06	$1,302.96	916.103	$273,528.03
22	$273,528.03	$2,219.06	$1,307.30	911.76	$272,220.72
23	$272,220.72	$2,219.06	$1,311.66	907.402	$270,909.06
24	$270,909.06	$2,219.06	$1,316.03	903.03	$269,593.03
25	$269,593.03	$2,219.06	$1,320.42	898.643	$268,272.61
26	$268,272.61	$2,219.06	$1,324.82	894.242	$266,947.79
27	$266,947.79	$2,219.06	$1,329.24	889.826	$265,618.55
28	$265,618.55	$2,219.06	$1,333.67	885.395	$264,284.88
29	$264,284.88	$2,219.06	$1,338.11	880.95	$262,946.77
30	$262,946.77	$2,219.06	$1,342.57	876.489	$261,604.19
31	$261,604.19	$2,219.06	$1,347.05	872.014	$260,257.14
32	$260,257.14	$2,219.06	$1,351.54	867.524	$258,905.60
33	$258,905.60	$2,219.06	$1,356.05	863.019	$257,549.56
34	$257,549.56	$2,219.06	$1,360.57	858.499	$256,188.99
35	$256,188.99	$2,219.06	$1,365.10	853.963	$254,823.89
36	$254,823.89	$2,219.06	$1,369.65	849.413	$253,454.24
37	$253,454.24	$2,219.06	$1,374.22	844.847	$252,080.02
38	$252,080.02	$2,219.06	$1,378.80	840.267	$250,701.23
39	$250,701.23	$2,219.06	$1,383.39	835.671	$249,317.83
40	$249,317.83	$2,219.06	$1,388.00	831.059	$247,929.83
41	$247,929.83	$2,219.06	$1,392.63	826.433	$246,537.20
42	$246,537.20	$2,219.06	$1,397.27	821.791	$245,139.93
43	$245,139.93	$2,219.06	$1,401.93	817.133	$243,737.99
44	$243,737.99	$2,219.06	$1,406.60	812.46	$242,331.39
45	$242,331.39	$2,219.06	$1,411.29	807.771	$240,920.10
46	$240,920.10	$2,219.06	$1,416.00	803.067	$239,504.10
47	$239,504.10	$2,219.06	$1,420.72	798.347	$238,083.39
48	$238,083.39	$2,219.06	$1,425.45	793.611	$236,657.93
49	$236,657.93	$2,219.06	$1,430.20	788.86	$235,227.73
50	$235,227.73	$2,219.06	$1,434.97	784.092	$233,792.76
51	$233,792.76	$2,219.06	$1,439.75	779.309	$232,353.00
52	$232,353.00	$2,219.06	$1,444.55	774.51	$230,908.45
53	$230,908.45	$2,219.06	$1,449.37	769.695	$229,459.08
54	$229,459.08	$2,219.06	$1,454.20	764.864	$228,004.88
55	$228,004.88	$2,219.06	$1,459.05	760.016	$226,545.83
56	$226,545.83	$2,219.06	$1,463.91	755.153	$225,081.92
57	$225,081.92	$2,219.06	$1,468.79	750.273	$223,613.13
58	$223,613.13	$2,219.06	$1,473.69	745.377	$222,139.44
59	$222,139.44	$2,219.06	$1,478.60	740.465	$220,660.84
60	$220,660.84	$2,219.06	$1,483.53	735.536	$219,177.32
61	$219,177.32	$2,219.06	$1,488.47	730.591	$217,688.84
62	$217,688.84	$2,219.06	$1,493.43	725.629	$216,195.41
63	$216,195.41	$2,219.06	$1,498.41	720.651	$214,697.00
64	$214,697.00	$2,219.06	$1,503.41	715.657	$213,193.59
65	$213,193.59	$2,219.06	$1,508.42	710.645	$211,685.17
66	$211,685.17	$2,219.06	$1,513.45	705.617	$210,171.73
67	$210,171.73	$2,219.06	$1,518.49	700.572	$208,653.23
68	$208,653.23	$2,219.06	$1,523.55	695.511	$207,129.68
69	$207,129.68	$2,219.06	$1,528.63	690.432	$205,601.05
70	$205,601.05	$2,219.06	$1,533.73	685.337	$204,067.32
71	$204,067.32	$2,219.06	$1,538.84	680.224	$202,528.48
72	$202,528.48	$2,219.06	$1,543.97	675.095	$200,984.51
73	$200,984.51	$2,219.06	$1,549.12	669.948	$199,435.40
74	$199,435.40	$2,219.06	$1,554.28	664.785	$197,881.12
75	$197,881.12	$2,219.06	$1,559.46	659.604	$196,321.66
76	$196,321.66	$2,219.06	$1,564.66	654.406	$194,757.00
77	$194,757.00	$2,219.06	$1,569.87	649.19	$193,187.13
78	$193,187.13	$2,219.06	$1,575.11	643.957	$191,612.02
79	$191,612.02	$2,219.06	$1,580.36	638.707	$190,031.66
80	$190,031.66	$2,219.06	$1,585.62	633.439	$188,446.04
81	$188,446.04	$2,219.06	$1,590.91	628.153	$186,855.13
82	$186,855.13	$2,219.06	$1,596.21	622.85	$185,258.92
83	$185,258.92	$2,219.06	$1,601.53	617.53	$183,657.38
84	$183,657.38	$2,219.06	$1,606.87	612.191	$182,050.51
85	$182,050.51	$2,219.06	$1,612.23	606.835	$180,438.28
86	$180,438.28	$2,219.06	$1,617.60	601.461	$178,820.68
87	$178,820.68	$2,219.06	$1,622.99	596.069	$177,197.68
88	$177,197.68	$2,219.06	$1,628.40	590.659	$175,569.28
89	$175,569.28	$2,219.06	$1,633.83	585.231	$173,935.45
90	$173,935.45	$2,219.06	$1,639.28	579.785	$172,296.17
91	$172,296.17	$2,219.06	$1,644.74	574.321	$170,651.42
92	$170,651.42	$2,219.06	$1,650.23	568.838	$169,001.20
93	$169,001.20	$2,219.06	$1,655.73	563.337	$167,345.47
94	$167,345.47	$2,219.06	$1,661.25	557.818	$165,684.23
95	$165,684.23	$2,219.06	$1,666.78	552.281	$164,017.44
96	$164,017.44	$2,219.06	$1,672.34	546.725	$162,345.10
97	$162,345.10	$2,219.06	$1,677.91	541.15	$160,667.19
98	$160,667.19	$2,219.06	$1,683.51	535.557	$158,983.68
99	$158,983.68	$2,219.06	$1,689.12	529.946	$157,294.57
100	$157,294.57	$2,219.06	$1,694.75	524.315	$155,599.82
101	$155,599.82	$2,219.06	$1,700.40	518.666	$153,899.42
102	$153,899.42	$2,219.06	$1,706.07	512.998	$152,193.35
103	$152,193.35	$2,219.06	$1,711.75	507.311	$150,481.60
104	$150,481.60	$2,219.06	$1,717.46	501.605	$148,764.14
105	$148,764.14	$2,219.06	$1,723.18	495.88	$147,040.96
106	$147,040.96	$2,219.06	$1,728.93	490.137	$145,312.03
107	$145,312.03	$2,219.06	$1,734.69	484.373	$143,577.34
108	$143,577.34	$2,219.06	$1,740.47	478.591	$141,836.87
109	$141,836.87	$2,219.06	$1,746.27	472.79	$140,090.59
110	$140,090.59	$2,219.06	$1,752.10	466.969	$138,338.50
111	$138,338.50	$2,219.06	$1,757.94	461.128	$136,580.56
112	$136,580.56	$2,219.06	$1,763.80	455.269	$134,816.77
113	$134,816.77	$2,219.06	$1,769.67	449.389	$133,047.09
114	$133,047.09	$2,219.06	$1,775.57	443.49	$131,271.52
115	$131,271.52	$2,219.06	$1,781.49	437.572	$129,490.03
116	$129,490.03	$2,219.06	$1,787.43	431.633	$127,702.60
117	$127,702.60	$2,219.06	$1,793.39	425.675	$125,909.21
118	$125,909.21	$2,219.06	$1,799.37	419.697	$124,109.84
119	$124,109.84	$2,219.06	$1,805.36	413.699	$122,304.48
120	$122,304.48	$2,219.06	$1,811.38	407.682	$120,493.10
121	$120,493.10	$2,219.06	$1,817.42	401.644	$118,675.68
122	$118,675.68	$2,219.06	$1,823.48	395.586	$116,852.20
123	$116,852.20	$2,219.06	$1,829.56	389.507	$115,022.64
124	$115,022.64	$2,219.06	$1,835.65	383.409	$113,186.99
125	$113,186.99	$2,219.06	$1,841.77	377.29	$111,345.21
126	$111,345.21	$2,219.06	$1,847.91	371.151	$109,497.30
127	$109,497.30	$2,219.06	$1,854.07	364.991	$107,643.23
128	$107,643.23	$2,219.06	$1,860.25	358.811	$105,782.97
129	$105,782.97	$2,219.06	$1,866.45	352.61	$103,916.52
130	$103,916.52	$2,219.06	$1,872.68	346.388	$102,043.85
131	$102,043.85	$2,219.06	$1,878.92	340.146	$100,164.93
132	$100,164.93	$2,219.06	$1,885.18	333.883	$98,279.75
133	$98,279.75	$2,219.06	$1,891.46	327.599	$96,388.28
134	$96,388.28	$2,219.06	$1,897.77	321.294	$94,490.51
135	$94,490.51	$2,219.06	$1,904.10	314.968	$92,586.42
136	$92,586.42	$2,219.06	$1,910.44	308.621	$90,675.98
137	$90,675.98	$2,219.06	$1,916.81	302.253	$88,759.16
138	$88,759.16	$2,219.06	$1,923.20	295.864	$86,835.96
139	$86,835.96	$2,219.06	$1,929.61	289.453	$84,906.35
140	$84,906.35	$2,219.06	$1,936.04	283.021	$82,970.31
141	$82,970.31	$2,219.06	$1,942.50	276.568	$81,027.82
142	$81,027.82	$2,219.06	$1,948.97	270.093	$79,078.84
143	$79,078.84	$2,219.06	$1,955.47	263.596	$77,123.38
144	$77,123.38	$2,219.06	$1,961.99	257.078	$75,161.39
145	$75,161.39	$2,219.06	$1,968.53	250.538	$73,192.87
146	$73,192.87	$2,219.06	$1,975.09	243.976	$71,217.78
147	$71,217.78	$2,219.06	$1,981.67	237.393	$69,236.11
148	$69,236.11	$2,219.06	$1,988.28	230.787	$67,247.83
149	$67,247.83	$2,219.06	$1,994.90	224.159	$65,252.93
150	$65,252.93	$2,219.06	$2,001.55	217.51	$63,251.37
151	$63,251.37	$2,219.06	$2,008.23	210.838	$61,243.15
152	$61,243.15	$2,219.06	$2,014.92	204.144	$59,228.23
153	$59,228.23	$2,219.06	$2,021.64	197.427	$57,206.59
154	$57,206.59	$2,219.06	$2,028.38	190.689	$55,178.21
155	$55,178.21	$2,219.06	$2,035.14	183.927	$53,143.08
156	$53,143.08	$2,219.06	$2,041.92	177.144	$51,101.16
157	$51,101.16	$2,219.06	$2,048.73	170.337	$49,052.43
158	$49,052.43	$2,219.06	$2,055.56	163.508	$46,996.87
159	$46,996.87	$2,219.06	$2,062.41	156.656	$44,934.47
160	$44,934.47	$2,219.06	$2,069.28	149.782	$42,865.19
161	$42,865.19	$2,219.06	$2,076.18	142.884	$40,789.01
162	$40,789.01	$2,219.06	$2,083.10	135.963	$38,705.91
163	$38,705.91	$2,219.06	$2,090.04	129.02	$36,615.86
164	$36,615.86	$2,219.06	$2,097.01	122.053	$34,518.85
165	$34,518.85	$2,219.06	$2,104.00	115.063	$32,414.85
166	$32,414.85	$2,219.06	$2,111.01	108.049	$30,303.83
167	$30,303.83	$2,219.06	$2,118.05	101.013	$28,185.78
168	$28,185.78	$2,219.06	$2,125.11	93.9526	$26,060.67
169	$26,060.67	$2,219.06	$2,132.19	86.8689	$23,928.48
170	$23,928.48	$2,219.06	$2,139.30	79.7616	$21,789.18
171	$21,789.18	$2,219.06	$2,146.43	72.6306	$19,642.74
172	$19,642.74	$2,219.06	$2,153.59	65.4758	$17,489.15
173	$17,489.15	$2,219.06	$2,160.77	58.2972	$15,328.39
174	$15,328.39	$2,219.06	$2,167.97	51.0946	$13,160.42
175	$13,160.42	$2,219.06	$2,175.20	43.8681	$10,985.22
176	$10,985.22	$2,219.06	$2,182.45	36.6174	$8,802.78
177	$8,802.78	$2,219.06	$2,189.72	29.3426	$6,613.06
178	$6,613.06	$2,219.06	$2,197.02	22.0435	$4,416.04
179	$4,416.04	$2,219.06	$2,204.34	14.7201	$2,211.69
180	$2,211.69	$2,219.06	$2,211.69	7.3723	0.00
			$300,000.00	$99431.48	$399,431.48

Principal amount = $300,000

Interest payment = $99,431.48

Total payments = $399,431.48

Working

jeff jeffy answered 2 years ago

Construct an amortization schedule for a loan with the following characteristics. Loan amount = $10,000; term...

Construct an amortization schedule for a loan with the following characteristics. Loan amount = $10,000; term = 5 years; interest rate = 8 percent; annual payments.

Finance- Amortization Schedule Create the amortization schedule for a loan of $5,000, paid monthly over two...

Finance- Amortization Schedule Create the amortization schedule for a loan of $5,000, paid monthly over two years using an 8 percent APR.

Implement an amortization schedule of a loan of at least 5 years in excel

Create a loan amortization schedule in excell for a $27,000 car loan that will be repaid...

Create a loan amortization schedule in excell for a $27,000 car loan that will be repaid over 48 months at an annual interest rate of 6%. What is your monthly payment? _$_ What is the total dollar amount of payments made over the life of this loan? $____ What is the total dollar amount of interest paid over the life of this loan? $___________ How many months will it take to pay off the loan if you pay an extra...

Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly...

Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly over two years using an 9 percent APR. (Round your answers to 2 decimal places.) Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1 2 3 4 5 6 7 ...

Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly...

Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly over three years using a 9 percent APR. (Round your answers to 2 decimal places.) Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1 2 3 4 5 6 7...

AMORTIZATION SCHEDULE a. Complete an amortization schedule for a $25,000 loan to be repaid in equal...

AMORTIZATION SCHEDULE a. Complete an amortization schedule for a $25,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 8% compounded annually. Round all answers to the nearest cent. Beginning Repayment Ending Year Balance Payment Interest of Principal Balance 1 $ $ $ $ $ 2 3 b. What percentage of the payment represents interest and what percentage represents principal for each of the three years? Round all...

Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over...

Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over 20 years with monthlypayments. The annual interest rate is 5.5 %. What is your monthly payment? $ What is the total dollar amount of payments made over the life of this loan? $__ What is the total dollar amount of interest paid over the life of this loan? $_ How many months will it take to pay off the loan if you pay an extra $100...

Amortization schedule Set up an amortization schedule for a $36,000 loan to be repaid in equal...

Amortization schedule Set up an amortization schedule for a $36,000 loan to be repaid in equal installments at the end of each of the next 3 years. The interest rate is 6% compounded annually. Round all answers to the nearest cent. Beginning Remaining Year Balance Payment Balance 1 $ $ $ 2 $ $ $ 3 $ $ $ What percentage of the payment represents interest and what percentage represents principal for each of the 3 years? Round all answers...

Excel Online Structured Activity: Amortization schedule The data on a loan has been collected in the...

Excel Online Structured Activity: Amortization schedule The data on a loan has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet a. Complete an amortization schedule for a $18,000 loan to be repaid in equal installments at the end of each of the next three years. The interest rate is 12% compounded annually. Round all answers to the nearest cent. Beginning Repayment Ending Year Balance...