• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1330
  • Last Modified:

loans Intrest calculation



hi
 please help me  to develop a loan calculator for solving
following data?
 


Loan amount 10000
Payment amount 121
No of Weeks 104
Intrest Rate ?


the above is the information with me. here is  the payment
schedule as  follows
i would like to know how can this type of payment
schedule will be generated. from the above data?

sincerely


rajesh

No             Interest  Principal    Balance
Loan                                         10,000.00
1     121.00     45.66      75.34   9,924.66
2     121.00     45.32      75.68   9,848.98
3     121.00     44.97      76.03   9,772.95
4     121.00     44.62      76.38   9,696.57
5     121.00     44.28      76.72   9,619.85
6     121.00     43.93      77.07   9,542.78
7     121.00     43.57      77.43   9,465.35
8     121.00     43.22      77.78   9,387.57
9     121.00     42.86      78.14   9,309.43
10    121.00     42.51      78.49   9,230.94
11    121.00     42.15      78.85   9,152.09
12    121.00     41.79      79.21   9,072.88
13    121.00     41.43      79.57   8,993.31
14    121.00     41.06      79.94   8,913.37
15    121.00     40.70      80.30   8,833.07
16    121.00     40.33      80.67   8,752.40
17    121.00     39.96      81.04   8,671.36
18    121.00     39.59      81.41   8,589.95
19    121.00     39.22      81.78   8,508.17
20    121.00     38.85      82.15   8,426.02
21    121.00     38.47      82.53   8,343.49
22    121.00     38.10      82.90   8,260.59
23    121.00     37.72      83.28   8,177.31
24    121.00     37.34      83.66   8,093.65
25    121.00     36.96      84.04   8,009.61
26    121.00     36.57      84.43   7,925.18
27    121.00     36.19      84.81   7,840.37
28    121.00     35.80      85.20   7,755.17
29    121.00     35.41      85.59   7,669.58
30    121.00     35.02      85.98   7,583.60
31    121.00     34.63      86.37   7,497.23
32    121.00     34.23      86.77   7,410.46
33    121.00     33.84      87.16   7,323.30
34    121.00     33.44      87.56   7,235.74
35    121.00     33.04      87.96   7,147.78
36    121.00     32.64      88.36   7,059.42
37    121.00     32.23      88.77   6,970.65
38    121.00     31.83      89.17   6,881.48
39    121.00     31.42      89.58   6,791.90
40    121.00     31.01      89.99   6,701.91
41    121.00     30.60      90.40   6,611.51
42    121.00     30.19      90.81   6,520.70
43    121.00     29.77      91.23   6,429.47
44    121.00     29.36      91.64   6,337.83
45    121.00     28.94      92.06   6,245.77
46    121.00     28.52      92.48   6,153.29
47    121.00     28.10      92.90   6,060.39
48    121.00     27.67      93.33   5,967.06
49    121.00     27.25      93.75   5,873.31
50    121.00     26.82      94.18   5,779.13
51    121.00     26.39      94.61   5,684.52
52    121.00     25.96      95.04   5,589.48
53    121.00     25.52      95.48   5,494.00
54    121.00     25.09      95.91   5,398.09
55    121.00     24.65      96.35   5,301.74
56    121.00     24.21      96.79   5,204.95
57    121.00     23.77      97.23   5,107.72
58    121.00     23.32      97.68   5,010.04
59    121.00     22.88      98.12   4,911.92
60    121.00     22.43      98.57   4,813.35
61    121.00     21.98      99.02   4,714.33
62    121.00     21.53      99.47   4,614.86
63    121.00     21.07      99.93   4,514.93
64    121.00     20.62     100.38   4,414.55
65    121.00     20.16     100.84   4,313.71
66    121.00     19.70     101.30   4,212.41
67    121.00     19.23     101.77   4,110.64

