{"id":6654,"date":"2023-05-24T15:54:22","date_gmt":"2023-05-24T15:54:22","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=6654"},"modified":"2024-05-31T15:29:19","modified_gmt":"2024-05-31T15:29:19","slug":"how-to-create-an-amortization-schedule-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-create-an-amortization-schedule-in-excel\/","title":{"rendered":"How to Create an Amortization Schedule in Excel?"},"content":{"rendered":"
\n
\n
\n
\n

Amortization Schedule in Excel is an invaluable tool for managing loans, mortgages, and other financial obligations. By using this feature, you can create detailed repayment plans that outline each payment’s allocation towards principal and interest over the loan term. With Amortization Schedule in Excel, you gain visibility into your repayment schedule, allowing you to track progress, forecast future payments, and make informed financial decisions. Whether you’re a borrower managing personal finances or a financial professional assisting clients, mastering the creation and utilization of amortization schedules in Excel is essential. Embrace this feature to stay organized, manage debt effectively, and achieve your financial goals with confidence.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n

This Content Covers:<\/strong><\/p>\n

    \n
  1. What Is an Amortization Schedule?<\/strong><\/a><\/li>\n
  2. Purpose and Benefits of an Amortization Schedule in Excel.<\/strong><\/a><\/li>\n
  3. How to Create an Amortization Schedule using Pre-Built Excel Templates?<\/strong><\/a><\/li>\n
  4. How to Create a Loan Amortization Schedule Manually?<\/strong><\/a><\/li>\n
  5. How to Make a Loan Amortization Schedule with Extra Payments in Excel?<\/strong><\/a><\/li>\n<\/ol>\n

    1. What Is an Amortization Schedule?<\/strong><\/h2>\n

    An amortization schedule is a table or chart that shows the details of each periodic payment made towards a loan or mortgage. It breaks down each payment into its principal and interest components and displays the remaining loan balance after each payment is made.<\/p>\n

    2. Purpose and Benefits of an Amortization Schedule in Excel.<\/strong><\/h2>\n

    Purpose: <\/strong>The purpose of an amortization schedule is to provide borrowers with a clear and comprehensive view of their loan payments and to help them understand how much of each payment goes towards paying off the principal amount and how much goes towards paying interest.<\/p>\n

    Benefits: <\/strong>There are several benefits of creating an amortization schedule. Some of them are given below.<\/p>\n

      \n
    1. Clear understanding:<\/strong> Understanding how their loan functions, including how much of their payment goes to interest and how much goes to paying off the loan principle, is beneficial for borrowers.<\/li>\n
    2. Budgeting:<\/strong> By knowing how much their payments would be throughout the course of the loan; borrowers may make the necessary adjustments to their budgets.<\/li>\n
    3. Comparison:<\/strong> It makes it simple to compare several loan possibilities and choose the one that best suits their financial circumstances.<\/li>\n
    4. Transparency<\/strong>: It makes the loan procedure transparent, assisting in avoiding misunderstandings or miscommunication between the borrower and lender.<\/li>\n<\/ol>\n

      3. How to Create an Amortization Schedule using Pre-Built Excel Templates?<\/strong><\/h2>\n

      There are plenty of pre-made Excel templates that you can use to keep track of your loan information. Now let\u2019s see how to create an amortization schedule using Excel templates.<\/p>\n

      Step 1:<\/strong> Open a worksheet and go to File.<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 2: <\/strong>Select New<\/strong> and enter the name of the type of amortization table you want to use in the search box.<\/p>\n

      \"Amortization<\/p>\n

      Step 3: <\/strong>If you find your desired template, click on it to see the preview, and select Create <\/strong>option to create this template inside your worksheet.<\/p>\n

      \"Amortization<\/p>\n

      Step 4: <\/strong>Now change the values from the upper section of the table according to your actual loan, interest, and other information. The rest of the table\u2019s data will be generated according to that.<\/p>\n

      \"Amortization<\/p>\n

      4. How to Create a Loan Amortization Schedule Manually?<\/strong><\/h2>\n

      Step 1: <\/strong>To create an amortization schedule manually, first we have to create the structure or the amortization table like this.<\/p>\n

      \"Amortization<\/p>\n

      Step 2: <\/strong>Then enter the loan amount, interest rate and other information like the picture below.<\/p>\n

      \"Amortization<\/p>\n

      Step 3: <\/strong>Now we have to use the PMT<\/strong> formula to count the payment. Select cell B8 and insert this formula inside the cell.<\/p>\n

      =PMT($C$3\/$C$5,$C$4*$C$5,$C$2)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 4: <\/strong>Press enter to get first period\u2019s payment. Here we can see that the payment is shown in red color which means this is a negative value. This happened because the loan is paid out of our bank accounts. So, by default, Excel the negative valued in red and enclose them inside parentheses. Follow the next step to get the result as a positive value.<\/p>\n

      \"Amortization<\/p>\n

      Step 5: <\/strong>To solve this issue we will use a Minus Sign (-) <\/strong>before each of the formulas for payment, interest, and principal. After using a minus sign and converting the result into positive, drag the fill-handle of B8 down to B17 to get all the payment data.<\/p>\n

      \"Amortization<\/p>\n

      Step 6: <\/strong>Now we will calculate the first period\u2019s interest in cell C8 by using the IPMT<\/strong> formula with a minus sign. Then drag the cell down to C17 to get all the interest data.<\/p>\n

      =-IPMT($C$3\/$C$5,A8,$C$4*$C$5,$C$2)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 7: <\/strong>Calculate the principal using the PPMT<\/strong> formula with a minus sign in D8 and drag it downwards.<\/p>\n

      =-PPMT($C$3\/$C$5,A8,$C$4*$C$5,$C$2)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 8: <\/strong>We have the payment, interest, and principal data. Now we have to calculate the balance after the first period\u2019s principal payment is paid. To calculate this, select cell E8 and subtract the first period\u2019s principal (D8) from total loan amount (C2).<\/p>\n

      \"Amortization<\/p>\n

      Step 9: <\/strong>Now we have the remaining balance after paying first period\u2019s principal. To calculate the remaining balance after paying second period\u2019s principle in cell E9, we will subtract the second period\u2019s principal (D9) from the remaining balance of E8.<\/p>\n

      \"Amortization<\/p>\n

      Step 10: <\/strong>Now simply just drag the fill-handle of E9 down to E17. In E17 the balance is $0 which means the loan will be paid by the 10th<\/sup> period with interest.<\/p>\n

      \"Amortization<\/p>\n

      Step 11: <\/strong>Now we will create a Loan Summary<\/strong> section to see how much has to be paid with interest.<\/p>\n

      \"Amortization<\/p>\n

      Step 12: <\/strong>Select G2 and use the SUM<\/strong> formula to sum the payments and get total payment.<\/p>\n

      \"Amortization<\/p>\n

      Step 13: <\/strong>Sum the interests to get total interest amount in G3.<\/p>\n

      \"Amortization<\/p>\n

      5. How to Make a Loan Amortization Schedule with Extra Payments in Excel?<\/strong><\/h2>\n

      Step 1: <\/strong>First create an amortization table like this. This table will have three parts- enter value, loan summary and the table. If you want to make this table re-usable, make sure to enter the maximum possible number of payment periods. I will use a lower loan amount to explain the whole process so instead of maximum, I\u2019ll create 30 payment periods.<\/p>\n

      \"Amortization<\/p>\n

      Step 2: <\/strong>If you want you can enter the values like loan amount, interest etc first and then use the formulas or create the table with formulas first then insert the values. Select G9 and link it with C2 (loan amount). So, any amount you enter in the Loan Amount<\/strong> section, will also be updated here for calculation.<\/p>\n

      =C2<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 3: <\/strong>Insert the following formula in G2 to get the Scheduled Payment<\/strong> and press Enter.<\/p>\n

      =IFERROR(-PMT(C3\/C5,C4*C5,C2),””)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 4: <\/strong>Now it\u2019s time to use formulas inside the table to make it usable. Some of the formulas cross-reference which might lead to inaccurate results initially.\u00a0So, keep inserting the formulas in the table until you enter the very last formula in your amortization table. First insert this formula in B10 and drag it downwards till the last cell of your table. This formula will calculate the scheduled payment.<\/p>\n

      =IFERROR(IF($G$2<=G9,$G$2,G9+G9*$C$3\/$C$5),””)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 5: <\/strong>Now use this formula in C10 to calculate extra payment. Make sure to drag it till the end for the whole table to work properly.<\/p>\n

      =IFERROR(IF($C$6<G9-E10,$C$6,G9-E10),””)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 6: <\/strong>This formula will calculate total payments. Insert it in D10, press Enter and drag the cell down.<\/p>\n

      =IFERROR(B10+C10,””)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 7: <\/strong>To calculate principal, this formula has to be inserted in E10.<\/p>\n

      =IFERROR(IF(B10>0,MIN(B10-F10,G9),0),””)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 8: <\/strong>Type of paste this following formula in F10 to get interest amount. Drag the fill-handle downwards.<\/p>\n

      =IFERROR(IF(B10>0,$C$3\/$C$5*G9,0),””)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 9: <\/strong>Finally insert this inside G10 to calculate the balance and drag to downwards. The amortization table is prepared. Now its time to enter the values.<\/p>\n

      =IFERROR(IF(G9 >0,G9-E10-C10,0),””)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 10: <\/strong>Enter the loan amount, interest rate, loan period, payment per year and extra payment values inside the corresponding cells. As soon as you insert the values, the table will return all the data in detail.<\/p>\n

      \"Amortization<\/p>\n

      Step 11: <\/strong>Now let\u2019s calculate the Loan Summary<\/strong>. Multiply Loan Period<\/strong> (C4) with Payment per year<\/strong> (C5) to get Scheduled no of payments<\/strong> in G3.<\/p>\n

      \"Amortization<\/p>\n

      Step 12: <\/strong>Use this COUNTIF<\/strong> formula in G4 to get Actual no of payments.<\/p>\n

      =COUNTIF(D10:D30,”>”&0)<\/strong><\/p>\n

      \"Amortization<\/p>\n

      Step 13:<\/strong> Use the SUM<\/strong> function in G5 to get total extra payments.<\/p>\n

      \"Amortization<\/p>\n

      Step 14: <\/strong>Get total interest amount in G6 by using SUM <\/strong>formula.<\/p>\n

      \"Amortization<\/p>\n

      Step 15: <\/strong>Now if you want you can hide the 0 Period row by right clicking on the row and selecting Hide<\/strong> option.<\/p>\n

      \"Amortization<\/p>\n

      Application of Amortization Schedule in Excel<\/h2>\n