Thanks Nishant for your assistance its woring perfectly fine..
Wish there is a rating system in this group where in we can give stars to
best reply...
Cheers
Niraj
On Sat, Jun 13, 2009 at 9:29 PM, Nishant Jain <nishantjai...@gmail.com>wrote:
>
> 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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
