In: Finance
what would be the principal ballon payment (that is excluding the fixed monthly payment) at the end of 5 years on a $150,000 loan with monthly payments based on a 30-year term at 12 percent compounded monthly?
the answer is 146,495 but i dont know how to get this answer.
n=30*12=360
r=0.12/12=0.01
PV=150,000
USing excel to get payments:
=PMT(0.01,360,-150000,0)
=$1,542.92
Now lets create amortization schedule for the 5 years:
No. | Due Date | Payment | Extra Payments | Additional Payment | Interest | Principal | Balance | |
$1,50,000.00 | ||||||||
1 | 01-10-2013 | 1,542.92 | 0.00 | 1,500.00 | 42.92 | 1,49,957.08 | ||
2 | 01-11-2013 | 1,542.92 | 0.00 | 1,499.57 | 43.35 | 1,49,913.73 | ||
3 | 01-12-2013 | 1,542.92 | 0.00 | 1,499.14 | 43.78 | 1,49,869.95 | ||
4 | 01-01-2014 | 1,542.92 | 0.00 | 1,498.70 | 44.22 | 1,49,825.73 | ||
5 | 01-02-2014 | 1,542.92 | 0.00 | 1,498.26 | 44.66 | 1,49,781.07 | ||
6 | 01-03-2014 | 1,542.92 | 0.00 | 1,497.81 | 45.11 | 1,49,735.96 | ||
7 | 01-04-2014 | 1,542.92 | 0.00 | 1,497.36 | 45.56 | 1,49,690.40 | ||
8 | 01-05-2014 | 1,542.92 | 0.00 | 1,496.90 | 46.02 | 1,49,644.38 | ||
9 | 01-06-2014 | 1,542.92 | 0.00 | 1,496.44 | 46.48 | 1,49,597.90 | ||
10 | 01-07-2014 | 1,542.92 | 0.00 | 1,495.98 | 46.94 | 1,49,550.96 | ||
11 | 01-08-2014 | 1,542.92 | 0.00 | 1,495.51 | 47.41 | 1,49,503.55 | ||
12 | 01-09-2014 | 1,542.92 | 0.00 | 1,495.04 | 47.88 | 1,49,455.67 | ||
13 | 01-10-2014 | 1,542.92 | 0.00 | 1,494.56 | 48.36 | 1,49,407.31 | ||
14 | 01-11-2014 | 1,542.92 | 0.00 | 1,494.07 | 48.85 | 1,49,358.46 | ||
15 | 01-12-2014 | 1,542.92 | 0.00 | 1,493.58 | 49.34 | 1,49,309.12 | ||
16 | 01-01-2015 | 1,542.92 | 0.00 | 1,493.09 | 49.83 | 1,49,259.29 | ||
17 | 01-02-2015 | 1,542.92 | 0.00 | 1,492.59 | 50.33 | 1,49,208.96 | ||
18 | 01-03-2015 | 1,542.92 | 0.00 | 1,492.09 | 50.83 | 1,49,158.13 | ||
19 | 01-04-2015 | 1,542.92 | 0.00 | 1,491.58 | 51.34 | 1,49,106.79 | ||
20 | 01-05-2015 | 1,542.92 | 0.00 | 1,491.07 | 51.85 | 1,49,054.94 | ||
21 | 01-06-2015 | 1,542.92 | 0.00 | 1,490.55 | 52.37 | 1,49,002.57 | ||
22 | 01-07-2015 | 1,542.92 | 0.00 | 1,490.03 | 52.89 | 1,48,949.68 | ||
23 | 01-08-2015 | 1,542.92 | 0.00 | 1,489.50 | 53.42 | 1,48,896.26 | ||
24 | 01-09-2015 | 1,542.92 | 0.00 | 1,488.96 | 53.96 | 1,48,842.30 | ||
25 | 01-10-2015 | 1,542.92 | 0.00 | 1,488.42 | 54.50 | 1,48,787.80 | ||
26 | 01-11-2015 | 1,542.92 | 0.00 | 1,487.88 | 55.04 | 1,48,732.76 | ||
27 | 01-12-2015 | 1,542.92 | 0.00 | 1,487.33 | 55.59 | 1,48,677.17 | ||
28 | 01-01-2016 | 1,542.92 | 0.00 | 1,486.77 | 56.15 | 1,48,621.02 | ||
29 | 01-02-2016 | 1,542.92 | 0.00 | 1,486.21 | 56.71 | 1,48,564.31 | ||
30 | 01-03-2016 | 1,542.92 | 0.00 | 1,485.64 | 57.28 | 1,48,507.03 | ||
31 | 01-04-2016 | 1,542.92 | 0.00 | 1,485.07 | 57.85 | 1,48,449.18 | ||
32 | 01-05-2016 | 1,542.92 | 0.00 | 1,484.49 | 58.43 | 1,48,390.75 | ||
33 | 01-06-2016 | 1,542.92 | 0.00 | 1,483.91 | 59.01 | 1,48,331.74 | ||
34 | 01-07-2016 | 1,542.92 | 0.00 | 1,483.32 | 59.60 | 1,48,272.14 | ||
35 | 01-08-2016 | 1,542.92 | 0.00 | 1,482.72 | 60.20 | 1,48,211.94 | ||
36 | 01-09-2016 | 1,542.92 | 0.00 | 1,482.12 | 60.80 | 1,48,151.14 | ||
37 | 01-10-2016 | 1,542.92 | 0.00 | 1,481.51 | 61.41 | 1,48,089.73 | ||
38 | 01-11-2016 | 1,542.92 | 0.00 | 1,480.90 | 62.02 | 1,48,027.71 | ||
39 | 01-12-2016 | 1,542.92 | 0.00 | 1,480.28 | 62.64 | 1,47,965.07 | ||
40 | 01-01-2017 | 1,542.92 | 0.00 | 1,479.65 | 63.27 | 1,47,901.80 | ||
41 | 01-02-2017 | 1,542.92 | 0.00 | 1,479.02 | 63.90 | 1,47,837.90 | ||
42 | 01-03-2017 | 1,542.92 | 0.00 | 1,478.38 | 64.54 | 1,47,773.36 | ||
43 | 01-04-2017 | 1,542.92 | 0.00 | 1,477.73 | 65.19 | 1,47,708.17 | ||
44 | 01-05-2017 | 1,542.92 | 0.00 | 1,477.08 | 65.84 | 1,47,642.33 | ||
45 | 01-06-2017 | 1,542.92 | 0.00 | 1,476.42 | 66.50 | 1,47,575.83 | ||
46 | 01-07-2017 | 1,542.92 | 0.00 | 1,475.76 | 67.16 | 1,47,508.67 | ||
47 | 01-08-2017 | 1,542.92 | 0.00 | 1,475.09 | 67.83 | 1,47,440.84 | ||
48 | 01-09-2017 | 1,542.92 | 0.00 | 1,474.41 | 68.51 | 1,47,372.33 | ||
49 | 01-10-2017 | 1,542.92 | 0.00 | 1,473.72 | 69.20 | 1,47,303.13 | ||
50 | 01-11-2017 | 1,542.92 | 0.00 | 1,473.03 | 69.89 | 1,47,233.24 | ||
51 | 01-12-2017 | 1,542.92 | 0.00 | 1,472.33 | 70.59 | 1,47,162.65 | ||
52 | 01-01-2018 | 1,542.92 | 0.00 | 1,471.63 | 71.29 | 1,47,091.36 | ||
53 | 01-02-2018 | 1,542.92 | 0.00 | 1,470.91 | 72.01 | 1,47,019.35 | ||
54 | 01-03-2018 | 1,542.92 | 0.00 | 1,470.19 | 72.73 | 1,46,946.62 | ||
55 | 01-04-2018 | 1,542.92 | 0.00 | 1,469.47 | 73.45 | 1,46,873.17 | ||
56 | 01-05-2018 | 1,542.92 | 0.00 | 1,468.73 | 74.19 | 1,46,798.98 | ||
57 | 01-06-2018 | 1,542.92 | 0.00 | 1,467.99 | 74.93 | 1,46,724.05 | ||
58 | 01-07-2018 | 1,542.92 | 0.00 | 1,467.24 | 75.68 | 1,46,648.37 | ||
59 | 01-08-2018 | 1,542.92 | 0.00 | 1,466.48 | 76.44 | 1,46,571.93 | ||
60 | 01-09-2018 | 1,542.92 | 0.00 | 1,465.72 | 77.20 | 1,46,494.73 | ||
61 | 01-10-2018 | 1,542.92 | 0.00 | 1,464.95 | 77.97 | 1,46,416.76 | ||
62 | 01-11-2018 | 1,542.92 | 0.00 | 1,464.17 | 78.75 | 1,46,338.01 |
As we can see that last payment at end of period 60 (5*12) is
146,494.73.
Interest = 150,000*0.01=1500
principal = payment- inetrest=1542.92-1500=42.92
This reduces the outstanding principal, which is further used to
calculate next interest payment