68    121.00     18.77     102.23   4,008.41
69    121.00     18.30     102.70   3,905.71
70    121.00     17.83     103.17   3,802.54
71    121.00     17.36     103.64   3,698.90
72    121.00     16.89     104.11   3,594.79
73    121.00     16.41     104.59   3,490.20
74    121.00     15.94     105.06   3,385.14
75    121.00     15.46     105.54   3,279.60
76    121.00     14.98     106.02   3,173.58
77    121.00     14.49     106.51   3,067.07
78    121.00     14.00     107.00   2,960.07
79    121.00     13.52     107.48   2,852.59
80    121.00     13.03     107.97   2,744.62
81    121.00     12.53     108.47   2,636.15
82    121.00     12.04     108.96   2,527.19
83    121.00     11.54     109.46   2,417.73
84    121.00     11.04     109.96   2,307.77
85    121.00     10.54     110.46   2,197.31
86    121.00     10.03     110.97   2,086.34
87    121.00      9.53     111.47   1,974.87
88    121.00      9.02     111.98   1,862.89
89    121.00      8.51     112.49   1,750.40
90    121.00      7.99     113.01   1,637.39
91    121.00      7.48     113.52   1,523.87
92    121.00      6.96     114.04   1,409.83
93    121.00      6.44     114.56   1,295.27
94    121.00      5.91     115.09   1,180.18
95    121.00      5.39     115.61   1,064.57
96    121.00      4.86     116.14     948.43
97    121.00      4.33     116.67     831.76
98    121.00      3.80     117.20     714.56
99    121.00      3.26     117.74     596.82
100   121.00      2.73     118.27     478.55
101   121.00      2.19     118.81     359.74
102   121.00      1.64     119.36     240.38
103   121.00      1.10     119.90     120.48
104   121.03      0.55     120.48          



0
rajeshnalankal
Asked:
rajeshnalankal
  • 15
  • 6
  • 4
  • +4
1 Solution
 
ozoCommented:
#!/usr/bin/perl
sub getinterest{
  # P =  Principle
  # r = interest Rate
  # S = Payemnt
  # n = Number of Payments

  my ($P,$m,$n) = @_;
  my $r0 = 0;
  my $r1 = 1;
  $S = sprintf"%.3f",$S;
  while( $r0 < $r1 ){
       my $r = ($r0+$r1)/2;
       my $s = sprintf"%.3f",($P*$r*((1+$r)**$n))/(((1+$r)**$n)-1.0);
       if( $S <= $s ){ $r1 = $r; }
       if( $S >= $s ){ $r0 = $r; }
  }
  return $r0;
}
$L = 10000;
$S = 121;
$n = 104;
$r = getinterest($L,$S,$n);
for( 1..$n ){
    $i = $L*$r;
    $p = sprintf"%.2f",$S-$i;
    $L -= $p;
    printf "%d\t%.2f\t\%.2f\t%.2f\t%.2f\n",$_,$S,$i,$p,$L;
}
0
 
criCommented:
If you have Excel, use it. Search the net, there must be a freeware available. Or make your own. As example, however w/o generating the table, see  http://www.accountingweb.com/cgi-bin/item.cgi?id=89234

On-Line: http://www.bankrate.com/goocalg/mortgage-calculator.asp?nav=mtg&page=calc_home
0
 
GwynforWebCommented:
Here is a Javascript program (put into a .htm and open in a browser or is easily converted to C or Java). I hopes this helps if you have any questions just ask.  GwynforWeb

<script>

Payment=121.0
n=104
Debt=10000.0

/* Calculate interest rate*/
Osmall=-1000
Obig=1000
Ismall=0
Ibig=(n*Payment-Debt)/(n*Payment)
Interest=(Ibig+Ismall)/2
do
{  Owed=Debt
   Interest=(Ismall+Ibig)/2
   for (i=1;i<=n;i++)
   {Owed=Owed*(1+Interest)-Payment}
   
   if ( Owed < 0 )
   {Osmall=Owed
    Ismall=Interest}
    else
    {Obig=Owed
    Ibig=Interest}

}

/* Output results */
while (Math.abs(Owed) > 0.001)
document.write('Interest=',Interest,'<br>')
Owed=Debt
document.write('......    Balance.......... ............ ..........Principal........ ............ ........Interest<br>' )
for (i=1;i<=n;i++)
   {Owedlast=Owed
    Owed=Owed*(1+Interest)-Payment
    document.write(i,'...  ',Owed,'...  ',Debt-Owed,'...  ',121-(Owedlast-Owed),'  ','<br>')}


</script>

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
GwynforWebCommented:
rajeshnalankal
  I should also point out that the above code is designed to avoid round off errors associated with raising numbers to high powers

 I have a comment statement in the wrong place, for clarity

/* Output results */
while (Math.abs(Owed) > 0.001)

should read

while (Math.abs(Owed) > 0.001)

/* Output results */

