Mortgage loan calculator in Excel. How to calculate a loan correctly in Excel? Creating a calculator in Microsoft Excel

Instead of using a calculator, use Microsoft Excel to do math!

You can enter simple formulas to add, divide, multiply, and subtract two or more numeric values. You can also use the AutoSum feature to quickly add up a series of values ​​without having to manually enter them into a formula. After you create the formula, you can copy it to adjacent cells - no need to create the same formula over and over.

Subtraction in Excel

Multiplication in Excel

Division in Excel

Simple formulas

All formula entries begin with an equal sign ( = ). To create a simple formula, simply enter an equal sign followed by the calculated numeric values ​​and the corresponding math operators: the plus sign ( + ) for addition, the minus sign ( - ) to subtract, an asterisk ( * ) for multiplication and the slash ( / ) to divide. Then press Enter and Excel will immediately calculate and display the result of the formula.

For example, if you enter the formula in cell C5 =12,99+16,99 and press Enter, Excel will calculate the result and display 29.98 in that cell.

A formula entered in a cell will appear in the formula bar whenever you select a cell.

Important: Although there is a function sums, function Subtract absent. Instead, use the minus (-) operator in your formula; For example, = 8-3 + 2-4 + 12. Alternatively, you can use the minus sign to convert a number to its negative value in a sum function. For example, the formula = SUM (12, 5, -3, 8, -4) uses the sum function to add 12, 5, subtract 3, add 8, and subtract 4 in that order.

Using AutoSum

The easiest way to add the SUM formula to a worksheet is to use the AutoSum function. Select a blank cell directly above or below the range you want to sum, and then open the ribbon tab home or Formula and select Autosum > Sum... The AutoSum feature automatically determines the range to add and creates a formula. It also works horizontally if you select a cell to the right or left of the range to be summed.

Note: AutoSum does not work with non-contiguous ranges.

Auto-sum vertical

The image above shows that the AutoSum function has automatically identified cells B2: B5 as the range to sum. You only need to press the ENTER key to confirm. If you need to add or exclude multiple cells, hold down the SHIFT key and press the corresponding arrow key until you select the range you want. Then press Enter to complete the task.

IntelliSense Feature Guide: sums (number1; [number2]; ...) the floating tag below the function is an IntelliSense tutorial. Clicking the sum or function name changes the blue hyperlink to the help topic for that function. Clicking on individual function items will highlight the corresponding representative unit in the formula. In this case, only the value B2: B5 is highlighted, as there is only one reference per number in this formula. Any feature will display an IntelliSense tag.

Horizontal AutoSum

For more information, see the article on the SUM function.

Avoiding rewriting a single formula

After creating a formula, you can simply copy it to other cells, rather than creating the same formula. You can copy the formula or use the fill handle to copy the formula to adjacent cells.

For example, when you copy a formula from cell B6 to cell C6, it automatically updates the cell references in column C.

Check that the cell references are correct when copying the formula. Cell references can change as long as they are relative. For more information, see the article Copy and paste a formula into another cell or sheet.

What to use in a formula to simulate calculator keys?

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 in tricky formulas, calculate interest, payment amounts, 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 balance of the 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 = K * S

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

Annuity coefficient formula:

