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.

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:

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.

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.

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 Variantarr = 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 = Schedule2End Function

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

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

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:

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:

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:

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.

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

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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

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!

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.

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.

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.

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.

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.

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.

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

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.

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…