# creating a macro to calculate interest

#### bhaktany

##### New Member
I am trying to creat a macro that performs interest calculations by pulling data from various fields in spreadsheet "MLS". For all rows, varies by each deal, in MLS, I will add the total interest and take that amount and place it in spreadsheet "Funding Memo". I am a beginner so i am asking for your help. Thanks again for your suggestions.

'
'accrued interest calculation
'
Worksheets("MLS").Activate
Worksheets("MLS").Range("DI1:DI1").Select
Worksheets("MLS").Range("DI1:DI1").Activate
ActiveCell.Value = "Accrued Interest Calculation"
'
'
'
Dim perdiem As String 'interest per day'
Dim numdays As String 'number of days of interest'
Dim upb2 As String 'current balance used to calculate the interest'
Dim intd As Date 'interest paid to date'
Dim pr_rate As String 'rate'
Dim settledate As Date
Dim interestcalc As String 'interest'

For a = 2 To 6500
Worksheets("MLS").Select.Activate
Set upb2(a) = Worksheets("MLS").Range("AF2")
Set pr_rate(a) = Worksheets("MLS").Range("AH2")
Set perdiem = Application.WorksheetFunction.Product(pr_rate, 0.00003)
Worksheets("Funding Memo").Select.Activate
Set settledate = Worksheets("Funding Memo").Range("A3")
Set numdays = Application.WorksheetFunction.Days360(intd, settledate)
Set interestcalc = CLng(Application.WorksheetFunction.Product(perdiem, numdays, upb2))
Worksheets("MLS").Range("DI2").Select
Set Selection = interestcalc
Next a
End

End Sub

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Quite a macro for a beginner.

Have you looked at the financial functions available in Excel to see if there is a formula you can use?

Replies
7
Views
221
Replies
3
Views
137
Replies
8
Views
84
Replies
1
Views
232
Replies
29
Views
761

1,203,468
Messages
6,055,599
Members
444,800
Latest member
KarenTheManager

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back