Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Fixed Rate Loan Amortization Schedule with Optional Extra Principal Payments

Patrick Matthews
CERTIFIED EXPERT
Published:
Updated:

Introduction


Using Microsoft Excel to create a fixed rate loan amortization schedule is a very common task: probably just about every skilled Excel user who has ever taken out a mortgage has either created such a schedule him/herself or downloaded one of the hundreds (thousands?) of such schedules available online.

Some of these schedules are somewhat limited, in that they generally:
may not allow extra payments to principal (which would retire the loan faster) at all;
may allow extra payments, but the extra amount is assumed to be constant and is applied to every scheduled payment;
may not allow for a future value, such as for a loan with a "balloon" payment at the end;
may be too tightly tied to a paradigm of a 30-year loan with monthly payments, such as is the case with most mortgages originated in the United States; and/or
either do not truly round results to appropriate currency intervals (such as nearest cent for the USA), typically relying on number formatting to fix the display at the desired level of precision without affecting the underlying values.

Several years ago I made my own attempt at a loan amortization schedule, available in my VBAExpress article Functions to return a loan amortization schedule in an array.  This example had three of the weaknesses described above:
It allowed extra payments, but the extra amount was constant and applied to every scheduled payment.
It did not allow for a future value, or “balloon” amount, at the end.
It treated the return values as true floating-point values, and did not round the results as appropriate for financial transactions.  For example, it would have returned a payment amount as $877.5715701, typically formatted to $877.57.

After reading mwvisa1's excellent article A Guide to the PMT, FV, IPMT and PPMT Functions, mwvisa1, brettdj, and I had a spirited discussion about how extra payments affect a loan amortization schedule and the components of each payment.  As a result of that discussion, I started tinkering with my old approach to overcome its weaknesses.

For those who simply cannot wait to get their hands dirty, here is a link to the sample file:

Amortization-Schedule.xls

For others who wish to know how that file came to be, and how to use it (and the VBA function that powers it), please read on.


The Difficulty: Handling Extra Payments



Absent extra principal payments, building an amortization table is very easy to do.  For one thing, Excel provides three very useful functions for the task:

PMT calculates the level payment for a fixed rate loan;
IPMT calculates the interest portion of any given numbered payment for a fixed rate loan;
PPMT calculates the principal portion of any given numbered payment for a fixed rate loan.

Each of those functions relies on one of the most common formulas in use in finance: the present value of a fixed annuity.

Present Value of an Annuity
Rearranging that formula yields the level payment amount paid each period:

Level Payment Formula
Finding the principal and interest amounts for any given payment within that stream is possible using a geometric progression, as described on Wikipedia.  Modifying that approach to take account of a constant extra payment is possible for an advanced formula jockey.

However, modifying it to take account of potentially multiple streams of extra payments, which may begin and end at arbitrary points in the overall schedule is rather too much to ask, and so I pass that task off to Visual Basic for Applications (VBA) code.  Doing this makes it much, much easier to accommodate a variety of extra-payment schemes within the amortization schedule, and also allows for proper handling of rounding, which just is not possible with a purely mathematical approach.


AmortSchedTraditional User Defined Function



To allow maximum flexibility for amortization schedules with additional payments made to principal — whether flat over the course of the loan, or periodic — I developed the AmortSchedTraditional function.  AmortSchedTraditional returns a two-dimensional array with a detailed payment schedule.

The first dimension, “vertical”, indexed by 1 to N, enumerates the actual number of payments made to retire the loan.  Note that if extra payments are made, this number may be less than the initial loan term specified.
The second dimension, “horizontal”, indexed 1 to 5, returns the balance before the payment is applied; the total payment made; the principal portion of the payment; the interest portion of the payment; and the new balance after applying the payment.

AmortSchedTraditional takes the following five arguments:

BeginPrincipal is the nominal loan amount.
PeriodRate is the interest rate applied per period.  For example, if the annual interest rate is 6%, and the loan compounds monthly, then the PeriodRate is 0.5% or 0.005 (0.06 / 12).
Periods is the stated length of the loan assuming no extra payments.  For example, for a thirty year loan using monthly payments, Periods equals 360.
Balloon specifies an optional amount of the original principal not repaid through the scheduled payments; in practice this amount may be paid out with the last payment as a lump sum "balloon" payment, or it may itself be refinanced.
ExtraPrin is an optional argument specifying one or more streams of extra payments to principal made over the course of the loan.  The extra amount for any given stream is constant, but the user may specify whether the stream is applied to all scheduled payments or to a given range of scheduled payments.  If the streams overlap, the amounts paid in any given period are added together.

