Multiple Apps

Functions to return a loan amortization schedule in an array

Ease of Use

Intermediate

Version tested with

2000, 2002

Submitted by:

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:

```			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

' 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

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:

2. Copy and paste the code in this article into the module
3. 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
4. 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: