Creating a calculator in Microsoft Excel. Loan calculator in Excel and formulas for monthly payments

The first loan calculator in Excel can be downloaded by.
However, Excel is not available on all computers. Mac and Linux users do not usually use Excel because it is a Microsoft product.
You can also use the prepayment calculator to calculate early repayment. It provides the ability to export calculation results to Excel.

Based on this calculator, a mortgage calculator for Android and iPhone was developed. You can find and download mobile versions of calculators from.

Advantages of this calculator:

  1. Loan calculator in Excel almost accurately calculates annuity payment schedule and differential payment schedule
  2. Changes in the payment schedule - accounting for early repayments in the reduction of the principal amount
  3. Construction and calculation of a payment schedule in the form of a table in Excel. The payment schedule table can also be edited
  4. The calculation takes into account leap and non-leap years. Due to this, the amount of accrued interest practically coincides with the values \u200b\u200bcalculated by VTB24 and Sberbank
  5. Accuracy of calculations - the calculations coincide with the calculations of the VTB24 and Sberbank loan calculators
  6. The calculator can be edited for yourself by setting different calculation options.

Disadvantages of a calculator

  1. Possible change in interest rate during loan payments is not taken into account
  2. If you make a calculation by making early payments to change the term and amount, then the calculation will be incorrect
  3. If the amount of interest accrued for the period is more than the amount of the annuity payment, then the calculation will not be correct
  4. The option is not calculated - the first payment is only interest. In the case when the date of issue does not coincide with the date of the first payment, you will need to pay interest to the bank for the period between the date of issue and the date of the first payment.
  5. The calculation is made for an interest rate with 2 decimal places.

It is devoid of all the above disadvantages. In general, the shortcomings are not very critical and they are inherent in any online loan calculator.
Other loan calculator in Excel can be downloaded by. This loan calculator does not allow you to calculate early repayment. However, its plus is that it calculates a loan with several interest periods. If the amount of interest on a loan for a given month is more than the amount of the annuity payment, then the graph for the first loan calculator in excel is not built correctly. In the graph, negative amounts are obtained.

Try to calculate, for example, a loan of 1 million rubles at 90 percent for a period of 30 years.
The second calculator does not have this drawback. However, he divides the loan into 2 periods, i.e. it is possible that after division in the graph there will again be negative values. Then the payment schedule must be divided into 3 or more periods.
Naturally, the file itself can also be edited to suit your needs.

Excel is a versatile analytical and computing tool that is often used by lenders (banks, investors, etc.) and borrowers (entrepreneurs, companies, individuals, etc.).

The functions of Microsoft Excel allow you to quickly navigate the tricky formulas, calculate interest, payments, and overpayments.

How to calculate loan payments in Excel

Monthly payments depend on the loan repayment scheme. Distinguish between annuity and differentiated payments:

  1. An annuity assumes that the client pays the same amount every month.
  2. With a differentiated scheme of debt repayment to a financial institution, interest is charged on the remaining loan amount. Therefore, monthly payments will decrease.

Annuity is used more often: it is more profitable for the bank and more convenient for most clients.

Calculation of annuity loan payments in Excel

The monthly amount of the annuity payment is calculated using the formula:

A \u003d K * S

  • A - the amount of the loan payment;
  • K - coefficient of annuity payment;
  • S is the loan amount.

Annuity coefficient formula:

K \u003d (i * (1 + i) ^ n) / ((1 + i) ^ n-1)

  • where i is the monthly interest rate, the result of dividing the annual rate by 12;
  • n - loan term in months.

Excel has a special function that counts annuity payments. This is PMT:

The cells turned red, and a minus sign appeared in front of the numbers. we will give this money to the bank, lose it.



Calculation of payments in Excel according to a differentiated repayment scheme

A differentiated payment method assumes that:

  • the principal amount is distributed over the periods of payment in equal installments;
  • interest on the loan is charged on the balance.

Formula for calculating differentiated payment:

DP \u003d NEO / (PP + NEO * PS)

  • DP - monthly loan payment;
  • OSZ - loan balance;
  • PP is the number of periods remaining until the end of the maturity period;
  • PS is the monthly interest rate (we divide the annual rate by 12).

Let's draw up a schedule of repayment of the previous loan according to a differentiated scheme.

The input data is the same:

Let's draw up a loan repayment schedule:


