month:0 before interest balance:100000
month:0 after interest balance:101481.68333333333 interest:1481.6833333333332
month:1 before interest balance:101481.68333333333
month:1 after interest balance:102985.32052166945 interest:1503.637188336111
month:2 before interest balance:102985.32052166945
month:2 after interest balance:104511.23685161893 interest:1525.9163299494892
month:2 after payment balance:94011.23685161893 payment:10500
month:3 before interest balance:94011.23685161893
month:3 after interest balance:95404.18567950989 interest:1392.9488278909623
month:4 before interest balance:95404.18567950989
month:4 after interest balance:96817.77359802557 interest:1413.5879185156846
month:5 before interest balance:96817.77359802557
month:5 after interest balance:98252.30641313191 interest:1434.532815106345
month:5 after payment balance:87752.30641313191 payment:10500
month:6 before interest balance:87752.30641313191
month:6 after interest balance:89052.51771187088 interest:1300.2112987389733
month:7 before interest balance:89052.51771187088
month:7 after interest balance:90371.99402472138 interest:1319.4763128505053
month:8 before interest balance:90371.99402472138
month:8 after interest balance:91711.02079818667 interest:1339.0267734652925
month:8 after payment balance:81211.02079818667 payment:10500
month:9 before interest balance:81211.02079818667
month:9 after interest balance:82414.31095818327 interest:1203.2901599965987
month:10 before interest balance:82414.31095818327
month:10 after interest balance:83635.43006793218 interest:1221.1191097489084
month:11 before interest balance:83635.43006793218
month:11 after interest balance:84874.64229601038 interest:1239.2122280782062
month:11 after payment balance:74374.64229601038 payment:10500
month:12 before interest balance:74374.64229601038
month:12 after interest balance:75476.63897513665 interest:1101.9966791262698
month:13 before interest balance:75476.63897513665
month:13 after interest balance:76594.96375539142 interest:1118.3247802547705
month:14 before interest balance:76594.96375539142
month:14 after interest balance:77729.85856752776 interest:1134.8948121363421
month:14 after payment balance:67229.85856752776 payment:10500
month:15 before interest balance:67229.85856752776
month:15 after interest balance:68225.99217694638 interest:996.1336094186308
month:16 before interest balance:68225.99217694638
month:16 after interest balance:69236.8853320335 interest:1010.8931550871183
month:17 before interest balance:69236.8853320335
month:17 after interest balance:70262.75672251736 interest:1025.8713904838517
month:17 after payment balance:59762.756722517355 payment:10500
month:18 before interest balance:59762.756722517355
month:18 after interest balance:60648.25152841544 interest:885.4948058980858
month:19 before interest balance:60648.25152841544
month:19 after interest balance:61546.86656327005 interest:898.6150348546101
month:20 before interest balance:61546.86656327005
month:20 after interest balance:62458.79622732693 interest:911.9296640568783
month:20 after payment balance:51958.79622732693 payment:10500
month:21 before interest balance:51958.79622732693
month:21 after interest balance:52728.661051227864 interest:769.8648239009318
month:22 before interest balance:52728.661051227864
month:22 after interest balance:53509.93283391373 interest:781.271782685868
month:23 before interest balance:53509.93283391373
month:23 after interest balance:54302.78059039169 interest:792.8477564779606
month:23 after payment balance:43802.78059039169 payment:10500
month:24 before interest balance:43802.78059039169
month:24 after interest balance:44451.79908993609 interest:649.0184995444018
month:25 before interest balance:44451.79908993609
month:25 after interest balance:45110.43398841849 interest:658.6348984824014
month:26 before interest balance:45110.43398841849
month:26 after interest balance:45778.82777041922 interest:668.3937820007319
month:26 after payment balance:35278.82777041922 payment:10500
month:27 before interest balance:35278.82777041922
month:27 after interest balance:35801.5482816889 interest:522.7205112696732
month:28 before interest balance:35801.5482816889
month:28 after interest balance:36332.01385565397 interest:530.4655739650708
month:29 before interest balance:36332.01385565397
month:29 after interest balance:36870.33924961755 interest:538.3253939635822
month:29 after payment balance:26370.33924961755 payment:10500
month:30 before interest balance:26370.33924961755
month:30 after interest balance:26761.06417122259 interest:390.72492160504163
month:31 before interest balance:26761.06417122259
month:31 after interest balance:27157.578398870235 interest:396.5142276476433
month:32 before interest balance:27157.578398870235
month:32 after interest balance:27559.96771174323 interest:402.38931287299374
month:32 after payment balance:17059.96771174323 payment:10500
month:33 before interest balance:17059.96771174323
month:33 after interest balance:17312.742410000177 interest:252.77469825694746
month:34 before interest balance:17312.742410000177
month:34 after interest balance:17569.26242883208 interest:256.5200188319043
month:35 before interest balance:17569.26242883208
month:35 after interest balance:17829.58326202968 interest:260.3208331976001
month:35 after payment balance:7329.583262029679 payment:10500
Here is the algorithm used to calculate this:
<html>
<body>
<pre>
<script>
function log(arg) {
document.body.innerHTML = document.body.innerHTML + arg + '\n<br>';
}
function clear() {
document.body.innerHTML = '';
}
var rate = .177802;
var balance = 100000;
var payment = 10500;
for(var i=0; i<12; i++){
for(var j=0; j<3; j++) {
log('month:'+(i*3+j)+' before interest balance:'+balance);
var interest = balance*rate/12;
balance = balance + interest;
log('month:'+(i*3+j)+' after interest balance:'+balance+' interest:' + interest);
if(j == 2) {
balance = balance - payment;
log('month:'+(i*3+j)+' after payment balance:'+balance+' payment:' + payment);
}
}
}
</script>
</pre>
</body>
</html>
<body>
<pre>
<script>
function log(arg) {
document.body.innerHTML = document.body.innerHTML + arg + '\n<br>';
}
function clear() {
document.body.innerHTML = '';
}
var rate = .177802;
var balance = 100000;
var payment = 10500;
for(var i=0; i<12; i++){
for(var j=0; j<3; j++) {
log('month:'+(i*3+j)+' before interest balance:'+balance);
var interest = balance*rate/12;
balance = balance + interest;
log('month:'+(i*3+j)+' after interest balance:'+balance+' interest:' + interest);
if(j == 2) {
balance = balance - payment;
log('month:'+(i*3+j)+' after payment balance:'+balance+' payment:' + payment);
}
}
}
</script>
</pre>
</body>
</html>
Reply@
ReplyDeleteScott IzuNovember 5, 2015 at 11:18 AM
Please see this blog post. http://sizustech.blogspot.com/2015/11/calculating-loan-amortization-with.html
I used the loan amount, interest rate and monthly payments you supplied. After 36 months, you would still owe $7,329. Please post further questions there and I can help you further.
Sir,
Here you calculated amortization of loan with help of interest rate but i have problem to find Interest Rate for quarterly payments. If i don't know interest rate then how could i do calculations further?
It is hard to solve for interest rate. Here is what I would do. Calculate the total owed after 36 months with an initial guess for the interest rate. If the total owed is greater than zero, your guessed interest rate is too high. If the total owed after 36 months is less than zero, your guessed interest rate is too low. Continue guessing until you narrow down the interest rate. This is a binary search solution rather than a direct calculation. For higher order polynomials, direct calculations are difficult.
ReplyDeleteAs per your suggestion i did calculation of interest rate for Monthly installment but same procedure will give different answer for quarterly installment.
DeleteBelow is the code that i used for monthly calculations.
m_Principle = p;
m_MonthalyPayments = m;
m_NoOfMonths = n;
double temp = (m_MonthalyPayments / m_Principle);
double answer = temp;
double diff = 100;
double numerator = 0;
double denomenator = 0;
double accuracy = .00001;
int maxIteration = 1000;
int index = 0;
try
{
for (index = 0; ((diff > accuracy) && (index < maxIteration)); index++)
{
temp = answer;
numerator = (m_Principle * (temp / m_MonthalyPayments)) + Math.Pow((1 + temp), -m_NoOfMonths) - 1;
denomenator = (m_Principle / m_MonthalyPayments) - (m_NoOfMonths * Math.Pow((1 + temp), (-m_NoOfMonths - 1)));
answer = temp - (numerator / denomenator);
diff = answer - temp;
if (diff < 0)
{
diff = -diff;
}
}
answer *= 1200;
if ((answer < 0) || Double.IsNaN(answer) || (index == maxIteration))
{
throw new ArithmeticException();
}
}
catch (Exception ex)
{
}
return answer;
In this code i used 400 instead of 1200 for quarterly installment calculation. this will give installment with quarterly compounding. but i need monthly compounding for quarterly installment.
I converted your code to javascript and entered the values for principle, monthly payments and number of months.
Deletevar m_Principle = 100000;
var m_MonthalyPayments = 10500;
var m_NoOfMonths = 36;
var temp = (m_MonthalyPayments / m_Principle);
var answer = temp;
var diff = 100;
var numerator = 0;
var denomenator = 0;
var accuracy = .00001;
var maxIteration = 1000;
for (var index = 0; ((diff > accuracy) && (index < maxIteration)); index++) {
temp = answer;
numerator = (m_Principle * (temp / m_MonthalyPayments)) + Math.pow((1 + temp), -m_NoOfMonths) - 1;
denomenator = (m_Principle / m_MonthalyPayments) - (m_NoOfMonths * Math.pow((1 + temp), (-m_NoOfMonths - 1)));
answer = temp - (numerator / denomenator);
diff = Math.abs(answer - temp);
}
answer *= 1200;
log('answer:'+answer);
I got this answer...
answer:122.15664432826964
So I think some of your calculations and algorithm need to be tweaked. I am not sure what it is currently doing.
By the way, for monthly compounding and quarterly installment, you will need two equations. One for months where the interest is compounded but no payment is made. A second equation is needed for months where the interest is compound and a payment is also made.