here it is
Function interest_cal(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 a As Double
Dim a1 As Double
Dim P1 As Double
Dim d1 As Integer
Dim interest_cal1 As Double
Dim interest_cal2 As Double
a1 = days / 15
a = days / 30
j = 1
x = 0.01 'this is the assumption
y = 0.02 'this is the assumption
If rate <= x And days <= 30 Then
interest_cal = Principle * (rate / 12)
Else
If rate > x And rate <= y And days <= 15 Then
interest_cal = (Principle * (rate / 12)) / 2
Else
If rate > x And rate <= y And days < 30 Then
interest_cal = ((Principle * (rate / 12)) / 30) * days
Else
If rate > x And rate <= y And days >= 30 Then
z = Application.WorksheetFunction.Floor(a, 1)
P1 = Principle
d1 = days - (z * 30)
Do While j <= z
interest_cal = P1 * (rate / 12)
P1 = P1 + interest_cal
j = j + 1
Loop
interest_cal1 = P1 - Principle
interest_cal2 = ((P1 * (rate / 12)) / 30) * d1
interest_cal = interest_cal1 + interest_cal2
Else
If rate > y And days <= 30 Then
interest_cal = Principle * (rate / 12)
Else
If rate > y And days > 30 Then
z = Application.WorksheetFunction.Ceiling(a, 1)
P1 = Principle
Do While j <= z
interest_cal = P1 * (rate / 12)
P1 = P1 + interest_cal
j = j + 1
Loop
interest_cal = P1 - Principle
Else
If rate = x And days > 30 Then
z = Application.WorksheetFunction.Ceiling(a1, 1)
If (z Mod 2) = 0 Then
z = Application.WorksheetFunction.Ceiling(a, 1)
P1 = Principle
Do While j <= z
interest_cal = P1 * (rate / 12)
P1 = P1 + interest_cal
j = j + 1
Loop
interest_cal = P1 - Principle
Else
z = Application.WorksheetFunction.Floor(a, 1)
P1 = Principle
Do While j <= z
interest_cal = P1 * (rate / 12)
P1 = P1 + interest_cal
j = j + 1
Loop
interest_cal1 = P1 - Principle
interest_cal2 = (P1 * (rate / 12)) / 2
interest_cal = interest_cal1 + interest_cal2
End If
End If
End If
End If
End If
End If
End If
End If
End Function
On Tue, Jun 16, 2009 at 6:44 PM, obahor o <[email protected]> wrote:
> 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 <[email protected]>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 <[email protected]>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 <[email protected]> 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 <
>>> [email protected]>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 <[email protected]> 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"
> ******************************************************
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
-~----------~----~----~----~------~----~------~--~---
