• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to Compute interest

Status
Not open for further replies.

nbeharry

New Member
Int period start_date = 5/1/2001

end_date = 7/31/2012


Between the start_date and end_date, there are multiple interest rates.


Sheet2 shows all interest rates and their interval.


I need help in writing a macro that will calculate interest for the entire period (5/1/2001-7/31/2012) based on the various interest rates.


Thanks.
 
Nbeharry


Firstly, Welcome to the Chandoo,org Forums


Have you looked at using some of the Excel Financial functions, like: ACCRINT(), ACCRINTM(), CUMIPMT(), PPMT() or some combination of these and other functions


Can you post a file with your data so a more specific answer can be looked at?

Refer: http://chandoo.org/forums/topic/vba-code-to-compute-interest
 
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!
 
Hi, nbeharry!

Reading the first green sticky posts as I wrote in my previous comment, there are the guidelines.

Regards!
 
Here is a copy of my file. Please take a look and let me know if you can help. Thanks.


http://www49.zippyshare.com/v/65383803/file.html
 
Hi Harry ,


Can you check this file , and see if the calculations are correct ?


http://www16.zippyshare.com/v/25389940/file.html


Narayan
 
Hi, nbeharry!


A few updates to the process:

a) Annual days (360, 365)

b) Calculation of simple and compound interest types

c) Monthly calculation (same day every month), Fixed days (30, etc.), Rate change (rare but as you requested it)

d) Added detail calculation per period (rate, interest, new amount)


Please download again the updated file from same previous link. Code yet updated at previous post.


Just advise if any issue.


Regards!


PS: For the 2,000.00 example you'd find that there's a difference in first entry only, I have 5.75 of interest for 14 days between 01/05 and 15/05 at 7.50% (for 365 days) and you have 6.16, which correspond to 15 days instead of 14. Please check this.
 
Hi, nbeharry!

You shouldn't have any problem, try saving it as an Excel 2003 .xls file.

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Hello Hui,

I am a new member, I got the same question like Nbeharry,

Could you explain your answer for me please? (what #39,#34 meaning sir?)

Option Explicit

Sub CalculateInterest()
' constants
' worksheets & ranges
Const ksWSParam = "Hoja1"
Const ksStart = "ParamDateStartCell"......

Thank you for your help.

Dung
 

dungpham20

' is Apostrophe
and
" is Quotation mark

As a new member, please reread Forum Rules from
to continue .
This thread is ... few years old and closed now.
 
Status
Not open for further replies.
Back
Top