?
Solved

loans Intrest calculation

Posted on 2003-03-08
30
Medium Priority
?
1,324 Views
Last Modified: 2012-06-27


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
Comment
Question by:rajeshnalankal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 6
  • 4
  • +4
30 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 8096984
#!/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
 
LVL 13

Expert Comment

by:cri
ID: 8097143
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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8098880
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8098897
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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8098902
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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8098924
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
 
LVL 45

Expert Comment

by:patrickab
ID: 8115258
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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8144559
patrickab,
     Are you sure that is right?  
0
 

Author Comment

by:rajeshnalankal
ID: 8145176
hi

i think GwynforWeb's way look smoother.

raj
0
 
LVL 31

Accepted Solution

by:
GwynforWeb earned 800 total points
ID: 8147148
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
 
LVL 45

Expert Comment

by:patrickab
ID: 8147279
GwynforWeb - thanks for picking me up on that.
rajeshnalankal - please ignore my formula. I just did not read your question sufficiently carefully.
0
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8161386
patrickab, Looks if you have used the normal compound interest equation, easy mistake to make. GwynforWeb :-)
0
 
LVL 3

Expert Comment

by:leojl
ID: 8185549

hello,

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

leo
0
 

Author Comment

by:rajeshnalankal
ID: 8186000
hi loeoji

GwynforWeb  is correct.

raj
0
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8188672
leo,
   Raj's numbers and those generated by my code correspond exactly.

Raj,
   Do you need anything else?

Gwyn
   






0
 
LVL 3

Expert Comment

by:leojl
ID: 8188786

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
 

Author Comment

by:rajeshnalankal
ID: 8192390
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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8198113
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
 
LVL 3

Expert Comment

by:leojl
ID: 8198458
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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8199016
Leo, It did remove them in the final version. Gwyn
0
 
LVL 3

Expert Comment

by:leojl
ID: 8200013

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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8200273
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
 
LVL 3

Expert Comment

by:leojl
ID: 8200353

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
 
LVL 24

Expert Comment

by:SunBow
ID: 8203978
done?
0
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8204213
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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8229138
Raj, What is going on?
0
 
LVL 3

Expert Comment

by:leojl
ID: 8229360

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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8230884
leo, You are probably right. -Gwyn
0
 

Author Comment

by:rajeshnalankal
ID: 8232750
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
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 8235077
Raj, No problem thanks for the points, it was an interesting problem.  Gwyn
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction On a scale of 1 to 10, how would you rate our Product? Many of us have answered that question time and time again. But only a few of us have had the pleasure of receiving a stack of the filled out surveys and being asked to do somethi…
This article seeks to propel the full implementation of geothermal power plants in Mexico as a renewable energy source.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question