Tutorial Playlist
68 Lessons1. Excel Tutorial
2. Excel Worksheet for Practice
3. Google Sheets Vs. Excel
4. Excel Shortcut Keys
5. Excel Keyboard Shortcuts
6. Excel AutoSum Shortcut
7. Redo Shortcut in Excel
8. Charts in Excel
9. Pivot Charts in Excel
10. Excel Sum Formula
11. Excel Percentage Formula
12. Excel Age Calculation Formula
13. Excel Range Formula
14. Round Off Formula in Excel
15. VLOOKUP Formula in Excel
16. Excel Transpose Formula
17. Average Equation in Excel
18. How to Use DATEDIF Formula in Excel
19. IRR Formula in Excel
20. Standard Deviation Formula
21. Excel Age Calculation Formula
22. Excel MAX Function
23. Excel LEFT Function
24. Excel RIGHT Function
25. Trim Function In Excel
26. LookUp function in Excel
27. Columns in Excel
28. How To Sort Data In Excel
29. How To Freeze Panes in Excel
30. Page Setup in Excel
31. How to Recover an Unsaved Excel File
32. Concatenate in Excel
33. Count In Excel
34. IF Condition In Excel
35. H LOOK UP in Excel
36. How Do You Move Columns in Excel
37. Split Cells in Excel
38. Remove Blank Rows in Excel
39. How To Lock Cells in Excel
40. Data Validation in Excel
41. How to Insert Checkbox in Excel
42. How To Highlight Duplicates in Excel
43. Fill Series in Excel
44. How to Create Excel Drop-Down List
45. What-If Analysis in Excel
46. How to Use SUMIFS Function in Excel
47. INDIRECT Function in Excel
48. Pivot Table in Excel
49. Slicers in Excel
50. How to Create a Dashboard in Excel
51. Excel Data Cleaning
52. Data Analysis In Excel
53. Goal Seek in Excel
54. Solver in Excel
55. Power Query in Excel
56. Macros In Excel
57. How To Make Graph in Excel
58. How To Make Histogram In Excel
59. How To Convert PDF to Excel Without Software
60. Barcode Font For Excel
61. Gantt Chart in Excel
62. Excel RANK function
63. AND Function in Excel
64. How to Calculate NPV
65. Format Painter in Excel
66. Count Colored Cells in Excel
67. Amortization Schedule in Excel
Now Reading
68. Master Cell References in Excel
Have you ever wondered how the monthly loan payment breaks down components like interest, principal and the like?
The answer is simple: Amortization calculator.
This is a specific kind of financial tool gives you a detailed breakdown of loan calculations. Fully understanding loan payment can be tricky. On top of that, financial jargons like ‘interests’, ‘principals’, or even ‘amortization’ can go unexplained, leaving you in doubt about where your earned money is going.
Therefore, based on my knowledge gained from years of using this tool, I have curated this detailed guide on the amortization table in Excel.
In general, the Amortization table and scheduling method is crucial for students with accounting courses. The main concept behind the amortization schedule is a table formation that assists in breaking down the loan payments on a fixed rate.
Car loans and mortgages fall under these loan processes. The loan amortization schedule in Excel helps to get a detailed and clear idea about the loan payment. It shows the exact amount of your payment for interest and the amount that is applied to reduce the principal amount (this is the amount that you borrowed).
As time passes with the consistency of the payments, the principal amount starts to increase, while the amount in the interest section starts to decrease. This decrease in interest shows the decline of loan payment as you continue to pay it.
The detailed breakdown through the Excel loan payment schedule gives you a detailed idea about how much from your total earnings are going towards reducing your debt. The table also indicates the amount is going towards the interest rates.
Amortization is the procedure of paying off a certain amount of debt within a tentaive deadline in regular instalments for both interest and principal. In this case, the loan is supposed to be repaid by a given maturity date.
The loan amortization schedule in Excel helps to represent the principal amount as well as the interest amount comprising all the levels until the loan is paid off at the end of its term. In this procedure, a portion of a high percentage of the monthly payment goes to the interest during the loan process. On the other hand, a maximum amount is added to the loan principal along with the following order of payment.
The amortization schedule is mostly calculated with the help of potential financial calculators, and various spreadsheet packages such as Microsoft Excel. These schedules are customisable based on how much loan is taken by the borrower as well as personal circumstances. Amortization calculators also allow you to advance the process for loan payments.
Amortization formulas are handy for creating a loan amortization table, offering a clear breakdown of interest and principal for easy loan payment calculations in Excel. The key formulas include:
Principal payment = Total Monthly Payment (TMP) - Outstanding Loan Balance (OLB)
To estimate monthly payments or compare loan options based on factors like loan amount and interest rate, use:
Monthly Payment (TMP) = [i * (1 + i)^n] / [(1 + i)^n - 1]
Where:
For instance, if your annual interest rate is 5%, the monthly interest rate would be approximately 0.41%. Similarly, if you have a 12-year loan, you'll make a total of 144 payments. These formulas simplify the process of understanding and calculating loan payments.
Now, let’s learn how to create an amortization chart to understand the procedure better.
Step 1: Setting up the amortization table
To start with the amortization table, first, you need to enter all required components of the loan payment. Such as:
● The annual interest rate will go into C2
● Loan term years will go in C3
● The number of payments per annum will go into C4
● The amount of loan taken will go in C5.
After creating the above chart, now you need to create the amortization table where you need to put a period, payments, interest rate, principal and balance from A7 to E7. After that, enter a series of numbers (from 1 to 39, for this example) which is equal to the total payment number in the period column.
Step 2: Calculation for the total payment amount (PMT function)
For the calculation of the total payment amount, you need to use the PMT function which includes rate, nper, pv, [fv], and [type]. Now to manage different types of payment frequencies appropriately, consistency is the ultimate key. One needs to maintain better consistency while putting values for rate and nper arguments.
● Rate: to obtain the rate, you can divide the annual interest rate by the number of payment periods per annum. The formula is ($C$2/$C$4).
● Nper: to obtain the value of nper, the payment period per annum needs to be multiplied by the number of years. The formula is ($C$3*$C$4).
● pv: for this particular argument, you can enter the loan amount. The syntax for pv is ($C$5).
● As for fv and type, they can be left out as they carry a default value which does not take an active part in the process.
After putting all the arguments, we get the following formula:
=PMT($C$2/$C$4, $C$3*$C$4, $C$5).
This formula will go into the cell B8. please note that you need to put the formula in an absolute cell that is referred to here. Once you put the formula in B8, drag the column down to achieve the same payment amounts for all periods.
Step 3: calculation of interest function
To find out the value of the interest of the loan payment, the IPMT formula is used which is:
=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
Here, the arguments are similar to the arguments included in the PMT function. However, in this case, a relative cell reference, A8 is included as the interest value is supposed to change by the time of periods of loan payment.
Step 4: calculation of principal function (PPMT function)
To continue with the calculation of the principal function, you need to enter the formula: =PPMT(I/Y, Period, Nper, PV, [FV], [Type]) in the principal column in D7.
In this case, all the formulas are the same as IPMT functions. Similar to the calculation of interest rate, you will need to add the period of payments to achieve the principal amount values. So the formula will go like this:
=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
Step 5: calculating the remaining balance value
To find out the remaining balance in the amortization table in Excel, you need to enter two different formulas. Firstly, for the first payment in the E8 cell, the C5 value which is the loan amount and the D8 value, the first period for payment will be added. The formula will be:
=C5+D8
After the first balance, the second balance will be calculated by adding up the previous balance value in E8 with the present principal in D9. This applies for the rest of the period payment until the month 39th (for this example). The formula is:
=E8+D9
So, this is how you can easily calculate the Excel loan repayment schedule by simple formulas to develop the amortization table.
The amortization table in Excel has various useful purposes that make the loan repayment process easier for the borrowers. I’ve listed a few advantages of this Excel feature that proved quite beneficial in my endeavors:
Crafting a loan repayment schedule is easiest via the amortization table in Excel. Now that we have summed up this tutorial, I hope it empowers you to learn about the table to track down your loan progress in future.
You can also find an online free amortization schedule calculator to undertake easier steps for your loan repayment planning. In addition to this, there are several advanced Excel courses you can opt for from UpGrad to master more Excel skills and financial analysis.
You can use the PMT formula in Excel to calculate the payment properly. For both principal and interest payments, you can use the PPMT and IPMT formulas respectively.
The amortization schedule can be created by calculating the interest portion and principal portion for every single payment over the term.
You can use the formula for the PMT function: =PMT(rate/12, nper, pv) for your monthly payment calculations.
The schedule amortization is known as a table that assists you in breaking down the loan into interest and principal that needs to be paid off over the loan terms.
You can calculate the loan amortization by using the PMT function in Excel.
There is no specific formula for the amortization table, thus, you can use the following:
You can list down the periodic payments along with the breakdown of principal and interest values as per the loan term, to prepare the loan amortization schedule.
The formula for calculating loan in excel is: =PMT(rate/12, nper, -pv)
Devesh
Passionate about Transforming Data into Actionable Insights through Analytics, with over 3+ years of experience working in Data Analytics, Data V…Read More
Talk to our experts. We’re available 24/7.
Indian Nationals
1800 210 2020
Foreign Nationals
+918045604032
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enrolling. upGrad does not make any representations regarding the recognition or equivalence of the credits or credentials awarded, unless otherwise expressly stated. Success depends on individual qualifications, experience, and efforts in seeking employment.
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enr...