Knowing the repayment table of a mortgage loan, makes you aware of what is being paid for the debt. The concepts that make up each of the quotas during the agreed term are understood. The balance of the debt is informed at all times to make an important decision.
The table is operated systematically and shows the gradual payments made every month. The detail of each of the concepts that compose it, in terms of capital, interests and others.
Components in the amortization table
To make a depreciation table you must have some elements.
- The amount of the mortgage loan. It is granted by the financial institution when it approves a loan.
- The number of payments. It is the term granted on the credit, either in months or years.
- Interest Rate In accordance with the policies of the financial institution, a percentage is applied to the value of capital. It is the utility that the bank obtains for granting the mortgage loan.
Usually an important element like others is added, which could be insurance. This is added month by month within the value of the fee for the agreed term.
Using the PAYMENT function of the Excel application, you can calculate the monthly payment amount with these variables.
Creation of the amortization table
As mentioned above, the amortization table needs the components to perform the different calculations of the monthly payment of the debt. It shows both the value of interest and the value to capital. In this case, the PAGOINT function is used to calculate interest. This uses the same program of the PAYMENT function, but adds another element to indicate the period number. This calculates the amount of interest to be paid for that period.
When calculating the value of the first period, and verifying that the result obtained is correct, the components can be left as the basis of absolute references. In this way, copying the data for more periods will not vary and the complete table would be formulated.
However, if what you want to know is how much you pay monthly to the capital and know your balance, it is calculated with the PAGOPRIN function.
Therefore, it can be observed that several functions must be used to complete the total amount of a monthly payment. So the conclusion is that the three functions are complementary. The sum of PAGOINT and PAGOPRIN, results in the same value of the PAYMENT function.
In the end, you can talk about a balance that is resulting after each monthly payment, this would be an independent value. It is added by means of a formula by means of macros so as not to be copied every time it is required to obtain a balance manually.