T
hanks again

GwynforWeb

0
 
GwynforWebCommented:
document.write(i,'...  ',Owed,'...  ',Debt-Owed,'...  ',121-(Owedlast-Owed),'  ','<br>')}

should read to be general

document.write(i,'...  ',Owed,'...  ',Debt-Owed,'...  ',Payment-(Owedlast-Owed),'  ','<br>')}

0
 
GwynforWebCommented:
everything tidied up gives (sorry for the typos I am the typo king of this area) :-)

<script>

Payment=121.0
n=104
Debt=10000.0

/* Calculate interest rate*/
Osmall=-1000
Obig=1000
Ismall=0
Ibig=(n*Payment-Debt)/(n*Payment)
Interest=(Ibig+Ismall)/2
do
{  Owed=Debt
   Interest=(Ismall+Ibig)/2
   for (i=1;i<=n;i++)
   {Owed=Owed*(1+Interest)-Payment}
   
   if ( Owed < 0 )
   {Osmall=Owed
    Ismall=Interest}
    else
    {Obig=Owed
    Ibig=Interest}

}
while (Math.abs(Owed) > 0.001)


/* Output results */

document.write('Interest=',Interest,'<br>')
Owed=Debt
document.write('......    Balance.......... ............ ..........Principal........ ............ ........Interest<br>' )
for (i=1;i<=n;i++)
   {Owedlast=Owed
    Owed=Owed*(1+Interest)-Payment
    document.write(i,'...  ',Owed,'...  ',Debt-Owed,'...  ',Payment-(Owedlast-Owed),'  ','<br>')}


</script>
0
 
patrickabCommented:
Loan amount 10000
Payment amount 121
No of Weeks 104
Interest Rate ?

One formula will do it:

=(10^(LOG(((10000+(121*104))/10000),10)/104))^(365/7) = 1.504480014% per year of 365 days.

Obviously all the amounts can have cell references instead - this will greater flexibility to the whole formula.

The way it works is to take the 104th root of the ratio between the initial amount and the final amount when the interest has been added. It then converts the LOG to a normal number 10^(....) and then raises it to the (365/7)th power to get it to the annual interest rate. If you need just a 52-week year then raise it to the 52nd power only.

Hope that helps

0
 
GwynforWebCommented:
patrickab,
     Are you sure that is right?  
0
 
rajeshnalankalAuthor Commented:
hi

i think GwynforWeb's way look smoother.

raj
0
 
GwynforWebCommented:
rajeshnalankal,
   I just took another look at the code I gave you and see I left a couple of varaibles in that I was using for debugging. The follwoing is therefore slightly shorter. GwynfroWeb

<script>

Payment=121.0
n=104
Debt=10000.0

/* Calculate interest rate*/

Ismall=0
Ibig=(n*Payment-Debt)/(n*Payment)
Interest=(Ibig+Ismall)/2

do
{ Owed=Debt
  Interest=(Ismall+Ibig)/2
  for (i=1;i<=n;i++)
    {Owed=Owed*(1+Interest)-Payment}
 
  if ( Owed < 0 )
    {Ismall=Interest}
  else
    {Ibig=Interest}  
}
while (Math.abs(Owed) > 0.001)


/* Output results */

document.write('Interest=',Interest,'<br>')
Owed=Debt
document.write('......    Balance.......... ............ ..........Principal........ ............ ........Interest<br>' )
for (i=1;i<=n;i++)
  {Owedlast=Owed
   Owed=Owed*(1+Interest)-Payment
   document.write(i,'...  ',Owed,'...  ',Debt-Owed,'...  ',Payment-(Owedlast-Owed),'  ','<br>')}


</script>
0
 
patrickabCommented:
GwynforWeb - thanks for picking me up on that.
rajeshnalankal - please ignore my formula. I just did not read your question sufficiently carefully.
0
 
GwynforWebCommented:
patrickab, Looks if you have used the normal compound interest equation, easy mistake to make. GwynforWeb :-)
0
 
leojlCommented:

hello,

Has anyone run a proposed answer to check if
the interest, principle and balance exactly
match the series given by raj??

leo
0
 
rajeshnalankalAuthor Commented:
hi loeoji

GwynforWeb  is correct.

raj
0
 
GwynforWebCommented:
leo,
   Raj's numbers and those generated by my code correspond exactly.

Raj,
   Do you need anything else?

