In this page we will see how to calculate the depreciation plan of your personal loan in a few simple steps. How to do it, with the calculation and simulation carried out using Excel in order to know immediately the monthly repayment installments that we will pay according to the amount we need and the duration.
Personal Loan Depreciation Plan: why calculate it
If you are interested in applying for a personal loan and want to find the solution that best meets your needs, then what you need to do is to calculate an amortization plan. The less experienced may not know exactly what we are talking about: the amortization plan is simply the way in which you decide to complete the repayment of the sum of money received on loan. In other words, therefore, it is simply the definition of the duration of the loan in terms of years in which to make the repayment, and the number of installments that will have to be paid each year (if they will be, for example, monthly, quarterly or half-yearly installments). When we go to request a personal loan, the number of installments to be paid each month is defined by the regulation of loans offered by the bank to which we have applied. As for personal loans, however, usually the installments are monthly. So from this point of view the customer does not have the possibility, if not in some rare cases, to choose how many installments to pay each year.
What can be chosen by the borrower is the duration of the loan, and therefore the number of years in which to complete the repayment. From this point of view it is good to consider how the economic treatment envisaged by our loan changes according to the duration we choose. Choosing a longer duration obviously means that we will go to defer our repayment in a higher number of installments, and as a result each installment will be of a lower amount. So if you want to complete the reimbursement in total serenity without fixing “heavy” installments, the loan that is right for you will provide an amortization plan with a rather long duration. Going to improve one aspect of our loan, however, there is one that worsens, and that is convenience. In addition to the chosen duration, what determines the total cost of our financing is the annual interest rate applied by the company. Choosing a longer duration, considering that we are talking about an annual rate, the interests we are going to pay will be altogether higher. This means that if instead the priority for your loan is to be convenient from the interest point of view, then you will have to orient yourself towards an amortization plan with a shorter duration.
On the basis of what has just been said, it is clear that it is not possible to define a personal loan that is better than the others. The choice of the ideal amortization plan will depend on what our needs are. This is why, before applying for a personal loan, it is very important to calculate an amortization plan based on the economic treatment offered by our bank. To date, there are many banks and financial companies on the financing market and therefore it is not easy to identify at a glance the product that best meets our needs. The calculation of the repayment plan allows us to make a comparison between the repayment installments and the expected interest so as to identify the best personal loan for us. Through the website of the main companies we can use the online loan simulator, which allows us to calculate a quote in a few simple clicks, simply specifying the amount we are interested in and the duration we prefer. However, this online service is not available on the websites of all companies, so the best solution is the calculation of the amortization schedule in Excel, which allows us to compare any personal loan that we can find online. How to do? It’s very easy! In the next few paragraphs we will explain step by step everything you need to do, and we assure you that no particular IT skills are required.
How to make an Amortization Plan: installment calculation formula
To create a “do-it-yourself” amortization plan, the first thing to do is to know how to calculate the monthly repayment installment for a specific personal loan. To do this it is necessary to know the formula for the calculation of the installment. Once this aspect is understood then the calculation of the repayment plan will be immediate starting from the information related to a particular personal loan. In particular what information do we need to be able to calculate our repayment rate? As we have already said, the data that we need to make the calculation are those that precisely identify our depreciation plan. First of all to be decisive is obviously the capital received on loan, and therefore the amount that we are going to request from our bank. What we can choose at will is the duration of the loan, which is also decisive for the calculation of the repayment installment that we will have to pay. On the other hand, according to the personal loans regulation envisaged by the credit institution to which we address, we will know the number of installments that we will have to pay each year. Usually this will be equal to 12, given that the rate of personal loans have monthly except in rare cases.
Once we have gathered all this information on the personal loan we want to receive and for which we want to calculate the repayment plan, the mathematical formula to be applied to know immediately the installment we will have to pay is as follows:
Thanks to this formula, therefore, a simple scientific calculator will be enough to know immediately the expected repayment rate. The calculation of the installment and more generally of the repayment plan of a loan is essential for two reasons. The first is the identification of the credit institution that offers the personal loan to which we are interested in the best conditions. As we have already said, there is no better loan at all, but it depends precisely on what our needs are. For this reason, thanks to the formula above we can compare the personal loan offered by two different companies. At the same conditions of the loan, from the monthly repayment installment we can immediately identify the product that suits us. It is clear that if you set the same amount and the same duration for a company I get a lower monthly rate compared to that calculated for a different company then I will no doubt address the first.
The second reason why it is very useful to calculate the installment using the formula above is to identify the ideal duration of the personal loan. In fact, if I have already decided which credit institution to contact to receive the amount of money I need, I could still be undecided about the conditions of the loan to be requested. In this case I can set all the conditions of the loan offered by the company to which I want to address, leaving as a variable to determine precisely the number of years of the amortization plan. So based on the monthly repayment rate we prefer we can identify the ideal duration for us. From this point of view we should always consider the usual two aspects related to the duration of any financing, which are to have light installments and to keep interest low. Going to improve one aspect will necessarily have to worsen the other, so to have light installments you will have to pay a little ‘more for the interest, while wanting to save on interest will necessarily have to set a higher repayment installment.
To see in practice how to calculate the monthly repayment installment for our depreciation plan, let’s see an example. To do this we consider the case of a customer interested in buying a new car for which he needs a personal loan of 10,000 euros. The loan for which the repayment installment is to be calculated has an annual interest rate of 7.5% (which, as can be seen below, must be included in the formula as 0.075). The rate of installments is monthly, which means that the number of annual installments is equal to 12. Finally, the duration for which the amortization plan is calculated is 5 years. In this example, the monthly repayment installment amounts to € 200.38.
The monthly amount to pay is definitely within reach for a loan of 10,000 euros. In case the result of this calculation is not what you wanted, then you could choose a higher or lower duration depending on whether you want to set a lower or higher installment. In general, however, we advise you to have in mind a maximum rate that you consider within your reach based on your income. The best loan is the one that comes close to this amount, as it is the most convenient loan available to you. In the following article we will see however how it is possible to calculate the amortization plan even without knowing the formula we have just seen, simply by using Microsoft Excel or similar programs.
Calculation and simulation in Excel of the Personal Loan Amortization Plan
After seeing through which formula you can calculate the repayment installment, let’s see how you can make the amortization plan of the personal loan that you want to request using Excel. First of all, it is good to remember that once we know the formula we can create our amortization plan spreadsheet from scratch. For those who know how to use Excel, in fact, it is very simple, since all the information on the financing and therefore the amount, the duration, the number of annual installments and the interest rate applied by the bank can be entered in separate cells. At this point, by writing in another cell the formula given above and specifying the cells in which the data necessary for the calculation are present, it will be possible to know immediately the expected repayment rate. If you want to have a detailed amortization plan in front of you that is similar to the estimate that can be presented by a credit institution, you can use a model that can be downloaded directly from the Microsoft Office website. Here we find in fact different models, or ” templates “, addressed to the end users of Excel and that greatly simplify our life.
In fact, for the less experienced it could be complicated to create a spreadsheet for the amortization plan of a personal loan. The solution to our problems is precisely the model that takes the name of ” Loan amortization plan “. Just download it for free to use it to calculate all the estimates we want. All we have to do is change the parameters of the loan by inserting those of the loan we are interested in and the change in results will be instantaneous. Although the use of the Office template is extremely simple and intuitive, let’s analyze all the information that will be reported in the cells of your spreadsheet. In the upper left part of the screen for calculating the depreciation plan there are the values that must be entered to calculate the estimate. The information that we will have to specify will always be the same and include the amount of the loan, the annual interest rate, the duration of the loan to be specified in years, the number of payments per year (annual installments) and the start date of the loan. You can also specify any additional payments, which as specified on the sheet itself are optional.
Based on the data we have entered, the entire amortization plan will be calculated. We will be presented separately all the repayment installments that we will have to pay, with the date by which we will have to make the payment. Furthermore, the part that will repay the capital provided by the bank and the part that will have to be paid for the interest can be seen for each installment. For each payment you can then see the initial balance and the final balance, as the difference between final balance and principal repaid. In the upper right part of the spreadsheet in Excel you can see the summary of the loan. Here the “planned payment” will represent the repayment installment that we will have to pay, the number of planned and actual payments (these two numbers are the same if no additional optional payments are specified). Finally, in the summary we will find the total amount that we will have to pay for the repayment and the amount related to the interest, as the difference between the principal repaid and the principal paid out. By specifying the name of the lender it will be possible to save the estimate and then create a copy of the Excel document to be modified with the data of the personal loan offered by another bank. In this way it will be possible to compare the two depreciation plans to choose the credit institution to which we can rely.