K = (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, 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 amount of the principal debt 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 = NEO / (PP + NEO * PS)

• DP - monthly loan payment;
• OSZ - the balance of the loan;
• PP - the number of periods remaining until the end of the maturity;
• 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: = $ B $ 2. In the second and subsequent ones - it is calculated by the formula: = IF (D10> $ 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: = E9 * ($ B $ 3/12).

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

Final payment: the sum of "interest" and "principal" in the current period: = 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:

The red number is the annuity (they took 100,000 rubles), the black number is the 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 = annuity payment - interest.

Interest Amount = Remaining Debt * Monthly Interest Rate.

The balance of the principal debt = 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 the third decimal place according to the following formula:

• UCS = 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 says that this is the standard time frame that occurs most often on the repayment schedule. In the example, BP = 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: = 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.

* .Xlsx files with examples of calculating formulas and functions. And also free samples: reports, forms of documents, templates, presentations of graphs and diagrams for self-study.

Sample works download in Excel

Moving average calculation download in Excel.
Download a ready-made example of calculating and forecasting using the moving average method. In this example, a moving average chart is also built.

Amount in words download.
The function translates the number and amount in words (words). You can specify the currency type in the function parameters. A ready-made example of a written macro for the user-defined function "CurrencyNumber".

Column-sorted table.
Macro for buttons in table column headers that, when clicked, sort the column according to other columns. An xlsm file with an example of how a macro works on a table.

Interactive crossword puzzle in Excel.
A ready-made template for performing crosswords using standard functions (without using VBA macros) creating interactivity: checking correct answers, counting the number of answers.

Excel program for creating crossword puzzles.
A handy program created by means of VBA-macros, which helps to quickly create symmetric crosswords with subsequent printing.

Download an example of calculating KTU.
Formulas for calculating the labor force participation rate (KTU) for calculating wages to employees. Form of the table for calculating points.

Download an example of calculating CFA.
Formulas for calculating the coefficient of financial activity (CFA). Calculation of the balance of financial stability and profitability of the enterprise.

Download barcode generator in Excel.
Macro for generating 13 and 8 digit barcodes, right in the cells of the worksheet. VBA code is open and editable.

Comparison chart in different variations.
Examples of building comparative charts of various types: Pie, Bar, Histogram, Graph, Dot, Petal.

IFRS transformation table.
Download the transformation table for building a budgeting model according to the "International Standard for Formation of Reports".

Transformation table - form.
Form of transformation table for dusting with data when creating reports with high arithmetic accuracy and clarity of adjustments.

Calculation of average earnings in case of reduction.
An example of calculating the average earnings when reducing employees in the state. The calculation indicates: average earnings, severance pay, as well as severance pay for the second month.

Calculation of average earnings for retirement.
Download a table in Excel with an example of calculating pension benefits according to the new rules. The calculation takes into account: average monthly earnings, earnings ratio, seniority coefficient, estimated pension, valorization amount, insurance pension until 2002, pension coefficient index and pension points.

An example of calculating the average earnings for a business trip.
Table of calculations of average earnings according to the payroll for the payment of travel allowances, both for permanent employees and for those who just started working.

Table for calculating the average earnings for sick leave.
The table displays the average daily earnings for calculating sick leave. For calculations, all statements for the billing period are analyzed.

Calculator for calculating average earnings for an employment center.
With the help of this table, it is easy to calculate the average earnings for the employment center for all accounting standards and requirements. All necessary indicators are taken into account and calculated: the settlement period, the amount and the order of accrual.

@Leeslav Believe it or not, at one time I also dissected a fox on a fox ... Then I had an idea 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) both in excel and in js will be the same !!! (just do not spread this secret to 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, dear? 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, we'll pull it !!! They pressed a button and the calculation came to them by mail - they printed it out and came to you right away 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 wagon 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 you sit from morning to evening) ... Pros - they will do the 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 at the same time? Yes in it! And just because you not 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 count on 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 will remain who makes the reports ...

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 through the use of 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 last 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 a basic 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 "Code".



2. The VBA editor window is launched. 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 = InputBox ("Enter data")
   "Calculating the result
   MsgBox strExpr & "=" & Application.Evaluate (strExpr)
   End Sub
   

   Instead of a phrase "Enter 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 format with support for macros. 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 disk 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 "Code".



7. After that, the macro window is launched. 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 make a calculation in it, write down 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 "Options…".



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 be launched. 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 we 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 ​​of one measure of measurement to another. The syntax for this function is as follows:

CONVER (number; out_ed_measure; end_ed_measure)

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

"Initial unit of measure"- an argument that defines the unit of measurement of the quantity to be converted. It is specified 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 set, both by values ​​and 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 headers 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 checking".



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



4. After that, we move to the tab of the same window. "Message for input"... 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 highlighted. 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 entered value must be a positive number." After that, in order to complete the work in the window for checking the entered values ​​and save the settings we entered, click on the button "OK".



6. As you can see, when you select a cell, 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 the 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 checking".



10. In the data verification window that opens, go to the tab "Options"... In field "Data type" set the parameter "List"... In field "A source" separated by semicolons ( ; ) we list the codes for the names of the mass values ​​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. Absolutely the same procedure in the window "Data checking" 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 the 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 we 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 data entry are protected from entering incorrect values, but the element for data output is not protected in any way. But you cannot 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 rewrite 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 blocking option from all sheet elements, then return this ability only to the cell for displaying the result, and only 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 we 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 we 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, which is reported in the dialog box that appears.

Thus, we have created a full-fledged calculator for converting mass values ​​into various units.

In addition, a separate article describes 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.