Question

Intro

Your company took out an amortizing loan with the following terms:

	A	B
1	Amount	5,000
2	Term (years)	30
3	Interest rate	7%

Part 1

What is the annual payment (as a positive number)? Use Excel's PMT() function.

Part 2

Create an amortization table in Excel. What is the outstanding balance at the end of the first year?

Part 3

What is the outstanding balance at the end of year 20?

Part 4

What is the outstanding balance at the end of year 30?

Answer 1

1.
402.93

2.
4947.07

3.
2830.03

4.
0.00

Payment	Loan beginning balance	Payment	Interest payment	Principal payment	Loan ending balance
1	5000	$402.93	$350.00	$52.93	$4,947.07
2	$4,947.07	$402.93	$346.29	$56.64	$4,890.43
3	$4,890.43	$402.93	$342.33	$60.60	$4,829.83
4	$4,829.83	$402.93	$338.09	$64.84	$4,764.98
5	$4,764.98	$402.93	$333.55	$69.38	$4,695.60
6	$4,695.60	$402.93	$328.69	$74.24	$4,621.36
7	$4,621.36	$402.93	$323.50	$79.44	$4,541.93
8	$4,541.93	$402.93	$317.93	$85.00	$4,456.93
9	$4,456.93	$402.93	$311.98	$90.95	$4,365.98
10	$4,365.98	$402.93	$305.62	$97.31	$4,268.67
11	$4,268.67	$402.93	$298.81	$104.13	$4,164.54
12	$4,164.54	$402.93	$291.52	$111.41	$4,053.13
13	$4,053.13	$402.93	$283.72	$119.21	$3,933.92
14	$3,933.92	$402.93	$275.37	$127.56	$3,806.36
15	$3,806.36	$402.93	$266.45	$136.49	$3,669.87
16	$3,669.87	$402.93	$256.89	$146.04	$3,523.83
17	$3,523.83	$402.93	$246.67	$156.26	$3,367.57
18	$3,367.57	$402.93	$235.73	$167.20	$3,200.36
19	$3,200.36	$402.93	$224.03	$178.91	$3,021.46
20	$3,021.46	$402.93	$211.50	$191.43	$2,830.03
21	$2,830.03	$402.93	$198.10	$204.83	$2,625.20
22	$2,625.20	$402.93	$183.76	$219.17	$2,406.03
23	$2,406.03	$402.93	$168.42	$234.51	$2,171.52
24	$2,171.52	$402.93	$152.01	$250.93	$1,920.59
25	$1,920.59	$402.93	$134.44	$268.49	$1,652.10
26	$1,652.10	$402.93	$115.65	$287.28	$1,364.82
27	$1,364.82	$402.93	$95.54	$307.39	$1,057.42
28	$1,057.42	$402.93	$74.02	$328.91	$728.51
29	$728.51	$402.93	$51.00	$351.94	$376.57
30	$376.57	$402.93	$26.36	$376.57	($0.00)