Excel loan calculator and monthly payment formulas. Mortgage loan calculator in Excel. How to correctly calculate a loan in 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 a formula, you can copy it to adjacent cells - no need to create the same formula again.

Subtraction in Excel

Multiplication in Excel

Division in Excel

Simple Formulas

All formula entries begin with an equals sign ( = ). To create a simple formula, simply enter an equal sign followed by the calculated numeric values ​​and the corresponding math operators: plus sign ( + ) for addition, minus sign ( - ) for subtraction, an asterisk ( * ) for multiplication and a slash ( / ) for division. Then press the Enter key 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 be displayed in the formula bar whenever you select the cell.

Important: Even though there is a function amounts, function Subtract is absent. Instead, use the minus operator (-) in the formula; For example, = 8-3 + 2-4 + 12. You can also use the minus sign to convert a number to its negative value in the 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 a SUM formula to a worksheet is to use the AutoSum function. Select an empty cell directly above or below the range you want to sum, and then click the tab on the ribbon home or Formula and choose AutoSum > Sum. The AutoSum feature automatically determines the range to sum and generates the formula. It also works horizontally if you select a cell to the right or left of the summed range.

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

AutoSum Vertical

The figure 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 appropriate arrow key until the desired range is highlighted. Then press the Enter key to complete the task.

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

AutoSum Horizontal

See the SUM function article for more information.

Avoiding rewriting one formula

Once you have created a formula, you can simply copy it to other cells instead of 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, the cell references in column C automatically change.

When copying a formula, check that the cell references are correct. Cell references can change if they are relative. See Copy and paste a formula to another cell or sheet for more information.

What to use in a formula to simulate calculator keys?

Calculator key	Excel Method	Description, example	Result
+ (plus key)		Use in a formula to add numbers. Example: =4+6+2
- (minus key)		Use in a formula to subtract numbers or indicate a negative number. Example: =18-12 Example: =24*-5 (24 times negative 5)
x (multiplication key)	* (asterisk)	Use in a formula to multiply numbers. Example: =8*3
÷ (divide key)	/ (slash)	Use in a formula to divide one number by another. Example: =45/5
% (percent key)	% (percent)	Use in a formula with * to multiply by a percentage. Example: =15%*20
√ (Square root)	SQRT (function)	Use the SQRT function in a formula to find the square root of a number. Example: =SQRT(64)
1/x (reciprocal)		Use the expression =1/ in the formula n, where n is the number to be divided by 1. Example: =1/8

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

The functions of the Microsoft Excel program allow you to quickly navigate intricate formulas, calculate interest, payout amounts, and overpayments.

How to calculate loan payments in Excel

Monthly payments depend on the loan repayment scheme. There are annuity and differentiated payments:

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

Annuity is more commonly used: it is more profitable for the bank and more convenient for most customers.

Calculation of annuity payments on a loan in Excel

The monthly amount of the annuity payment is calculated by 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 ratio 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 is the term of the loan in months.

There is a special function in Excel that counts annuity payments. This is the PLT:

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



Calculation of payments in Excel according to the differentiated repayment scheme

The differentiated payment method assumes that:

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

Differentiated payment calculation formula:

DP \u003d 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 period;
• PS - interest rate per month (annual rate divided by 12).

We will draw up a repayment schedule for the previous loan according to a differentiated scheme.

The input data is the same:

Let's make a loan repayment schedule:

Loan balance: in the first month equals the whole 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 term of the loan; E9 - loan balance in the previous period; G9 - the amount of the principal debt in the previous period.

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

