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

Correction:
Please read Last line Compound = int_amt * (to_dt - st_dt+1) / 365
as
Compound = int_amt * (to_dt - fm_dt+1) / 365

On Dec 2, 1:51 am, Vikram wrote:
Sorry Once again:
Please read Last line Compound = int_amt * (to_dt - st_dt+1) / 365
as
Compound = int_amt * (to_dt - fm_dt+1) / (to_dt - org_st_dt)