The best software product for preparing business plans in Word and Excel format
Strategic-Line Harmony of planning in your business

Credit products. Calculation methods and algorithms



Credit products. Methods for calculating annuities, simple or compound interest, methods for determining the number of days (ACT / ACT, ACT / 360, 360/360)

Planning finances in business plans is first and foremost - the selection of financing to eliminate the shortage of funds. To do this, you will need a tool that can simulate loans of any complexity - calculate annuities, choose formulas and calculation algorithms, determine ways of repaying loans (ACT / ACT, ACT / 360, 360/360), take into account differential payments and interest rates, refinancing rate, etc.

With Budget-Plan Express, you can easily schedule loan or rental products (leasing payments) of any complexity. To simplify the interface, the user selects the type of credit product from the list: “Standard”, “Annuity”, “Consumer”. By choosing the type of credit product, the user immediately selects a formula or algorithm by which payments will be calculated.

Budget-Plan Express is a software product for preparing business plans and presentations in Word and Excel format, optimal for small businesses and studies (for students, graduate students, MBI students, etc.)

Find the best financing for your project - yourself! Try the new version of the program (6.02). Details ...


The concept of simple and compound interest

The calculation of credit payments according to the simple interest, based on the annual interest rate, is calculated by the formula:

    K(t) = Z (1 + pt),

    Where:
    K(t) – payments for period t.
    Z – credit amount.
    t – is the number of years or the number of years ratio (t = number of days / 360 or = number of days / 365. If the calculation step is a month, then t = number of months / 12).
    p – interest rate.

The calculation of compound interest takes into account interest money. That is, the loan takes into account not only the amount of the debt, but also the accumulated interest.

The formula for compound interest is calculated from the formula for simple interest. For example, the calculation of final payments, taking into account accruing interest, for the 1st and 2nd year will be as follows:

    K1 = Z (1+ p)
    K2 = Z(1+ p)(1+ p) = Z(1+ p)2

    Where:
    K(1), K(2) – payments for the 1st and 2nd years of the loan.

Accordingly, the calculation of credit payments according to the compound interest scheme, for n years, is calculated by the formula:

    Kn Z(1+ p)(1+ p)...(1+ p) = Z( 1+ p )n

    Where:
    n – number of years or the number of years ratio (n = number of days / 360 or n = number of months / 12).

Accounting in the formula for previously accrued interest is more fair from the point of view of the borrower, of course, if the money and interest remain with the borrower until payment.

Example. Z = 1,000,000, p = 0.12 (12%) and n = 1.5 (18 months). Calculate loan options - at simple and compound interest.

    According to the simple interest scheme:

    K(1.5) = Z (1 + pt) = 1 000 000 (1 + 0.12 × 1.5) = 1 180 000

    According to the compound interest scheme:

    K(1.5) = Z( 1+ p )n = 1 000 000 (1 + 0.12)1.5 ≈ 1 185 287

Thus, payments under the compound interest scheme are “fairer” and more profitable for the lender, and are understandable for the borrower, again, if this money and interest remain with the borrower until payment, i.e., are paid off at the end of the term. But if the same money is paid monthly, the “fairness” of this formula loses its original meaning and changes the meaning exactly the opposite (see consumer credit, formulas [4], [5]).



Formulas (types of credit products)

