Credit products. Practical planning

Before choosing financing - it doesn't matter if it is loans, investments, shares in capital - you need to find out the amount of financing. To do this, you need to look at the cash flow chart at the end of the period in the Cash Flow table. The chart is called from the line "balance at the end of the period" from the context menu (right mouse button).

See example graph:

Commentary on the chart. The need for funding on the chart is below the zero line, in the negative zone. And the amount that you see on the scale on the left is the absolute need for funding. When choosing a loan financing, you only need to make an adjustment for interest. That is, the approximate amount of interest payments (loan cost) must be added to the funding requirement.

☛ Please note, unlike the standard planning horizon, the horizon for calculating project performance indicators is 15 years (180 months). This horizon allows you to correctly assess the performance indicators, since the investment does not always pay off in the first three years of the project. The horizon for calculating performance indicators is configured in the "general settings" form in the "investment analysis" tab.

1. We enter the form of planning loan products. First of all, let's add a new loan product using the "add" edit menu item, or press F2.

2. In the "loan product" tab, select the type of loan product, enter the loan amount, interest rate, start and end of loan payments. You can specify the start and duration in months (if necessary, you can specify exact dates).

When choosing the type of loan product, you choose the loan formula. The program presents three main formulas that are used in lending practice - "Standard", "annuity" and "consumer". These are, of course, conventional names used here to simplify the user experience. For detailed information on the formulas for calculating loan products, see the section "Calculation methods and algorithms".

3. Select the "settings" tab. Here you can clarify the parameters of the loan - the calculation step (in months or days), the method of accounting for the annual cycle, the limit percentage, the calculated interest (complex or simple) and the currency of the loan calculation.

Accounting for the annual cycle, the so-called "English", "French" or "German" (ACT / ACT, ACT / 360, 360/360) accounting methods, determine the number of days in a year for calculating a loan. The settings will be taken into account by the formulas, that is, if you specify the calculation step in days, then the formula will calculate the loan in days.

You can specify the system currency, for example, "ruble", or one of the three settlement currencies (dollar, euro, yuan). Recall that currencies are configured by the user in the general settings.

☛ Also, please note that all calculations of loans taken out in non-system currency will be displayed in the business plan tables in the system currency, for example, in rubles. The calculated currency is not indicated in the financial tables; it is converted into the system currency.

For foreign currency loans, exchange rate differences are determined for each month (the rate is calculated automatically, according to the table of rates, which the user fills in on the "financial analysis" page). You can view the calculation of a foreign currency loan, for example, in dollars, in the tab "table of payments".

4. In the next tabs - "basic payments", "other payments" and "interest rate adjustment" - you can add settings. Here you can specify the frequency of payments (for example, month, quarter, half year, and so on), deferred payments, various methods of calculating payments (for example, progressive payments), additional one-time and recurring payments, etc.

☛ Note! You can specify your payment schedule instead of using the program's calculations. To do this, in the payments table, you need to set the checkbox "Payments in accordance with the schedule" ... and after that the payments table can be edited.

5. Now it remains to perform the calculations. Here you can perform both test calculations and the final calculation of all loan products. Test calculations allow determining the optimal financing option at the stage of financing selection.

☛ Every time, after a test calculation, perform a "cleanup" of the table data.

After you have verified that your loan products meet the financing requirements, calculate the “financial plan” table for all loan products.

And finally, after the entire financial plan has been built, when the plans for "operating and financial leases", "reserves for future expenses" and "financial instruments" are determined ... then you can calculate the financial plan, and it will be included in the final calculation of a business plan (Data → Calculate, or F9).

☛ Important! It is recommended to calculate the financial and investment plan together with other financial plans - with profit and loss statements, cash flows and balance sheets.

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:

1. Debt payments
2. Interest payments
3. Paid debt
4. The balance of the debt;
5. Other payments
6. Losses (profit) from exchange rate differences
7. 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.

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:

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


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

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:

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

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

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:

1. "Standard" loan product involves the calculation of differential payments according to the formulas simple and complex percent.
2. "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.
3. "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.

Terms of credit: 9000 amount, loan term – 18 months rate – 12%, payments – monthly. To calculate the annuity by the standard formula for easy percent.

1. step. Add new product: the item "add" (F2).
2. 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".


3. step . Let's set a simple percentage - "Settings tab → Calculated percentage" Simple ".
4. step . Let's fix the amount - "Loan product" sum = 9 000 ".
5. step . Set the rate - "Loan product → Percent = 12% ".
6. step . We will fix the loan term - "Loan product → Duration = 18 ".
7. step . Let's establish the period of the beginning - "Credit product?" period ".


8. step . We will establish the frequency of payments - "Basic Payments", the frequency of payments, " month ."


9. step. Clear previous calculations. "Clear button → summary table → OK".
10. 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":

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:





1. step. Add new product: the item "add" (F2).
2. step. Select a product from the list – "Loan product → product Type → Consumer".
3. 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.