Gwyn
   






0
 
leojlCommented:

hello,

Gwyn .... once again I commend you for super
good solution.

raj ... time to accept Gwen's comment as
the answer with an A grade.

leo
0
 
rajeshnalankalAuthor Commented:
hi Gwyn

thank u for your amazing solution. please
tell me how will u reach there.i hope u can tell
how to solve it.

sorry i am not a mathematical expert.

if u dont mind send your mail id to me at
rajeshnalankal@yahoo.com

sincerely

raj
0
 
GwynforWebCommented:
Raj and Leo, Thank you for your kind words.

Raj,
For a given number of payments (n), initial debt (debt), payment amount and interest amount it is easy to calculate how much is owed after n payments by simple going through each week subtracting the payment and adding the interest,ie

  Owed=Debt
  for (i=1;i<=n;i++)
  {Owed=Owed*(1+Interest)-Payment}

so after this piece of code is finished the varaible Owed tells us how much is owed. The trick is of course to choose the interest rate so that the amount owed is exactly zero. There is not in general an analytical solution to finding the correct interest rate. A solution can be obtained to any degree of accuracy by using a numerical method. I have used here what is called the method of bi-section.

   We start with an initial guess for the interest rate and see how much is owed after the given number of weeks at that interest rate, if Owed is +ve my guess is too high so try a lower value, a -ve  means my guess is too low so try a higher value. By repeating this process in a systematic manner it is posible to find a solution to any accuracy you want. It is  similar to a binary search and is very efficient.

  I start with a initial guess that I know is half way between a value that is to high and one that is to low. By seeing how much is owed I can now narrow the interval to half the size. This process is then repeated until I have the accracy I want.

GwynforWeb


 
0
 
leojlCommented:
hi Gwen and Raj

That is a neat solution Gwen.
I have written a Visual Basic
program ... so I can easily make
a graph as it iterates to a solution.
( 25 passes through to meet your criteria)

I do not think Obig and Osmall are used
so it might be easier for Raj to understand
your code if they were taken out.

I will repeat raj ... time to take Gwens
comment as an answer with an A grade.

leo
0
 
GwynforWebCommented:
Leo, It did remove them in the final version. Gwyn
0
 
leojlCommented:

Gwyn

OK ... I see.  I had just printed the first
version as a guide to write a VB program.

Many years ago we used a similar technique to
calculate ROI ( internal cash flow return on
investment) for a series of outflow and inflow
of money to a project. For ROI the money in and
money out can be arbitrary amounts. The technique
is to iterate to an interest rate such that the
present value of the outflow is equal to the
present value of the inflow.

leo
0
 
GwynforWebCommented:
leo, As numerical methods go it is very simple but if you can find a value that is too high and one too low then it works extremely well. You are assured of convergance (provided your function has no singularities), it is easy to program and is easy to understand. It is well suited to many simple actuarial problems that do not have analytical solutions. -Gwyn
0
 
leojlCommented:

Gwyn ... are you an actuary? One of our sons is
an actuary.

An interesting thing about ROI is that for really
unusual inflow, outflow of money to the project
it is possible to get more than one interest rate
answer ...  On a boreing day at work I wrote a
Fortran program to run on our mainframe. The task
was to find simple cash flows that would yield two
different interest rates ... like a valid answer for
5.1% and also something like 22.3% ... fun to do
and I have ofter wondered if there could be a real
world application.

leo
0
 
SunBowCommented:
done?
0
 
GwynforWebCommented:
leo- No I am not a actuary but I have an interest in just about everything. Actuarial work is over looked by some as being boring (confusing it with accounting). It is a very interesting field. Concerning the problem you looked at, interesting, (the above problem also has multiple solutions but they are either negative or break other rules of lending), I do not know if it has an application.   -Gwyn
0
 
GwynforWebCommented:
Raj, What is going on?
0
 
leojlCommented:

I think he forgot you now that he has
a solution ...  or perhaps he is on a trip
and does not have access to a computer.

leo
0
 
GwynforWebCommented:
leo, You are probably right. -Gwyn
0
 
rajeshnalankalAuthor Commented:
hi

actually i do not have access to a computer till now.

sorry for the inconvenience.

 thanks to gwyn and leo.


with regards
raj

0
 
GwynforWebCommented:
Raj, No problem thanks for the points, it was an interesting problem.  Gwyn
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 15
  • 6
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now