Principal payment: the total loan amount divided by the term: =IF(D9

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

Let's enter the formulas in the appropriate 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 an annuity (they took 100,000 rubles), the black one is a differentiated method.

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:

Calculate the monthly interest rate and loan payments:

Fill in the table like this:

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

Principal amount = annuity payment - interest.

Amount of interest = balance of 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 a loan of 500,000 rubles;
• returned to the bank - 684,881.67 rubles. (the sum of all payments on the loan);
• the overpayment amounted to 184,881.67 rubles;
• interest rate - 184,881.67 / 500,000 * 100, or 37%.
• A harmless commission of 1% cost the borrower very dearly.

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

Calculation of the total cost of the loan in Excel

According to the Consumer Credit Law, a new formula is now applied to calculate the total cost of credit (TCC). UCS is determined as a percentage with an accuracy of three decimal places according to the following formula:

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

Let's take the following loan data as an example:

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

It is necessary to determine the base period (BP). The law says that this is the standard time interval that occurs most often in 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 into 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.

TIC according to the new formula coincided with the annual interest rate on the loan.

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

@Leeslav , You won’t believe it, but at one time I also cut it on a lisaped ... Then I had an idea in my head, just like you, I began to delve into and study JS and made the calculator myself ... It all depends on your desire. ..

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

What to put numbers in cells in Excel, what to insert these numbers into 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 ... A good idea - implement it, and you will be happy: visitors will sit on your calculator and "play" with it : counted what they wanted, expensive? changed the bar from 200 to 150 (at least radio-ohm, at least select-ohm) and immediately saw how much cheaper ... still expensive? ceiling height instead of 2.7 took 2.5 - even cheaper ... but still expensive? instead of 40, they took a 30-ku floorboard - even cheaper ... okay, but I want even cheaper ... Insulation instead of 150mm they put 100mm - oh, normul, we'll pull it !!! They pressed the button and the calculation came to their mail - they printed it out and came to you right away with this piece of paper ...

And additional options can be turned on / off by checkbox (count / not count) ...

And along 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 optimizing, you can fix the miscalculation and the user's email - did not come within a week or two: they wrote a letter automatically - they say they made a miscalculation on the site, we are waiting for you, welcome ...

Yes, you can think of "goodies" for a wagon and a small cart ... Think globally, but you grabbed the steering wheel of a lisaped ...

Explain what is the problem Your formulas from excel-I do in js??? In total and business for 2-3 pm "take apart" into parts (according to separate formulas) this "crazy" tablet 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 till night) ... 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" to breed - 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 competitors, seeing your "curve" implementation, will take the idea and make a normal service? And where are you with that? 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 an adaptive one, to make a calculator for production is no less complicated than your tablet: which will calculate according to the same data, but not the cost of the order for the customer, but the cost of production ... And there are the piecework wages of workers, and electricity, and consumables, and depreciation, and dofiga of all sorts of terms / variables ... And I will do it - .la I will !! ! And after I do the accounting, he will leave "by reduction" - only the chief accountant who makes the reports will remain ...

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

*.xlsx files with examples of formula and function calculations. As well as free samples: reports, forms of documents, templates, presentations of graphs and diagrams for self-study.

Examples of work download in Excel

Download moving average calculation in Excel.
Download a ready-made example of calculating and making a forecast using the moving average method. In this example, a moving average chart is also plotted.

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

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

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

Program in Excel for creating crossword puzzles.
A convenient program created using VBA-macros, which helps you quickly create symmetrical crossword puzzles with subsequent printing.

Download an example of the calculation of KTU.
Formulas for calculating the labor participation rate (KTU) for calculating wages for employees. The form of the table for the calculation of points.

Download an example of CFA calculation.
Formulas for calculating the financial activity ratio (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.

Comparative diagram in different variations.
Examples of building comparative charts of various types: Pie, Bar, Histograms, Graphs, Scatter, Radar.

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

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

Calculation of average earnings in case of reduction.
An example of calculating average earnings when laying off staff. 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 benefits for pensions 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.
The table of calculations of average earnings according to the payroll for the payment of travel allowances, both for permanent employees and those who have just entered the job.

Table for calculating 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 the employment center.
Using this table, it is easy to calculate the average earnings for an employment center for all accounting standards and requirements. All necessary indicators are taken into account and calculated: billing period, amount and accrual procedure.

Automated table for accurate calculation of length of service, 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 a full: year, month and day.

The first loan calculator in Excel can be downloaded from .
But Excel is not available on all computers. MAC and Linux users don't usually use Excel because this is a Microsoft product.
You can also use the early repayment calculator to calculate the 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. The loan calculator in Excel almost accurately calculates the annuity payment schedule and the differentiated payment schedule
2. Changes in the payment schedule - accounting for early repayments to reduce the amount of the principal debt
3. Construction and calculation of the payment schedule in the form of a table in Excel. The payment schedule table can also be edited
4. The calculation takes into account both leap and non-leap years. Due to this, the amount of accrued interest practically coincides with the values ​​calculated by VTB24 and Sberbank
5. Accuracy of calculations - calculations coincide with the calculations of the VTB24 loan calculator and Sberbank
6. The calculator can be edited for yourself, setting different calculation options.

Calculator Disadvantages

1. No allowance for a possible change in the interest rate during loan repayments
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 greater 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 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.
5. The calculation is made for the interest rate with 2 decimal places.

All of the above shortcomings are devoid of. In general, the shortcomings are not very critical and they are inherent in any online loan calculator.
Another loan calculator in Excel can be downloaded from. This loan calculator does not allow you to calculate early repayment. However, its advantage is that it calculates a loan with several interest periods. If the amount of interest on the loan for a given month is greater than the amount of the annuity payment, then the graph for the first loan calculator in excel is not built correctly. The graph shows negative numbers.

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 the division, the graph will again have 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.