## Loan Amortization Schedule in ExcelAn amortising loan is simply a fancy phrase for a loan with instalment payments due for the duration of the loan. In essence, every loan amortises in some manner. For instance, there would be 12 regular instalments for a loan with full amortisation over 12 months. Part of each payment goes towards interest, and part goes towards the Principal. You can create a loan amortisation schedule that details all loan payments. An amortisation schedule is a table that displays the remaining balance following each payment, breaks down each instalment in interest and Principal, and details the periodic payments of a loan/mortgage over time. ## How to make an Excel loan amortisation scheduleUsing the following Excel functions, we can create a mortgage or loan amortisation schedule: **PMT function**determines the total amount due regularly. This sum does not change throughout the loan.**PPMT function**retrieves the portion of all payments applied to the loan principal or the total amount borrowed. With each additional payment, this sum rises.**IPMT function**determines how much interest is paid on each transaction. With every payment, this sum gets smaller.
Let's now walk through each phase of the procedure. ## 1. Establish the amortisation scheduleTo begin with, specify the input cells that will include the known loan components: **D4:**the interest rate per year/Annual Interest rate**D5:**Loan length in years/Long term**D6:**Amount of payments made annually/Payments per year**D7:**Loan Amount
The next step is making an amortisation table with the labels Period, Payment, Interest, Principal, and Balance in B9:F9. Enter a string of numbers in the Period column (1-12 in this example) that represent the total number of payments: Now that we have established all the necessary elements, let's move on to the most fascinating aspect: loan amortisation calculations. ## 2. Use the PMT formula to determine the total payment amount.A It would help if you used consistent values for the **Rate:**computed by dividing the annual interest rate by the total number of payment periods**($D$4/$D$6)**.**Nor**is the product of the number of years and the number of payment periods**($D$5*$D$6)**.- Enter a loan amount
**($D$7)**in the**pv** - Since the amount after the final payment is meant to be zero and payments are made towards the conclusion of each period, the
**fv**&**type**arguments can be left out since the defaults function perfectly for us.
Combining the arguments above yields the following formula: = PMT($D$4/$D$6,$D$5*$D$6,$D$7) We use absolute cell references since this formula should automatically copy to the cells below. When you input the PMT formula into ## 3. Use the IPMT formula to calculate interestEach periodic payment's interest component can be found using an =IPMT($D$4/$D$6, B10, $D$5*$D$6,$D$7) All arguments within the PMT formula are identical, except the payment period-specific per argument. This input is provided via a relative cell reference After copying this formula down to ## 4. Use the PPMT formula to find the PrincipalUse the following PPMT formula to determine the principal portion of each periodic payment: = PPMT($D$4/$D$6, B10 ,$D$5*$D$6, $D$7) The arguments and syntax are precisely identical as in the previously mentioned IPMT formula: This formula starts in Here's a ## 5. Obtain the remaining BalanceTwo separate formulas will determine the remaining balance for each period. Add the loan amount ( = D7+E10 The Principal is really deducted from the loan amount as it is a negative number, while the loan amount is a positive number. Add the Principal for this period to the preceding balance for the following and all subsequent periods: = F10+E11 The formula above is copied along the column and goes up to And that's it! We have completed our monthly loan amortisation programme.
Excel functions display the payment, interest, and Principal as negative amounts since loans are paid from your bank account. The graphic illustrates how certain values are, by default, highlighted with red and surrounded by parenthesis. A negative sign should be used before the Instead of adding, use subtraction for the Balance formulae, as the screenshot below illustrates: ## Amortisation Schedule for a variable number of periodsIn the example above, we created a loan amortisation schedule for the predetermined number of payment terms. For a particular loan or mortgage, this simple one-time fix is effective. A more thorough technique is required to design a reusable amortisation schedule using a variable number for periods, as explained below. ## 1. Enter the maximum number of periodsPut the maximum payments you will accept for any loan within the Period column. To input a number sequence more quickly, use Excel's ## 2. In amortisation calculations, use IF statements.It would help if you found a way to restrict the computations to the true amount of payments due for a certain loan, as you now have a lot of inflated period numbers. To achieve this, enclose each formula in an IF statement. The IF statement's logical test determines if a period number within that row equals or is less than the total payments. The empty string gets returned if the result of the logical test is FALSE; if it is TRUE, the associated function is calculated. The following formulas should be entered in the respective cells, assuming that Period 1 is in row 8. These should then be copied throughout the entire table.
=IF(B10<=$D$5*$D$6,PMT($D$4/$D$6, $D$5*$D$6, $D$7))
=IF(B10<=$D$5*$D$6,IPMT($D$4/$D$6,B10,$D$5*$D$6,$D$7), "")
=IF(B10<=$D$5*$D$6,PPMT($D$4/$D$6, B10, $D$5*$D$6, $D$7), "")
The formula of Period 1 ( = D7+E10 The formula looks like this in Period 2 ( =IF(B10<=$D$5*$D$6, F10+E10, "") Consequently, you will obtain an accurately computed amortisation plan and numerous blank rows containing the period numbers following loan payback. ## 3. Hide unnecessary period numbersConsider the work completed and move on to the next stage if you can tolerate seeing unnecessary period numbers shown following the most recent payment. Creating a conditional formatting rule that alters the font colour to white for any rows following the last payment is completed will help you conceal any unneeded periods if you aim for perfection. To accomplish this, choose the To verify whether the period number within column B exceeds the total number of payments, put the following formula in the relevant box: =$B$10>$D$5*$D$6 An important reminder: Make sure you multiply the cells corresponding to the Loan term and Payments per year ( Next, select the white font colour by clicking the Format... option. Completed! Change the font colour to white for any rows that come beyond the final payment period. ## 4. Create a Summary of the loanAdd a few extra formulas at the top of your amortisation schedule so that you can quickly examine the summary information regarding your loan.
=-SUM(C10:C21)
=-SUM(D10:D21) In the formulas above, delete the minus sign if your payments are positive numbers. And that's it! We are ready to proceed with our finished loan amortisation programme! ## How to use Excel to create a loan amortisation Schedule with Extra PaymentsHopefully, the amortisation schedules covered in the earlier examples are simple to make and follow. They do not, however, provide a helpful feature that many borrowers would find appealing: the ability to make extra payments to expedite loan repayment. This example will examine making a loan amortization schedule with extra payments. ## 1. Declare the input cellsStart by configuring the input cells as usual. For the purpose of making our formulas simpler to read, let's call these cells as follows: - The interest rate for the year is
**D4**. - The loan term, expressed in years, is
**D5**. - Number of payments made a year, or
**D6**, - Loan Amount -
**D7** - Extra Payment Amount-
**D8**
## 2. Determine a payment scheduleThe scheduled payment amount, or the amount owed on a loan if no additional payments are made, is a preset cell needed in addition to the input cells for our additional computations. We use the following formula to determine this amount: =IFERROR(-PMT(Interest Rate/Payments Per Year, Loan Term*Payments Per Year, Loan Amount), "")
=IFERROR(-PMT(D4/D6,D5*D6,D7), "") Please note that to have a positive result, we have to place a minus sign before the PMT function. We encapsulate the PMT formula inside the Put this formula in a cell (let's use ## 3. Establish the amortization tableWith the headings from the screenshot below, create a loan amortisation table. Put a string of integers in the Period column that starts using zero (since you may conceal the Period 0 row later when necessary). Enter the maximum number of payment periods that can be entered if you want to build a reusable amortization schedule. Select the balance value corresponding to the initial loan amount in Period 0. This row's remaining cells will all remain empty: The formula for =D7 ## 4. Create calculations for an amortisation schedule that includes Extra payments.This is a crucial aspect of what we do. We must perform all the maths since Excel's built-in functions cannot be used for further payments. ## Note: In this illustration, row 12 corresponds to Period 0 and row 13 to Period 1. Please be careful to change the cell references if the beginning of your amortisation table is in a different row.After entering the following formulas on row 13 (Period 1), repeat the process for each subsequent period.
Use the scheduled payment if the amount (designated as cell =IFERROR(IF(Schedule Payment<=H12, Schedule Payment, H12+H12*Interest Rate/Payments Per Year), "") We use the IFERROR function to encapsulate this formula and all others as an additional safety measure. If certain input cells are empty / contain invalid data, this will stop many errors.
Apply the following logic to an IF formula: You should refund the ExtraPayment if it is less than the distinction between the Principal for this period ( =IFERROR(IF(Extra Payment<H12-F13, Extra Payment, H12-F13), "") As a helpful hint, enter any variable amount you have for additional payments directly within the Extra Payment section.
Add the extra payment ( =IFERROR(C13+D13, "")
The lesser of the two values-scheduled payment minus interests ( =IFERROR(IF(C13>0, MIN(C13-G13, H12), 0), "") It should be noted that the Principal only represents the portion of the regular payment-not the additional payment-that is applied to the Principal of the loan.
Divide the yearly interest rate (cell =IFERROR(IF(C13>0, Interest Rate/Payments Per Year*H12, 0), "")
Subtract the principal amount for the payment ( =IFERROR(IF(H12 >0, H12-F13-D13, 0), "") ## Note: Some of the formulas may produce incorrect answers because they cross-reference (not circularly!). Therefore, please wait to begin troubleshooting until you have entered the final formula of your amortisation table.Your loan amortisation schedule at this stage, if everything is done correctly, should resemble this: ## 5. Hide Additional PeriodsUtilise this tip to set up the conditional formatting rule that hides values in unused periods. The distinction is that this time, we fill the rows with zero or empty values for Balance (column H) and Total Payment (column E) by using the white font colour: =AND(OR($E12=0, $E12=""), OR($H12=0, $H12="")) And just like that, no rows using zero values are visible. To conceal zero values in extra periods, implement a conditional formatting rule. ## 6. Write a Summary of the loanTo add the final touch for perfection, you can use the following formulas to produce the most crucial details about a loan:
The number of years multiplied by the annual payment amount is: =Loan Term*Payments Per Year
Count the cells that are larger than zero in the Total Payment column, starting with Period 1: =COUNTIF(E13:E24,">"&0)
Start with Period 1 and add up the cells in the Extra Payment column. =SUM(D13:D24)
Start with Period 1 and add up the cells in the Interest column. =SUM(G13:G24) Once you hide the row containing Period 0, the loan's amortization schedule, including extra payments, will be completed! The final result is displayed in the screenshot below: Next TopicCreating a Database in Excel |