Toolerz Amortization Calculator is a user-friendly designed calculator that can give a complete breakdown of every monthly payment, showing how much goes toward principal and how much goes toward interest

The amortization calculator gives the monthly payment amount in a fixed-rate mortgage.

Amortization Calculator Formula

The formula for calculating the monthly payment (EMI) in an amortizing loan is:

Where:

EMI is the Equated Monthly Installment,

P is the principal amount or loan amount,

r is the monthly interest rate (annual rate of interest divided by 12 and multiplied by 0.01 to convert to a decimal),

n is the total number of monthly installments (loan tenure in years multiplied by 12).

In the context of amortization, each monthly payment consists of two parts: principal and interest payment. Each month a portion of principle amount and the respective interest amount has been paid.

Interest Payment=Pxr

The portion of the EMI that goes towards the principal is calculated by subtracting the interest payment from the total EMI:

Principal Payment=EMI-InterestPayment

After each monthly payment, the outstanding loan balance is reduced by the principal payment. The process is repeated for each month until the loan amount is paid fully.

The Outstanding Balance is the remaining loan balance after "n" payments.

This formula helps you understand how much of the principal is remaining after a certain number of payments have been made.

What is Amortization?

Amortization is a financial process that involves the gradual repayment of a
loan over a given period. It breaks down the total amount borrowed into
smaller, periodic payments, mostly monthly payments.

Each payment consists of two components: principal & interest.

In each re-payment, there is a deduction of a portion of principal and interest.

In simple terms, for example, you took a loan to buy a house. Amortization ensures that you repay this loan gradually instead of total payment at once.

Each month, a portion of your payment goes towards repaying the loan amount (principal), and the rest covers the interest. Over time, the balance between principal and interest in your payments shifts.

In the early stages, more goes towards interest, but as the loan is paid down, a larger share contributes towards the principal amount.

People who are willing to take loans must understand the amortization process before approaching banks.

It helps them track their progress in repaying the loan and comprehend the long-term cost of borrowing.

Loan Amortization Calculator With Extra Payments in Excel

In this section, we have explained how to create a Loan Amortization Calculator with Extra Payments in Excel Sheet.

Creating a loan amortization calculator with extra payments in Excel involves setting up a table to track each payment, including principal, interest, and the additional payments made in between.

In the below section, we have explained with a simplified example of how you can create a loan amortization calculator with extra payments in an Excel Sheet:

For instance, you have the following input details:

• Loan amount: Rs. 100,000
• Annual rate of interest: 5%
• Loan term: 5 years (60 months)
• Monthly payment: Calculated using the PMT function in Excel.
• Extra payment: Rs. 50 every month.

Set Up Your Table:

Create 6 columns A, B, C, D, E, F

Column A: Payment Number (1, 2, 3, ...)

Column B: Payment Date (Start with the loan start date)

Column C: Loan Amount

Column D: Monthly Interest (Previous Remaining Balance * Monthly Interest Rate)

Column E: Monthly Principal (Monthly Payment - Monthly Interest + Extra Payment)

Column F: Remaining Balance (Previous Remaining Balance - Monthly Principal)

In the first row of Column C, enter the loan amount (Rs. 100,000).

In the first row of Column D, calculate the Monthly Interest using a formula =F2 * 5% / 12 (assuming an annual interest rate of 5%).

Remember to monitor and update your amortization table, especially when you make extra payments, to see how they impact the repayment timeline and total interest paid.

What is an Amortization Schedule?

An amortization schedule is a table/ spreadsheet/Excel sheet that provides a detailed breakdown of each loan payment over the entire term of a loan.

It outlines how each payment is allocated for principal amount and interest, and it shows the remaining balance to be paid after each payment.

The schedule is a helpful tool for borrowers as it offers a clear picture of how the loan is being repaid over the given term.

The below figure gives a clear picture of how the Amortization Schedule looks like.

Key Components of Amortization Schedule

The following are the Key components of an amortization schedule:

Payment Count: Each row in the schedule represents a specific payment, numbered sequentially like 1, 2, 3, till the term ends.

Payment Date: The date on which each payment is due.

Amount Paid: The total amount to be paid for each installment, including both principal and interest.

Principal Repayment: The portion of the payment that goes towards the principal re-payment.

Interest Payment: The amount paid towards the interest is calculated till the date of payment.

Total Payment: The sum of the principal repayment and interest payment for each installment is the total payment.

Remaining Balance: The outstanding loan balance after each payment is nothing but the remaining balance. It decreases over time as you continue to pay the monthly installments.

What is Amortization in a Loan?

Amortization in a loan refers to the process of gradually repaying debt through scheduled or periodic payments over a specified period.

These payments consist of principal and interest amounts.

As we make regular payments, the loan amortization schedule outlines how each payment is allocated between principal and interest.

Common types of loans with amortization schedules include mortgage loans, car loans, and personal loans.

FAQ