Loan debt balance: in the first month is equal to the total amount: \u003d $ B $ 2. In the second and subsequent ones - it is calculated by the formula: \u003d IF (D10\u003e $ B $ 4; 0; E9-G9). Where D10 is the number of the current period, B4 is the loan term; E9 - the loan balance in the previous period; G9 - the amount of the principal debt in the previous period.

Interest payment: the loan balance in the current period multiplied by the monthly interest rate, which is divided by 12 months: \u003d E9 * ($ B $ 3/12).

Payment of the principal debt: the total loan amount divided by the term: \u003d IF (D9

Final payment: the sum of "interest" and "principal" in the current period: \u003d F8 + G8.

Let's enter the formulas in the corresponding columns. Let's copy them to the whole table.


Let's compare the overpayment with an annuity and a differentiated loan repayment scheme:

Red number - annuity (they took 100,000 rubles), black - a differentiated method.

The formula for calculating interest on a loan in Excel

Let's calculate the interest on the loan in Excel and calculate the effective interest rate, having the following information on the loan offered by the bank:

Let's calculate the monthly interest rate and loan payments:

Let's fill in a table like this:


The commission is taken monthly from the entire amount. The total loan payment is the annuity payment plus commission. The principal amount and the amount of interest are part of the annuity payment.

Principal amount \u003d annuity payment - interest.

Interest Amount \u003d Remaining Debt * Monthly Interest Rate.

The balance of the principal debt \u003d the balance of the previous period - the amount of the principal debt in the previous period.

Based on the table of monthly payments, we calculate the effective interest rate:

  • took out a loan of 500,000 rubles;
  • returned to the bank - 684,881.67 rubles. (the sum of all payments on the loan);
  • overpayment amounted to 184,881.67 rubles;
  • interest rate - 184,881, 67 / 500,000 * 100, or 37%.
  • The harmless 1% commission cost the borrower very dearly.

The effective interest rate for the loan without commission is 13%. The calculation is carried out in the same way.

Calculating the full cost of a loan in Excel

According to the Consumer Credit Law, a new formula is now applied for calculating the Total Cost of Credit (CLV). UCS is determined as a percentage with an accuracy of three decimal places using the following formula:

  • UCS \u003d i * BWP * 100;
  • where i is the interest rate of the base period;
  • BWP is the number of base periods in a calendar year.

Take, for example, the following loan data:

To calculate the full cost of the loan, you need to draw up a payment schedule (see procedure above).


It is necessary to determine the base period (BP). The law states that this is the standard time frame that occurs most often on the repayment schedule. In the example, BP \u003d 28 days.

Now you can find the interest rate of the base period:

We have all the necessary data - we substitute them in the UCS formula: \u003d B9 * B8

Note. To get percentages in Excel, you do not need to multiply by 100. It is enough to set the percentage format for the cell with the result.

The effective interest rate under the new formula coincided with the annual interest rate on the loan.

Thus, the simplest PMT function is used to calculate the annuity payments on a loan. As you can see, the differentiated repayment method is somewhat more complicated.

@Leeslav , You will not believe, but at one time I also dissected on a fox ... Then an idea was born in my head, just like you, I began to delve into and study JS and made a calculator myself ... It all depends on your desire. ..

Your formula counts cubes, and the result of arithmetic operations (I will reveal a great secret) in excel and in js will be the same !!! (just don't share this secret in the public)

That in Excel to put numbers in cells, that to insert these numbers in inputs - everything is considered the same !!!

If you want to implement the idea beautifully - implement it ... Well, it's not a problem to count the cubes of wood based on the perimeter of the house, the height of the ceilings and the thickness of the timber ... It's a good idea - implement it, and you will be happy: visitors will sit on your calculator and "play" with it : considered what they wanted, expensive? changed the timber from 200 to 150 (at least radio, at least select) and immediately saw how much cheaper ... still expensive? the height of the ceilings instead of 2.7 was taken 2.5 - even cheaper ... but still expensive? instead of 40, they took a floorboard with 30 - even cheaper ... ok, but I want it even cheaper ... Instead of 150mm, they put 100mm insulation - oh, normal, let's pull it !!! They pressed a button and the calculation came to them in the mail - they printed it out and came to you immediately with this piece of paper ...

And additional options can be enabled / disabled by checkbox (count / do not count) ...

And together with sending the miscalculation to the user, you can enter it into your database - he brought it, and you already have your own one ...

Also, by correcting, you can fix the miscalculation and the user's email - it did not come within a week or two: they wrote an automatic letter - they say they did a miscalculation on the site, we are waiting for you, welcome ...

Yes, you can think of "buns" for a carriage and a small cart ... Think globally, and you grabbed the steering wheel of the Lisapeda ...

Explain what the problem is Your formulas from excel I do in js ??? All in all, for 2-3 evenings, "disassemble" into parts (according to separate formulas) this "crazy" plate of yours and draw up a calculation algorithm. To code it - even for me, a lamer, a couple of weeks is enough (if I sit from morning to evening) ... Pros - they will do coding in a couple of days ...

Quintessence: I like your idea, if you want to do it - take it and do it !!! If you want to shake the air and "blah blah blah" shake it - not for me, there is something useful to do ... Just keep in mind - while you make friends with the form from the site with excel it is quite possible to write a normal settlement service .. And when competitors see your "curve" implementation, will they take the idea and make a normal service? And where are you? Yes in it! And just because you not the first!!! "On horseback" is always the one who knows how to look into the future ...

P.S. Regarding the complexity of the calculations: My "calculators" for the links are very simple, but they are for users, but in my plans, after I finish turning the site into adaptive, make a calculator for production - no less complicated than your plate: which will be calculated according to the same data, but not the cost of the order for the customer, but the cost of production ... And there is the piecework wages of workers, and electricity, and consumables, and amortization and even a lot of all sorts of terms / variables ... And I will make it - I will !! ! And after I do the boo, it leaves "on reduction" - only the chief accountant who makes the reports will remain ...

PPS. And about the translation of other calculators: you will suffer with the first one - the rest will not even notice how you will do it, "meeting" ...

For regular users of Excel, it is no secret that in this program you can perform various mathematical, engineering and financial calculations. This opportunity is realized by using various formulas and functions. But, if Excel is constantly used to carry out such calculations, then the issue of organizing the necessary tools for this directly on the sheet becomes relevant, which will significantly increase the speed of calculations and the level of convenience for the user. Let's find out how to make a similar calculator in Excel.

This task becomes especially urgent, if necessary, to constantly carry out the same type of calculations and calculations associated with a certain type of activity. In general, all calculators in Excel can be divided into two groups: universal (used for general mathematical calculations) and narrow-profile. The latter group is divided into many types: engineering, financial, credit investment, etc. It is on the functionality of the calculator that, first of all, the choice of the algorithm for its creation depends.

Method 1: using macros

First of all, let's look at the algorithms for creating custom calculators. Let's start by creating the simplest universal calculator. This tool will perform elementary arithmetic operations: addition, multiplication, subtraction, division, etc. It is implemented using a macro. Therefore, before proceeding with the creation procedure, you need to make sure that you have macros and the developer panel enabled. If this is not the case, then you should definitely.

  1. After the above presets are completed, we move to the tab "Developer"... Click on the icon "Visual Basic", which is located on the tape in the toolbox "The code".

  2. The VBA editor window starts. If the central area is displayed in gray and not white, then this means that there is no field for entering the code. To enable its display, go to the menu item "View" and click on the inscription "Code" in the list that appears. You can press the function key instead of these manipulations F7... In any case, a field for entering the code will appear.

  3. Here, in the central area, we need to record the macro code itself. It looks like this:

    Sub Calculator ()
    Dim strExpr As String
    "Entering data for calculation
    strExpr \u003d InputBox ("Enter data")
    "Calculating the result
    MsgBox strExpr & "\u003d" & Application.Evaluate (strExpr)
    End Sub

    Instead of a phrase "Enter the data" you can write down any other that is more acceptable to you. It will be located above the expression input field.

    After the code is entered, the file needs to be overwritten. However, it should be saved in a macro-enabled format. Click on the floppy disk icon on the VBA editor toolbar.

  4. The window for saving the document starts. Go to the directory on your hard drive or removable media where we want to save it. In field "File name" we assign the document any desired name or leave the one that is assigned to it by default. Mandatory in the field "File type" choose the name from all available formats Excel Macro-Enabled Workbook (* .xlsm)... After this step, click on the button "Save" at the bottom of the window.

  5. After that, you can close the macro editor window by simply clicking on the standard close icon in the form of a red square with a white cross in its upper right corner.

  6. To run a computing tool using a macro, while in the tab "Developer", click on the icon "Macros" on the tape in the toolbox "The code".

  7. After that, the macro window starts. Select the name of the macro that we just created, select it and click on the button "Run".

  8. After completing this action, the calculator, created on the basis of the macro, starts.

  9. In order to perform a calculation in it, write the required action in the field. It is most convenient to use for these purposes the numeric keypad, which is located on the right. After the expression is entered, click on the button "OK".

  10. Then a small window appears on the screen, which contains the answer to the solution of the given expression. To close it, click on the button "OK".

  11. But you must admit that it is rather inconvenient to go to the macros window every time you need to perform computational actions. Let's simplify the implementation of starting the compute window. To do this, being in the tab "Developer", click on the icon already familiar to us "Macros".

  12. Then, in the macro window, select the name of the desired object. Click on the button "Parameters…".

  13. After that, the window is launched even smaller than the previous one. In it, we can set a combination of hot keys, when pressed, the calculator will start. It is important that this combination is not used to call other processes. Therefore, it is not recommended to use the first characters of the alphabet. The first shortcut key is set by the Excel program itself. This is the key Ctrl... The next key is set by the user. Let this be the key V (although you can choose another). If this key is already used by the program, then one more key will be automatically added to the combination - S hift... We enter the selected character into the field "Keyboard shortcut" and click on the button "OK".

  14. Then close the macro window by clicking on the standard close icon in the upper right corner.

Now, when typing the selected hotkey combination (in our case Ctrl + Shift + V) will launch the calculator window. Agree, this is much faster and easier than calling it through the macro window every time.

Method 2: applying functions

Now let's look at the option of creating a narrow-profile calculator. It will be designed to perform specific, specific tasks and placed directly on the Excel sheet. Excel's built-in functions will be used to create this tool.

For example, let's create a tool for converting mass values. In the process of creating it, we will use the function CONVER... This operator belongs to the engineering block of built-in Excel functions. Its task is to convert the values \u200b\u200bof one measure of measurement to another. The syntax for this function is as follows:

CONV (number; ref_measure; con_ed_measure)

"Number" Is an argument in the form of a numerical value of the quantity that needs to be converted into another measure.

"Initial unit of measure" - an argument that defines the unit of measurement of the quantity to be converted. It is set by a special code that corresponds to a specific unit of measurement.

"End unit of measure" - an argument defining the unit of measurement of the quantity to which the original number is converted. It is also set using special codes.

We should dwell on these codes in more detail, since we will need them later when creating a calculator. Specifically, we need the codes for the units of measure of mass. Here is a list of them:

  • g - gram;
  • kg - kilogram;
  • mg - milligram;
  • lbm - British pound;
  • ozm - ounce;
  • sg - slag;
  • u Is an atomic unit.

It should also be said that all the arguments of this function can be specified both by values \u200b\u200band by references to the cells where they are located.

  1. First of all, we make a blank. Our computing tool will have four fields:
    • Converted value;
    • Initial unit of measure;
    • Conversion result;
    • End unit of measure.

    Set the headings under which these fields will be placed, and select them with formatting (fill and borders) for more visualization.

    Into the fields "Convertible value", "Initial measurement limit" and "End dimension limit" we will enter data, and in the field "Conversion Result" - the final result is displayed.

  2. Let's make it so that in the field "Convertible value" the user could only enter valid values, namely numbers greater than zero. Select the cell into which the value to be converted will be entered. Go to the tab "Data" and in the toolbox "Working with data" click on the icon "Data validation".

  3. The tool window starts "Data validation"... First of all, let's make the settings in the tab "Parameters"... In field "Data type" select the parameter from the list "Valid"... In field "Value" also from the list we choose the parameter "More"... In field "Minimum" set the value «0» ... Thus, only real numbers (including fractions) that are greater than zero can be entered into this cell.

  4. After that, we move to the tab of the same window "Message to enter"... Here you can give an explanation of what exactly needs to be entered by the user. He will see it when the value input cell is selected. In field "Message" write the following: "Enter the amount of mass to be converted".

  5. Then we move to the tab "Error message"... In field "Message" we should write the recommendation that the user will see if he enters incorrect data. Let's write the following: The value entered must be a positive number. After that, to complete the work in the window for checking the entered values \u200b\u200band save the settings we entered, click on "OK".

  6. As you can see, when a cell is selected, a hint for input appears.

  7. Let's try to enter an incorrect value there, for example, text or a negative number. As you can see, an error message appears and input is blocked. Click on the button "Cancel".

  8. But the correct value is entered without problems.

  9. Now go to the field "Initial unit of measure"... Here we will make it so that the user will select a value from a list consisting of those seven mass values, the list of which was given above when describing the function arguments CONVER... You cannot enter other values.

    Select the cell under the name "Initial unit of measure"... Click on the icon again "Data validation".

  10. In the data verification window that opens, go to the tab "Parameters"... In field "Data type" set the parameter "List"... In field "A source" separated by semicolons ( ; ) we list the codes for the names of mass quantities for the function CONVER, which was discussed above. Next, click on the button "OK".

  11. As you can see, now if you select the field "Initial unit of measure", then a triangle-shaped pictogram appears to the right of it. When you click on it, a list with the names of mass units opens.

  12. Exactly the same procedure in the window "Data validation" we carry out with the cell with the name "End unit of measure"... It also produces exactly the same list of units of measurement.

  13. After that, go to cell "Conversion Result"... It will contain the function CONVER and output the result of the calculation. Select this element of the sheet and click on the icon "Insert function".

  14. Starts up Function wizard... Go to the category in it "Engineering" and select the name there "TRANSFER"... Then click on the button "OK".

  15. The operator arguments window opens CONVER... In field "Number" you should enter the coordinates of the cell under the name "Convertible value"... To do this, place the cursor in the field and left-click on this cell. Her address is immediately displayed in the field. In the same way, we enter the coordinates into the fields "Initial unit of measure" and "End unit of measure"... Only this time we click on the cells with the same names as these fields.

    After all the data has been entered, click on the button "OK".

  16. As soon as we have completed the last action, in the cell window "Conversion Result" the result of the conversion of the value was immediately displayed, according to the previously entered data.

  17. Let's change the data in the cells "Convertible value", "Initial unit of measure" and "End unit of measure"... As you can see, the function automatically recalculates the result when changing parameters. This indicates that our calculator is fully functional.

  18. But we didn't do one important thing. Cells for entering data are protected from entering incorrect values, but the element for outputting data is not protected in any way. But you can't enter anything into it at all, otherwise the calculation formula will simply be deleted and the calculator will become inoperative. By mistake, you can enter data in this cell yourself, not to mention third-party users. In this case, you will have to re-write the entire formula. You need to block any data entry here.

    The problem is that the lock is set on the sheet as a whole. But if we block the sheet, then we will not be able to enter data into the input fields. Therefore, in the properties of the cell format, we will need to remove the possibility of blocking from all elements of the sheet, then return this possibility only to the cell for displaying the result and after that block the sheet.

    Left-click on the element at the intersection of the horizontal and vertical coordinates panel. This selects the entire sheet. Then right-click on the selection. A context menu opens, in which we select a position "Cell format ...".

  19. The formatting window starts. Go to the tab in it "Protection" and uncheck the parameter "Protected cell"... Then click on the button "OK".

  20. After that, select only the cell for displaying the result and click on it with the right mouse button. In the context menu, click on the item "Cell format".

  21. Again, in the formatting window, go to the tab "Protection", but this time, on the contrary, set a check mark next to the parameter "Protected cell"... Then click on the button "OK".

  22. After that, we move to the tab "Review" and click on the icon "Protect Sheet"located in the toolbox "Changes".

  23. The window for setting sheet protection opens. In field "Password to disable sheet protection" we enter a password with which, if necessary, in the future it will be possible to remove protection. The rest of the settings can be left unchanged. Click on the button "OK".

  24. Then another small window opens, in which you should re-enter the password. We do this and click on the button "OK".

  25. After that, when you try to make any changes to the output cell of the result, the actions will be blocked, as indicated in the dialog box that appears.

Thus, we have created a full-fledged calculator for converting mass values \u200b\u200binto various units.

In addition, a separate article tells about the creation of another type of narrow-profile calculator in Excel for calculating loan payments.

Method 3: enable built-in Excel calculator

In addition, Excel has its own built-in universal calculator. True, by default, the button to launch it is absent on the ribbon or on the quick access panel. Let's see how to activate it.


As you can see, Excel has a lot of options for creating calculators for different needs. This feature is especially useful when performing narrow-profile calculations. Well, for ordinary needs, you can use the built-in tool of the program.

© 2020 Mobile and Computer Gadgets