Taking the credit situation of Yifan Company as an example, this section discusses how to design a set of informative "loan management statistics table" with Excel (see the sample file "Table 5-4 loan management statistics table").
First, the basic framework and function display
"Loan Management Statistics" consists of loan account (see Figure 5-7 1) and loan account (see Figure 5-72).
Figure 5-7 1 credit account
Figure 5-72 loan ledger
The loan ledger will be responsible for recording the information of each loan and inquiring and warning the total amount payable in the future.
Our goal is: we only need to manually enter the credit contract number, financial institution, total credit amount and credit period in the credit ledger, and manually enter the basic information such as the credit contract number, loan contract number, loan principal, loan start date, term, annual interest rate and interest settlement period in the loan ledger to realize the following functions.
1. Main information functions
(1) Automatically displays the currently used credit line, available credit line and the total available credit line of all credits.
(2) Automatically display the current total loan amount, the interest payable and the total principal and interest payable of each (and all) loan this month.
2. Auxiliary information function
After the user enters a user-defined term (no more than 30 days), the total amount of principal, interest and principal and interest due in the next few days will be automatically prompted, and specific detailed records will be identified.
II. Basic Premises and Assumptions
1. The credit and financing period is months.
Under normal circumstances, the credit period and financing period provided by financial institutions for enterprises are calculated in whole months (or can be converted into whole months). For example, six months and one year (12 months). But there are also some cases that are calculated in days (can't be converted into a whole month). For example, 100 days. This section only considers the case that the relevant period can be calculated in months.
2. The loan principal shall be repaid in one lump sum on the maturity date.
In the repayment of loan principal, for most enterprises and financial institutions, it is still customary to use a one-time repayment method on the maturity date. Therefore, this section does not consider non-mainstream situations such as prepayment, installment repayment, deferred repayment, inability to repay or even fraudulent loans.
3. Only the monthly interest settlement period and the quarterly interest settlement period are considered, and the loan term is longer than the interest settlement period.
Monthly interest settlement and quarterly interest settlement are the most widely used interest settlement methods at present. In order to avoid discussing too complicated issues, this section only considers the above two methods of interest settlement. When the loan term is less than 3 months, the interest can only be settled monthly.
4. Loan maturity date and interest settlement rules
What needs to be declared is that the following interest settlement rules are the current general rules of financial institutions in China, and they are not hypothetical. If there are changes in policies, let's downgrade them to assumptions.
(1) Maturity date of loan (credit).
The maturity date of loan (credit) is different from that of time deposit. The maturity date of the deposit is generally the year (month) of the other party, and the maturity date of the loan is generally the day before the year (month) of the other party. For example, the start date is also 20 13 10, and the term is one year. The maturity date of time deposit is 201412, and the maturity date of loan is generally 20 13 12.
(2) Interest settlement date.
1) Monthly interest settlement and interest payment: the 20th of each month is the interest settlement date, and the interest will be paid the next day.
That is, the loan interest from 2 1 last month to 20/this month is paid on 2 1 this month, and so on. Of course, the premise is that the loan start date is earlier than 2 1 of last month and the loan maturity date is later than the 20th of this month.
2) Interest shall be settled quarterly. The 20th day of the last month of each quarter is the interest settlement date, and interest shall be paid the next day.
That is, the loan interest from 2 1 day of last quarter to 20/day of this quarter is paid on 2 1 day of this quarter. Of course, the premise is that the loan start date is earlier than 2 1 of last quarter and the loan maturity date is later than 20th of last quarter.
3) On the maturity date of the loan, settle and pay the unpaid interest.
(3) When calculating the daily interest, the whole year is calculated as 360 days.
Readers with experience in fund management should find a phenomenon: the total interest actually paid by a loan is always slightly larger than the total interest calculated according to the loan interest rate. This is because the daily interest used for each interest settlement during the loan period is calculated as 360 days a year, but there are 365 (or 366) days in the whole year. In this way, the actual annual interest paid is equal to the loan principal × annual interest rate ×(365÷360).
(4) Interest bearing start and end dates.
From the rules of loan maturity date, it can be seen that the loan interest is calculated according to the number of days actually occupied by the funds (rather than the fixed deposit interest is calculated according to the mode of counting the head and not counting the tail), so the calculation of the value date has two principles: the value date is the later of the loan start date and the last interest settlement date; The interest-bearing deadline is the earlier of the current interest settlement date and the loan maturity date.
Specifically, when the loan start date is earlier than the day after the previous interest settlement date, if the loan maturity date is later than the current interest settlement date, the start and end date of the current interest is from the day after the previous interest settlement date to the current interest settlement date; If the maturity date of the loan is less than the interest settlement date of the current period, the start and end date of the current interest is from the day after the interest settlement date of the previous period to the loan maturity date.
For example, at present, it is July 1 1, 20 14, 10/4 to repay a loan, and the interest will be settled monthly for one year. Then the loan start date (20 14 15 10 month) is earlier than the previous interest settlement date (2014 June 2 14) and the loan maturity date (2015/kloc)
If the above loan is for half a year and the maturity date of the loan (20 14, 14) is earlier than the current value date (20 14, July 20th), the current value date is 2014,21~ 20.
When the loan start date is later than the next day of the previous interest settlement date, and the loan maturity date is later than the current interest settlement date, the current interest-bearing start date is from the loan start date to the current interest settlement date; If the maturity date of the loan is less than the current interest settlement date, the start and end date of the current interest is from the loan start date to the loan maturity date. The latter case only occurs when the loan term is less than one month, so it is a small probability event.
For example, the current date is 20 1 1, July 20 14, and June 28, 20 14 recorded a loan, with interest paid monthly for one year. Then the loan start date (June 28th, 20 14) is later than the day after the last interest settlement date (June 20th, 20 14), and the loan maturity date (June 27th, 20 15) is later than the current interest settlement date (July 20th, 20 14).
Third, matters needing attention
In order to facilitate subsequent expansion and query, attention should be paid to keeping the names uniform when manually entering relevant parameters (for example, when the name of a bank is involved in "financial institution", the name specification should be unified to avoid multiple names of the same financial institution).
In the first section of this chapter, we have mentioned the use of order setting in data validity to prevent the occurrence of "the same name and surname". For example, in this way, the interest settlement period in the loan ledger can be set as "month" and "quarter". But for financial institutions, this method is not very easy to use, because the scope of this parameter is not very limited, and you can't predict whether there will be new institutions, so you can't design alternatives. Therefore, we can only emphasize the norms when filling in information here.
Fourth, knowledge point equipment
Before reading the following contents in this section, please confirm whether the relevant knowledge points in Figure 5-73 have been basically equipped in your brain.
Before explaining the case in this section, we insert a knowledge point that will be used soon: the definition and use of names.
Generally speaking, the definition name is similar to "let X= some information" commonly used in mathematics, where "some information" can be a constant, a cell range or a formula. After the setting is completed, we can use "X" to realize the function of "some information" given to it. Using names can simplify complex formulas and make them easier to understand and maintain. Below, let's give an example to explain it briefly.
Figure 5-73 Related Knowledge Points
In the order in Figure 5-74, we use the definition name to set the formula of total amount as "total amount". The operating steps are as follows: Formula tab → Define name group → Define name (see Figure 5-74).
Figure 5-74 Use of Definition Names
At this point, we can use the defined name to set the total amount formula (see Figure 5-75).
The formula of D3:D5 unit area is: = total amount.
Figure 5-75 Replace the traditional formula with the defined name
Using the definition name is similar to setting auxiliary parameters, which can simplify the lengthy compound formula step by step and enhance the understandability of the compound formula. There will be specific applications below.
Five, the main information formula design method
After a general understanding of the framework, functions and related matters needing attention of "loan management statistics table", this paper discusses the design of "loan management statistics table" through the case of Yifan company's loan.
Case 5-4 As of July 1 1, 2065438, Yifan Company granted credit to financial institutions, as shown in Figure 5-76, and the loans granted were all in RMB.
Figure 5-76 Yifan Company Credit Information Table
Figure 5-77 Yifan Company Loan Information Table
Combined with the structure of "loan management statistics table", the relevant design methods are as follows.
1. Design method of credit account formula (1)
(1) Instant date display (D2 cell).
To make the table always show the date of "today", we only need to use a simple date formula under the condition of ensuring that the date of the computer system is correct (see Figure 5-78).
The formula of D2 cell is: = Today ()
Figure 5-78 forever set the formula of "today"
(2) Credit maturity date (H4:H8 unit area).
The formula of this parameter is also relatively simple, and we have seen a similar situation in the third section of this chapter. It's just that the due date in this table needs to be accurate to a specific date, not just one month. It should be noted that 1 needs to be subtracted from the daily parameter because the loan maturity date is not calculated by year (month).
The formula of H4 cells is:
=IF (or (F4= "",G4= ""), DATE (year (G4), month (G4)+F4, day (G4)- 1))
After filling in the column, you can complete the setting of the credit term formula (see Figure 5-79).
At present, there are three parameters in the credit ledger, namely, used credit line (D4:D8 cell area), available credit line (E4:E8 cell area) and currently available credit line (G2 cell), and these parameters have not been designed. Since these three figures can only reflect their functions after the loan business happens, we will come back to discuss them later.
2. Design method of loan subsidiary ledger formula
(1) Instant date display (cell A3).
There's nothing to say about this. The formula of A3 cell is: =TODAY ()
Figure 5-79 Credit Maturity Formula
(2) Financial institutions (C7:C 16 unit area).
As the financial institution is the existing data in the credit ledger, there is no need to enter it repeatedly, and the VLOOKUP function is used to find the reference. At the same time, we also need to consider that when a nonexistent credit contract is accidentally entered (there is no matching financial institution at this time), we should prompt the formula in time.
The formula for cell C7 is:
= if (a7 = "","",if error (vlookup (a7, credit account! $ A $4: $ B $8,2,0), "Credit contract number does not exist").
After filling in the column, the matching of financial institution information can be completed (see Figure 5-80).
Figure 5-80 VLOOKUP function automatically matches related information
If the user still needs to check the credit maturity date, total credit amount and other information under the corresponding credit contract in the loan ledger, the same method should be adopted, and the details are not repeated here.
(3) Loan maturity date (G7:G 16 unit area).
This idea is the same as the formula of maturity in the credit ledger.
The formula of G7 cell is:
=IF (or (E7= "",F7= ""), DATE (year (F7), month (F7)+E7, day (F7)- 1))
After filling in the column, the loan maturity date can be calculated (see Figure 5-8 1).
Figure 5-8 1 loan maturity formula
(4) Interest calculation start date of this month (T7:T 16 cell area).
Next, enter the formula design of data such as principal and interest payable this month and interest payable this month. We know that an important prerequisite for calculating interest is to determine the number of days for calculating interest. As the interest-bearing deadline of this period is clear (interest settlement date or loan maturity date this month), we need to see how to calculate the interest-bearing start date this month.
The value date of this month can be understood as the day after the last interest settlement date before this month (called "the last interest settlement date"). Because it is only an auxiliary parameter, in order to avoid the auxiliary parameter affecting the clear reading of important information, we put it in the area (T column) outside the main table.
From the interest rules, we know that the value date of this month will be different according to the loan start and end date and interest settlement period, as shown in Figure 5-82.
Figure 5-82 has actually provided us with the idea and logic of formula design. In order to avoid the final formula being too long, before designing the formula, we can set the definition names of several commonly used indicators (see Figure 5-83).
Figure 5-82 Various situations of the interest-bearing start date of this month
Figure 5-83 Parameters defining names in this section (1)
Step 1: Clean up the environment.
As can be seen from Figure 5-8 1, as long as one of the three parameters of term, loan start date and interest settlement period is missing, the value date of this month cannot be calculated. So we come to the conclusion that:
The first step formula of T7 cells is:
=IF (or (E7= "",F7= "",I7= "",enter the second step)
Step 2: Make sure that the loan has been repaid before this month.
Since we assume that all loans will be paid off in one lump sum on the maturity date, the key of the second step is to judge whether the loans have been paid off before this month.
If the maturity date of the loan is not later than the last day of last month (G7
The second step formula of T7 cells is:
= IF(G7 & lt； =EOMONTH (interest settlement date of this month,-1), "closed", enter the third step)
Note that the cell in the third row is involved here, and you need to set an absolute reference to ensure that subsequent columns are filled correctly. The following formula design should also follow this principle.
Step 3: Determine the monthly interest settlement method.
Because the day after the last interest settlement date, there is a difference between the monthly interest settlement mode and the quarterly interest settlement mode. In monthly interest settlement mode, the day after the last interest settlement date is 2 1 of last month, and in quarterly interest settlement mode, the day after the last interest settlement date is 2 1 of last quarter. Therefore, after entering the third step, we can only discuss it step by step with the interest settlement cycle.
The third step formula of T7 cell:
=IF(I7= "monthly", if (F7
Step 4: Determine the interest settlement in quarterly interest settlement mode.
Judging from the previous steps, entering the fourth step means that it is an outstanding loan under the quarterly interest settlement mode.
Quarterly interest settlement, like monthly interest settlement, is still to judge the relationship between the loan start date and the last interest settlement date. However, the last interest settlement date of the quarterly interest settlement mode is a little more troublesome, and it must be the interest settlement date of the last quarter.
So, how do we determine the end of the quarter? Find a logical rule-the number of months at the end of a quarter must be an integer multiple of 3.
So, we can see this rule:
The number of months at the end of the quarter is divided by 3, and the remainder is 0.
The number of the first month after the quarter is divided by 3, and the remainder is 1.
The second month after the end of the quarter is divided by 3, and the remainder is 2.
We will do the opposite of this law:
The first month after the end of the quarter minus 1 is the number of months at the end of last quarter.
The number of months after the end of the quarter minus 2 is the number of months at the end of last quarter.
The number of months at the end of the quarter minus 3 is the number of months at the end of last quarter.
The last month of last quarter can be calculated by subtracting the remainder of the current month divided by 3 from the current month. But there is one exception. When the remainder is 0, we need to subtract 3. Therefore, we need to use the MOD function to calculate the remainder.
The fourth step formula of T7 cells is:
= IF(F7 & lt； =DATE (year (interest settlement DATE this MONTH), month (interest settlement date this month) -if (mod (interest settlement date this month), 3) = 0,3, mod (interest settlement date this month), 3), day (interest settlement date this month), date (year (interest settlement date this month).
Synthesize the formula of the above steps.
The complete molecular formula of T7 cell is:
=IF (or (E7= "",F7= "",I7= "",if (G7 <; =EOMONTH (interest settlement date of this month,-1), "closed", IF(I7= "monthly", if (F7
After filling in the column, the interest-bearing start date of each loan in this month can be obtained (see Figure 5-84, Figure 5-84a is an example in June, and Figure 5-84b is an example in July).
Figure 5-84 Formula for Interest Date Starting in this Month
(5) Interest payable this month (K7:K 16 unit area).
Interest payable this month refers to the interest actually paid this month, and its calculation formula is:
Interest payable this month = (loan principal × annual interest rate ÷360)× interest-bearing period days = daily interest × interest-bearing period days.
Based on the basic assumptions in this section and the information in the prerequisite section, we know that the interest period will be different according to the loan start and end dates, as shown in Figure 5-85.
Figure 5-85 Various situations during this month's interest-bearing period
In addition, we should also consider:
1) Interest payable this month includes interest paid on the settlement date and interest paid on the maturity date of this month.
Interest payment on this month's interest settlement date refers to the interest paid on this month's interest settlement date, and interest payment on this month's maturity date refers to the interest paid on the loan due to this month's maturity. When the maturity date of the loan happens to be the interest settlement date of the current month, we assume that all interest will be paid on the interest settlement date.
2) For loans with quarterly interest settlement mode, interest is only paid on the interest settlement date at the end of the quarter.
Accordingly, our design steps are as follows.
Step 1: Clean up the environment.
As can be seen from the relevant information in Figure 5-85, when the loan maturity date and interest settlement period are empty and the loan maturity date is before this month (at this time, the value date of this month is displayed as "settled"). The interest payable this month is 0.
The first step formula of K7 cells is:
=IF (or (G7= ",I7=", T7= ",0, enter the second step)
Step 2: The loan is due this month and the interest payable this month.
Judging whether the loan maturity date is this month is also a question of comparing whether the loan maturity date and the interest settlement date of this month are in the same year and the same month. In the current month, the interest period of interest payable in this month is from the day after the value date of this month to the loan maturity date.
It should be noted that, since both the start date and the end date of the interest period need interest, 1 should be added after subtracting the two dates when calculating the interest days.
For example, the start date of interest calculation is 20 1 41,and the end date of interest calculation is 20 14 14 3, which lasts for 3 days, but the difference between the two dates is 2, so it is necessary to add1.
The second step formula of K7 cells is:
= If (12 * year (G7)+ month (G7) = 12 * year (interest settlement date of this month)+month (interest settlement date of this month), round (D7 * H7 * (G7-T7+1)/366.
Step 3: Interest payable for loans due this month.
If the loan expires after this month, the interest payable this month needs to be differentiated according to the interest settlement cycle mode. The loan is in quarterly interest settlement mode, and this month is not the end of the quarter (AND(I7= "quarter ",mod (month (a3), 3).
The third step formula of K7 cells is:
=IF(AND(I7= "quarter", MOD (month (interest settlement date of this month), 3)
Synthesize the formula of the above steps.
The complete formula of K7 cells is:
=IF(OR(G7= ",I7=", T7= ",settled), 0, if (12 * year (G7)+ month (G7) = 12 * year (interest settlement date of this month)+month (interest settlement date of this month) &
After filling in the column, you can get the interest payable for each loan this month (see Figure 5-86).
From the perspective of space alone, the formula of interest payable this month does not seem complicated. However, it should be noted that if there is no interest-bearing start date (cell T7) in this month, all cell parameters related to column T in this month's interest payable formula must be replaced with the complete formula of interest-bearing start date in this month. In that case, the scale will not be very large.
Just like climbing Mount Everest, we need to set up several base camps on the way. When we find that there are many logical levels and branches in the setting of a formula, we should immediately enable some intermediate information (for example, when calculating the interest payable this month, the interest calculation start date of this month belongs to an intermediate information) as the base camp, which will gradually simplify the formula design. In order to avoid brain damage in the process of conquering the peak of the formula. Of course, defining a name can also be seen as a way to establish a base camp.
Figure 5-86 Interest payable formula for this month
(6) Pay interest on the settlement date of this month (L7:L 16 unit area) and the maturity date of this month (M7:M 16 unit area).
The reason why we should distinguish the interest payable area of this month according to its payment time is because in general, a large amount of interest will be paid on the interest settlement day of the month. Therefore, it is necessary for us to pay close attention to the interest payment on the settlement date. Moreover, when we realize the function of querying the principal and interest payable in the next few days, we also need to distinguish the time of interest payment.
According to the above relationship, we can know that:
Interest payment on the settlement date of this month+interest payment on the maturity date of this month = interest payable this month.
Therefore, in the case that the interest payable this month has been calculated, we only need to know one of the two parameters, that is, the interest payment on the settlement date and the interest payment on the maturity date, and the other can be solved by inverse algorithm. In this case, we will analyze and discuss the formula for paying interest on the settlement date.
According to the interest rules, the relevant influencing factors of interest payment on interest settlement day are shown in Figure 5-87.
Figure 5-87 Relationship between Maturity Date and Interest Settlement Period on Maturity Date
Although there are six situations in Figure 5-87, there are actually only three possibilities for this month's interest settlement date: there is no interest, and the interest payment is equal to the interest payable this month, and the interest payment is equal to the interest payable from the day after the previous interest settlement date to this month's interest settlement date. According to the above logic, our design steps are as follows:
Step 1: No interest will be calculated on the interest settlement date of this month.
When one of the following circumstances occurs, no interest will be paid on the interest settlement date of this month:
1) The interest payable this month is 0.
2) The maturity date of the loan is before the interest settlement date of this month.
3) In the quarterly interest settlement mode, this month is not the end of the quarter.
The first step formula of L7 cells is:
= If (or (K7=0, G7 < interest settlement date of this month, and (I7= "quarter", MOD (month (interest settlement date of this month), 3).
Step 2: The interest paid on this month's interest settlement date is equal to the interest payable this month.
If the maturity date of the loan is later than this month, it means that interest will not be due this month. Therefore, the interest paid on this month's interest settlement date is equal to the interest payable this month. Otherwise, it means that the maturity date of the loan is after the interest settlement date of this month and before next month. At this time, the interest payment on the interest settlement date of this month is equal to the interest payable from the day after the previous interest settlement date to the interest settlement date of this month.
The second step formula of L7 cell is:
= IF(G7 & gt； EOMONTH (interest settlement date of this month, 0), K7, ROUND(D7*H7* (interest settlement date of this month-T7+1)/360,2))
Combine the above formula.
Complete formula of L7 cell:
= If (or (K7=0, G7 < interest settlement date of this month, and (I7= "quarter", MOD (month (interest settlement date of this month), 3).
At this point, we will calculate and set the interest payment formula on the due date.
The formula of M7 cells is =K7-L7.
After filling in the column, you can get interest payment on the settlement date and interest payment on the maturity date (see Figure 5-88).
Figure 5-88 Interest Payment Formula on Settlement Date and Interest Payment Formula on Maturity Date
(7) Principal and interest payable this month (J7:J 16 unit area).
The principal and interest payable this month is the sum of the loan principal and interest payable this month. Among them, the interest payable this month has been listed in K, so the key to the problem is the principal payable this month.
If the loan principal needs to be paid this month, it means that the loan will expire this month, which in turn uses the judgment of whether the year when the loan expires is equal to the current year. If they are equal, the principal and interest payable this month is equal to the loan principal plus the interest payable this month, otherwise only the interest payable this month will be considered.
The formula of cell N7 is:
= if (G7 = ",0, if (12 * year (G7)+ month (G7) = 12 * year (interest settlement date of this month)+month (interest settlement date of this month), D7+K7))
After filling in the column, you can get the principal and interest payable this month (see Figure 5-89).
Figure 5-89 Formula of principal and interest payable this month
(8) Current total loan (B3 unit).
The current total loan amount is the sum of the loan principal that has not yet expired, and the calculation logic is naturally to judge whether the loan maturity date is later than "today". This of course depends on the SUMIF function (see Figure 5-90).
The formula of B3 unit is = sumif (G7: G 16, ">" & a3, D7:D 16).
Figure 5-90 Current Loan Amount Formula
(9) Interest payable this month (cell C3) and principal and interest payable this month (cell D3). This goes directly to the summation function.
The formula of C3 cell is =SUM(K7:K 16).
The formula of D3 cell is =SUM(J7:J 16).
Of course, you can also set the interest payable on the interest settlement date of this month to meet the management needs, so I won't go into details here.
At this point, the main information formula of the loan ledger has been set.
Next, we will go back and finish the unfinished projects in the credit ledger.
3. Design method of credit account formula (Part 2)
(1) Used credit line (D4:D8 community).
Used line refers to the loan amount obtained in the relevant credit contract but not yet returned. This definition contains two meanings. First, it is necessary to identify the loans under its own contract, and it is not allowed to count the loans under other contracts into its own name; Secondly, it should be clear whether the relevant loans are still in use, because the loans that have been returned due will no longer occupy the credit line.
In this way, we know that the amount used is a multi-conditional summation problem.
D4 cell formula:
=SUMIFS (loan ledger! $D$7:$D$ 16, loan ledger! $A$7:$A$ 16, A4, loan ledger! $G$7:$G$ 16，" >& amp$D$2)
After filling in the column, the current used line of each credit contract can be obtained (see Figure 5-9 1).
Figure 5-9 1 used credit line formula
(2) Available lines (E4:E8 cell area).
The first impression that the available credit line gives us is naturally the total credit line minus the used credit line. However, this can only be the first feeling, and our second feeling should be immediately thought of. The premise of this logic is that the credit contract has not expired. In this way, our formula is logical.
The formula of E4 cell is = if (H4 >; 2 USD, C4-D4, 0 USD)
After column padding is performed, available quotas can be obtained (see Figure 5-92).
Figure 5-92 Available Credit Limit Formula
(3) Current available credit line (G2 cell).
This parameter is mainly used to summarize the available quotas.
The formula of G2 cell is =SUM(E4:E8).
At this point, the main information formula of the credit subsidiary ledger has also been completely completed (see Figure 5-93).
Figure 5-93 Main Information of Credit Account