Published on

49,566 Points

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.

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:

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

Rearranging that formula yields the level payment amount paid each period:

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.

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:

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

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

(to open source code in a new tab, click here)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).

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).

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.

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.)

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.

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:

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.

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:

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.

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:

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.

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.

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

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

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

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.

=-=-=-=-=-=-=-=-=-=-=-=-=-

Was this article helpful?

label that is just below and to the right of this text.

=-=-=-=-=-=-=-=-=-=-=-=-=-

Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days freeClick here to view the full article

COLLABORATE WITH CERTIFIED PROFESSIONALS

Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.

Ask questions about what you read

If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.

Learn from the best.