Credit products. Practical planning
Calculation of loan products
In the edit form of loan products can be set and perform the credit calculation, which will be part of the "financial plan" that shows all the payments associated with the repayment and servicing the debt. Calculated following lines:
- Debt payments
- Interest payments
- Paid debt
- The balance of the debt;
- Other payments
- Losses (profit) from exchange rate differences
- Plan-actual deviations (user revision).
The program allows you to create up to 20 of loan products, each of which stores information about the parameters individual settings.
The final calculation of all lending products created by a user is displayed in the summary table "financial plan". The result of the calculations for each loan product to be stored in the "Table of payments". In the same table you can enter your own credit data calculated in another program.
Purpose of the "payments" table
With the "paytable" can work in two modes – "calculation" and "data editing". Modes are switched depending on the flag set "Payments, in accordance with the schedule":
Example of a non-editable table (the flag is disabled, available for editing),
is filled in when calculating:
Regardless of the setting of the flag, to edit is always available string PAD (plan-fact deviations) for updates.
Example of an edited table (the flag is set, it is available for editing),
edited by user:
If you use an editable table (i.e. set the flag "Payments schedule"), for the calculation it is necessary to specify 2 parameters:
- Amount of financing.
If the amount of financing does not coincide with the amount of repayment of debt, the program will warn about the error: "Payments on the product do not correspond to the amount of debt."
- Funding period (start month, end month and duration).
From the "Payment table" only those payments that are included in the specified period will be considered.
Other parameters for the edited table do not matter.
Execution of test and final calculations
To calculate a single product: "Calculation button → Selected list element → OK"
To perform the calculation of all loan products, you need to choose the appropriate form element: "Calculation button → Final calculation (calculate all) → OK"
After clicking "Calculation", the program will offer
two options of calculation, as shown in the figure below:
- The selected item in the list.
The selected product (from the list) will be calculated. Testing credit methods is an opportunity to evaluate a financial model and choose the most appropriate loan scheme.
☛ Note, before the next calculation, it is necessary to delete the old data - the "Clear" button.
- Final calculation (calculate all).
All the products in the list are calculated sequentially. If the flag "Payments, in accordance with the schedule" is set, the calculation is not performed, but the data from the "payment table" is used.
Before the next calculation, it is necessary to delete the old data - the "Clear" button.
Planning credit products
In "Budget-Plan Express" it is possible to choose three types of "loan product" from the drop-down list: Standard, Annuity, and Consumer. When choosing a product type, you choose the formula and algorithm by which payments will be calculated:
- "Standard" loan product involves the calculation of differential payments according to the formulas simple and complex percent.
- "Annuity" loan product is equal to the sum of the (usually monthly) payments that include the amount of accrued interest on loans and principal amount. Uses two formulas to calculate annuities – using simple and complex percent.
- "Consumer" loan product as a "Standard" is calculated according to standard formulas, simple and complex percent. However, the loan is payable in equal installments – annuities that are calculated by simple division of the sum of all payments (principal and interest) by the number of payments.
Examples of planning for annuities
Example 1. Calculation of annuities according to the standard formula. The calculation formulas loan product "Consumer".
Terms of credit: 9000 amount, loan term – 18 months rate – 12%, payments – monthly. To calculate the annuity by the standard formula for easy percent.
- step. Add new product: the item "add" (F2).
- step. Choose a product from the list – "Loan product → product Type → Consumer". After you select a new item from the list, re-setting to "default".
- step . Let's set a simple percentage - "Settings tab → Calculated percentage" Simple ".
- step . Let's fix the amount - "Loan product" sum = 9 000 ".
- step . Set the rate - "Loan product → Percent = 12% ".
- step . We will fix the loan term - "Loan product → Duration = 18 ".
- step . Let's establish the period of the beginning - "Credit product?" period ".
- step . We will establish the frequency of payments - "Basic Payments", the frequency of payments, " month ."
- step. Clear previous calculations. "Clear button → summary table → OK".
- step. Calculate. "Calculation button → the Selected list element → OK".
Result: paid debt = 9 000, interest paid = 1 620, final payment = 10 620, annuity payment = 590.
A fragment of the calculation of the credit the table "financial plan":
Example 2. Calculation of annuities according to the standard
formula and in another currency. The calculation formulas loan product the "Consumer".
The terms of the loan: the amount of 18 000, loan term – 36 months interest rate of 9%, payments – monthly. To calculate the annuity by the standard formula for complex percent. To calculate the currency (e.g., in dollars).
Let forecast the dollar following:
- step. Add new product: the item "add" (F2).
- step. Select a product from the list – "Loan product → product Type → Consumer".
- step. Select the settlement currency, the "General Settings → currency → Dollar".
The settlement currency must be added to the currency list. For this currency, the user must set the rate for each month.
- step. Set a simple percentage - the Settings tab → Calculated percentage → Complex .
- step. Let's set the amount - "Loan product → Amount = 18 000 ".
- step. Set the rate - "Loan product → Percent = 9% "
- step. We will fix the term of the loan - "Loan product → Duration = 36 "
- step. Set the start period - "Loan product", period "
- step. We will establish the frequency of payments - "Basic Payments", Frequency of Payments → Month "
- step. We clear the table from previous calculations. "Clear button → Final table, OK "
- step. We calculate. "Calculate button → Selected item in the list → OK "
Result: paid debt = 18 000, interest paid = 5 328 total payments = 23 328, annuity payment = 648.
A fragment of the calculation of the loan in accordance with the conditions in the "table of payments":
Please note, the payment of a debt calculated in dollars (on the "paytable" specified currency – USD), however, at the time of payments exchange rate differences are calculated in the base currency (in rubles). Also, in the "financial plan" all the calculations presented in the base currency (in rubles). Thus, in the calculation of the loan in dollars, "a financial plan" will be recalculated into rubles at the forecast rate. Exchange differences are recorded in a separate line and be reflected in the "statement of profit and loss" as unrealized expenses (income).
Result:: the paid debt = 1 217 520, interest paid = 360 396 total payments = 1 577 916, annuity payment = 43 831. In addition, the calculated "loss (gain) from foreign exchange differences" for each month.
A fragment of the result of the conversion of the loan in the system currency (rubles):
☛ Note, the exchange rate difference is negative, that is, in this case it was considered the foreign exchange gain, as the ruble (in this demo) has been strengthened in a period of 36 months from 67,64 to 49.00 in the last period.
Example 3. Calculation of annuity, taking into account the calculation of interest-bearing money, for simple percent. Calculation of the loan product "Annuity".
The terms of the loan: the amount of 150 000, loan term 4 years interest rate 10%, payment once a year. To calculate the annuity, subject to accrual of interest of money,
easy percent.
- step. Add new product: the item "add" (F2).
- step. Select a product from the list – "Credit product → product Type → Annuity".
- step. Set simple percent "tab Settings → the Estimated percentage → Simple".
- step. Set the amount of "Credit product → Amount = 150 000".
- step. Set rate – "Loan product → Rate = 10%"
- step. Set the term of the loan – "Loan product → Duration = 48"
- step. Set the period to start, the "Credit product → период"
- step. Set the frequency of payments – the "Principal payments → payment Frequency → year"
- step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
- step. Calculate. The "Calculation button → list box item Selected → ОК"
Result: annuity payment = 45 652.
Calculation by this formula is more fair for the borrower. If a calculation by the standard formula (Example 1), the result will be the following:
Result: annuity payment = 500 52.
You can also specify a delay of payments, but in the case of annuity payments, deferred interest and debt must be the same. If necessary, the program will adjust the annuity in the recent period the amount of the debt.
The following formula for the calculation of the annuity takes into account complex percent and is most common in banking practice.
Example 4.
Calculation of annuity, taking into account the accrual of interest-bearing money, for complex percent. Calculation of the loan product "Annuity".
The terms of the loan: the amount of 200 000, loan term – 5 years rate – 6%, payment once a year. To calculate the annuity, subject to accrual of interest of money,
complex percent.
- step. Add new product: the item "add" (F2).
- step. Select a product from the list – "Credit product → product Type → Annuity".
- step. Set complex percent – "Settings → the Estimated percentage → Complex".
- step. Set the amount of "Credit product → Amount = 200 000".
- step. Set rate – "Loan product → Rate = 6%"
- step. Set the term of the loan – "Loan product → Duration = 60"
- step. Set the beginning of the period – "Loan product → period"
- step. Set periodicity of payments – the "Principal payments → payment Frequency → year"
- step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
- step. Calculate. The "Calculation button → list box item Selected → ОК"
Result: paid debt = 200 000, interest paid = 37 397 total payment = 237 397, annuity payment = 47 479.
Calculation by this formula is more fair for the borrower. If we calculate according to the formula of Example 2, the result will be the following:
Result: annuity payment = 529 53.
Examples of planning payments, depending on the balance of the debt
Example 5. To calculate the credit for simple percent. The calculation formulas loan product Standard.
The terms of the loan: the amount of 150 000, loan term – 5 years interest rate of 5%, the payment at the end of the year. To calculate the credit for easy percent.
- step. Add new product: the item "add" (F2).
- step. Select a product from the list – "Credit product → product Type → Standard".
- step. Set simple percent – the "Settings → the Estimated percentage → Simple".
- step. Set the amount of "Credit product → Amount = 150 000".
- step. Set rate – "Loan product → Rate = 5%"
- step. Set the term of the loan – "Loan product → Duration = 60"
- step. Set the beginning of the period – "Loan product → period"
- step. Set periodicity of payments – "Principal payments → payment Frequency → year"
- step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
- step. Calculate. The "Calculation button → list box item Selected → ОК"
Result: paid debt = 150 000, interest paid = 22 500, and final payment = 172 500:
Example 6. Payment of the debt, with the changing arifmeticheskoe progression. The calculation formulas loan product Standard.
The terms of the loan: the amount of 200 000, loan term – 5 years rate – 6% payout – at the end of the year. To calculate the credit for easy percent, with an arithmetic progression = 5000.
- step. Add new product: the item "add" (F2).
- step. Select a product from the list – "Credit product → product Type → Standard".
- step. Set simple percent – the "Settings → the Estimated percentage → Simple".
- step. Set the amount of "Credit product → Amount = 200 000".
- step. Set rate – "Loan product → Rate = 6%"
- step. Set the term of the loan – "Loan product → Duration = 60"
- step. Set the beginning of the period – "Loan product → period"
- step. Set periodicity of payments – the "Principal payments → payment Frequency → year"
- step. Set arithmetic progression – "Basic pay → Progressive payment → Arithmetic → 5000"
- step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
- step. Calculate. The "Calculation button → list box item Selected → ОК"
Result: paid debt = 200 000, interest paid = 39 000, the final payoff = 239 000:
Example 7. Payment of the debt, with the changing geometricheskoi progression. The calculation formulas loan product Standard.
The terms of the loan: the amount of 200 000, loan term – 5 years interest rate of 10%, payments at the end of the year. To calculate the credit for complex percent, with a geometric progression = 5%.
- step. Add new product: the item "add" (F2).
- step. Select a product from the list – "Credit product → product Type → Standard".
- step. Set complex percent – "Settings → the Estimated percentage → Complex".
- step. Set the amount of "Credit product → Amount = 200 000".
- step. Set rate – "Loan product → Rate = 10%"
- step. Set the term of the loan – "Loan product → Duration = 60"
- step. Set the beginning of the period – "Loan product → period"
- step. Set periodicity of payments – the "Principal payments → payment Frequency → year"
- step. Set geometric progression – "Basic pay → Progressive payment → Geometric → 5"
- step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
- step. Calculate. The "Calculation button → list box item Selected → ОК"
Result: paid debt = 200 000, interest paid = 61 950, the final payoff = 261 950:
Example 8. Repayment of debt on a loan - equal payments. Calculation according to the formulas of the credit product "Standard".
The terms of the loan: the amount of 200 000, loan term – 5 years interest rate of 10%, payments at the end of the year. To calculate the credit for complex interest, the payment of a debt by equal payments = 30 000.
- step. Add new product: the item "add" (F2).
- step. Select a product from the list – "Credit product → product Type → Standard".
- step. Set complex percent – "Settings → the Estimated percentage → Complex".
- step. Set the amount of "Credit product → Amount = 200 000".
- step. Set rate – "Loan product → Rate = 10%"
- step. Set the term of the loan – "Loan product → Duration = 60"
- step. Set the beginning of the period – "Loan product → period"
- step. Set periodicity of payments – the "Principal payments → payment Frequency → year"
- step. Set payments equal payments – "Basic pay → Return a fixed amount → 30 000"
If necessary, the program will adjust the amount of the last period – the amount of the debt.
- step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
- step. Calculate. The "Calculation button → list box item Selected → ОК"
Result: paid debt = 200 000 (a 5 year paid 80 000), interest paid = 70 000, the final payoff = 270 000:
Edit the table of loan products
Example 9. To add a loan, partially repaid in the last period. To make information about the loan in the "table of payments" need to set the flag "Payments, in accordance with the schedule".
Suppose the company has to repay the loan, and you must add information on payments in past periods and information on the forthcoming payments.
Data on the credit: amount of the loan 3 200 000, with a rate of 5%. The amount of debt is 1 163 636, the amount of paid interest is 98 300. The remaining part of the loan must be repaid within 7 months under the scheme:
- step. Add new product: the item "add" (F2).
- step. Set flag "Payments, in accordance with the schedule – Table payments → Set a flag".
- step. Set the amount of "Credit product → Amount = 3 200 000".
- step. Set rate – "Loan product → Rate = 5%".
- step. Set the loan period is not less than 8 periods of account (conditional "last period" + 7 months) – "Loan product → Duration = 8".
- step. Set the beginning of the period – "Loan product → Last period".
- step. Add the payments in the repayment of the debt to the "table of payments", as shown in figure.
- step. Add payments of interest repayment to the "table of payments", as shown in figure.
Total debt payments for a specified period should coincide with the amount of the loan, otherwise, this loan product will be ignored by the program with the error message: "Payment does not correspond to the amount of the debt".
- step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
- step. Calculate. The "Calculation button → list box item Selected → ОК"
Then, included in the "table of payments" data will become part of a PivotTable "financial plan" that shows all the payments associated with the repayment and servicing the debt.
To perform the calculation of all loan products, you need to choose the appropriate form element: "Button to count → the Final calculation (calculate all) → OK"