Choosing the type of credit product, the user selects the formula and algorithm by which payments will be calculated:

  1. "Standard" loan product involves the calculation of differentiated payments for simple and compound interest.
  2. An “Annuity” credit product is equal in amount (usually monthly) payments, which include the amount of interest charged on a loan and the amount of principal. Two formulas are used to calculate annuities - using simple and compound interest.
  3. "Consumer" credit product, as well as "Standard" is calculated by the standard formulas of simple and complex interest. However, the loan is paid in equal payments - annuity. It is calculated by simply dividing the amount (debt and interest) by the number of payments.

  1. Standard
  2. A “standard” credit product involves the calculation of differentiated payments at simple and compound interest. It is not even a formula, but rather an algorithm for calculating differentiated payments. Differential payment - when the principal amount of the loan is paid in equal installments, and the interest accrued with each subsequent period decreases, and the total amount of payment decreases accordingly. The peculiarity of the calculation algorithm is that interest money is charged depending on the balance of the debt.

    For example, if you pay the debt and accrued interest on a monthly basis, the interest in the next month will be less, respectively, in the amount of the reduced debt. That is, interest is calculated each time on the amount of current debt. This algorithm of calculation, so to speak, implements the principle of absolute “fairness” - both in relation to the lender and the borrower.

    But it (the calculation algorithm) has its drawbacks: since every month we deal with different payments, payments need to be monitored and recalculated every month. This disadvantage will be eliminated by the following formula, which proposes to adjust debt payments in such a way as to obtain annuities - equal payments.


  3. Annuity
  4. An annuity, in a broad sense, is cash flow with equal intervals and equal cash flows. An annuity payment is equal in amount (for example, a monthly or quarterly) loan payment, which includes the amount of accrued interest on a loan and the amount of principal debt. Budget – Plan Express uses two formulas for calculating annuities — using simple and compound interest.

    1. Annuity using simple interest.

    The formula for calculating annuities is derived from the annuity formula for simple interest. Let A be the annuity (equal payment), in different n periods:

      Z(1+np) = A [1+(n-1)p] + A [1+(n-2)p] + ... A

      from here we find the annuity [*]:
      Annuity payment - equal in amount (for example, monthly or quarterly) loan payment

      [1]

      Where:
      A – annuity payment using simple interest,
      Z – is the loan amount,
      n – is the coefficient of years,
      p – interest rate ratio.
      *[to convert the formula [1] we use the formula for the sum of an arithmetic progression]


    2. Annuity with compound interest.

    By analogy, a formula is derived for calculating annuities for compound interest. Let А be an annuity, for different n periods:

      Z(1+p)(n) = A (1+p)(n-1) + A (1+p)(n-2) + ... A

      from here we find the annuity [*]:
      Annuity payment - equal in amount (for example, monthly or quarterly) loan payment

      [2]

      Where:
      A – annuity payment using compound interest,
      Z – is the loan amount,
      n – is the coefficient of years,
      p – interest rate ratio.
      *[to convert formula [2], use the formula of the sum of a finite geometric progression]

    Another variant (equivalent transformation [3]) of the same formula can be obtained by separating the numerator and denominator by (1 + p)n:

      Annuity payment - equal in amount (for example, monthly or quarterly) loan payment

      [3]

    This formula [2], [3], for calculating compound interest, is the most common. Usually, debt repayment involves monthly or quarterly payments.

    According to the established practice, banks, as a rule, consider an annuity payment according to this formula [2], [3].


  5. Consumer
  6. The “consumer” loan, like the “Standard” one, is calculated using the standard formulas of simple and compound interest. However, the loan is paid in equal payments - annuities, which are calculated by simply dividing the sum of all payments (debt and interest) by the number of payments:

      Annuity payment - equal in amount (for example, monthly or quarterly) loan payment

      [4]

      Annuity payment - equal in amount (for example, monthly or quarterly) loan payment

      [5]

      Where:

      A – annuity payment using simple [4] and compound [5] percent;
      m – number of annuities.


Conclusion

In the practice of banks (when lending to legal entities, budget organizations) the formula [2] and [3], using compound interest, is the most common for calculating annuities. This formula for calculating annuities [2] and [3] is called the formula of the current (accrued) value of the permanent financial rent. Also, this formula is used to calculate discounted cash flows (see the “Present Value” section.).

The formula for calculating a consumer loan using annuities is essentially “unfair” in relation to a borrower, and even more so if annuities are calculated according to the compound interest scheme. Judge for yourself, after the borrower has already repaid part of the amount, for example, half the amount of the debt, he also continues to pay interest on the already repaid debt. That is, half of the money is already owned by the bank, it manages half of the debt, but at the same time the borrower continues to pay interest for a part of the debt that does not already belong to him. This “injustice” is simply explained: consumer loans, as a rule, are loans with a high degree of risk. This is a premium for future risks, that is, banks insure themselves against future risks.

Therefore, when a rate is announced, for example, 12.5%, it is necessary to clarify - by what formula is a loan considered. The real rate may be an order of magnitude greater than the declared one if the loan is taken for more than 1 year.

☛ To find out the real rate, compare different credit conditions, calculated using different formulas, use the program. Here you can calculate loan products of almost any complexity.




