Hi Niraj:
If you don't mind can you send the working code that Nishant helped u with.
I am also having a problem getting it to work. I am also trying to do
something similar.
Thanks.
Mog
On Tue, Jun 16, 2009 at 3:49 AM, NIRAJ KOTHARI <nirajskoth...@gmail.com>wrote:
>
> 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
>>
>>
>
> >
>
--
******************************************************
Omoghene Obahor
www.obahor.com
Cell: 214-493-7403
Fax: 866-338-1397
"As a Man Thinks So is He"
******************************************************
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