You can supply ExtraPrin one of three possible ways:

An arbitrary number of values, separated by commas, as shown in Example 1 in the sample workbook.  In this case, the function evaluates the values in groups of three, for which the first value is the extra payment amount, the second value is the start period for the extra payment stream, and the third value is end period.  If the start period is omitted or zero, the function treats the start period as if it were the first period.  If the end period is omitted or zero, the function treats the end period as if it were the last period.
A one-dimensional array of values.  As above, the function takes these values in groups of three (extra amount, start period, end period), and allows start and/or end period to be omitted.
A two-dimensional array of values, as shown in Example 2 in the sample workbook.  In this case, the function assumes that the first dimension enumerates the payment streams, and that the second dimension contains the extra amount, start period, and end period for each stream (also allowing start/end periods to be omitted).

Some additional notes regarding AmortSchedTraditional:

Unlike with Excel's and VBA's PMT, IPMT, and PPMT functions, all arguments and all outputs for AmortSchedTraditional are either positive numbers or zero.  Thus, negative amounts for the Balloon or ExtraPrin arguments are ignored.
If the Balloon amount is greater than zero, principal payments (regular or extra) will not be assessed once the loan balance reaches the balloon amount, and the schedule returned will always stretch to the number of payments specified by the Periods argument.  (The function continues to calculate the interest payments due.)
The function considers only the cost of servicing the loan: i.e., the interest and principal payments.  No provision is made for required "escrow" payments such as for taxes and insurance that the lender may require the borrower to pay periodically.
The function assumes that there is no penalty for early repayment of the loan.
You can return an entire amortization schedule covering N periods by selecting a range N rows high and five columns wide, and then entering AmortSchedTraditional as an array formula.  Be advised that if you use more rows in your range than the number of payments returned in the schedule, the extraneous rows in the range will show an #N/A error.  (As with all array formulas, do not type in the curly braces that bracket the formula, and remember to use Ctrl+Shift+Enter to enter the formula.  Excel will then display the braces to indicate that this is an array formula)
If you specify extra payments, the function only processes these extra payments to the extent that they are needed to continue paying down the principal.  For example, you may indicate an extra payment of $200 to be made on every scheduled payment, but if on the last payment some or all of that $200 is not needed to retire the loan, then it will not be paid.

In addition to returning the full schedule of payments for a loan, this function can also perform analogous service to the PMT, IPMT and PPMT functions: if the user needs to know the interest or principal portion of the Xth payment in the schedule, all the user needs to do is fetch that element from the array.  For example, to get the beginning balance, total payment, principal portion, interest portion, and ending balance of the 10th payment, one could use:

In an Excel formula:
                      
                      =INDEX(AmortSchedTraditional(Begin_Balance,Period_Rate,Periods,Balloon,ExtraPayments),10,1)
                      =INDEX(AmortSchedTraditional(Begin_Balance,Period_Rate,Periods,Balloon,ExtraPayments),10,2)
                      =INDEX(AmortSchedTraditional(Begin_Balance,Period_Rate,Periods,Balloon,ExtraPayments),10,3)
                      =INDEX(AmortSchedTraditional(Begin_Balance,Period_Rate,Periods,Balloon,ExtraPayments),10,4)
                      =INDEX(AmortSchedTraditional(Begin_Balance,Period_Rate,Periods,Balloon,ExtraPayments),10,5)
                      
                      ----------------------------------------------------
                      In VBA:
                      
                      Dim arr As Variant
                      
                      arr = AmortSchedTraditional(Begin_Balance, Period_Rate, Periods, Balloon, ExtraPayments)
                      
                      MsgBox "10th start balance amount is: " & arr(10, 1)
                      MsgBox "10th total payment amount is: " & arr(10, 2)
                      MsgBox "10th principal payment amount is: " & arr(10, 3)
                      MsgBox "10th interest payment amount is: " & arr(10, 4)
                      MsgBox "10th new balance amount is: " & arr(10, 5)

Open in new window


Here is the source code for AmortSchedTraditional:

Function AmortSchedTraditional(BeginPrincipal As Currency, PeriodRate As Double, Periods As Long, _
                          Balloon As Currency, ParamArray ExtraPrin())
                          
                          ' Function by Patrick Matthews
                          
                          ' You are free to use and distribute this function freely, so long as you attribute authorship,
                          ' For more information please see: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3331-Fixed-Rate-Loan-Amortization-Schedule-with-Optional-Extra-Principal-Payments.html
                          
                          ' This function returns an array with the amortization schedule for a loan with known beginning
                          ' principal, period rate, number of periods, 'balloon' balance to be left at the end of the
                          ' amortization schedule, and optional schedule of extra principal payments.
                          
                          ' Notes:
                          ' 1) All arguments should be positive
                          '
                          ' 2) Period rate is the effective fixed rate assessed each period.  For example, if the annual
                          '    rate is 6% and payments are made each month, the the period rate is 6% / 12 = 0.5%
                          '
                          ' 3) Periods is the stated length of the loan assuming no extra payments.  If extra payments
                          '    are made, the effective number of periods may be less than this (unless a Balloon amount
                          '    is specified)
                          '
                          ' 4) The Balloon argument specifies an amount of the original principal not repaid through the
                          '    scheduled payments.
                          '    a) This may be retired as a lump sum payout at the end of the schedule, or itself may be
                          '       refinanced; in either event, it is not included in the payout schedule
                          '    b) If a Balloon amount is specified, the function will never return a schedule with fewer
                          '       payments than specified in the Periods argument.  If necessary, the function will stop
                          '       applying principal payments (i.e., go into "interest only" mode)
                          '    c) The new balance after applying the last payment will always be equal to the balloon
                          '       amount, and the sum of all principal payments will be (BeginPrincipal - Balloon)
                          '    d) Negative balloon amounts are ignored
                          '
                          ' 5) ExtraPrin allows a highly flexible schedule of extra principal payments
                          '    a) If an array is passed as the first item in ExtraPrin, then only that array gets used.
                          '       Such an array can be one dimensional or two dimensional.  (If the array has >2 dimensions,
                          '       only the first 2 get used.)
                          '    b) In all cases, function tries to take elements from ExtraPrin in groups of three.  First
                          '       in the group is the amount of the extra payment, 2nd the starting period in which to
                          '       apply the extra amount, 3rd is the ending period in which to apply the extra amount
                          '    c) You can create multiple streams of extra payments, and those streams can overlap.  If
                          '       there is an overlap, the extra payments from each stream are added together
                          '    d) If the start period of an extra payment stream is zero or omitted, the start period is
                          '       assumed to be the first period
                          '    e) If the end period of an extra payment stream is zero or omitted, the end period is
                          '       assumed to be the last period
                          '    f) Any extra amount in the extra payments schedule is ignored if it is not >0.  No negative
                          '       amortization allowed!
                          '
                          ' 6) The function considers only the cost of servicing the loan: i.e., the interest and principal
                          '    payments.  No provision is made for required "escrow" payments such as for taxes and
                          '    insurance that the lender may require the borrower to pay periodically
                          '
                          ' 7) The function assumes that there is no penalty for early repayment of the loan
                          
                          ' 8) As these are financial, amounts are held as Currency values.  The rounding is controlled by
                          '    a constant, RoundInterval.  For the USA, use 0.01 for RoundInterval (rounding to the penny).
                          '    For whatever locale you are working in, change this constant to conform to local rules.  For
                          '    example, to round to the nearest quarter-unit of currency, use 0.25.  For the nearest half-unit
                          '    use 0.5.  To round to the nearest unit, use 1.  To round to the nearest 5 units, use 5, etc.
                          
                          ' 9) Since the "level payment" defining the baseline for each scheduled payment almost never lands
                          '    exactly on a multiple of the correct rounding interval, it thus must be rounded.  A second
                          '    constant, RoundLevelPmt, controls whether this rounding is unbiased, biased upward, or biased
                          '    downward
                          
                          ' 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 Excel, 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 Currency
                          Dim Schedule2() As Currency
                          Dim BeginBal As Currency
                          Dim Counter As Long
                          Dim Counter2 As Long
                          Dim LevelPay As Currency
                          Dim NumPayments As Long
                          Dim ExtraPrinArr() As Currency
                          Dim ExtraPrinCounter As Long
                          Dim ApplyToCounter As Long
                          Dim ApplyToStart As Long
                          Dim ApplyToEnd As Long
                          Dim arr As Variant
                          Dim Is1D As Boolean
                          
                          ' This constant controls how currency figures are rounded.  As noted above:
                          ' To nearest hundredth of a unit:  0.01
                          ' To nearest tenth of a unit    :  0.1
                          ' To nearest quarter of a unit  :  0.25
                          ' To nearest half of a unit     :  0.5
                          ' To nearest unit               :  1
                          ' To nearest 5 units            :  5
                          ' To nearest 10 units           : 10
                          ' etc
                          
                          Const RoundInterval As Currency = 0.01
                          
                          ' This constant controls how the regularly scheduled "level payment" is calculated.  Most
                          ' often, the level payment will have to be rounded; if the level payment is rounded up, then
                          ' the last payment will be slightly smaller than the others because in effect you have been
                          ' making tiny (i.e., between zero the rounding interval) extra payments throughout the schedule.
                          ' If the level payment was rounded down, then the last payment will be slightly larger than the
                          ' other payments to make up the shortfall.
                          ' To allow the level payment to be rounded without bias, use 0 for RoundLevelPmt
                          ' To always round the level payment up, use a number greater than 0 for RoundLevelPmt
                          ' To always round the level payment down, use a number less than 0 for RoundLevelPmt
                          
                          Const RoundLevelPmt As Long = 0
                          
                          ' Make sure arguments are rounded using correct interval
                          
                          BeginPrincipal = Round(BeginPrincipal / RoundInterval, 0) * RoundInterval
                          Balloon = Round(Balloon / RoundInterval, 0) * RoundInterval
                          
                          ' Dimension array with 'first pass' amortization schedule
                          
                          ReDim Schedule(1 To Periods, 1 To 5) As Currency
                          
                          ' Build array for extra payments to principal.  Each element of the array holds the extra
                          ' principal payment applicable in that period.  Array initializes to zero for each item
                          
                          ReDim ExtraPrinArr(1 To Periods) As Currency
                          
                          ' Skip if nothing is passed for ExtraPrin argument
                          
                          If UBound(ExtraPrin) > -1 Then
                          
                              ' Values passed in for extra payments, so evaluate.  Where aplicable, round the extra
                              ' amount to correct interval
                              
                              If Not IsArray(ExtraPrin(0)) Then
                                  
                                  ' First item was not an array.  Process each element of ExtraPrin.  Process in groups
                                  ' of three.  First is extra amount, second is start period, third is end period
                                  
                                  For ExtraPrinCounter = 0 To UBound(ExtraPrin) Step 3
                                      
                                      ' Test to make sure start period is there.  If not, assume start = 1
                                      
                                      If (ExtraPrinCounter + 1) <= UBound(ExtraPrin) Then
                                          ApplyToStart = ExtraPrin(ExtraPrinCounter + 1)
                                      Else
                                          ApplyToStart = 1
                                      End If
                                      
                                      ' Reset 0 start to 1
                                      
                                      If ApplyToStart < 1 Then
                                          ApplyToStart = 1
                                      End If
                                      
                                      ' Test to make sure end period is there.  If not, assume end = num of periods
                                      
                                      If (ExtraPrinCounter + 2) <= UBound(ExtraPrin) Then
                                          ApplyToEnd = ExtraPrin(ExtraPrinCounter + 2)
                                      Else
                                          ApplyToEnd = Periods
                                      End If
                                      
                                      ' Make sure end does not exceed Periods; reset 0 to num of Periods
                                      
                                      If ApplyToEnd > Periods Or ApplyToEnd = 0 Then
                                          ApplyToEnd = Periods
                                      End If
                                      
                                      ' Use loop to add extra principal amount to the array.  Skip zero/negative extra amounts
                                      
                                      For ApplyToCounter = ApplyToStart To ApplyToEnd
                                          If ExtraPrin(ExtraPrinCounter) > 0 Then
                                              ExtraPrinArr(ApplyToCounter) = ExtraPrinArr(ApplyToCounter) + _
                                                  Round(ExtraPrin(ExtraPrinCounter) / RoundInterval, 0) * RoundInterval
                                          End If
                                      Next
                                  Next
                              Else
                                  
                                  ' First element was an array.  Determine whether this array is 1-D or 2-D.  Trying to find
                                  ' upper bound of second dimension will cause an error if the array is 1-D
                                  
                                  arr = ExtraPrin(0)
                                  On Error Resume Next
                                  ExtraPrinCounter = UBound(arr, 2)
                                  If Err = 0 Then
                                      Is1D = False
                                  Else
                                      Err.Clear
                                      Is1D = True
                                  End If
                                  On Error GoTo 0
                                  
                                  If Is1D Then
                                  
                                      ' Process elements in groups of three, as above
                                      
                                      For ExtraPrinCounter = LBound(arr) To UBound(arr) Step 3
                                          If (ExtraPrinCounter + 1) <= UBound(arr) Then
                                              ApplyToStart = arr(ExtraPrinCounter + 1)
                                          Else
                                              ApplyToStart = 1
                                          End If
                                          If ApplyToStart < 1 Then
                                              ApplyToStart = 1
                                          End If
                                          If (ExtraPrinCounter + 2) <= UBound(arr) Then
                                              ApplyToEnd = arr(ExtraPrinCounter + 2)
                                          Else
                                              ApplyToEnd = Periods
                                          End If
                                          If ApplyToEnd > Periods Or ApplyToEnd = 0 Then
                                              ApplyToEnd = Periods
                                          End If
                                          For ApplyToCounter = ApplyToStart To ApplyToEnd
                                              If arr(ExtraPrinCounter) > 0 Then
                                                  ExtraPrinArr(ApplyToCounter) = ExtraPrinArr(ApplyToCounter) + _
                                                      Round(arr(ExtraPrinCounter) / RoundInterval, 0) * RoundInterval
                                              End If
                                          Next
                                      Next
                                  
                                  Else
                                      
                                      ' Array is at least 2-D; if there are 3+ dimensions, ignore them.  Assume that first
                                      ' dimension enumerates the distinct extra payment streams, and that the second
                                      ' dimension contains (in this order) the extra amount, the start period, and the end
                                      ' period, although the start/end periods can be omitted
                                      
                                      For ExtraPrinCounter = LBound(arr, 1) To UBound(arr, 1)
                                      
                                          ' If lower bound < upper bound, then an element for the start period must be there.
                                          ' If it is, get it and evaluate it.  If there is no start period element, then the
                                          ' start period will always be 1
                                          
                                          If LBound(arr, 2) < UBound(arr, 2) Then
                                              ApplyToStart = arr(ExtraPrinCounter, LBound(arr, 2) + 1)
                                          Else
                                              ApplyToStart = 1
                                          End If
                                          If ApplyToStart < 1 Then
                                              ApplyToStart = 1
                                          End If
                                          
                                          ' If (lower bound + 1) < upper bound, then an element for the end period must be
                                          ' there.  If it is, get it and evaluate it.  If there is no end period element,
                                          ' then the end period will always be the last period
                                          
                                          If (LBound(arr, 2) + 1) < UBound(arr, 2) Then
                                              ApplyToEnd = arr(ExtraPrinCounter, LBound(arr, 2) + 2)
                                          Else
                                              ApplyToEnd = Periods
                                          End If
                                          If ApplyToEnd > Periods Or ApplyToEnd = 0 Then
                                              ApplyToEnd = Periods
                                          End If
                                          
                                          ' Determine extra payment amounts for each period.  Skip where extra amount is
                                          ' zero or negative
                                          
                                          For ApplyToCounter = ApplyToStart To ApplyToEnd
                                              If arr(ExtraPrinCounter, LBound(arr, 1)) > 0 Then
                                                  ExtraPrinArr(ApplyToCounter) = ExtraPrinArr(ApplyToCounter) + _
                                                      Round(arr(ExtraPrinCounter, LBound(arr, 1)) / RoundInterval, 0) * RoundInterval
                                              End If
                                          Next
                                      Next
                                  End If
                              End If
                          End If
                              
                          BeginBal = BeginPrincipal
                          
                          If Balloon < 0 Then Balloon = 0
                          
                          ' Determine level payment before extra payments and round to appropriate interval.  If the number
                          ' is rounded down, the last payment will be slightly larger to make up the shortfall.  If rounded
                          ' up, the last payment will be slightly smaller
                          
                          LevelPay = -Pmt(PeriodRate, Periods, BeginPrincipal, -Balloon)
                          If RoundLevelPmt > 0 Then
                              If LevelPay / RoundInterval > Int(LevelPay / RoundInterval) Then
                                  LevelPay = (1 + Int(LevelPay / RoundInterval)) * RoundInterval
                              Else
                                  LevelPay = Int(LevelPay / RoundInterval) * RoundInterval
                              End If
                          ElseIf RoundLevelPmt = 0 Then
                              LevelPay = Round(LevelPay / RoundInterval, 0) * RoundInterval
                          Else
                              LevelPay = Int(LevelPay / RoundInterval) * RoundInterval
                          End If
                      
                          ' Loop through each scheduled payment
                          
                          For Counter = 1 To Periods
                              
                              ' Increment actual number of payments made
                              
                              NumPayments = NumPayments + 1
                              
                              ' Set beginning balance for this payment
                              
                              Schedule(Counter, 1) = BeginBal
                              
                              ' Interest is easy to determine; do it first.  Since all the starting values are already
                              ' rounded appropriately, as long as this one is too then the rest of the values will also
                              ' be rounded correctly
                              
                              Schedule(Counter, 4) = Round(BeginBal * PeriodRate / RoundInterval, 0) * RoundInterval
                              
                              ' 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.  Also curtail principal
                              ' payment so that the new balance never dips below the balloon amount, if applicable
                              
                              Schedule(Counter, 3) = IIf((LevelPay - Schedule(Counter, 4)) < (BeginBal - Balloon), _
                                  LevelPay - Schedule(Counter, 4), BeginBal - Balloon)
                              
                              ' Apply extra principal if applicable.  If a balloon amount is specified, do not apply any
                              ' extra payments if that would bring the new balance under the balloon amount
                              
                              If ExtraPrinArr(Counter) > 0 Then
                                  If ExtraPrinArr(Counter) <= (BeginBal - Balloon - Schedule(Counter, 3)) Then
                                      Schedule(Counter, 3) = Schedule(Counter, 3) + ExtraPrinArr(Counter)
                                  Else
                                      Schedule(Counter, 3) = Schedule(Counter, 3) + (BeginBal - Balloon - Schedule(Counter, 3))
                                  End If
                              End If
                              
                              ' 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
                              Else
                                  BeginBal = Schedule(Counter, 5)
                              End If
                              
                              If Counter = Periods And BeginBal > 0 Then
                                  Schedule(Counter, 2) = Schedule(Counter, 2) + BeginBal
                                  Schedule(Counter, 3) = Schedule(Counter, 3) + BeginBal
                                  Schedule(Counter, 5) = 0
                              End If
                              
                          Next
                          
                          ' Create a new array that has only as many 'rows' as there are payments to be made
                          
                          ReDim Schedule2(1 To NumPayments, 1 To 5) As Currency
                          
                          For Counter2 = 1 To NumPayments
                              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
                      

