In an earlier post we had seen the different types of financial products that exist among which there are the Credits these are very useful instruments when you need some money to be able to buy goods with which we do not have enough money.
Usually this type of credits are used for the purchase of houses, cars, credit cards, etc. However the money that comes from these credits comes from banking institutions, department stores or some other type of institution.
On the other hand the money of these institutions comes from investors who expect to have a profit for the money granted and the greater the time to pay back the money borrowed the greater the profits, that is why for this type of credits are created the amortization tables Which distribute to each payment in a set period the amounts of money that will be returned each payment made and a sum called interest that is the expected profit for the money borrowed.
In this post we will show how to create a table of amortization of a credit in a set period, for this we will need the use of Microsoft office Excel, with which without the need to overwhelm us with complex formulas we can create our own amortization table.
For the creation of our amortization table we will use an example with a $ 100,000 value loan with a prepayment of $ 30,000 at a rate of 12% over a 12 month period, plus we will also add a 16% interest tax.
Creation of the amortization table
The amortization table in Excel will be the breakdown of each of the monthly payments to know the exact amount destined both to the payment of interest and to the payment of the principal of our debt. The interest payment calculation will be done with the PAGOINT function of Excel. This function uses four arguments. The first is the interest rate, which we will divide by 12 to have an annualized rate, the second argument is the number of the period from which we want to obtain interest, the third argument is the total period of the loan and the The current value of the loan.
Using our loan example, we will calculate the interest payable in the first period using a formula such as the following:
=PAGOINT(12%/12,1,36, -70,000)
Note that the formula in cell D11 refers to the variables in column C and I have placed them as absolute references because I want these references to remain fixed when copying the formula down. The second argument of the PAGOINT function refers to column D which is precisely where the corresponding payment number is.
On the contrary, to get the amount that is paid month by month to our debt, we must use the PAGOPRIN function of Excel. The syntax of this function will be almost identical to that of the PAGOINT function. Consider the following formula that helps us to obtain the principal payment for the first period:
=PAGOPRIN(12%/12,1,36, -70,000)
In this way we will be able to calculate the amount of our monthly payment that will be destined to the payment of capital of our debt. Similarly, the second argument of the function indicates the period number for which we are doing the calculation. Note the result by including this formula in our table using the previously defined variables:
If we verify we can see that the sum of the payment of interest and payment to capital for all periods gives us the total obtained with the PAYMENT function. In this way we can deduce that these three functions are complementary: The sum of the result of the PAGOINT and PAGOPRIN functions will always be equal to the result of the PAYMENT function.
To finish the amortization table we can add other additional columns, for example the unfinished balance in each of the periods:
The unpaid balance is the amount of the credit less the sum of all payments to capital made so far. The balance is reduced with each payment but is not a constant reduction because at the beginning we paid more interest than at the end but in the last payment we settled the total amount of credit.
As we have seen it is very complex to make our own table however if you have noticed maybe it would be better to create a macro because if we want to do with different deadlines in a post post will show how to make our own macro.
I include a link to download the example of the table in Excel and additional the macro already made.