I have revised your file and have sent you an email... hope it will be
useful
On Jun 13, 3:04 pm, NIRAJ KOTHARI <nirajskoth...@gmail.com> wrote:
> Hey Nishant
>
> Thanks for your efforts , your code looks absolutely perfect but i am unable
> to make proper use of it.
>
> i am herewith attaching a Sample Example of my data can you please help me
> out on How to use this Macro VBA script to get interest for all the data at
> once whenever i open the excel sheet.
>
> Please explain steps in detail so that i can follow the steps for my actual
> data
>
> Thanks
> Niraj Kothari
>
> On Sat, Jun 13, 2009 at 10:36 AM, Nishant Jain <nishantjai...@gmail.com>wrote:
>
>
>
>
>
> > I have tried to convert this for you... just check this out
>
> > Sub interest(Principle As Double, rate As Double, days As Integer)
> > Dim i As Integer
> > Dim x As Double
> > Dim y As Double
> > Dim z As Integer
> > Dim j As Integer
> > Dim P1 As Double
> > Dim d1 As Integer
> > Dim interest1 As Double
> > Dim interest2 As Double
> > x = 0.1 'this is the assumption
> > y = 0.2 'this is the assumption
> > If rate <= x And days <= 30 Then
> > interest = Principle * (r / 12)
> > Else
> > If rate > x And r <= y And days <= 15 Then
> > interest = (Principle * (r / 12)) / 2
> > Else
> > If rate > x And r <= y And days < 30 Then
> > interest = ((Principle * (r / 12)) / 30) * days
> > Else
> > If rate > y And days > 30 Then
> > z = Application.WorksheetFunction.Ceiling((d / 30), 1)
> > P1 = Principle
> > Do While j <= z
> > P1 = P1 * (rate / 12)
> > P1 = P1 + interest
> > j = j + 1
> > Loop
> > interest = P1 - Principle
> > Else
> > If rate = x And days > 30 Then
> > If (days Mod 15) = 0 Then
> > z = Application.WorksheetFunction.Ceiling((d /
> > 30), 1)
> > P1 = Principle
> > Do While j <= z
> > interest = P1 * (rate / 12)
> > P1 = P1 + interest
> > j = j + 1
> > Loop
> > interest = P1 - Principle
> > Else
> > d1 = Application.WorksheetFunction.Ceiling
> > (((days / 15) - 1) * 15, 1)
> > z = d1 / 2
> > P1 = Principle
> > Do While j <= z
> > interest = P1 * (rate / 12)
> > P1 = P1 + interest
> > j = j + 1
> > Loop
> > interest1 = P1 - Principle
> > interest2 = (P1 * (rate / 12)) / 2
> > interest = interest1 + interest2
> > End If
> > End If
> > End If
> > End If
> > End If
> > End If
> > End Sub
>
> > On Jun 12, 6:37 pm, NIRAJ KOTHARI <nirajskoth...@gmail.com> wrote:
> > > Dear All,
>
> > > Kindly please provide me a macro code for the below algorithm which is
> > used
> > > to calculate compound interest. Also i would appreciate if you can
> > provide
> > > me details on how to use that macro.
>
> > > Int_Rate(Principle p, ROI r,Days d)
> > > {
> > > int i;
> > > if(r =<x && d<= 30)
> > > {
> > > i = p * ( r/12)
> > > return i;
> > > }
>
> > > /* the above IF calculates the interest for minimum 30 days if the
> > interest
> > > rate is below x% per annum */
>
> > > else if(r > x && r <= y && d<= 15)
> > > {
> > > i = (p * ( r/12))/2
> > > return i;
> > > }
>
> > > /* the above IF calculates the interest for minimum 15 days if the
> > interest
> > > rate is above x% and below or equal to y% per annum */
>
> > > else if(r > x && r<=y && d < 30)
> > > {
> > > i = ((p * ( r/12))/30)*d
> > > return i;
> > > }
>
> > > /* the above IF calculates the interest for n days in a month(more than
> > 15)
> > > if the interest rate is above x% and less than or equal to y% per annum*/
>
> > > else if(r> y && d >30)
> > > {
> > > z = (d/30) /* Any number having a decimal should be
> > converted
> > > to next immedate integer .Ex: No.s like 1.01, 1.2 ,1.3,1.66 ,1.99 should
> > be
> > > taken as z = 2.*/
> > > p1=p;
> > > for (j=1; j<=z ; j=j+1 )
> > > {
> > > i = p1 * ( r/12)
> > > p1=p1+i
> > > }
> > > i = p1 -p
> > > return i;
> > > }
>
> > > /* the above IF calculates the interest for n months if the interest rate
> > is
> > > above y% per annum.Ex for 95 days ,n =4; for 59 days ,n=2; months
> > compounded
> > > monthly*/
>
> > > else if(r==x && d>30)
> > > {
> > > if( (d/15) == even) /* d/15 should be converted to next
> > > immediate integer if it is having a decimal.ex 3.01,3.02,3.99 should be
> > > converted to 4 */
> > > {
> > > z = (d/30) /* Any number having a decimal should be
> > > converted to next immedate integer .Ex: No.s like 1.01, 1.2 ,1.3,1.66
> > ,1.99
> > > should be taken as z = 2.*/
> > > p1=p;
> > > for (j=1; j<=z ; j=j+1 )
> > > {
> > > i = p1 * ( r/12)
> > > p1=p1+i
> > > }
> > > i = p1 -p
> > > return i;
> > > }
>
> > > /* the above IF calculates the interest for n months if the interest rate
> > is
> > > equal to x% per annum.Ex for d=60 days ,z =2; for d=85 days ,z=3; months
> > > compounded monthly*/
>
> > > else if( (d/15) == odd) /* d/15 should be converted
> > to
> > > next immediate integer if it is having a decimal.ex 4.01,4.02,4.99 should
> > be
> > > converted to 5 */
> > > {
> > > d1=((d/15)-1) * 15 /* d/15 should be converted
> > to
> > > next immediate integer if it is having a decimal.ex 4.01,4.02,4.99 should
> > be
> > > converted to 5.So now for example if d=63,d/15=5(as 4.02 is converted to
> > > 5),d1=5-1=4 */
>
> > > z=d1/2 /* for 2 months */
> > > p1=p;
> > > for (j=1; j<=z ; j=j+1 )
> > > {
> > > i = p1 * ( r/12)
> > > p1=p1+i
> > > }
> > > i1 = p1 -p
> > > i 2= (p1 * ( r/12))/2
> > > i=i1+i2
> > > return i;
>
> > > }
> > > /* the above IF calculates the interest for n months if the
> > > interest rate is equal to x% per annum.Here the number of days are odd
> > > multiples of 15 when converted to nearest integer.Ex for d=65 days,d1=60
> > > days ,z =2; for d=95 days, d1=90 days ,z=3; months compounded monthly*/
>
> > > }
>
> > > }
>
> > > Thanks for your help please guide me
>
> > > Thanks & Regards
> > > Niraj Kothari
>
>
>
> Sample.xls
> 62KViewDownload
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
