__LINK__ Download Amortization Schedule Calculator Excel
Joyce Langwell
An amortization schedule is a list of payments for a mortgage or loan, which shows how each payment is applied to both the principal amount and the interest. The schedule shows the remaining balance still owed after each payment is made, so you know how much you have left to pay. To create an amortization schedule using Excel, you can use our free amortization calculator which is able to handle the type of rounding required of an official payment schedule. You can use the free loan amortization schedule for mortgages, auto loans, consumer loans, and business loans. If you are a small private lender, you can download the commercial version and use it to create a repayment schedule to give to the borrower.
Usually, the interest rate that you enter into an amortization calculator is the nominal annual rate. However, when creating an amortization schedule, it is the interest rate per period that you use in the calculations, labeled rate per period in the above spreadsheet.
Basic amortization calculators usually assume that the payment frequency matches the compounding period. In that case, the rate per period is simply the nominal annual interest rate divided by the number of periods per year. When the compound period and payment period are different (as in Canadian mortgages), a more general formula is needed (see my amortization calculation article).
A loan payment schedule usually shows all payments and interest rounded to the nearest cent. That is because the schedule is meant to show you the actual payments. Amortization calculations are much easier if you don't round. Many loan and amortization calculators, especially those used for academic or illustrative purposes, do not do any rounding. This spreadsheet rounds the monthly payment and the interest payment to the nearest cent, but it also includes an option to turn off the rounding (so that you can quickly compare the calculations to other calculators).
When an amortization schedule includes rounding, the last payment usually has to be changed to make up the difference and bring the balance to zero. This might be done by changing the Payment Amount or by changing the Interest Amount. Changing the Payment Amount makes more sense to me, and is the approach I use in my spreadsheets. So, depending on how your lender decides to handle the rounding, you may see slight differences between this spreadsheet, your specific payment schedule, or an online loan amortization calculator.
Believe it or not, a loan amortization spreadsheet was the very first Excel template I downloaded from the internet. Since then, I've discovered the great boost in productivity that can come from not having to start from scratch, and hopefully this page will help you get a head start. This page lists the best places to find an Excel amortization spreadsheet for creating your own amortization table or schedule.
If you want a spreadsheet for creating an amortization table for a loan or mortgage, try one of the calculators listed below. There are some of my most powerful and flexible templates. A feature that makes most of the Vertex42 amortization calculators more flexible and useful than most online calculators is the ability to include optional extra payments. And of course with a spreadsheet, you can save your results.
Creates an amortization table for BOTH fixed-rate and adjustable rate mortgages. This one is by far the most feature-packed of all my amortization calculators. It has has been refined and improved over years of use and feedback received from both professionals and every-day home buyers.
This may seem similar to the regular loan amortization schedule, but it is actually very different. This spreadsheet is for creating an amortization table for a so-called "simple interest loan" in which interest accrues daily instead of monthly, bi-weekly, etc.
My article "Amortization Calculation" explains the basics of how loan amortization works and how an amortization table or "schedule" is created. You can delve deep into the formulas used in my Loan Amortization Schedule template listed above, but you may get lost, because that template has a lot of features and the formulas can be complicated.
You can also find a free excel loan amortization spreadsheet by doing a search in Excel after going to File > New. Some of them use creative Excel formulas for making the amortization table and a couple allow you to manipulate the schedule by including extra payments. The new online Microsoft template gallery doesn't have as many loan-related templates as the old gallery, but you can still find a few in the Financial Management category.
Basically, all loans are amortizing in one way or another. For example, a fully amortizing loan for 24 months will have 24 equal monthly payments. Each payment applies some amount towards principal and some towards interest. To detail each payment on a loan, you can build a loan amortization schedule.
An amortization schedule is a table that lists periodic payments on a loan or mortgage over time, breaks down each payment into principal and interest, and shows the remaining balance after each payment.
That's it! Our monthly loan amortization schedule is done:Tip: Return payments as positive numbersBecause a loan is paid out of your bank account, Excel functions return the payment, interest and principal as negative numbers. By default, these values are highlighted in red and enclosed in parentheses as you can see in the image above.
For the Balance formulas, use subtraction instead of addition like shown in the screenshot below:Amortization schedule for a variable number of periodsIn the above example, we built a loan amortization schedule for the predefined number of payment periods. This quick one-time solution works well for a specific loan or mortgage.
As the result, you have a correctly calculated amortization schedule and a bunch of empty rows with the period numbers after the loan is paid off.3. Hide extra periods numbersIf you can live with a bunch of superfluous period numbers displayed after the last payment, you can consider the work done and skip this step. If you strive for perfection, then hide all unused periods by making a conditional formatting rule that sets the font color to white for any rows after the last payment is made.
That's it! Our loan amortization schedule is completed and good to go!Download loan amortization schedule for Excel
How to make a loan amortization schedule with extra payments in ExcelThe amortization schedules discussed in the previous examples are easy to create and follow (hopefully :). However, they leave out a useful feature that many loan payers are interested in - additional payments to pay off a loan faster. In this example, we will look at how to create a loan amortization schedule with extra payments.
=LoanAmount 4. Build formulas for amortization schedule with extra paymentsThis is a key part of our work. Because Excel's built-in functions do not provide for additional payments, we will have to do all the math on our own.
If all done correctly, your loan amortization schedule at this point should look something like this:5. Hide extra periodsSet up a conditional formatting rule to hide the values in unused periods as explained in this tip. The difference is that this time we apply the white font color to the rows in which Total Payment (column D) and Balance (column G) are equal to zero or empty:
Optionally, hide the Period 0 row, and your loan amortization schedule with additional payments is done! The screenshot below shows the final result:Download loan amortization schedule with extra payments
Amortization schedule Excel templateTo make a top-notch loan amortization schedule in no time, make use of Excel's inbuilt templates. Just go to File > New, type "amortization schedule" in the search box and pick the template you like, for example, this one with extra payments:Then save the newly created workbook as an Excel template and reuse whenever you want.
That's how you create a loan or mortgage amortization schedule in Excel. I thank you for reading and hope to see you on our blog next week!
Amortization Schedule examples (.xlsx file)
You may also be interested in
- How to calculate compound interest in Excel
- How to find CAGR (compound annual growth rate) in Excel
- Calculating percentage in Excel with formula examples
- Using NPER function in Excel
- How to calculate present value of annuity in Excel
- FV function in Excel to calculate future value
- Compare two files / worksheets
- Combine Excel files into one
- Merge Excel tables by matching column data or headers
- Merge multiple sheets into one
- Merge rows without losing data
- Create calendar in Excel
(drop-down and printable) - 3 ways to remove spaces between words
- Compare 2 columns in Excel for matches and differences
- Sum and count cells by color
Can it be possible client wise auto update loan amortization table?
Also if possible interest rate change so auto update automatic in excel
Extra Payments means (Start at Payment No,Extra Payment,Payment Interval,Extra Annual Payment,Payment,Total Extra Payments) Additional Payment ,Variable or Fixed Rate ,Impact of interest rate HIKE on your loan EMI & repayment schedule & Impact of interest rate CUT on your loan EMI & repayment schedule ? how to create in excel & Suppose provide only interest
Loan Amortization Schedule Excel is a loan calculator that outputs an amortization schedule in excel spreadsheet. The loan amortization schedule excel has all the monthly payments for your loan with breakdown for interest, principle and remaining balance.
9738318194