V
Vikram
Objective of Function: To find compounded interest for a given period.
User Input Required:
Start_Date =7/17/2007 (Date of Placing a Deposit)
From_Date =7/10/2007(Date from which user wants interest)
To_Date = 10/31/2007(Date till which user wants interest)
Principal_Amount = 50,000 (Amount invested/deposited)
Interest_Rate = 10.25%
Compounding_Frequency = 3 (in months if qtrly then 3, yearly then 12,
monthly then 1 etc)
So the output of the function in above case should be 440.72 (ie when
start date is 7/17/2007), The function which I have created works fine
one above case but when start date is changed suppose to 4/17/2007
then it fails to give the correct answer, if anyone can figure out why
then please help me.
Given below is the function which I have created. It on
Function Compound(Start_Date As Date, From_Date As Date, To_Date As
Date, Principal_Amount As Double, Interest_Rate As Double,
Compounding_Frequency As Integer)
'storing user input in temp variables for ease of reference
st_dt = Start_Date
fm_dt = From_Date
to_dt = To_Date
pr_amt = Principal_Amount
int_rate = Interest_Rate
freq = Compounding_Frequency
freq_ctr = 1
Dim LDM As Date
Dim tmp_dt As Date
Dim tmp_date_2 As Date
org_st_dt = st_dt
Do
LDM = [atpvbaen.xls].EoMonth(st_dt, 0)
If [atpvbaen.xls].Edate(org_st_dt, freq * freq_ctr) <= LDM Then
'MsgBox "Hi"
tmp_date_2 = [atpvbaen.xls].Edate(org_st_dt, freq * freq_ctr)
int_amt = int_amt + pr_amt * int_rate * (tmp_date_2 - st_dt) / 365
freq_ctr = freq_ctr + 1
pr_amt = pr_amt + int_amt
Else
int_amt = int_amt + pr_amt * int_rate * (LDM - st_dt + 1) / 365
st_dt = LDM + 1
End If
tmp_dt = [atpvbaen.xls].Edate(org_st_dt, freq * freq_ctr)
If [atpvbaen.xls].EoMonth(tmp_dt, 0) > to_dt Then int_amt = int_amt +
pr_amt * int_rate * (to_dt - [atpvbaen.xls].Edate(org_st_dt, freq *
(freq_ctr - 1)) + 1) / 365
Loop Until [atpvbaen.xls].EoMonth(tmp_dt, 0) > to_dt
Compound = int_amt * (to_dt - st_dt+1) / 365
End Function
User Input Required:
Start_Date =7/17/2007 (Date of Placing a Deposit)
From_Date =7/10/2007(Date from which user wants interest)
To_Date = 10/31/2007(Date till which user wants interest)
Principal_Amount = 50,000 (Amount invested/deposited)
Interest_Rate = 10.25%
Compounding_Frequency = 3 (in months if qtrly then 3, yearly then 12,
monthly then 1 etc)
So the output of the function in above case should be 440.72 (ie when
start date is 7/17/2007), The function which I have created works fine
one above case but when start date is changed suppose to 4/17/2007
then it fails to give the correct answer, if anyone can figure out why
then please help me.
Given below is the function which I have created. It on
Function Compound(Start_Date As Date, From_Date As Date, To_Date As
Date, Principal_Amount As Double, Interest_Rate As Double,
Compounding_Frequency As Integer)
'storing user input in temp variables for ease of reference
st_dt = Start_Date
fm_dt = From_Date
to_dt = To_Date
pr_amt = Principal_Amount
int_rate = Interest_Rate
freq = Compounding_Frequency
freq_ctr = 1
Dim LDM As Date
Dim tmp_dt As Date
Dim tmp_date_2 As Date
org_st_dt = st_dt
Do
LDM = [atpvbaen.xls].EoMonth(st_dt, 0)
If [atpvbaen.xls].Edate(org_st_dt, freq * freq_ctr) <= LDM Then
'MsgBox "Hi"
tmp_date_2 = [atpvbaen.xls].Edate(org_st_dt, freq * freq_ctr)
int_amt = int_amt + pr_amt * int_rate * (tmp_date_2 - st_dt) / 365
freq_ctr = freq_ctr + 1
pr_amt = pr_amt + int_amt
Else
int_amt = int_amt + pr_amt * int_rate * (LDM - st_dt + 1) / 365
st_dt = LDM + 1
End If
tmp_dt = [atpvbaen.xls].Edate(org_st_dt, freq * freq_ctr)
If [atpvbaen.xls].EoMonth(tmp_dt, 0) > to_dt Then int_amt = int_amt +
pr_amt * int_rate * (to_dt - [atpvbaen.xls].Edate(org_st_dt, freq *
(freq_ctr - 1)) + 1) / 365
Loop Until [atpvbaen.xls].EoMonth(tmp_dt, 0) > to_dt
Compound = int_amt * (to_dt - st_dt+1) / 365
End Function