Advance Loan Amortization EMI Calc
Advance Loan Amortization EMI Calc
Advance Loan Amortization EMI Calc
Cells filled with yellow colour are input cells. Please fill it with relevent data
Cells filled with pink colour are output cells.
However , When you take up a loan, the actual EMI coming is not exactly the same as what you are getting in the above s
Also, There may be a chance that
Put values and cell D10 same as C3 and expand it till bottom. You can double click as shown below to ca
Ignore Negative values that are showing at the end part of sheet i.e. row 250 where no of due paym
Sheet "Loan Amortization- Advance e.g." is just to show the below cases
CASE 1 change in Interest rate and corresponding change in EMI keeping the tenure same
Let say in July'2009 interest rate got changed to 10.5% , put the new rate in cell D18 and carry the same ra
you will observe that in cell F18, EMI got increased to 10066 , the actual EMI deducted is say 10100, put it
CASE 2 change in Interest rate and corresponding change in tenure keeping the EMI same
Let say in Dec'2009 interest rate got changed to 11% , put the new rate in cell D21 and carry the same rate
you will observe that in cell F21, EMI got increased to 10400 , to keep the EMI same as 10100, we need to
Now putting the new tenture in F22 will do this.
CASE 3 You make a part payment and want to change either your EMI and / or your tenure
Let say in Feb,2010 , you do a part payment of 30000 , put it in cell J25 and mark it to red colour
you will observe in F26 that your EMI is reduced to 9789
Now you can either change your EMI or your tenure. In the example I chaged G26 to 9790 , my new EMI
I Know you are going to love this excel tool. Finally this is what I want to make sure
Do remember that making part payment is always better that investing your money somewhere else.
helpful when you are planning to take up a loan
w much you are paying for the loan e.g. Total interest
ou are getting in the above sheet may be due to rounding and lending company practices
ey are showing.
ell D18 and carry the same rate it till end. Just mark it to red colour , which indicate a change
deducted is say 10100, put it in cell G18, change its colour to red and carry the same value till end
D21 and carry the same rate it till end. Just mark it to red colour , which indicate a change
I same as 10100, we need to increase the tenure in cell B22, sunch that F22 will reduced to app. 10100
mewhere else.
Enter Values Loan Summary
Loan Amount Rs.100,000.00 Scheduled Payment - EMI Rs.965.02
Annual Interest Rate 10.00 % Scheduled Number of Payments 240
Loan Period in Years 20 Actual Number of Payments 240
Number of Payments Per Year 12 Total Early Payments Rs.-
Start Date of Loan 1/Nov/08 Total Interest Rs.131,605.19
Optional Extra Payments
Lender Name:
Scheduled Extra
Payment Date Beginning Balance Payment Payment Total Payment Principal Interest Ending Balance
12/1/2008 Rs.100,000.00 Rs.965.02 Rs.- Rs.965.02 Rs.131.69 Rs.833.33 Rs.99,868.31
1/1/2009 99,868.31 965.02 - 965.02 132.79 832.24 99,735.53
2/1/2009 99,735.53 965.02 - 965.02 133.89 831.13 99,601.63
3/1/2009 99,601.63 965.02 - 965.02 135.01 830.01 99,466.63
4/1/2009 99,466.63 965.02 - 965.02 136.13 828.89 99,330.49
5/1/2009 99,330.49 965.02 965.02 137.27 827.75 99,193.23
6/1/2009 99,193.23 965.02 - 965.02 138.41 826.61 99,054.81
7/1/2009 99,054.81 965.02 - 965.02 139.56 825.46 98,915.25
8/1/2009 98,915.25 965.02 - 965.02 140.73 824.29 98,774.52
9/1/2009 98,774.52 965.02 - 965.02 141.90 823.12 98,632.62
10/1/2009 98,632.62 965.02 - 965.02 143.08 821.94 98,489.54
11/1/2009 98,489.54 965.02 965.02 144.28 820.75 98,345.26
12/1/2009 98,345.26 965.02 - 965.02 145.48 819.54 98,199.78
1/1/2010 98,199.78 965.02 - 965.02 146.69 818.33 98,053.09
2/1/2010 98,053.09 965.02 - 965.02 147.91 817.11 97,905.18
3/1/2010 97,905.18 965.02 - 965.02 149.15 815.88 97,756.04
4/1/2010 97,756.04 965.02 - 965.02 150.39 814.63 97,605.65
5/1/2010 97,605.65 965.02 965.02 151.64 813.38 97,454.01
6/1/2010 97,454.01 965.02 - 965.02 152.90 812.12 97,301.10
7/1/2010 97,301.10 965.02 - 965.02 154.18 810.84 97,146.92
8/1/2010 97,146.92 965.02 - 965.02 155.46 809.56 96,991.46
9/1/2010 96,991.46 965.02 - 965.02 156.76 808.26 96,834.70
10/1/2010 96,834.70 965.02 - 965.02 158.07 806.96 96,676.63
11/1/2010 96,676.63 965.02 - 965.02 159.38 805.64 96,517.25
12/1/2010 96,517.25 965.02 - 965.02 160.71 804.31 96,356.54
1/1/2011 96,356.54 965.02 - 965.02 162.05 802.97 96,194.49
2/1/2011 96,194.49 965.02 - 965.02 163.40 801.62 96,031.09
Scheduled Extra
Payment Date Beginning Balance Payment Payment Total Payment Principal Interest Ending Balance
3/1/2011 96,031.09 965.02 - 965.02 164.76 800.26 95,866.33
4/1/2011 95,866.33 965.02 - 965.02 166.14 798.89 95,700.19
5/1/2011 95,700.19 965.02 - 965.02 167.52 797.50 95,532.67
6/1/2011 95,532.67 965.02 - 965.02 168.92 796.11 95,363.75
7/1/2011 95,363.75 965.02 - 965.02 170.32 794.70 95,193.43
8/1/2011 95,193.43 965.02 - 965.02 171.74 793.28 95,021.69
9/1/2011 95,021.69 965.02 - 965.02 173.17 791.85 94,848.51
10/1/2011 94,848.51 965.02 - 965.02 174.62 790.40 94,673.90
11/1/2011 94,673.90 965.02 - 965.02 176.07 788.95 94,497.82
12/1/2011 94,497.82 965.02 - 965.02 177.54 787.48 94,320.28
1/1/2012 94,320.28 965.02 - 965.02 179.02 786.00 94,141.26
2/1/2012 94,141.26 965.02 - 965.02 180.51 784.51 93,960.75
3/1/2012 93,960.75 965.02 - 965.02 182.02 783.01 93,778.74
4/1/2012 93,778.74 965.02 - 965.02 183.53 781.49 93,595.20
5/1/2012 93,595.20 965.02 - 965.02 185.06 779.96 93,410.14
6/1/2012 93,410.14 965.02 - 965.02 186.60 778.42 93,223.54
7/1/2012 93,223.54 965.02 - 965.02 188.16 776.86 93,035.38
8/1/2012 93,035.38 965.02 - 965.02 189.73 775.29 92,845.65
9/1/2012 92,845.65 965.02 - 965.02 191.31 773.71 92,654.35
10/1/2012 92,654.35 965.02 - 965.02 192.90 772.12 92,461.44
11/1/2012 92,461.44 965.02 - 965.02 194.51 770.51 92,266.93
12/1/2012 92,266.93 965.02 - 965.02 196.13 768.89 92,070.80
1/1/2013 92,070.80 965.02 - 965.02 197.76 767.26 91,873.04
2/1/2013 91,873.04 965.02 - 965.02 199.41 765.61 91,673.63
3/1/2013 91,673.63 965.02 - 965.02 201.07 763.95 91,472.55
4/1/2013 91,472.55 965.02 - 965.02 202.75 762.27 91,269.80
5/1/2013 91,269.80 965.02 - 965.02 204.44 760.58 91,065.36
6/1/2013 91,065.36 965.02 - 965.02 206.14 758.88 90,859.22
7/1/2013 90,859.22 965.02 - 965.02 207.86 757.16 90,651.36
8/1/2013 90,651.36 965.02 - 965.02 209.59 755.43 90,441.76
9/1/2013 90,441.76 965.02 - 965.02 211.34 753.68 90,230.42
10/1/2013 90,230.42 965.02 - 965.02 213.10 751.92 90,017.32
11/1/2013 90,017.32 965.02 - 965.02 214.88 750.14 89,802.44
12/1/2013 89,802.44 965.02 - 965.02 216.67 748.35 89,585.77
1/1/2014 89,585.77 965.02 - 965.02 218.47 746.55 89,367.30
2/1/2014 89,367.30 965.02 - 965.02 220.29 744.73 89,147.01
3/1/2014 89,147.01 965.02 - 965.02 222.13 742.89 88,924.88
4/1/2014 88,924.88 965.02 - 965.02 223.98 741.04 88,700.90
5/1/2014 88,700.90 965.02 - 965.02 225.85 739.17 88,475.05
6/1/2014 88,475.05 965.02 - 965.02 227.73 737.29 88,247.32
7/1/2014 88,247.32 965.02 - 965.02 229.63 735.39 88,017.69
8/1/2014 88,017.69 965.02 - 965.02 231.54 733.48 87,786.15
Scheduled Extra
Payment Date Beginning Balance Payment Payment Total Payment Principal Interest Ending Balance
9/1/2014 87,786.15 965.02 - 965.02 233.47 731.55 87,552.68
10/1/2014 87,552.68 965.02 - 965.02 235.42 729.61 87,317.26
11/1/2014 87,317.26 965.02 - 965.02 237.38 727.64 87,079.89
12/1/2014 87,079.89 965.02 - 965.02 239.36 725.67 86,840.53
1/1/2015 86,840.53 965.02 - 965.02 241.35 723.67 86,599.18
2/1/2015 86,599.18 965.02 - 965.02 243.36 721.66 86,355.82
3/1/2015 86,355.82 965.02 - 965.02 245.39 719.63 86,110.43
4/1/2015 86,110.43 965.02 - 965.02 247.43 717.59 85,862.99
5/1/2015 85,862.99 965.02 - 965.02 249.50 715.52 85,613.50
6/1/2015 85,613.50 965.02 - 965.02 251.58 713.45 85,361.92
7/1/2015 85,361.92 965.02 - 965.02 253.67 711.35 85,108.25
8/1/2015 85,108.25 965.02 - 965.02 255.79 709.24 84,852.46
9/1/2015 84,852.46 965.02 - 965.02 257.92 707.10 84,594.55
10/1/2015 84,594.55 965.02 - 965.02 260.07 704.95 84,334.48
11/1/2015 84,334.48 965.02 - 965.02 262.23 702.79 84,072.24
12/1/2015 84,072.24 965.02 - 965.02 264.42 700.60 83,807.82
1/1/2016 83,807.82 965.02 - 965.02 266.62 698.40 83,541.20
2/1/2016 83,541.20 965.02 - 965.02 268.84 696.18 83,272.36
3/1/2016 83,272.36 965.02 - 965.02 271.09 693.94 83,001.27
4/1/2016 83,001.27 965.02 - 965.02 273.34 691.68 82,727.93
5/1/2016 82,727.93 965.02 - 965.02 275.62 689.40 82,452.30
6/1/2016 82,452.30 965.02 - 965.02 277.92 687.10 82,174.38
7/1/2016 82,174.38 965.02 - 965.02 280.24 684.79 81,894.15
8/1/2016 81,894.15 965.02 - 965.02 282.57 682.45 81,611.58
9/1/2016 81,611.58 965.02 - 965.02 284.93 680.10 81,326.65
10/1/2016 81,326.65 965.02 - 965.02 287.30 677.72 81,039.35
11/1/2016 81,039.35 965.02 - 965.02 289.69 675.33 80,749.66
12/1/2016 80,749.66 965.02 - 965.02 292.11 672.91 80,457.55
1/1/2017 80,457.55 965.02 - 965.02 294.54 670.48 80,163.01
2/1/2017 80,163.01 965.02 - 965.02 297.00 668.03 79,866.01
3/1/2017 79,866.01 965.02 - 965.02 299.47 665.55 79,566.54
4/1/2017 79,566.54 965.02 - 965.02 301.97 663.05 79,264.58
5/1/2017 79,264.58 965.02 - 965.02 304.48 660.54 78,960.09
6/1/2017 78,960.09 965.02 - 965.02 307.02 658.00 78,653.07
7/1/2017 78,653.07 965.02 - 965.02 309.58 655.44 78,343.49
8/1/2017 78,343.49 965.02 - 965.02 312.16 652.86 78,031.33
9/1/2017 78,031.33 965.02 - 965.02 314.76 650.26 77,716.57
10/1/2017 77,716.57 965.02 - 965.02 317.38 647.64 77,399.19
11/1/2017 77,399.19 965.02 - 965.02 320.03 644.99 77,079.16
12/1/2017 77,079.16 965.02 - 965.02 322.70 642.33 76,756.47
1/1/2018 76,756.47 965.02 - 965.02 325.38 639.64 76,431.08
2/1/2018 76,431.08 965.02 - 965.02 328.10 636.93 76,102.98
Scheduled Extra
Payment Date Beginning Balance Payment Payment Total Payment Principal Interest Ending Balance
3/1/2018 76,102.98 965.02 - 965.02 330.83 634.19 75,772.15
4/1/2018 75,772.15 965.02 - 965.02 333.59 631.43 75,438.57
5/1/2018 75,438.57 965.02 - 965.02 336.37 628.65 75,102.20
6/1/2018 75,102.20 965.02 - 965.02 339.17 625.85 74,763.03
7/1/2018 74,763.03 965.02 - 965.02 342.00 623.03 74,421.03
8/1/2018 74,421.03 965.02 - 965.02 344.85 620.18 74,076.19
9/1/2018 74,076.19 965.02 - 965.02 347.72 617.30 73,728.47
10/1/2018 73,728.47 965.02 - 965.02 350.62 614.40 73,377.85
11/1/2018 73,377.85 965.02 - 965.02 353.54 611.48 73,024.31
12/1/2018 73,024.31 965.02 - 965.02 356.49 608.54 72,667.82
1/1/2019 72,667.82 965.02 - 965.02 359.46 605.57 72,308.37
2/1/2019 72,308.37 965.02 - 965.02 362.45 602.57 71,945.92
3/1/2019 71,945.92 965.02 - 965.02 365.47 599.55 71,580.44
4/1/2019 71,580.44 965.02 - 965.02 368.52 596.50 71,211.93
5/1/2019 71,211.93 965.02 - 965.02 371.59 593.43 70,840.34
6/1/2019 70,840.34 965.02 - 965.02 374.69 590.34 70,465.65
7/1/2019 70,465.65 965.02 - 965.02 377.81 587.21 70,087.84
8/1/2019 70,087.84 965.02 - 965.02 380.96 584.07 69,706.89
9/1/2019 69,706.89 965.02 - 965.02 384.13 580.89 69,322.76
10/1/2019 69,322.76 965.02 - 965.02 387.33 577.69 68,935.42
11/1/2019 68,935.42 965.02 - 965.02 390.56 574.46 68,544.86
12/1/2019 68,544.86 965.02 - 965.02 393.81 571.21 68,151.05
1/1/2020 68,151.05 965.02 - 965.02 397.10 567.93 67,753.95
2/1/2020 67,753.95 965.02 - 965.02 400.41 564.62 67,353.55
3/1/2020 67,353.55 965.02 - 965.02 403.74 561.28 66,949.81
4/1/2020 66,949.81 965.02 - 965.02 407.11 557.92 66,542.70
5/1/2020 66,542.70 965.02 - 965.02 410.50 554.52 66,132.20
6/1/2020 66,132.20 965.02 - 965.02 413.92 551.10 65,718.28
7/1/2020 65,718.28 965.02 - 965.02 417.37 547.65 65,300.91
8/1/2020 65,300.91 965.02 - 965.02 420.85 544.17 64,880.06
9/1/2020 64,880.06 965.02 - 965.02 424.35 540.67 64,455.71
10/1/2020 64,455.71 965.02 - 965.02 427.89 537.13 64,027.82
11/1/2020 64,027.82 965.02 - 965.02 431.46 533.57 63,596.36
12/1/2020 63,596.36 965.02 - 965.02 435.05 529.97 63,161.31
1/1/2021 63,161.31 965.02 - 965.02 438.68 526.34 62,722.63
2/1/2021 62,722.63 965.02 - 965.02 442.33 522.69 62,280.30
3/1/2021 62,280.30 965.02 - 965.02 446.02 519.00 61,834.28
4/1/2021 61,834.28 965.02 - 965.02 449.74 515.29 61,384.55
5/1/2021 61,384.55 965.02 - 965.02 453.48 511.54 60,931.06
6/1/2021 60,931.06 965.02 - 965.02 457.26 507.76 60,473.80
7/1/2021 60,473.80 965.02 - 965.02 461.07 503.95 60,012.73
Cumulative
Interest
Rs.833.33
1,665.57
2,496.70
3,326.71
4,155.60
4,983.35
5,809.97
6,635.42
7,459.72
8,282.84
9,104.78
9,925.52
10,745.07
11,563.40
12,380.51
13,196.38
14,011.02
14,824.40
15,636.51
16,447.36
17,256.91
18,065.18
18,872.13
19,677.77
20,482.08
21,285.05
22,086.67
Cumulative
Interest
22,886.93
23,685.82
24,483.32
25,279.42
26,074.12
26,867.40
27,659.25
28,449.65
29,238.60
30,026.08
30,812.09
31,596.60
32,379.60
33,161.09
33,941.05
34,719.47
35,496.33
36,271.63
37,045.34
37,817.46
38,587.97
39,356.86
40,124.12
40,889.73
41,653.68
42,415.95
43,176.53
43,935.41
44,692.57
45,448.00
46,201.68
46,953.60
47,703.74
48,452.10
49,198.64
49,943.37
50,686.26
51,427.30
52,166.48
52,903.77
53,639.16
54,372.64
Cumulative
Interest
55,104.20
55,833.80
56,561.45
57,287.11
58,010.78
58,732.44
59,452.07
60,169.66
60,885.19
61,598.63
62,309.98
63,019.22
63,726.32
64,431.27
65,134.06
65,834.66
66,533.06
67,229.24
67,923.18
68,614.85
69,304.25
69,991.35
70,676.14
71,358.59
72,038.69
72,716.41
73,391.74
74,064.65
74,735.13
75,403.16
76,068.71
76,731.76
77,392.30
78,050.30
78,705.74
79,358.61
80,008.87
80,656.50
81,301.50
81,943.82
82,583.46
83,220.39
Cumulative
Interest
83,854.58
84,486.01
85,114.67
85,740.52
86,363.55
86,983.72
87,601.02
88,215.43
88,826.91
89,435.44
90,041.01
90,643.58
91,243.13
91,839.63
92,433.06
93,023.40
93,610.61
94,194.68
94,775.57
95,353.26
95,927.72
96,498.93
97,066.85
97,631.47
98,192.75
98,750.67
99,305.19
99,856.29
100,403.94
100,948.12
101,488.78
102,025.91
102,559.48
103,089.45
103,615.79
104,138.48
104,657.48
105,172.77
105,684.31
106,192.07
106,696.02
Principal Value Rs.1,000,000.00
Start Date of Loan 1/Nov/08
Annual Interest Rate 10.00 %
Loan Period in Years 20
No of Payment In a Year 12
Total No of Payment for Loan 240
Rate / period 0.0083333333