Open in new window

(to open source code in a new tab, click here)


Loan Amortization Schedule Sample File




Please use this file as a sample workbook for experimenting with the AmortSchedTraditional function and creating your own amortization schedules:

Amortization-Schedule.xls

This workbook has two worksheets, Example 1 and Example 2.  Both worksheets have the same structure, but they provide the extra principal payments in different ways:
Example 1 specifies ExtraPrin using multiple values separated by commas.
Example 2 specifies ExtraPrin using a two-dimensional array (passed as a range).

In both worksheets, the cells requiring user input have a yellow background color.
Enter the beginning balance of the loan (1).
Enter the period rate (2). Be sure to convert any annual rates into the rate assessed per period!
Enter the number of periods (3), basically the number of years multiplied by the payments made per year.
Enter the balloon amount, if applicable (4).
Enter information for your extra payment streams, if applicable (5). The sample file allows for up to 6 different streams of extra payments, although AmortSchedTraditional can take an arbitrary number of them.
If you wish to retrieve the details for any given payment, enter the period number in (6).

Worksheet Screenshot
Under the input area, the worksheet will display the full schedule of payments for the specified loan.  The worksheets are capable of showing the full schedule for loans having up to 1,000 payments.  To the right of the input area, the worksheets show the details for a user-selected single payment, as well as summary results for the schedule (net actual number of payments, and total nominal amounts paid out).


