Amortization Table Excel Download

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.

Download File 🔥 https://byltly.com/2y4Iik 🔥

The next thing you do is to create an amortization table with the labels (Period, Payment, Interest, Principal, Balance) in A7:E7. In the Period column, enter a series of numbers equal to the total number of payments (1- 24 in this example):With all the known components in place, let's get to the most interesting part - loan amortization formulas.

2. Calculate total payment amount (PMT formula)The payment amount is calculated with the PMT(rate, nper, pv, [fv], [type]) function.

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.

In the Period column, insert the maximum number of payments you are going to allow for any loan, say, from 1 to 360. You can leverage Excel's AutoFill feature to enter a series of numbers faster.

2. Use IF statements in amortization formulasBecause you now have many excessive period numbers, you have to somehow limit the calculations to the actual number of payments for a particular loan. This can be done by wrapping each formula into an IF statement. The logical test of the IF statement checks if the period number in the current row is less than or equal to the total number of payments. If the logical test is TRUE, the corresponding function is calculated; if FALSE, an empty string is returned.

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.

Enter this formula in some cell (G2 in our case) and name that cell ScheduledPayment.3. Set up the amortization tableCreate a loan amortization table with the headers shown in the screenshot below. In the Period column enter a series of numbers beginning with zero (you can hide the Period 0 row later if needed).

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

Note. Because some of the formulas cross reference each other (not circular reference!), they may display wrong results in the process. So, please do not start troubleshooting until you enter the very last formula in your amortization table.

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 inHow to calculate compound interest in ExcelHow to find CAGR (compound annual growth rate) in ExcelCalculating percentage in Excel with formula examplesUsing NPER function in ExcelHow to calculate present value of annuity in ExcelFV function in Excel to calculate future valueExcel: featured articlesCompare two files / worksheetsCombine Excel files into oneMerge Excel tables by matching column data or headersMerge multiple sheets into oneMerge rows without losing dataCreate calendar in Excel

(drop-down and printable)3 ways to remove spaces between wordsCompare 2 columns in Excel for matches and differencesSum and count cells by colorvar b20CategorySlug = "create-loan-amortization-schedule-excel";Table of contents

Hi. Trying to modify this table to account for additional monthly charges.

which adds to the loan amount instead of extra payments which reduces the loan amount. Do I simply add a minus sign to the extra payment figures or is there another way I can do this? Considering the interest rate is on the amount borrowed and not the total repayment amount. Thanks.

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

Is there a way to keep the Annual Interest Rate cell from rounding up? My actual annual interest rate is 19.6% and when I enter it into the cell, the number is automatically rounded up to 20%, which causes the running balance to be slightly off. I am not very experienced with excel and have probably done something incorrectly. I appreciate your help and information.

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.

Loan Payment Schedules: The workbook also contains 2 other worksheets for basic loan payment tracking. The difference between the two has to do with how unpaid interest is handled. In the first, unpaid interest is added to the balance (negative amortization). In the second (the one shown in the screenshot), unpaid interest is accrued in a separate interest balance.

This spreadsheet provides a more advanced way to track actual payments than the Payment Schedule included in the standard Loan Amortization Schedule. It can be used to track missed payments, late payments, early payments, fees, and escrow. It does not handle daily interest accrual like Bonus #2. It is designed to avoid negative amortization.

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

There are two scenarios in which you could end up with negative amortization in this spreadsheet (interest being added to the balance). The first is if your payment isn't enough to cover the interest. The second is if you choose a compound period that is shorter than the payment period (for example, choosing a weekly compound period but making payments monthly).

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