In: Math
This exercise is designed to be solved using technology such as
calculators or computer spreadsheets.
You borrow $18,000 with a term of four years at an APR of 8%. Make
an amortization table. How much equity have you built up halfway
through the term? (Round your answer to two decimal places.)
The following table captures the entire cash flow during the 48 months
Month | Payment | Interest Paid | Principal Paid | Balance | ||
Original Principal | 18000 | 0 | 18000 | |||
Loan term (Yrs) | 4 | 1 | 439.4326021 | $120.00 | $319.43 | $17,680.57 |
APR | 8% | 2 | 439.4326021 | $117.87 | $321.56 | $17,359.01 |
Payments per Yr | 12 | 3 | 439.4326021 | $115.73 | $323.71 | $17,035.30 |
4 | 439.4326021 | $113.57 | $325.86 | $16,709.44 | ||
Instalment | $439.43 | 5 | 439.4326021 | $111.40 | $328.04 | $16,381.40 |
6 | 439.4326021 | $109.21 | $330.22 | $16,051.18 | ||
7 | 439.4326021 | $107.01 | $332.42 | $15,718.75 | ||
8 | 439.4326021 | $104.79 | $334.64 | $15,384.11 | ||
9 | 439.4326021 | $102.56 | $336.87 | $15,047.24 | ||
10 | 439.4326021 | $100.31 | $339.12 | $14,708.12 | ||
11 | 439.4326021 | $98.05 | $341.38 | $14,366.74 | ||
12 | 439.4326021 | $95.78 | $343.65 | $14,023.09 | ||
13 | 439.4326021 | $93.49 | $345.95 | $13,677.14 | ||
14 | 439.4326021 | $91.18 | $348.25 | $13,328.89 | ||
15 | 439.4326021 | $88.86 | $350.57 | $12,978.32 | ||
16 | 439.4326021 | $86.52 | $352.91 | $12,625.41 | ||
17 | 439.4326021 | $84.17 | $355.26 | $12,270.14 | ||
18 | 439.4326021 | $81.80 | $357.63 | $11,912.51 | ||
19 | 439.4326021 | $79.42 | $360.02 | $11,552.50 | ||
20 | 439.4326021 | $77.02 | $362.42 | $11,190.08 | ||
21 | 439.4326021 | $74.60 | $364.83 | $10,825.25 | ||
22 | 439.4326021 | $72.17 | $367.26 | $10,457.98 | ||
23 | 439.4326021 | $69.72 | $369.71 | $10,088.27 | ||
24 | 439.4326021 | $67.26 | $372.18 | $9,716.09 | ||
25 | 439.4326021 | $64.77 | $374.66 | $9,341.44 | ||
26 | 439.4326021 | $62.28 | $377.16 | $8,964.28 | ||
27 | 439.4326021 | $59.76 | $379.67 | $8,584.61 | ||
28 | 439.4326021 | $57.23 | $382.20 | $8,202.41 | ||
29 | 439.4326021 | $54.68 | $384.75 | $7,817.66 | ||
30 | 439.4326021 | $52.12 | $387.31 | $7,430.34 | ||
31 | 439.4326021 | $49.54 | $389.90 | $7,040.44 | ||
32 | 439.4326021 | $46.94 | $392.50 | $6,647.95 | ||
33 | 439.4326021 | $44.32 | $395.11 | $6,252.84 | ||
34 | 439.4326021 | $41.69 | $397.75 | $5,855.09 | ||
35 | 439.4326021 | $39.03 | $400.40 | $5,454.69 | ||
36 | 439.4326021 | $36.36 | $403.07 | $5,051.62 | ||
37 | 439.4326021 | $33.68 | $405.76 | $4,645.87 | ||
38 | 439.4326021 | $30.97 | $408.46 | $4,237.41 | ||
39 | 439.4326021 | $28.25 | $411.18 | $3,826.22 | ||
40 | 439.4326021 | $25.51 | $413.92 | $3,412.30 | ||
41 | 439.4326021 | $22.75 | $416.68 | $2,995.61 | ||
42 | 439.4326021 | $19.97 | $419.46 | $2,576.15 | ||
43 | 439.4326021 | $17.17 | $422.26 | $2,153.89 | ||
44 | 439.4326021 | $14.36 | $425.07 | $1,728.82 | ||
45 | 439.4326021 | $11.53 | $427.91 | $1,300.91 | ||
46 | 439.4326021 | $8.67 | $430.76 | $870.15 | ||
47 | 439.4326021 | $5.80 | $433.63 | $436.52 | ||
48 | 439.4326021 | $2.91 | $436.52 | ($0.00) |
The monthly payment is calculated as the excel formula PMT(8%/12, 4*12, -18000)
The Interest paid is calculated as IPMT(8%/12, i, 4*12, -18000) where i denotes the months from 1 to 48.
The Principal paid is calculated as PPMT(8%/12, i, 4*12, -18000)
The Balance is calculated as the successive reductions of Principa oaid from the original principal.
Thus, the equity built to 24 months can be calculated now. $9,716.09 is the balance after 24 months, with an initial value of $18,000. Thus the equity built is $18,000 - $9,716.09 = $8283.91