Sample Amortization Schedules



The possibilities for creating your own schedules are limited only by your imagination, but to help get you started I have defined four scenarios in the sample workbook.  You can either use the Scenario Manager to view them, or you can simply enter the appropriate values into the Example 1 worksheet.

Scenario 1: Basic $100k Mortgage

To use this scenario, either enter the appropriate values into the yellow cells as shown below, or recall the scenario "Basic $100k Mortgage" using the Scenario Manager.  (In Excel 2007/2010, click What If Analysis on the Data tab of the Ribbon, and select Scenario Manager.  Select the scenario, and click Show and then Close.  In Excel 2003, select Tools|Scenarios from the menu.  Select your scenario from the list, and then click Show and Close.)

Basic $100k Mortgage
In this scenario, the loan amount is $100,000, the annual rate is 10% (and thus the period rate is 0.8333%), and the scheduled number of periods is 360 (that is, like most mortgages in the USA, it is a 30-year mortgage with monthly payments).  The payment each month is $877.57, although the last payment will be $880.97, because the level monthly payment was rounded down.  Since no extra payments are made, the loan is retired right on schedule at payment #360.

As seen in the screen shot above, the total interest paid over the course of the loan is approximately $216,000.

Scenario 2: $100k Mortgage with Constant Extra Payment