Methods and algorithms of calculation


1. Options for calculating loan products

In the program, all payments are discounted at the end of periods and the payments are called postinumero.

The maximum term of calculating loans is 10 years (120 months).
Note, as the timeline in Budget Plan Express 3 waaga (36 months), all payments, after 36 months, refer to the future.

In "General settings" to specify General options:
  1. Calculation step (in months, days)
  2. Annual cycle accounting method (ACT / ACT, ACT / 360, 360/360);
  3. Limit percentage
  4. Estimated percent (simple, complex)
  5. Calculation currency.
Choosing a formula and calculation conditions, you can simulate almost any calculation. To terms of calculation, in addition to general settings, include:
  1. Periodicity of payments;
  2. Deferred debt;
  3. Deferred percent;
  4. Accounting for an arithmetic or geometric progression
  5. Accounting for other one-time payments
  6. Accounting for other periodic payments;
  7. Bid adjustment.
For custom calculations you can use the tab "table of payments" where you can specify the payments schedule.


2. Payments calculated in the currency

All payments are displayed in the "table of payments" in the currency to which they relate. At the time of payment, in the "table of payments" are calculated expense (income) related to currency exchange in system (basic) currency. At the same time, all calculations in the financial plan represented in the system (primary) currency. In the statement of profit and loss exchange rate differences reflected in the line (16): "other non-operating expenses (income)" and not included in "debt servicing Costs".

When calculating the credit, for example, in dollars, in the "financial plan" they will be recalculated in rubles at the forecasted rate.


3. Progressive payment of the debt

Progressive payouts are only used for the "standard" loan product where the percent money is repaid depending on the balance owed.

    1. Payments varying in arithmetic progression:

      Z = [2B1 + d (n-1)] n / 2,

      hence the first payment of the debt:

      B1 = Z / n - d(n-1) / 2

      где:
      Z – amount of debt,
      B1 – first payment of debt,
      d – difference of arithmetic progression (sum).


    2. Payments, and variable geometric progression:

      Z = B1 [qn - 1] / [q - 1],

      hence the first payment of the debt:

      B1 = Z [q - 1] / [qn - 1]

      где:
      Z – amount of debt,
      B1 – first payment of debt,
      q – denominator of geometric progression (percent).


4. Methods for determining the number of days

In world practice there are several ways of determining the term of loan return t (in years) for loans issued to period calculated in days. In each of these ways time the repayment of the loan t (in years) is calculated by the formula:

    t = s / g,

    where s and g are defined depending on the calculation method:

    1. "English" way or ACT / ACT . The number of s is equal to the exact number of days of a loan minus one day (day of issue and date of repayment of the loan is considered one day), the number of g is equal to the exact number of days in year (365 or 366). This method is called English and is often referred to as a way 365/365 or ACT/ACT.

    2. "French" way or ACT / 360 . The number of s is equal to the exact number of days of a loan minus one day (day of issue and date of repayment of the loan is considered one day), the number g is equal to 360 (a year has 12 months of 30 days). This method called the French and is often referred to as a way 365/360 or ACT/360.

    3. "German" way or 360/360 . The number g is 360 (in the year 12 months to 30 days), the number s consists of a full number of months (30 days each) plus an accurate number of days in the remaining incomplete month minus one day (the day of issue and the day of repayment of the loan are considered one day). This method is called German and is often referred to as method 360/360.

In financial practice, to determine the exact number of loan days t, use special date tables.

In the "Budget-Plan Express" algorithm for determining the exact number of days is already contained in the calculations. To use this algorithm, you need to specify the calculation step in days (the "settings" tab).


5. Method of calculating the maximum percent at the refinancing rate

The percentage limits is the marginal value of percent deductions, including percent and sum differences on obligations. Is calculated based on refinancing rate: refinancing rate multiplied by a factor. Depends on the tax law (of any country) in a particular case. In some tax jurisdictions, the ratio may depend on the loan currency. For example, marginal rate in rubles = refinancing rate * 1.8 or ceiling rate currency = refinancing rate * 0.8 or greater.



Оплатите лицензию в личном кабинете Select your Budget-Plan Express licenses and pay in your personal account - in rubles or other currency






Share:
Copyright © 2007-, support@strategic-line.ru: send a message