What Is an Amortization Schedule and How to Calculate It
When taking a large loan from a bank, you’ll often be required to deal with a so-called amortization schedule. Don’t know what it is, exactly? In this article, we’ll explain the definition of amortization, how to calculate it, and how to construct your own amortization schedule.
Amortization Meaning in Accounting
In accounting, amortization is a method by which the stated value of an intangible asset or a loan is progressively decreased over a defined period. A borrower pays an equal portion of money across a set period instead of dealing with changing interest rates.
Amortization of intangible assets, such as copyrights, trademarks, or patents, is the distribution of the price paid over the useful life of the asset in question. That guarantees that accounting records accurately reflect the declining value of the asset.
Amortization of Loans
Loan amortization refers to the repayment of a loan in equal, periodic installments over a specified time. Every payment includes two components. The first is the principal, which is the amount borrowed initially. The second is interest, which is the borrowing fee.
Initially, a larger percentage of the payment is allocated to interest, while later, the majority of payments will be directed towards the principal. This goes on until the borrower repays the loan completely. Amortization simplifies debt management through forecasted, consistent payments.
Amortization of Loans Formula
So, how can you calculate amortization? Here’s the formula for you:
A stands for monthly payment
P stands for principal, or how much you borrowed initially
r stands for the monthly interest rate
n stands for the total number of payments
Looks scary, no doubt. But if you do your calculations carefully and step by step, the formula won’t cause you any problems.
- Find out the loan data: Determine the loan period in months (n), the annual interest rate (r), and the principal (P).
- Find the monthly interest rate: divide your annual interest rate by 12.
- Determine the overall number of installments (n): Multiply the loan term in years by 12.
- Fill in the formula with the values: to find A, enter P, r, and n into the formula.
How to Calculate Amortization in Excel
There’s specific tools for automatically calculating amortization, meaning less trouble for you! But if you need to get a better picture of what’s going on, it’s easier to do the calculation in a spreadsheet. As a bonus, you’ll get a better understanding of how the formula works.
Here’s what the Excel amortization formula looks like:
Where:
rate – Annual interest rate ÷ 12 (to get the monthly rate).
nper – Total number of payments (loan term in years × 12).
pv – Loan amount (principal).
Fv (extra argument) – The foreseeable value you hope to achieve after the final payment. You can skip it, and it’ll be assumed as 0.
Type (extra argument) – Identifies when payments are due. (“0” – at the end of the period, “1” – at the start of the period).
How to Prepare the Amortization Schedule?
An amortization schedule is essentially a plan for debt repayment. It includes every installment for the entire loan period. You can see the distribution of each payment, detailing the amount that covers interest and the portion that goes towards principal reduction.
The plan shows when each monthly payment is due, how much you need to pay, the split between interest and the main amount, and how much is left to pay after each installment. It provides borrowers with a clear understanding of how far they’ve come in settling their debts and the time required to fully repay the loan.
To create a table, you’ll need five columns: payment number, payment amount, interest, principal, and remaining loan balance.
This is how it works:
- Calculate the fixed recurring payment with the amortization formula.
- Next, calculate the amount of interest for each installment period by multiplying the remaining principal by the monthly interest rate.
- Remove the interest from the overall payment to determine the principal portion.
- To update the amount owed, subtract the principal from the balance of the loan left.
- Repeat the previous steps until the loan is paid off in full.
So, if you have a $100,000 loan with a 30-year term and a 6% annual interest rate, the start of your table will look like this:
Payment # | Payment | Interest | Principal | Remaining Balance |
|---|---|---|---|---|
1 | $599.55 | $500.00 | $99.55 | $99,900.45 |
2 | $599.55 | $499.50 | $100.05 | $99,800.40 |
3 | $599.55 | $499.00 | $100.55 | $99,699.85 |
And this is how it will look in the final two months of the final year of repayment:
Payment # | Payment | Interest | Principal | Remaining Balance |
|---|---|---|---|---|
1 | $599.55 | $5.98 | $593.57 | $601.64 |
2 | $599.55 | $3.01 | $596.54 | $5.10 (leftover) |
Pros and Cons of Amortization
Amortization implies that repayments are made in regular, fixed installments.Naturally, this means some pros and cons.
Pros:
- Controllable Payments: Knowing the whole payment structure in advance allows for better loan management.
- Equity Creation: Over time, the debtor’s equity in the asset grows as a portion of each payment is used to lower the debt.
- Lower Interest Costs: You pay less interest as the principal balance drops throughout the term of the loan.
- Tax Savings: You may get extra tax benefits if the loan type is appropriate.
Cons:
- Possible Higher Interest Costs: If you make only minimum payments and have a long loan term, you may pay more interest.
- Long-Term Obligation: You’ll have to repay the loan for years, which can become untenable if your financial situation changes.
- Risk of Negative Equity: The debtor may owe more than the asset is worth if its value drops.
Practical Example of Amortization
Imagine you want a new car. It costs $50,000, so you apply for a five-year loan with a 7% interest rate.
For a 7% annual rate, the monthly interest rate will be 0.00583 (0.07 divided by 12). Then, to determine the total sum of monthly payments, you multiply the loan period in years by 12. For a 5-year loan, it’s 60 months.
Then you use the amortization formula for calculations:
A = 50,000×(1+0.0058333)60 − 10.0058333×(1+0.0058333)60
If you’ve done the math right, you’ll end up with a monthly payment of $990.06. The total sum would be $59,404 ($990.06 × 60 ≈ $59,404), meaning you’d pay $9,403.60 in interest.
Why Is Amortization Important in Accounting?
So, why use this method over other loan management strategies? Can you save money with it? Here are some benefits to consider:
- Expense Adjustment: Distributes asset costs steadily and brings expenditures and revenues into line.
- Asset Valuation: Great at tracking the value of intangible assets.
- Tax Deduction: Allows borrowers to reduce their taxable income.
- Improved Financial Planning: Motivates budgeting and efficient resource utilization.
Overall, the main benefit of amortization is that you’ll get a clear picture of your finances and would be able to make smarter decisions.
Conclusion
Now you know how to calculate amortization and what it is used for! If you make payments consistently, amortization can help you manage finances with a predictable repayment structure. Besides, you can build equity and sometimes even save on taxes by opting for this method. But your long-term savings depend on loan terms, interest rates, and extra payments. A final bit of advice: always check if your bank will charge a penalty for making early payments and plan your schedule accordingly.