In this new scenario, we take the same loan as for Scenario 1, except that this time our thrifty home-buyers decide to add an extra $100 to each payment so that they will retire the loan earlier and save on interest costs.  To view this scenario, either retrieve it with the Scenario Manager per the instructions with Scenario 1, or simply enter the values in the yellow cells as shown below:

$100k Mortgage with Constant Extra Payment
Note that now the monthly payment will be $977.57, i.e., $100 more than what the level payment would have been otherwise.  We also note that now the loan will be retired with payment #231, and the total interest paid has dropped to approximately $125,00, or more than $90,000 (~42%) less in interest than if no extra payments were made.  (This ignores the time value of money, of course, and makes no reference to the opportunity cost of foregone alternative investment options as well as the possible tax benefits that would accrue if, as in the USA, mortgage interest is tax-deductible in the home-buyers locale.)

Note also that, since we want the $100 extra payment to apply to all payments made, I had to specify the payment amount, but I was able to omit the start period for the extra payments as well as the end period.

Scenario 3: $100k Mortgage with Rising Extra Payments

In this third scenario, we have that same $100,000 mortgage, but this time there are multiple extra payment flows: our home-buyers are so anxious to retire the loan early that they decide to increase their extra payments over time, perhaps to coincide with anticipated increases in income.  Again, either retrieving the scenario from the Scenario Manager or entering the values below into the yellow cells on our worksheet:

$100k Mortgage with Rising Extra Payments
The base payment is still $877.57, and for the first year (payments #1-12) the actual amount paid reflects an extra $100, bringing the total payment to $977.57.  However, starting with payment #13, the extra amount rises to $200 (total payment $1,077.57).  At payment #25 (not shown here, but you can scroll down in the worksheet), the payment increases again to $1,177.57, and stays there for the duration of the schedule.

With this aggressive extra payment strategy, our home-buyers retire the loan after just 159 payments--fewer than half the originally scheduled 360 payments.  Total interest paid also decreases to about $83,000--almost $133,000 (~62%) less than originally planned in Scenario 1.  Again, of course, this ignores the time value of money, taxes, opportunity cost, etc.

Note that in this scenario, there are three distinct streams of extra payments.  I assigned definite start and end periods for the first two streams, but then by omitting the end period for the third stream, I indicate that that stream is to continue for the rest of the loan.

Scenario 4: $100k Mortgage with Series of One-Time Extra Payments

This final scenario demonstrates how to set up one or more "one-time" extra payments.  Suppose our home-buyers have several term deposits that they know are coming due at one-year intervals, and they want to see how applying the proceeds from those term deposits to their mortgage might hasten retirement of the loan.  Again retrieving the scenario with the Scenario Manager or simply entering the values into the appropriate cells:

$100k Mortgage with Series of One-Time Extra Payments
Here we have six extra payments scheduled.  Since the start and end period is the same for each stream, we can see that each is effectively a one-time payment.  The loan is retired after 289 payments, rather than the scheduled 360.  The payment each month is usually $877.57, but for the six indicated payments (#s 12, 24, 36, 48, 60, and 72), it jumps to $1,877.57.  Total interest paid is about $159,000.


Adjusting the Length of Schedule Output



To provide a high degree of flexibility in using the attached workbook to make your own loan amortization schedules, I used a range 1,000 cells tall for my array formula, thus allowing you to display a schedule of up to 1,000 payments.  Most loans do not involve so many payments, and in this case, any cells in that range that the AmortSchedTraditional function does not "need" because the loan is already repaid will show a #N/A error, because the range has exceeded the number of elements in the returned array.

In the example below, the loan is paid off, on schedule, after the 360th payment.  Of course, this means that in the rows allocated for the unused payments 361 - 1000, Excel will show the #N/A error.

#N/A Error Example
Some users may find it discomfiting to see those error results, even though in this case nothing is actually wrong.  To help with this, you may adopt two possible approaches: using VBA event code to "hide" the unneeded rows, or using Conditional Formatting to alter the appearance of those rows (such as by changing the font color to be the same as the background color.)

To use VBA, you could trap the Worksheet_Calculate event, such as with this:

Private Sub Worksheet_Calculate()
                          
                          With Me.Range("Schedule")
                              .Rows("1:" & Me.Range("NetPayments").Value).Hidden = False
                              If Me.Range("NetPayments").Value < .Rows.Count Then
                                  .Rows((Me.Range("NetPayments").Value + 1) & ":" & .Rows.Count).Hidden = True
                              End If
                          End With
                          
                      End Sub

Open in new window


I included similar procedures in the two worksheets for the attached sample workbook, but I commented both out:
Before using this code, users should first understand how the AmortSchedTraditional function, and this workbook, operate; and
If you are going to add your own formulas to these worksheets, performance may suffer from running this code on every worksheet calculation

To use these procedures, simply:
Open the workbook in Excel
Use Alt+F11 to get to the VB Editor
If the Project Explorer (see below) is not visible, use Ctrl+R to show it.  Find Amortization Schedule in it, and expand the node for Microsoft Excel Objects
VB Editor Project Explorer
Double click the lines for Example 1 and Example 2 to open the modules, and then remove the single-quotes at the beginning of each line of code

To use Conditional Formatting, use the following work steps in Excel 2007 or 2010:
Open the attached file in Excel
On the Example 1 worksheet, select the range A15:E:F1014
On the Home tab of the Ribbon, click Conditional Formatting, and then New Rule
In the New Formatting Rule dialog, select 'Use a formula to determine which cells to format'.  In the formula text box, enter this formula:  =ISNA($B15)
Click the Format button, and go to the Font tab.  For Color, select the same color as you are using for the cell's background Fill color (if you have not changed this, use white)

In Excel 2003 the work steps are similar:
Select the range
Select Format|Conditional Formatting from the menu
For Condition1, use 'Formula is', and enter the formula  =ISNA($B15)
Click the Format button, and go to the Font tab to choose your font color


Using AmortSchedTraditional in Other VBA/VB6 Projects



While I designed the AmortSchedTraditional function for the specific goal of using it to build a loan amortization schedule in Excel, the source code makes no reference to Excel objects, and can be used without modification in any VBA or Visual Basic 6 application.  (For example, the Round and Pmt functions used in the source code are from the VBA library, and are not the Excel versions of these functions.)

For example, in an Access database being used to store payment schedules for installment loans, one could use AmortSchedTraditional to create an amortization schedule in code, and then add the elements of the returned array to a table holding the details of the repayment schedule.



=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
18
36,652 Views
Patrick Matthews
CERTIFIED EXPERT

Comments (16)

CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
Glad to help :)
ExpExchHelpAnalyst

Commented:
matthewspatrick:

Wow... very impressive approach and very informative.

When downloading the XLS and plugging in the numbers (where matching), I didn't see the monthly payment... and it the total price seemed to be way off.

Not questioning the XLS... I'm sure it works great for a mortgage... not sure if it helps me w/ my current question though.

Any additional thoughts?

EEH
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
EEH,

Make sure you have macros enabled.  The workbook depends on VBA code to work its voodoo.

With macros enabled, once you enter the loan parameters in B1:B4, your base payment will appear in D1.

Patrick
CERTIFIED EXPERT
Top Expert 2012

Commented:
For what it is worth, there is a CLR function created so there is no need to use T-SQL code.  You can download it from Loan Amortisation in SQL Server (PMT, FV, IPMT, PPMT).  Notice that the author gives credit to Kevin's article here.

Anthony
Chris BaileyPresident

Commented:
Patrick
I am very impressed with your VB code Function amortization schedule with optional extra principal payments. I have used it in an excel macro and it produces an answer very close to what I am looking for. The variable payment amount is I believe the difference (in my case I would want this amount to be fixed over the term of the loan). My question to you has anyone written this VB function in SQL? The reasoning is that we are in the taxi business, offering car loans to drivers repayable weekly over a 2 to 3 year term so integrating this code into our billing software (written in SQL) would work much better for us.
Regards
Chris Bailey

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.