Using Microsoft Excel as a calculator. Creating a calculator in Microsoft Excel

The first loan calculator in Excel can be downloaded by.
But 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 the 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.

The advantages of this calculator:

Loan calculator in Excel almost accurately calculates annuity payment schedule and differential payment schedule
Changes in the payment schedule - accounting for early repayments in the reduction of the principal amount
Construction and calculation of a payment schedule in the form of a table in Excel. The payment schedule table can also be edited
The calculation takes into account the leap year and the non-leap year. Due to this, the amount of accrued interest practically coincides with the values calculated by VTB24 and Sberbank
Accuracy of calculations - the calculations coincide with the calculations of the credit calculator VTB24 and Sberbank
The calculator can be edited for yourself by setting different calculation options.

Disadvantages of a calculator

Possible change in interest rate during loan payments is not taken into account
If you make a calculation by making early payments to change the term and amount, then the calculation will be incorrect
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
The option is not calculated - the first payment is only interest. In the event that 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.
The calculation is made for an interest rate with 2 decimal places.

It is devoid of all the above-mentioned shortcomings. 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 schedule 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.

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.

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".

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.

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 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 format with support for macros. Click on the floppy disk icon on the VBA editor toolbar.

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.

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.

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".

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

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

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

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".

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".

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

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".

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

Now, when typing the selected combination of hot keys (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:

CONV (number; out_ed_meas; end_ed_meas)

"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 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 set, both by values and by references to the cells where they are located.

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.
We 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.

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".

The tool window starts "Data checking"... 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 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.

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".

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".

As you can see, when you select a cell, a hint for input appears.

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".

But the correct value is entered without problems.

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".

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".

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.

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.

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".

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

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".

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

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.

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 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 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 ...".

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

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".

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".

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

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".

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

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.

* .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 ratio, 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.

Automated table for accurate calculation of seniority, taking into account leap years (366 days) and taking into account months with a different number of calendar days (30, 31, 28 and 29) With the division of periods (days worked) into full: year, month and day.

@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 - it's 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 ??? In total, 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 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" on 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 as for the translation of other calculators: you will suffer with the first one - the rest will not even notice how you will do it, "meeting" ...