amortization schedule for mortgage loan

Question

For a mortgage loan of $110,000 and a down payment of $20,000 financed at 10% for 25 years, construct the amortization schedule for the first years. Show five columns: payment number, monthly interest portion, monthly principal portion, monthly payment, and the balance. 

Answer 1

Table :  first year :  12 monthly payments

 

Payment number, Beginning principal balance, Monthly interest deduction, Monthly principal deduction, Monthly payment, Ending principal balance

 

 

  1 90000.00 -750.00 -67.83 -817.83 89932.17
  2 89932.17 -748.87 -68.96 -817.83 89863.21
  3 89863.21 -747.73 -70.10 -817.83 89793.11
  4 89793.11 -746.57 -71.26 -817.83 89721.85
  5 89721.85 -745.40 -72.43 -817.83 89649.42
  6 89649.42 -744.22 -73.61 -817.83 89575.81
  7 89575.81 -743.00 -74.83 -817.83 89500.98
  8 89500.98 -741.79 -76.04 -817.83 89424.94
  9 89424.94 -740.56 -77.27 -817.83 89347.67

10 89347.67 -739.33 -78.51 -817.83 89269.16
11 89269.16 -738.07 -79.76 -817.83 89189.40
12 89189.40 -736.80 -81.03 -817.83 89108.37

 

 

 

 

 

Answer 2

The first step is to determine the periodic interest rate. The payments are monthly so we need a monthly rate. If the 10% is a nominal annual rate, then the monthly rate is 10%/12 = 0.833%. If 10% is an annual effective rate, the monthly rate is 1.1^(1/12)-1=0.797%. I will assume 10% is a nominal annual rate.

 

Let's define some parameters:

 

L: loan amount = $110,000-$20,000 = $90,000

i: periodic interest rate = 0.833% per month

n: number of payments = 25*12 = 300

P: level monthly payment

 

The next step is to calculate the level monthly payment using this formula:

 

P=Li/(1-(1+i)^-n)

 

Plugging in the values above we get $817.83

 

The interest portion of the payment is the prior balance times i.

The principal portion is the difference between the payment and the interest portion.

 

The table will look like this,

 

 

Payment No interest principal payment balance
                                       $90,000.00
1 $750.00 $67.83 $817.83 $89,932.17
2 $749.43 $68.40 $817.83 $89,863.77
3 $748.86 $68.97 $817.83 $89,794.81
4 $748.29 $69.54 $817.83 $89,725.27
5 $747.71 $70.12 $817.83 $89,655.15
6 $747.13 $70.70 $817.83 $89,584.44
7 $746.54 $71.29 $817.83 $89,513.15
8 $745.94 $71.89 $817.83 $89,441.26
9 $745.34 $72.49 $817.83 $89,368.77
10 $744.74 $73.09 $817.83 $89,295.68
11 $744.13 $73.70 $817.83 $89,221.98
12 $743.52 $74.31 $817.83 $89,147.67
13 $742.90 $74.93 $817.83 $89,072.74
14 $742.27 $75.56 $817.83 $88,997.18
15 $741.64 $76.19 $817.83 $88,920.99
16 $741.01 $76.82 $817.83 $88,844.17
17 $740.37 $77.46 $817.83 $88,766.71
18 $739.72 $78.11 $817.83 $88,688.60
19 $739.07 $78.76 $817.83 $88,609.84
20 $738.42 $79.42 $817.83 $88,530.42
21 $737.75 $80.08 $817.83 $88,450.35
22 $737.09 $80.74 $817.83 $88,369.60
23 $736.41 $81.42 $817.83 $88,288.18
24 $735.73 $82.10 $817.83 $88,206.09
25 $735.05 $82.78 $817.83 $88,123.31
26 $734.36 $83.47 $817.83 $88,039.84
27 $733.67 $84.17 $817.83 $87,955.67
28 $732.96 $84.87 $817.83 $87,870.81
29 $732.26 $85.57 $817.83 $87,785.23
30 $731.54 $86.29 $817.83 $87,698.95
31 $730.82 $87.01 $817.83 $87,611.94
32 $730.10 $87.73 $817.83 $87,524.21
33 $729.37 $88.46 $817.83 $87,435.75
34 $728.63 $89.20 $817.83 $87,346.55
35 $727.89 $89.94 $817.83 $87,256.60
36 $727.14 $90.69 $817.83 $87,165.91