Personal Loan Calculation: guide, formula and how to calculate in Excel

Related image

Here is a very useful guide in which we will see how to calculate the installment for a personal loan . In particular, we will see how to calculate in Excel the monthly repayment installment and the total amount that we will have to repay, the result of the sum of money received on loan and the amount of interest.

When we are looking for a personal loan, the first thing we have to do is compare the different products available on the market. A decisive factor is that of the required requisites , which we will have to possess in order to have access to a specific product. Among the forms of loan reserved to us, we must however identify the best one for us , based on what our needs are. So we will have to choose whether we prefer to complete the repayment as quickly as possible, or to extend the loan term in order to obtain a lower monthly payment. Many companies offer their customers the possibility of simulating personal loans using the appropriate software available on the website. In case you want to calculate the repayment installment for a loan offered by a company that does not allow us to perform the online simulation, it is possible to do it using a calculator , thanks to the special formula.

To calculate the repayment installment, the data that we will have at our disposal are the amount we wish to request, the number of installments in which we intend to make the repayment and the cadence of the same (for example if monthly, bi-monthly, quarterly, etc.) and finally the annual interest rate (Tan) applied by the bank for the product to which we are interested. With these data at our disposal, it will be enough to use the following formula to immediately know the amount of the repayment installment that we will pay. Attention: in the calculation of the installment the “Tan” constant must be replaced with the interest rate expressed in decimals . This means that for example if the funding provides a 5% Tan, then in the formula you have to write 0.05.

At a glance it might seem too complicated a calculation, however with the scientific calculator or simply using that of your smartphone , it will take a few seconds to get the result you are looking for. For completeness, we present an example in which we consider a loan of 10,000 euros , for which we choose a duration of 5 years . In the event that the bank provides a monthly repayment installments and an annual interest rate of 7.5% , the amount of each installment will be as follows:

If you are interested in a certain amount but you are still undecided about the ideal duration that allows you to easily pay the repayment installments, but without having to pay too much for the interests, then the best thing to do is to make the calculation repeatedly , changing the number of installments. In the event that your bank provides for payment of monthly installments, for example, our advice is to direct you to an installment that does not exceed 20% of your net salary. This is the criterion that also regulates the assignment of the fifth, and is a good threshold for not changing the monthly budget available to us.

A different criterion by which it is possible to identify the product that is right for us is by choosing the reverse route with respect to what has just been done. If before we considered a customer who had clear ideas about what was the amount he needed and wanted to know the repayment installment that would have to pay, this time we start from the installment to know the amount that we can request . Many times, in fact, it may be useful to know the maximum sum to which you can have access. In this way you can in fact adjust for example on the budget at your disposal when buying a car, or assess whether or not to proceed with the renovation of the house based on the estimate that was presented to us by the company to which we turned . In all these cases, we therefore have an idea of how much we can spend each month to repay the loan, and we must choose the duration of the loan to know how much money we can actually receive through the personal loan offered by our bank. The formula for the calculation of the amount that can be requested is easily obtained from the one previously seen, dividing the installment we want to fix for everything that first multiplied the amount we were going to request.

Also in this case it is useful to see with a practical example how the formula above can be used. For the choice of the monthly payment , we use the criterion previously reported, thus considering 20% ​​of the net salary received. We consider a customer who receives a monthly salary of 1,200 euros . To calculate the installment that will be useful for our calculations, it will be enough to divide this amount by 5 (or, equivalently, multiply by 0.20). What we get is that the maximum installment that can be faced each month by our customer will be equal to 240 euros . If we want to know how much the customer can request by choosing a refund in 10 years , considering an annual interest rate of 5% , the calculation will be as follows:

As you can easily imagine, the greater the number of installments that we will pay and the greater the total amount that we will be able to receive on loan. Also in this case it is very useful to make several attempts that allow us to identify the characteristics of our ideal loan . In addition to the duration of the loan, the second factor that must be taken into consideration is the annual interest rate expected by the company to which we address. Even in this case it is easy to imagine how important a lower interest rate is. Taking a look at the formula, in fact, it is clear the contribution of the interest rate considered (0.05) in determining the amount that can be requested by the customer. The recommendations that must be made are therefore two: the first is to try different times between those provided by the bank; the second is instead to evaluate different financing options among those available on the market, to identify the one that most satisfies us.

How to calculate the installment of the Personal Loan in Excel

We have seen what are the mathematical formulas that allow us to calculate the repayment installment starting from the amount we want to request and the amount that can be requested starting from the installment we want to pay each month. A program that can help us for our purposes is Microsoft Excel , from the Microsoft Office package. If you do not have the license to use this software, you can still download the LibreOffice package on your computer, which offers virtually the same functionality, but does not include any cost for the download or for use. For those who do not know it, Excel is a spreadsheet, which allows us to perform all the operations we want in a simple and intuitive way . This greatly simplifies the life for the calculation of the installment and the amount that we can request, also thanks to the functions that are made available on the software.

Regarding the calculation of the repayment installment that we will have to face, the function that we will have to use takes the name of RATA . The parameters that we will insert in brackets are three, and will be divided by “;”. The first parameter is the so-called ” Period rate “, which is equivalent to the division between the annual interest rate and the number of installments that we will pay each year, which depends on the regulation of the bank to which we rely. The second parameter that we will have to insert is the total number of installments that we will pay, obviously equal to the number of annual installments multiplied by the number of years in which we intend to complete the reimbursement. The third and last parameter that will be required is the amount we want to receive on loan . This last value must be entered as a negative sign , as it represents our initial debt.

To use the function described above, all we have to do is write in a cell ” = RATA (Tan / RateAnnue; RateAnnue * NumAnni; – Amount) “. If we want to make an immediate comparison between different possible solutions in a simple and fast way, the best thing to do is to write the individual parameters that we need in separate cells . In fact, a function of our spreadsheet is to perform operations using the values ​​present in the other cells. If, for example, in cell A1, we enter the annual interest rate, in cell A2 the number of annual installments, in cell A3 the number of years in which we make the reimbursement and in cell A4 the amount we are going to request, the function previously reported will become ” = RATA (A1 / A2; A2 * A3; -A4) “. In this way we will have the advantage of being able to compare two different solutions simply by modifying one of the four cells containing the parameters of the loan.

As seen above, it can also be very useful to calculate the amount that can be requested starting from the installment that we consider ideal based on the net salary we receive on a monthly basis. The function that helps us in this case is ” VA “. The parameters to be inserted are also three times, and are respectively the period rate, the number of installments that we intend to pay each year and the amount of each installment, which must be entered as a negative sign. Choosing to insert in the cell A1 the Tan, in cell A2 the number of annual fees, in cell A3 the number of years necessary for the reimbursement and in cell A4 the monthly payment, the formula that will allow us to calculate the amount that we can request will be ” = VA (A1 / A2; A2 * A3; -A4) “. Using these simple functions we will have at our disposal a loan simulator that will allow us to compare different financing options in a few simple clicks.