Option Explicit 
 
 ' Loan amortization schedule functions
 
Function AmortSchedTraditional(BeginPrincipal As Double, PeriodRate As Double, Periods As Long, _ 
    Optional ExtraPrin As Double = 0) 
     
     ' This function returns an array with the amortization schedule for a loan with known
     ' beginning principal, period rate, number of periods, and optional fixed extra principal
     ' with each payment.  All arguments should be positive
     
     ' The function returns an array of length (1 To N, 1 To 5), where N is the number of payments
     ' ultimately needed to retire the loan (may be different from initial Periods argument if extra
     ' principal payments are made)
     
     ' In the second dimension, the values are:
     ' 1: Balance before payment X
     ' 2: Total amount of payment X
     ' 3: Principal amount of payment X
     ' 4: Interest amount of payment X
     ' 5: Balance after payment X
     
     ' To use this function in the Excel UI, use an array formula.  If the array area has more rows than the
     ' function returns, the 'extra' rows will show a #N/A error.  You can use Conditional Formatting
     ' to dynamically hide such results.
     
     ' This function will work in other VBA and VB projects.
     
    Dim Schedule() As Double 
    Dim Schedule2() As Double 
    Dim BeginBal As Double 
    Dim Counter As Long 
    Dim Counter2 As Long 
    Dim LevelPay As Double 
     
     ' Dimension array with 'first pass' amortization schedule
    ReDim Schedule(1 To Periods, 1 To 5) As Double 
     
     ' Negative extra payments are suppressed
    If ExtraPrin < 0 Then ExtraPrin = 0 
     
    BeginBal = BeginPrincipal 
    LevelPay = -Pmt(PeriodRate, Periods, BeginPrincipal) + ExtraPrin 
     
    For Counter = 1 To Periods 
        Schedule(Counter, 1) = BeginBal 
         ' Interest is easy to determine; do it first
        Schedule(Counter, 4) = BeginBal * PeriodRate 
         ' Amount of principal portion depends in part on the remaining balance before the payment
         ' is applied; the principal amount can never be larger than this
        Schedule(Counter, 3) = IIf((LevelPay - Schedule(Counter, 4)) < BeginBal, _ 
        LevelPay - Schedule(Counter, 4), BeginBal) 
         ' Total payment = principal portion + interest portion
        Schedule(Counter, 2) = Schedule(Counter, 3) + Schedule(Counter, 4) 
         ' Determine balance after payment is applied.  Check for a balance approaching zero
        Schedule(Counter, 5) = BeginBal - Schedule(Counter, 3) 
        If Schedule(Counter, 5) < 0.01 Then 
            Schedule(Counter, 5) = 0 
            Exit For 
        End If 
        BeginBal = Schedule(Counter, 5) 
    Next 
     
     ' Create a new array that has only as many 'rows' as there are payments to be made
    ReDim Schedule2(1 To Counter, 1 To 5) As Double 
    For Counter2 = 1 To Counter 
        Schedule2(Counter2, 1) = Schedule(Counter2, 1) 
        Schedule2(Counter2, 2) = Schedule(Counter2, 2) 
        Schedule2(Counter2, 3) = Schedule(Counter2, 3) 
        Schedule2(Counter2, 4) = Schedule(Counter2, 4) 
        Schedule2(Counter2, 5) = Schedule(Counter2, 5) 
    Next 
     
     ' Assign the function return value
    AmortSchedTraditional = Schedule2 
     
End Function 
Function AmortSchedSpecialPmt(BeginPrincipal As Double, PeriodRate As Double, DesiredPay As Double) 
     
     ' This function returns an array with the amortization schedule for a loan with known
     ' beginning principal, period rate, and desired amount of each payment.  All arguments
     ' should be positive
     
     ' If the desired payment is not larger than the first period's interest due, the function will
     ' return an error (because the loan cannot be retired).
     
     ' The function returns an array of length (1 To N, 1 To 5), where N is the number of payments
     ' ultimately needed to retire the loan
     
     ' In the second dimension, the values are:
     ' 1: Balance before payment X
     ' 2: Total amount of payment X
     ' 3: Principal amount of payment X
     ' 4: Interest amount of payment X
     ' 5: Balance after payment X
     
     ' To use this function in the Excel UI, use an array formula.  If the array area has more rows than the
     ' function returns, the 'extra' rows will show a #N/A error.  You can use Conditional Formatting
     ' to dynamically hide such results.
     
     ' This function will work in other VBA and VB projects.
     
    Dim Schedule() As Double 
    Dim BeginBal As Double 
    Dim Counter As Long 
    Dim NperResult As Double 
     
     ' Determine the number of payments needed.  Round all decimals up to the next integer
    NperResult = NPer(PeriodRate, DesiredPay, -BeginPrincipal) 
    ReDim Schedule(1 To IIf((NperResult - Int(NperResult)) > 0.000001, Int(NperResult) + 1, _ 
    Int(NperResult)), 1 To 5) As Double 
     
    BeginBal = BeginPrincipal 
     
    For Counter = 1 To UBound(Schedule, 1) 
        Schedule(Counter, 1) = BeginBal 
         ' Interest is easy to determine; do it first
        Schedule(Counter, 4) = BeginBal * PeriodRate 
         ' Amount of principal portion depends in part on the remaining balance before the payment
         ' is applied; the principal amount can never be larger than this
        Schedule(Counter, 3) = IIf((DesiredPay - Schedule(Counter, 4)) < BeginBal, _ 
        DesiredPay - Schedule(Counter, 4), BeginBal) 
         ' Total payment = principal portion + interest portion
        Schedule(Counter, 2) = Schedule(Counter, 3) + Schedule(Counter, 4) 
         ' Determine balance after payment is applied.  Check for a balance approaching zero
        Schedule(Counter, 5) = BeginBal - Schedule(Counter, 3) 
        If Schedule(Counter, 5) < 0.01 Then 
            Schedule(Counter, 5) = 0 
            Exit For 
        End If 
        BeginBal = Schedule(Counter, 5) 
    Next 
     
     ' Assign the function return value
    AmortSchedSpecialPmt = Schedule 
     
End Function