We can visualize the impact with a nice chart (requires some extra work) like this:ĭo check the download workbook for details on how the chart is setup. Go ahead and play with the table by typing some values in the “Extra payment” column. Step 3: Your mortgage will end when the “Eff. Closing Balance is opening balance minus principal paid minus extra payment.Ĭomplete this table with necessary formulas and fill everything down.Extra Payment is the input column where we can type any extra payments.We can get this with the PPMT() function. Principal Paid is the amount of principal paid in each month.=ROUND(NPER($E$7/12,$E$10,$D13),0) will tell us how many months it is rounded. We can use NPER function to get the answer here. Effective term is how long it would take you to pay off the mortgage based on the opening balance, and agreed upon monthly payment (calculated in Step 1) and interest rate (Cell E7).For subsequent months, this will same as previous month’s closing balance.
Opening Balance is same as loan amount for month=1.
Related: Read about SEQUENCE and other Dynamic Array functions in Excel. You can use =SEQUENCE(360) to automatically generate all the months. So, set up a range of 360 months (or longer if you want to cater for longer mortgages). In my case, let’s say loan is $500,000, term is 20 years and APR (Interest rate) is 5.35% per annum.Īs extra payment will bring down the outstanding loan term, we need to set up an amortization table to see the impact clearly. There are different types of payroll and Payment Schedules, which is associated and based on an employee’s work schedule.Take a look at some we listed below: One-time payroll. That’s how our loan amortization calculator in excel works.Step 1: Calculate the monthly (or weekly / fortnightly) payment:Īssuming you have the Loan amount, term & APR in three cells E5, E6 & E7, we can use the PMT() function to calculate the periodic payment. With the help of, you can have access to a wide array of payment excel templates, including but not limited to payment schedules for loans, contract payment, lease payment, construction payment, and among others. In that way, you will be able to see if they are attractive or not. Besides that, if the loan amortization schedule does not match your needs you may check other scenarios. Once the schedule is created it is easy to observe your payments (principal and interest) and your Outstanding Loan Balance. Thus, just put your financial data in and let the loan amortization calculator template help you to find the best option for you. To emphasize, the input parts have been clarified with white cells. After you type in the inputs, you may create an amortization schedule with fixed monthly payment and balloon in Excel.įurthermore, you can select your payment frequency from the drop-down, which contains Monthly, Quarterly, Semi-Annually, Annually installment options in this excel loan amortization table. This simple amortization calculator Excel tool will help you to create your repayment schedule plan for the money you borrow for a mortgage, student loan, farm loan or any other credit type.īasically, this Excel amortization schedule template is a tool where you can input the amount of money, interest rate, payment frequency, balloon payment, and loan term according to your situation. If you need an Excel Amortization Schedule Template, this product will provide you with a one-sheet solution.