Contents
Who else, but I think loans are evil. Especially consumer. Business loans are another matter, but for ordinary people, the “money in 15 minutes, all you need is a passport” mousetrap works flawlessly, offering pleasure here and now, and paying for it sometime later. And the main problem, in my opinion, is not even in predatory percentages or in the fact that this “later” will come anyway. Credit kills growth motivation. Why strain, study, develop, look for additional sources of income, if you can stupidly go to the nearest bank and there they will issue you a loan on extortionate terms in half an hour, along the way competently diluting for insurance and other extras?
So I really hope that the material below will not be useful to you.
But if it happens that you or your loved ones have to get into this business, then it would be nice to at least roughly estimate the amount of loan payments, overpayment, terms, etc. before going to the bank. “Massage the numbers” in advance, as I call it 🙂 Microsoft Excel can help a lot in this matter.
Option 1. Simple loan calculator in Excel
For a quick estimate, a loan calculator in Excel can be made in a couple of minutes using just one function and a couple of simple formulas. To calculate the monthly payment on an annuity loan (i.e. a loan where payments are made in equal amounts – most of them are now) in Excel there is a special function PLT (PMT) from category Financial (Financial). Select the cell where you want to get the result, click on the button fx in the formula bar, find the function PLT in the list and click OK. In the next window, you will need to enter the arguments for the calculation:
- Bet – the interest rate on the loan in terms of the repayment period, i.e. for months. If the annual rate is 12%, then one month should account for 1%, respectively.
- Kper – the number of periods, i.e. loan term in months.
- Ps – initial balance, i.e. credit amount.
- Bs – the final balance, i.e. the balance with which we should, in theory, come to the end of the term. Obviously =0, i.e. No one owes nothing to nobody.
- A type – Method of accounting for monthly payments. If equal to 1, then payments are taken into account at the beginning of the month, if equal to 0, then at the end. In Our Country, the vast majority of banks work according to the second option, so we enter 0.
It will also be useful to estimate the total amount of payments and overpayment, i.e. the amount we give to the bank for the temporary use of its money. This can be done with simple formulas:
Option 2. Adding detail
If you want a more detailed calculation, then you can use two more useful Excel financial functions – OSPLT (PPMT) и PRPLT (IPMT). The first of them calculates that part of the next payment, which falls on the payment of the loan itself (the body of the loan), and the second can calculate the part that will fall on the bank’s interest. Let’s add to our previous example a small table header with a detailed calculation and numbers of periods (months):
Function OSPLT (PPMT) in cell B17 is entered by analogy with PLT in the previous example:
Only parameter added Period with the number of the current month (payments) and fixing the $ sign of some links, because later we will copy this formula down. Function PRPLT (IPMT) to calculate the percentage is entered in the same way. It remains to copy the entered formulas down to the last loan period and add columns with simple formulas for calculating the total amount of monthly payments (it is constant and equal to the one calculated above in cell C7) and, for fun, the remaining amount of debt:
To make our calculator more versatile and able to automatically adjust to any loan term, it makes sense to slightly tweak the formulas. In cell A18, it is better to use a formula like:
=IF(A17>=$C$7;””;A17+1)
This formula checks using the IF (IF) function whether we have reached the last period or not, and displays an empty text string (“”) if we have, or the number of the next period. When copying such a formula down a large number of lines, we will get period numbers just up to the desired limit (loan term). In the remaining cells of this row, you can use a similar construction with a check for the presence of a period number:
=IF(A18<>“”; current formula; «»)
Those. if the period number is not empty, then we calculate the payout amount using our HPMT and OSPT formulas. If there is no number, then we output an empty text string:
Option 3. Early repayment with a reduction in the term or payment
The calculator implemented in the previous version is not bad, but it does not take into account one important point: in real life, you will most likely make additional payments for early repayment at a convenient opportunity. To implement this, we can add a column with additional payments to our model, which will reduce the balance. However, most banks in such cases offer a choice: to reduce either the amount of the monthly payment or the term. For clarity, it is better to calculate each such scenario separately.
In case of reduction of the period, you will have to additionally use the function IF check – we have not reached the zero balance ahead of schedule:
And in the event of a decrease in the payment, recalculate the monthly installment starting from the period following the early payment:
Option 4. Loan calculator with irregular payments
There are loan options where the client can pay irregularly, on any arbitrary dates, making any available amounts. The interest rate on such loans is usually higher, but there is more freedom. You can even borrow more money from the bank in addition to your existing loan. To calculate using this model, you will have to calculate interest and balance with an accuracy not up to a month, but up to a day:
It is assumed that:
- in the green cells, the user enters arbitrary payment dates and their amounts
- negative amounts – our payments to the bank, positive – we take an additional loan to the existing one
- it is better to calculate the exact number of days between two dates (and the percentages that fall on them) using the function PLEASE (YEARFRAC)