4. step. Set a simple percentage - the Settings tab → Calculated percentage → Complex .
5. step. Let's set the amount - "Loan product → Amount = 18 000 ".
6. step. Set the rate - "Loan product → Percent = 9% "
7. step. We will fix the term of the loan - "Loan product → Duration = 36 "
8. step. Set the start period - "Loan product", period "


9. step. We will establish the frequency of payments - "Basic Payments", Frequency of Payments → Month "


10. step. We clear the table from previous calculations. "Clear button → Final table, OK "
11. 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.

1. step. Add new product: the item "add" (F2).
2. step. Select a product from the list – "Credit product → product Type → Annuity".


3. step. Set simple percent "tab Settings → the Estimated percentage → Simple".
4. step. Set the amount of "Credit product → Amount = 150 000".
5. step. Set rate – "Loan product → Rate = 10%"
6. step. Set the term of the loan – "Loan product → Duration = 48"
7. step. Set the period to start, the "Credit product → период"
8. step. Set the frequency of payments – the "Principal payments → payment Frequency → year"


9. step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
10. 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.

1. step. Add new product: the item "add" (F2).
2. step. Select a product from the list – "Credit product → product Type → Annuity".


3. step. Set complex percent – "Settings → the Estimated percentage → Complex".
4. step. Set the amount of "Credit product → Amount = 200 000".
5. step. Set rate – "Loan product → Rate = 6%"
6. step. Set the term of the loan – "Loan product → Duration = 60"
7. step. Set the beginning of the period – "Loan product → period"
8. step. Set periodicity of payments – the "Principal payments → payment Frequency → year"


9. step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
10. 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.

1. step. Add new product: the item "add" (F2).
2. step. Select a product from the list – "Credit product → product Type → Standard".


3. step. Set simple percent – the "Settings → the Estimated percentage → Simple".
4. step. Set the amount of "Credit product → Amount = 150 000".
5. step. Set rate – "Loan product → Rate = 5%"
6. step. Set the term of the loan – "Loan product → Duration = 60"
7. step. Set the beginning of the period – "Loan product → period"


8. step. Set periodicity of payments – "Principal payments → payment Frequency → year"


9. step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
10. step. Calculate. The "Calculation button → list box item Selected → ОК"

Result: paid debt = 150 000, interest paid = 22 500, and final payment = 172 500:

1. step. Add new product: the item "add" (F2).
2. step. Select a product from the list – "Credit product → product Type → Standard".


3. step. Set simple percent – the "Settings → the Estimated percentage → Simple".
4. step. Set the amount of "Credit product → Amount = 200 000".
5. step. Set rate – "Loan product → Rate = 6%"
6. step. Set the term of the loan – "Loan product → Duration = 60"
7. step. Set the beginning of the period – "Loan product → period"


8. step. Set periodicity of payments – the "Principal payments → payment Frequency → year"
9. step. Set arithmetic progression – "Basic pay → Progressive payment → Arithmetic → 5000"


10. step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
11. step. Calculate. The "Calculation button → list box item Selected → ОК"

Result: paid debt = 200 000, interest paid = 39 000, the final payoff = 239 000:

1. step. Add new product: the item "add" (F2).
2. step. Select a product from the list – "Credit product → product Type → Standard".


3. step. Set complex percent – "Settings → the Estimated percentage → Complex".
4. step. Set the amount of "Credit product → Amount = 200 000".
5. step. Set rate – "Loan product → Rate = 10%"
6. step. Set the term of the loan – "Loan product → Duration = 60"
7. step. Set the beginning of the period – "Loan product → period"


8. step. Set periodicity of payments – the "Principal payments → payment Frequency → year"
9. step. Set geometric progression – "Basic pay → Progressive payment → Geometric → 5"


10. step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
11. step. Calculate. The "Calculation button → list box item Selected → ОК"

Result: paid debt = 200 000, interest paid = 61 950, the final payoff = 261 950:

1. step. Add new product: the item "add" (F2).
2. step. Select a product from the list – "Credit product → product Type → Standard".


3. step. Set complex percent – "Settings → the Estimated percentage → Complex".
4. step. Set the amount of "Credit product → Amount = 200 000".
5. step. Set rate – "Loan product → Rate = 10%"
6. step. Set the term of the loan – "Loan product → Duration = 60"
7. step. Set the beginning of the period – "Loan product → period"


8. step. Set periodicity of payments – the "Principal payments → payment Frequency → year"
9. 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.


10. step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
11. 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:

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:

1. step. Add new product: the item "add" (F2).
2. step. Set flag "Payments, in accordance with the schedule – Table payments → Set a flag".


3. step. Set the amount of "Credit product → Amount = 3 200 000".
4. step. Set rate – "Loan product → Rate = 5%".
5. step. Set the loan period is not less than 8 periods of account (conditional "last period" + 7 months) – "Loan product → Duration = 8".
6. step. Set the beginning of the period – "Loan product → Last period".


7. step. Add the payments in the repayment of the debt to the "table of payments", as shown in figure.
8. 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".


9. step. Clear (reset) the table from the previous calculations. "Clear button → the Final table → ОК"
10. 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"

Select your Budget-Plan Express licenses and pay in your personal account - in rubles or other currency