VBA Code explanation

[pombiux]

Hey guys, I have been cracking my head last 2-3 days with the following coding, but just aren't able to understand the syntax. The code works well, but I am a beginner in this. Any help on the meaning of it would be great. Thanks!

Code:

Sheets("Payment Schedule").Range("A2:f" & Sheets("Payment Schedule").UsedRange.Rows.Count).Clear

ReDim schedule(1 To numberOfPayments + 2, 1 To 6)

schedule(1, 6) = loanAmount
Sheets("Payment Schedule").Range("A1") = "Payment Number"
Sheets("Payment Schedule").Range("b1") = "Date"
Sheets("Payment Schedule").Range("C1") = "Amount"
Sheets("Payment Schedule").Range("D1") = "Interest"
Sheets("Payment Schedule").Range("E1") = "Principal"
Sheets("Payment Schedule").Range("F1") = "Balance"

For i = 2 To numberOfPayments + 1

    schedule(i, 1) = i - 1
    If paymentsPerYear = 1 Then
        schedule(i, 2) = DateAdd("yyyy", i - 2, loanStartDate)
    ElseIf paymentsPerYear = 2 Then
        schedule(i, 2) = DateAdd("m", 6 * (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 4 Then
        schedule(i, 2) = DateAdd("q", (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 6 Then
        schedule(i, 2) = DateAdd("m", 2 * (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 12 Then
        schedule(i, 2) = DateAdd("m", (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 24 Then
        If i Mod 2 = 0 Then
            schedule(i, 2) = DateAdd("m", 0.5 * (i - 2), loanStartDate)
            'schedule(i, 2) = DateAdd("d", 15, schedule(i, 2))
        Else
            schedule(i, 2) = DateAdd("m", 0.5 * (i - 2), loanStartDate)
            schedule(i, 2) = DateAdd("d", 14, schedule(i, 2))
        End If
    ElseIf paymentsPerYear = 26 Then
        schedule(i, 2) = DateAdd("ww", 2 * (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 52 Then
        schedule(i, 2) = DateAdd("ww", (i - 2), loanStartDate)
    End If


    '    If schedule(i - 1, 6) < schedule(i - 2, 6) - amountPerPayment * (interestRatePerPayment) Then
    '        schedule(i - 1, 3) = amountPerPayment + Round(schedule(i - 1, 6) / (1 - interestRatePerPayment), 2)
    '        schedule(i - 1, 4) = Round(interestRatePerPayment * schedule(i - 1, 3), 2)
    '        schedule(i - 1, 5) = schedule(i - 1, 3) - schedule(i - 1, 4)
    '        schedule(i - 1, 6) = schedule(i - 2, 6) - schedule(i - 1, 5)
    '        schedule(i, 1) = Null
    '        schedule(i, 2) = Null
    '        Exit For
    '    Else
    schedule(i, 3) = amountPerPayment
    schedule(i, 4) = Round(interestRatePerPayment * schedule(i - 1, 6), 2)
    schedule(i, 5) = schedule(i, 3) - schedule(i, 4)
    schedule(i, 6) = schedule(i - 1, 6) - schedule(i, 5)
    '    End If
    If i > 2 And schedule(i, 6) < amountPerPayment - schedule(i - 1, 6) * interestRatePerPayment Then
        schedule(i, 3) = amountPerPayment + schedule(i, 6)
        schedule(i, 4) = Round(interestRatePerPayment * schedule(i - 1, 6), 2)
        schedule(i, 5) = schedule(i, 3) - schedule(i, 4)
        schedule(i, 6) = schedule(i - 1, 6) - schedule(i, 5)
        Exit For
    End If


Next


Sheets("Payment Schedule").Range("A2:F" & UBound(schedule) + 1) = schedule


Sheets("Payment Schedule").Range("B:B").NumberFormat = "dd-mmm-yy"
Sheets("Payment Schedule").Range("C:F").NumberFormat = "$#,##0.00"


Sheets("Parameters").Range("dateAtEndOfTerm") = schedule(paymentsPerYear * term, 2)


'Total interest paid at end of term
Dim totalInterestAtEndOfTerm As Double
totalInterestAtEndOfTerm = 0
For i = 2 To term * paymentsPerYear + 1
    totalInterestAtEndOfTerm = totalInterestAtEndOfTerm + schedule(i, 4)
Next
Sheets("Parameters").Range("interestAtEndOfTerm") = totalInterestAtEndOfTerm


'Total principal paid at end of term
Dim totalPrincipalAtEndOfTerm As Double
totalPrincipalAtEndOfTerm = 0
For i = 2 To term * paymentsPerYear + 1
    totalPrincipalAtEndOfTerm = totalPrincipalAtEndOfTerm + schedule(i, 5)
Next
Sheets("Parameters").Range("principalAtEndOfTerm") = totalPrincipalAtEndOfTerm


'Balance at end of term
Sheets("Parameters").Range("balanceAtEndOfTerm") = schedule(term * paymentsPerYear + 1, 6)


'Total interest paid at end of amortization period
Dim totalInterestAtEndOfAmortization As Double
totalInterestAtEndOfAmortization = 0
For i = 2 To amortizationPeriod * paymentsPerYear + 2
    totalInterestAtEndOfAmortization = totalInterestAtEndOfAmortization + schedule(i, 4)
Next
Sheets("Parameters").Range("interestAtEndOfAmortization") = totalInterestAtEndOfAmortization


'Total principal paid at end of amortization period
Dim totalPrincipalAtEndOfAmortization As Double
totalPrincipalAtEndOfAmortization = 0
For i = 2 To amortizationPeriod * paymentsPerYear + 2
    totalPrincipalAtEndOfAmortization = totalPrincipalAtEndOfAmortization + schedule(i, 5)
Next
Sheets("Parameters").Range("principalAtEndOfAmortization") = totalPrincipalAtEndOfAmortization


'Date at end of amortization period
Sheets("Parameters").Range("DateAtEndOfAmortization") = Sheets("Payment Schedule").Range("B3").End(xlDown).Value

 

[easy2understandexcel]

Is there any particular part you have a question about? It would take a long time to write up an explanation of that much code...

 

[pombiux]

Thanks for such a quick answer! Yeah, i think the most confusing part for me is the following:

I understand numberOfPayments, loanStartDate, paymentsPerYear, amountPerPament, interestRatePerPayment are all defined previously and that I understand, its mostly that schedule(i,xxx) part and the assigining of the date using all those i+'s , and i Mod 2 part

Also he uses this code to place the loanAmount on cell F2, which I dont understand why it shows up on that particular cell.....: schedule(1, 6) = loanAmount

[COLOR=#333333]For i = 2 To numberOfPayments + 1[/COLOR]
    schedule(i, 1) = i - 1
    If paymentsPerYear = 1 Then
        schedule(i, 2) = DateAdd("yyyy", i - 2, loanStartDate)
    ElseIf paymentsPerYear = 2 Then
        schedule(i, 2) = DateAdd("m", 6 * (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 4 Then
        schedule(i, 2) = DateAdd("q", (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 6 Then
        schedule(i, 2) = DateAdd("m", 2 * (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 12 Then
        schedule(i, 2) = DateAdd("m", (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 24 Then
        If i Mod 2 = 0 Then
            schedule(i, 2) = DateAdd("m", 0.5 * (i - 2), loanStartDate)
            'schedule(i, 2) = DateAdd("d", 15, schedule(i, 2))
        Else
            schedule(i, 2) = DateAdd("m", 0.5 * (i - 2), loanStartDate)
            schedule(i, 2) = DateAdd("d", 14, schedule(i, 2))
        End If
    ElseIf paymentsPerYear = 26 Then
        schedule(i, 2) = DateAdd("ww", 2 * (i - 2), loanStartDate)
    ElseIf paymentsPerYear = 52 Then
        schedule(i, 2) = DateAdd("ww", (i - 2), loanStartDate)
    End If


    '    If schedule(i - 1, 6) < schedule(i - 2, 6) - amountPerPayment * (interestRatePerPayment) Then
    '        schedule(i - 1, 3) = amountPerPayment + Round(schedule(i - 1, 6) / (1 - interestRatePerPayment), 2)
    '        schedule(i - 1, 4) = Round(interestRatePerPayment * schedule(i - 1, 3), 2)
    '        schedule(i - 1, 5) = schedule(i - 1, 3) - schedule(i - 1, 4)
    '        schedule(i - 1, 6) = schedule(i - 2, 6) - schedule(i - 1, 5)
    '        schedule(i, 1) = Null [COLOR=#333333]    '        schedule(i, 2) = Null[/COLOR]