Hi, nbeharry!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about your question...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/VBA%20code%20to%20Compute%20interest%20%28for%20nbeharry%20at%20chandoo.org%29.xlsm
This is the VBA code:
-----
Option Explicit
Sub CalculateInterest()
' constants
' worksheets & ranges
Const ksWSParam = "Hoja1"
Const ksStart = "ParamDateStartCell"
Const ksEnd = "ParamDateEndCell"
Const ksAnnual = "ParamAnnualDaysCell"
Const ksMode = "ParamInterestModeCell"
Const ksType = "ParamCapitalizationTypeCell"
Const ksPeriod = "ParamCapitalizationPeriodCell"
Const ksCapital = "ParamCapitalCell"
Const ksInterest = "ParamInterestCell"
Const ksWSHistory = "Hoja2"
Const ksHistory = "HistoryTable"
Const ksWSDetail = "Hoja3"
Const ksDetail = "DetailTable"
' others
Const ksModeSimple = "Simple"
Const ksModeCompound = "Compound"
Const ksTypeMonthly = "Monthly"
Const ksTypeFixedDays = "Fixed days"
Const ksTypeRateChange = "Rate change"
' declarations
' ranges
Dim rngS As Range, rngE As Range, rngA As Range, rngM As Range, rngT As Range
Dim rngP As Range, rngC As Range, rngI As Range, rngH As Range, rngD As Range
Dim dDateStart As Date, dDateEnd As Date, iAnnualDays As Integer, sInterestMode As String
Dim sCapitalizationType As String, iCapitalizationPeriod As Integer
Dim cCapital As Currency, cInterest As Currency
' others
Dim dWork As Date, dWork1 As Date, dAux As Date, cRate As Currency
Dim cCWork As Currency, cCWork1 As Currency, cCAcum As Currency
Dim cIWork As Currency, cIAcum As Currency
Dim iFrom As Integer, iPeriods As Integer
Dim I As Integer, J As Integer
' start
' ranges
Set rngS = Worksheets(ksWSParam).Range(ksStart)
Set rngE = Worksheets(ksWSParam).Range(ksEnd)
Set rngA = Worksheets(ksWSParam).Range(ksAnnual)
Set rngM = Worksheets(ksWSParam).Range(ksMode)
Set rngT = Worksheets(ksWSParam).Range(ksType)
Set rngP = Worksheets(ksWSParam).Range(ksPeriod)
Set rngC = Worksheets(ksWSParam).Range(ksCapital)
Set rngI = Worksheets(ksWSParam).Range(ksInterest)
Set rngH = Worksheets(ksWSHistory).Range(ksHistory)
Set rngD = Worksheets(ksWSDetail).Range(ksDetail)
With rngD
If .Rows.Count > 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
End With
' parameters
dDateStart = rngS.Cells(1, 1).Value
dDateEnd = rngE.Cells(1, 1).Value
iAnnualDays = rngA.Cells(1, 1).Value
sInterestMode = rngM.Cells(1, 1).Value
sCapitalizationType = rngT.Cells(1, 1).Value
iCapitalizationPeriod = rngP.Cells(1, 1).Value
cCapital = rngC.Cells(1, 1).Value
cInterest = rngI.Cells(1, 1).Value
' process
With rngH
' 1st entry
cRate = 0
J = 0
For I = 1 To .Rows.Count
dAux = .Cells(I, 1).Value
If dAux < dDateStart Then J = I Else Exit For
Next I
If J > 0 Then
iFrom = J
cRate = .Cells(J, 2).Value
Else
iFrom = 1
End If
' each monthly in period
dWork = dDateStart
cCWork = cCapital
dWork1 = dWork
cCWork1 = cCWork
iPeriods = 0
Do Until dWork >= dDateEnd
' period
iPeriods = iPeriods + 1
' ending date
Select Case sCapitalizationType
Case ksTypeMonthly
dWork = DateSerial(Year(dDateStart), _
Month(dDateStart) + iPeriods * iCapitalizationPeriod, _
Day(dDateStart))
Case ksTypeFixedDays
dWork = DateSerial(Year(dDateStart), _
Month(dDateStart), _
Day(dDateStart) + iPeriods * iCapitalizationPeriod)
Case ksTypeRateChange
iFrom = iFrom + 1
dWork = .Cells(iFrom, 1).Value
End Select
' rate
J = 0
For I = iFrom To .Rows.Count
dAux = .Cells(I, 1).Value
If dAux <= dWork Then J = I Else Exit For
Next I
If J > 0 Then cRate = .Cells(J, 2).Value
' interest mode
Select Case sInterestMode
Case ksModeSimple
cIWork = cCapital * (cRate * (dWork - dWork1) / iAnnualDays)
Case ksModeCompound
cIWork = cCWork * (cRate * (dWork - dWork1) / iAnnualDays)
End Select
cIAcum = cIAcum + cIWork
' new capital
cCWork = cCapital + cIAcum
' detail
rngD.Cells(iPeriods + 1, 1).Value = dWork
rngD.Cells(iPeriods + 1, 2).Value = iPeriods
rngD.Cells(iPeriods + 1, 3).Value = CDbl(cRate)
rngD.Cells(iPeriods + 1, 4).Value = cCWork1
rngD.Cells(iPeriods + 1, 5).Value = cIWork
rngD.Cells(iPeriods + 1, 6).Value = cCWork
' next
dWork1 = dWork
cCWork1 = cCWork
Loop
End With
' interest
rngI.Cells(1, 1).Value = cCWork
' endbr />Set rngD = Nothing
Set rngH = Nothing
Set rngI = Nothing
Set rngC = Nothing
Set rngP = Nothing
Set rngM = Nothing
Set rngT = Nothing
Set rngA = Nothing
Set rngE = Nothing
Set rngS = Nothing
Beep
End Sub
-----
Regards!