≡ Menu

Loan Amortization Schedule In Excel

A schedule calculator can be used as a useful financial tool to determine an ideal monthly payment amount for a mortgage loan. This tool is also helpful for allocating amounts of fixed payments towards monthly interest and principal. You can easily create your own custom schedules by using Microsoft Excel.

How To Create A Loan Amortization Schedule In Excel?

These schedules will usually assign a higher value to the interest component during the initial payment period and a higher value towards the principal during the later period. Although there are various free online calculators, you can build your own schedules with an excel program.

While making a large purchase, you should consider analyzing the loans you are taking, to decide if you are getting good interest rates and term for the loans. By planning effective payment schedules, you will be able to essentially ensure that you can meet the required payment on the due date for each pay period. You can follow the below easy steps and you will be able to create these mortgage schedules in MS Excel.


1. Open an Excel spreadsheet on your computer after clicking on the “Start” button on the tool bar. Next, select “All Programs,” and select “Microsoft Office” and click on “Excel program”

2. Click on the box in column “A” of row “2” (A2). Type in the “Start” section and add the start date of the mortgage for which you have created this schedule.

3. Click on the box in column “A” of row “3” (A3). Type in the start date of the first payment schedule for your loan. Continue to fill in all of the rows in column A until you have finished typing in all the dates of the final payments for your mortgage.

4. Click on the space located in B1 and then type in the words “Amount.”

5. Use the mortgage calculators to figure out which of your payment amounts will be used every month. Usually if you have to type out the amount for the mortgage, you should include the length of this mortgage in either months or years. Calculate the yearly interest rates and then click on “Calculate” to get a monthly amount that you will have to pay in order to finish paying your mortgage within the designated time.

6. Click on the space located in B2 and then type out the total amount of your mortgage.

7. Click on the space located in B3 and then type out the monthly amount due as was calculated by your calculator. Select an amount and right-click on it. Finally click on “Copy.”

8. Click on the B4 box and then drag the cursor down to column B until you reach the row containing the dates in column A. Next, release the mouse. After right-clicking anywhere in the selected space in column B, just select “Paste.”

9. Save this file by clicking on “File” and selecting “Save.” Type out the name you wish to use for this file and then click again on “Save.”

These type of schedules can be created as a table that will outline the payment plans for your mortgage loans. These payment plans are determined by using a calculator. You can create schedules in MS Excel spreadsheet programs after ensuring that you are aware how much and when you have to pay these amounts.

How To Use Excel For Loan Amortization?

You can calculate the mortgage or personal loan amount quickly by using an Excel using simple formulas. The calculations are required to assess the monthly amount needed in order to pay the loan. This is vital since most of these payments are made at the start towards paying off the interest. The Excel formula will be able to calculate the payment amounts depending upon the original mortgage amount, annual interest rate, number of payments each year and total years to payoff.

A proper schedule will illustrate the interest, principal and balance associated with every payment period for the mortgage amount. This is often used for repaying home loans. In other words, you can also create your own schedules for the loans by using a spreadsheet program. Majority of the excel spreadsheets already possess a function that can help calculate the payment amounts which is often the most crucial step. Once you have calculated the payments, the rest tends to be a series of repetitive subtraction and multiplication operations that will help ascertain the values associated for each payment.

This is the final amount due for a specific interval of time while calculating the monthly or annual rate of interest. A monthly amortization calculation can be used to ascertain the payment of loans such as a car or a home loan. While calculating these monthly amortizations for a mortgage, you will require a scientific calculator which does not round off the figures.

Benefits Of Using This Type Of A Schedule Calculator!

A significant benefit of this process is that it is used for calculating the payouts of a mortgage over its life span. Every mortgage can be amortized, including personal and auto mortgages. This process involves calculation of monthly payments for the life of this loan period after taking into consideration a principal balance and interest rate.

There are several benefits of learning how to make payment calculators and schedules in Excel. These benefits can be learned by preparing the schedules and include details related to any or all of the following:

– Amount paid towards the principal
– Amount paid towards the interest
– Balance of mortgage once the payments are made
– The total savings after you have made the extra monthly payments
– An amount required to pay off the mortgage early and the effects of mortgage prepayment
– Comparison of various mortgage amounts, time of mortgage and interest rates to determine the best cost effective options.

An amortization payment is a general term that you should be familiar with prior to you making any financial transaction. Essentially, this type of a table will show you how much of the total monthly payment will go towards the principal and what portion of this payment gets applied towards interest.

According to financial advisers, you should use only a maximum of twenty-five percent of the monthly take home salary towards house payment. This percentage includes the principle amount, interest amount, insurance and taxes. In other words, your payment schedules will help you to figure out what amount of house payment you can afford.

{ 0 comments… add one }

Leave a Comment