VBA Express : Multiple Apps - Functions to return a loan amortization schedule in an array

Multiple Apps

Functions to return a loan amortization schedule in an array

Ease of Use

Intermediate

Version tested with

2000, 2002

Submitted by:

matthewspatrick

Description:

Two functions that return an array with the amortization schedule for a loan, based on loan terms and payment style. Functions are fully VB-compliant and may be used in any VBA or VB project (including, but not only, Excel)

Discussion:

Suppose you are considering making a purchase funded by a loan, such as taking out a mortgage to buy a house. You may want to mock up an amortization schedule to see just what you are getting yourself into. You may also want to test various scenarios related to making additional payments to principal. The included function AmortSchedTraditional can help you with this analysis. Or, your business may have taken out a loan, and you are wondering (a) how long will it take to pay off the loan if I pay $X per period, and what will my payment schedule look like? The included function AmortSchedSpecialPmt can help you with that analysis.

Code:

instructions for use

			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

How to use:

Add a regular module to your VB/VBA project
Copy and paste the code in this article into the module
Call the functions from other Functions or Subs in your code, or if you are adding these functions to an Excel function, use the functions in worksheet array formulas
If you use the function in an Excel array formula, make sure the array has enough rows to accommodate the expected number of payments and five columns. If your array has more rows than their are payments, the "extra" rows will show an #N/A error. You may wish to hide these using Conditional Formatting (see the example workbook)

Test the code:

Download the sample file below
On the two worksheets, change the values in the parameter cells at the top of the worksheets, and see how the amortization schedules adjust

Sample File:

amortization.zip 54.74KB

Approved by mdmackillop

This entry has been viewed 75 times.