Question

Assume Sam borrowed $120,000 for a home mortgage, to be repaid at 8% interest over 3 years with monthly payments.

1. How many monthly payments does Sam has to pay for 3 year?
2. How much is the monthly payment?
3. How much is the interest payment for the third month?
4. How much interest is paid over the life of the loan?

Hint:

• Annual percentage rate (APR) = ??
• Monthly discount rate (rate) = ??
• Number of payment (Nper) = ??
• Monthly payment (pmt) = ??
• pmt(rate, nper, pv, fv =0, type= 0) = ??
• the monthly payment schedule for the next 3 years.

Answer 1

1.

=12*3=26

2.

=120000*(8%/12)/(1-1/(1+8%/12)^(12*3))=3760.36

3.

760.40

4.

15373.10

APR=8%

Monthly discount rate=8%/12=0.66%

Number of payments=36

PV=-120000

FV=0

Monthly payment=3760.36

Payment	Loan beginning balance	Payment	Interest payment	Principal payment	Loan ending balance
1	120000	$3,760.36	$800.00	$2,960.36	$117,039.64
2	$117,039.64	$3,760.36	$780.26	$2,980.10	$114,059.54
3	$114,059.54	$3,760.36	$760.40	$2,999.97	$111,059.57
4	$111,059.57	$3,760.36	$740.40	$3,019.97	$108,039.60
5	$108,039.60	$3,760.36	$720.26	$3,040.10	$104,999.50
6	$104,999.50	$3,760.36	$700.00	$3,060.37	$101,939.14
7	$101,939.14	$3,760.36	$679.59	$3,080.77	$98,858.37
8	$98,858.37	$3,760.36	$659.06	$3,101.31	$95,757.06
9	$95,757.06	$3,760.36	$638.38	$3,121.98	$92,635.07
10	$92,635.07	$3,760.36	$617.57	$3,142.80	$89,492.28
11	$89,492.28	$3,760.36	$596.62	$3,163.75	$86,328.53
12	$86,328.53	$3,760.36	$575.52	$3,184.84	$83,143.69
13	$83,143.69	$3,760.36	$554.29	$3,206.07	$79,937.62
14	$79,937.62	$3,760.36	$532.92	$3,227.45	$76,710.17
15	$76,710.17	$3,760.36	$511.40	$3,248.96	$73,461.21
16	$73,461.21	$3,760.36	$489.74	$3,270.62	$70,190.58
17	$70,190.58	$3,760.36	$467.94	$3,292.43	$66,898.16
18	$66,898.16	$3,760.36	$445.99	$3,314.38	$63,583.78
19	$63,583.78	$3,760.36	$423.89	$3,336.47	$60,247.31
20	$60,247.31	$3,760.36	$401.65	$3,358.72	$56,888.59
21	$56,888.59	$3,760.36	$379.26	$3,381.11	$53,507.49
22	$53,507.49	$3,760.36	$356.72	$3,403.65	$50,103.84
23	$50,103.84	$3,760.36	$334.03	$3,426.34	$46,677.50
24	$46,677.50	$3,760.36	$311.18	$3,449.18	$43,228.32
25	$43,228.32	$3,760.36	$288.19	$3,472.18	$39,756.15
26	$39,756.15	$3,760.36	$265.04	$3,495.32	$36,260.82
27	$36,260.82	$3,760.36	$241.74	$3,518.63	$32,742.20
28	$32,742.20	$3,760.36	$218.28	$3,542.08	$29,200.12
29	$29,200.12	$3,760.36	$194.67	$3,565.70	$25,634.42
30	$25,634.42	$3,760.36	$170.90	$3,589.47	$22,044.95
31	$22,044.95	$3,760.36	$146.97	$3,613.40	$18,431.56
32	$18,431.56	$3,760.36	$122.88	$3,637.49	$14,794.07
33	$14,794.07	$3,760.36	$98.63	$3,661.74	$11,132.33
34	$11,132.33	$3,760.36	$74.22	$3,686.15	$7,446.18
35	$7,446.18	$3,760.36	$49.64	$3,710.72	$3,735.46
36	$3,735.46	$3,760.36	$24.90	$3,735.46	$0.00