<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Fixed Rate Loan Amortization Schedule with Optional Extra Principal Payments

Published on
48,748 Points
31,448 Views
18 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick

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
Comment
16 Comments
LVL 58

Expert Comment

by:harfang
I like! I wish I could have linked to this article a few years ago: reimbursement schedules handling Swiss Francs are so very rare among the thousand and some available on the Internet!

(°v°)
0
LVL 32

Expert Comment

by:DrDamnit
Impressive!
0
LVL 61

Expert Comment

by:Kevin Cross
Very nice, Patrick! You have my yes vote above.
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

LVL 93

Author Comment

by:Patrick Matthews
Thank you for the compliments :)
0
LVL 38

Expert Comment

by:younghv
I remember trying to fool around with this concept a few years ago and go nowhere.
Great - USABLE - stuff.
Thank you for writing it.
"Yes" vote above.
0
LVL 18

Expert Comment

by:Ravi Agrawal
Brilliant, so much to learn

Yes Vote of course.

Ravi.
0

Expert Comment

by:easycapital
Food for thought.  Yearly rate of 12%, monthly rate should be calculated as = (1 + Yearly rate) ^ (1 /12) - 1

Let me know.
JP
0
LVL 93

Author Comment

by:Patrick Matthews
JP,

Good question.  You are hitting on the difference between the annual interest rate and the effective yield.

Mortgage contracts in the USA are typically stated as an annual rate (for example, 30 year fixed rates are currently at between 4.75% and 5%, if you can qualify), compounded monthly.  That is why I set up the UDF and my examples to assume that the period rate is always the annual rate divided by the number of periods in a year.

Thanks for your comment and the "helpful" vote!

Patrick
0
LVL 38

Expert Comment

by:younghv
Hey Patrick,
I'm trying to work these numbers from a different angle.

Through the years I have paid down the principal in various amounts and at various times.

I have the current principal amount, the fixed interest rate, and the current montly P&I payment, but can't figure out how to get a new payment schedule set up. If I can get that done, I want to play with some different Principal Only payments to see how much beer I can afford on a regular basis.

Using Excel 2003.

If there is a simple way to set this up, please let me know - otherwise I'll post a question in the Excel Zone.

Thanks - back on-line in a few hours.

Vic
0
LVL 93

Author Comment

by:Patrick Matthews
Vic,

You should be able to use this to create a new schedule that only includes the payments remaining on the loan.  Trying to reconstruct the entire schedule, including payments already made, is a bridge too far :)

Put the current balance (remaining principal) in B1, the Period Rate (for a monthly payment, annual rate divided by 12) in B2, and the remaining payments in B3.  If there is a balloon amount, put that in B4.

Now, you should see what the remaining schedule looks like without any additional payments to principal.  If you want to model extra payments, use B7:D12 to enter that information.

Make sense?

Patrick
0
LVL 38

Expert Comment

by:younghv
Aha!
The hangup was the number of remaining payments.

The schedule from the lender only shows the original "Maturity Date" and has never been adjusted to account for the earlier principal only payments.

I used the basic Excel Amortization Template to SWAG the remaining "periods" and now I'm cooking with gas.

This is fun stuff - thanks for the feedback.
0
LVL 93

Author Comment

by:Patrick Matthews
Glad to help :)
0

Expert Comment

by:ExpExchHelp
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
0
LVL 93

Author Comment

by:Patrick Matthews
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
0
LVL 75

Expert Comment

by:Anthony Perkins
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
0

Expert Comment

by:Chris Bailey
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
0

Featured Post

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Join & Write a Comment

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month