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:

  1. Add a regular module to your VB/VBA project
  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:

  1. Download the sample file below
  2. 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 100 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express