이 섹션에서는 Excel 을 사용하여 정보성이 강한 대출 관리 통계표 (샘플 파일 표 5-4 대출 관리 통계표 참조) 를 설계하는 방법에 대해 설명합니다.
첫째, 기본 프레임 워크 및 기능 데모
대출 관리 통계는 대출 계좌 (그림 5-7 1 참조) 와 대출 계좌 (그림 5-72 참조) 로 구성됩니다.
그림 5-7 1 신용 계정
그림 5-72 대출 원장
대출 장부는 각 대출에 대한 정보를 기록하고 미래의 미지급총액에 대한 조회와 경보를 할 책임이 있다.
Dell 의 목표는 신용 계약 번호, 금융 기관, 신용 총액, 신용 기간, 대출 계약 번호, 대출 계약 번호, 대출 원금, 대출 시작 날짜, 기간, 연간 이자율, 이자 기간 등의 기본 정보를 신용대출 장부에 수동으로 입력하기만 하면 다음과 같은 기능을 수행할 수 있다는 것입니다.
1. 주요 정보 기능
(1) 현재 사용된 신용 한도, 사용 가능한 신용 한도 및 모든 신용에 사용할 수 있는 총 신용 한도를 자동으로 표시합니다.
(2) 현재 대출 총액, 이번 달 각 (및 전체) 대출에 대한 미지급이자 및 미지급이자 총액을 자동으로 표시합니다.
2. 보조 정보 기능
사용자가 사용자 정의 기간 (30 일 이내) 을 입력하면 다음 며칠 동안 만료되는 원금, 이자 및 이자 합계를 자동으로 알려주고 특정 상세 레코드를 식별합니다.
둘째, 기본 전제와 가정
1. 신용 및 자금 조달 기간은 한 달입니다.
일반적으로 금융기관이 기업에 제공하는 신용기간과 융자기간은 모두 한 달 (또는 한 달로 환산할 수 있음) 로 계산됩니다. 예를 들어 반년 (6 개월) 과 1 년 (12 개월) 이 있습니다. 그러나 일별로 계산되는 경우도 있다 (전체 월로 변환할 수 없음). 예를 들면 100 일. 이 섹션에서는 관련 기간이 월 단위로 계산될 수 있는 상황만 고려합니다.
대출 원금은 만기일에 한 번에 상환해야합니다.
대출 원금 상환에 있어서 대부분의 기업과 금융기관에게는 여전히 만기일 일회성 상환 방식을 채택하는 데 익숙해져 있다. 그래서 이 섹션에서는 조기 상환, 할부 상환, 연기상환, 무력상환, 심지어 사기 등 비주류 상황을 고려하지 않는다.
3. 월이자 및 분기 이자 기간만 고려하며 대출 기간은 이자 기간보다 길다.
월결산 및 분기 결산은 현재 가장 널리 사용되는 결산 방식이다. 토론이 너무 복잡한 문제를 피하기 위해 이 섹션에서는 위의 두 가지 결합 방법만 고려합니다. 대출 기간이 3 개월 이하일 때는 월별로 이자를 받을 수밖에 없다.
4. 대출 만기일 및 이자 규칙
다음과 같은 이자 규칙은 우리 금융기관의 현행 일반 규칙이며 가설적인 것이 아니라는 점을 분명히 해야 한다. 정책이 바뀌면 가설로 강등합시다.
(1) 대출 (신용) 만기일.
대출 (신용) 의 만기일은 정기예금의 만기일과 다르다. 예금 만기일은 일반적으로 상대년 (월) 이고 대출 만기일은 일반적으로 상대년 (월) 전날이다. 예를 들어 시작 날짜도 20 13 10 이고 기간은 1 년입니다. 정기 예금 만기일은 20 14 14 12 이고 대출 만기일은 일반적으로 20 13 12 입니다
(2) 결산일.
1) 월별 이자 지불: 매월 20 일은 이자일, 다음날은 이자를 지급합니다.
지난달 2 1 이달 20 까지의 대출 이자는 이달 2 1 지불 등. 물론 대출 시작일이 지난달 2 1 보다 빠르며 대출 만기일이 이달 20 일 이후라는 전제가 있다.
2) 분기별 이자, 분기별 마지막 달의 20 일은 이자일, 다음날은 이자를 지급한다.
즉, 지난 분기 2 1 일부터 이번 분기 20 일까지의 대출 이자는 이번 분기 2 1 일에 지급됩니다. 물론 대출 시작일이 전분기 2 1 보다 빠르며 대출 만기일이 전분기 20 일 이후인 것으로 전제된다.
3) 대출 만기일에 미지급이자를 청산하고 지급한다.
(3) 일일 이자를 계산할 때, 일년 내내 360 일로 계산한다.
자금 관리 경험이 있는 독자는 한 대출이 실제로 지불하는 이자 총액이 항상 대출 금리에 따라 계산한 이자 총액보다 약간 크다는 현상을 발견해야 한다. 대출 기간 중 각 이자에 사용되는 일일 이자는 1 년 360 일로 계산되지만, 연간 365 (또는 366) 일이 있기 때문이다. 이렇게 실제로 지불하는 연간 이자는 대출 원금 × 연간 이자율 ×(365÷360) 과 같다.
(4) 이자 시작일과 종료일.
대출 만기일의 규칙에서 볼 수 있듯이 대출 이자는 자금의 실제 점유일 수 (정기 예금 이자가 계산대로 계산되지 않는 패턴 대신) 를 기준으로 계산되므로 이자일 계산에는 두 가지 원칙이 있습니다. 이자일은 대출 시작일과 이전 이자 중 나중 중 하나입니다. 이자 마감일은 현재 이자일과 대출 만기일 중 빠른 것이다.
특히 대출 시작일이 이전 이자일 다음날보다 이전인 경우 대출 만기일이 현재 이자일 이후이면 현재 이자의 시작일과 종료일은 이전 이자일 다음날부터 현재 이자일까지입니다. 대출 만기일은 당기 이자일보다 작고, 당기이자 시작일과 종료일은 전기 이자일의 다음날부터 대출 만기일까지이다.
예를 들어 현재 7 월 1 1, 20 14, 10 월 14 도 대출 1 년 대출 시작일 (20 14 15 10 월) 이 이전 결산일 (20 14 6 월 2/klls) 보다 빠릅니다
상기 대출이 반기기이고 대출 만기일 (20 14, 14) 이 당기기일 (20 14, 7 월 20 일) 보다 이전인 경우 당기간
대출 시작일이 이전 기간 결산일 다음날보다 늦을 때 대출 만기일은 현재 기간 결산일보다 늦고, 현재 이자 시작일은 대출 시작일부터 현재 기간 결산일까지입니다. 대출 만기일이 당기 이자일보다 작으면 당기 이자 시작일과 종료일은 대출 시작일부터 대출 만기일까지다. 후자의 경우는 대출 기간이 한 달도 안 되는 경우에만 나타나므로 작은 확률 사건이다.
예를 들어 현재 날짜는 20 1 1, 20 14 년 7 월, 20 14 년 6 월 28 일 대출 1 년을 기록합니다 대출 시작일 (20 14 년 6 월 28 일) 은 마지막 결산일 (20 14 년 6 월 20 일) 다음날, 대출 만기일 (20 15) 이후다
셋째, 주의사항
후속 확장 및 조회를 용이하게 하기 위해 관련 매개변수를 수동으로 입력할 때 이름의 통일을 유지해야 합니다 (예: "금융기관" 에서 은행명을 언급할 때 이름 사양을 통일하여 동일한 금융기관의 여러 이름을 피해야 함).
이 장의 첫 번째 섹션에서는 데이터 유효성에 순서 설정을 사용하여 "같은 이름의 성" 이 발생하지 않도록 했습니다. 예를 들어 이런 방식으로 대출 원장의 이자 기간을' 월' 과' 분기' 로 설정할 수 있습니다. 하지만 금융기관에게는 이 방법이 잘 작동하지 않는다. 금융기관이라는 매개변수의 범위 제한이 크지 않아 새로운 기관이 나타날지 예측할 수 없기 때문에 대안을 설계할 수 없기 때문이다. (빌 게이츠, 금융기관, 금융기관, 금융기관, 금융기관, 금융기관, 금융기관, 금융기관, 금융기관) 그래서 여기서는 정보를 작성할 때의 규범만 강조할 수 있다.
넷째, 지식 포인트 장비
이 섹션의 다음 내용을 읽기 전에 그림 5-73 의 관련 지식 포인트가 기본적으로 뇌에 장착되어 있는지 확인하십시오.
이 섹션에서 사례를 설명하기 전에 사용할 지식 포인트 (이름 정의 및 사용) 를 삽입합니다.
일반적으로 정의 이름은 수학에서 일반적으로 사용되는 "X= 정보 설정" 과 유사합니다. 여기서 "정보" 는 상수, 셀 영역 또는 공식일 수 있습니다. 설정이 완료되면 "X" 를 사용하여 "일부 정보" 기능을 구현할 수 있습니다. 이름을 사용하면 복잡한 공식을 단순화하여 더 쉽게 이해하고 유지할 수 있습니다. 이제 예를 들어 간단히 설명하겠습니다.
그림 5-73 관련 지식 포인트
그림 5-74 의 주문에서 정의명을 사용하여 총액을 정의하는 공식을' 총액' 으로 설정합니다. 절차는 공식 탭 → 이름 그룹 정의 → 이름 정의 (그림 5-74 참조) 입니다.
그림 5-74 정의 이름 사용
이때 정의된 이름을 사용하여 총 금액 공식을 설정할 수 있습니다 (그림 5-75 참조).
D3:D5 셀 면적의 공식은 = 총량입니다.
그림 5-75 기존 공식을 정의된 이름으로 대체
정의 이름을 사용하는 것은 보조 매개변수 설정과 유사합니다. 긴 복합 공식을 점진적으로 단순화하여 복합 공식의 이해를 높일 수 있습니다. 다음은 구체적인 응용이 있을 것이다.
다섯째, 주요 정보 공식 설계 방법
대출관리통계표' 의 틀, 기능 및 관련 주의사항에 대해 대충 파악한 후, 1 돛회사 대출 사례를 통해' 대출관리통계표' 의 설계를 검토했다.
사례 5-4 1 돛회사는 7 월 1 1, 2065438 이 금융기관에 그림 5-76 과 같이 대출을 발행하여 그림 5-77 과 같이 인민폐로 발행했다.
그림 5-76 항해 회사 신용 정보 양식
그림 5-77 항해 회사 대출 정보 양식
대출 관리 통계표의 구조와 결합해 관련 설계 방법은 다음과 같다.
1. 신용 계정 공식 설계 방법 (1)
(1) 인스턴트 날짜 표시 (D2 셀).
양식에 항상 "오늘" 날짜를 표시하려면 컴퓨터 시스템 날짜가 정확한지 확인하는 간단한 날짜 공식을 사용하면 됩니다 (그림 5-78 참조).
D2 셀의 공식은 = 오늘 () 입니다
그림 5-78 은 항상 "오늘" 공식을 설정합니다
(2) 신용 만기일 (H4:H8 구역).
이 매개 변수의 공식도 비교적 간단합니다. 이 장의 3 절에서 비슷한 상황을 보았습니다. 다만 이 표의 만기일은 한 달이 아니라 구체적인 날짜까지 정확해야 한다. 대출 만기일은 연 (월) 으로 계산되지 않으므로 일 매개 변수에서 1 을 빼야 한다는 점에 유의해야 합니다.
H4 세포의 공식은 다음과 같습니다.
=IF (또는 (F4= "",G4= ""), DATE (연도 (G4), 월 (G4)+F4, 일 (G4)-/kloc
기재한 후 신용 기간 공식 설정을 완료할 수 있습니다 (그림 5-79 참조).
현재, 신용장에는 이미 사용된 신용 한도 (D4:D8 구역), 사용 가능한 신용 한도 (E4:E8 구역) 및 현재 사용 가능한 신용 한도 (G2 단위) 의 세 가지 매개변수가 있습니다. 이러한 매개변수는 아직 공식을 설계하지 않았습니다. 이 세 가지 데이터는 대출 업무가 발생한 후에야 그 역할을 반영할 수 있기 때문에, 우리는 나중에 다시 돌아와서 토론할 것이다.
2. 대출 상세 회계 공식 설계 방법
(1) 인스턴트 날짜 표시 (A3 셀).
이것은 말할 것도 없다. A3 셀의 공식은 =TODAY () 입니다
그림 5-79 신용 만료 공식
(2) 금융기관 (C7:C 16 구역).
금융기관은 신용대출장부에 이미 있는 데이터이기 때문에 중복 입력이 필요 없고 VLOOKUP 함수를 사용하여 참조를 찾습니다. 동시에, 우리는 실수로 존재하지 않는 신용계약을 입력했을 때 (현재 일치하는 금융기관이 없음) 제때에 공식을 제시해야 한다는 점도 고려해야 한다.
C7 셀의 공식은 다음과 같습니다.
= if (a7 = "",",",if error (vlookup (a7, 신용 계정! $ a $4: $ b $8,2,0), "신용 계약 번호가 없습니다.").
기재한 후 금융기관 정보 일치를 완료할 수 있습니다 (그림 5-80 참조).
그림 5-80 VLOOKUP 기능 관련 정보 자동 일치
사용자가 대출 데스크에서 해당 신용계약에 따른 신용만기일, 신용총액 등의 정보를 조회해야 하는 경우에도 같은 방법을 사용해야 합니다. 여기서는 더 이상 군말을 하지 않습니다. (데이비드 아셀, Northern Exposure (미국 TV 드라마), 신용명언)
(3) 대출 만기일 (G7:G 16 구역).
이 생각은 신용 원장의 만기일 공식과 같다.
G7 셀의 공식은 다음과 같습니다.
=IF (또는 (E7= "",F7= ""), DATE (연도 (F7), 월 (F7)+E7, 일 (F7)-/kloc
기재한 후 대출 만기일 계산을 완료할 수 있습니다 (그림 5-8 1 참조).
그림 5-8 1 대출 만기일 공식
(4) 이번 달 이자 시작일 (T7:T 16 셀 영역).
이어 이번 달 이자지급, 이번 달 이자 지급 등의 데이터에 대한 공식 설계에 들어간다. 우리는 이자를 계산하는 중요한 전제 조건은 이자를 계산하는 일수를 결정하는 것임을 알고 있다. 현재 이자 마감일이 명확하기 때문에 (이번 달 이자일 또는 대출 만기일) 이번 달 이자 시작일이 어떻게 계산되는지 살펴봐야 합니다.
이달 이자일은 이달 전 마지막 이자일 다음날 ('마지막 이자일' 이라고 함) 을 이해할 수 있다. 보조 매개변수일 뿐이므로 보조 매개변수가 중요한 정보의 명확한 읽기에 영향을 주지 않도록 마스터 테이블 외부의 영역 (T 열) 에 배치합니다.
이자 규칙에서 알 수 있듯이, 대출 시작 및 종료 날짜와 이자 기간에 따라 이번 달의 이자 날짜는 그림 5-82 와 같이 다를 수 있습니다.
그림 5-82 는 실제로 공식 설계에 대한 아이디어와 논리를 제공합니다. 최종 공식이 너무 길어지지 않도록 공식을 설계하기 전에 자주 사용되는 몇 가지 지표에 대한 공식의 정의 이름을 설정할 수 있습니다 (그림 5-83 참조).
그림 5-82 이번 달 이자 시작 날짜의 다양한 상황
그림 5-83 이 섹션에서 이름을 정의하는 매개변수 (1)
첫 번째 단계: 환경을 정리합니다.
그림 5-8 1 에서 볼 수 있듯이 기간, 대출 시작일, 이자 기간 세 가지 매개 변수 중 하나가 누락되면 이달 시작일을 계산할 수 없습니다. 그래서 우리는 결론을 내렸습니다.
T7 세포의 첫 번째 단계 공식은 다음과 같습니다.
=IF (또는 (E7= "",F7= "",I7= """"",두 번째 단계로 이동)
두 번째 단계: 이번 달 전에 대출이 상환되었는지 확인하십시오.
모든 대출이 만기일에 한꺼번에 상환된다고 가정한 이상, 두 번째 단계의 관건은 이번 달 전에 대출금이 이미 상환되었는지 여부를 판단하는 것이다.
대출 만기일이 지난달 마지막 날보다 늦지 않은 경우 (G7
T7 세포의 두 번째 단계 공식은 다음과 같습니다.
= IF(G7 & lt；; =EOMONTH (이번 달 마감일,-1), "마감됨", 3 단계로 이동)
여기에는 세 번째 행에 있는 셀이 포함되며 후속 열이 제대로 채워질 수 있도록 절대 참조를 설정해야 합니다. 아래의 레시피 디자인도 이 원칙을 따라야 한다.
세 번째 단계: 월별 결제 방법을 결정합니다.
지난 이자 날 다음날 월이자 패턴과 분기 이자 패턴이 다르기 때문이다. 월 이자 패턴에서 최종 이자 일 다음날은 지난달 2 1 이고, 분기 이자 패턴에서 최종 이자 일 다음날은 이전 분기의 2 1 입니다. 그래서 3 단계에 들어서면, 이자 주기에 따라 차근차근 토론할 수밖에 없다.
T7 세포의 3 단계 공식:
=IF(I7= "매월", if (F7
4 단계: 분기별 이자 모드에서 이자를 결정합니다.
앞의 단계에서 볼 때, 4 단계에 들어간다는 것은 분기 결산 모드에서 아직 갚지 않은 대출이라는 것을 의미한다.
분기별 결산은 월별 이자와 마찬가지로 대출 시작일과 최종 결산일 간의 관계를 판단하는 것으로 남아 있다. 하지만 분기 결산 패턴의 마지막 결산일은 좀 번거롭습니다. 마지막 분기의 결산일이어야 합니다.
그렇다면 분기 말 어떻게 결정할까요? 논리적 법칙을 찾으십시오. 분기 말의 월 수는 3 의 정수 배수여야 합니다.
그래서 우리는 이 법칙을 볼 수 있습니다.
분기 말 월수를 3 으로 나누고 나머지는 0 이다.
분기가 끝난 후 첫 달의 숫자를 3 으로 나누고 나머지는 1 입니다.
분기가 끝난 후 두 번째 달을 3 으로 나누고 나머지는 2 이다.
우리는 이 법칙과 반대되는 일을 할 것입니다.
분기 말일의 첫 달에서 1 을 빼면 지난 분기 말의 월 수입니다.
분기 말월 수에서 2 를 빼면 지난 분기 말 월 수이다.
분기 말 월수에서 3 을 빼면 지난 분기 말 월수이다.
이전 분기의 마지막 달은 현재 월 수에서 현재 월 수를 3 의 나머지로 나누어 계산할 수 있습니다. 하지만 한 가지 예외가 있습니다. 나머지가 0 일 때 우리는 3 을 빼야 한다. 따라서 MOD 함수를 사용하여 나머지를 계산해야 합니다.
T7 세포의 네 번째 단계 공식은 다음과 같습니다.
= IF(F7 & lt；; =DATE (년 (이번 달 결산일), MONTH (이번 달 결산일) -if (mod (이번 달 결산일), 3) = 0,3,mod (이번 달 결산일), 3
위 단계를 종합하는 공식.
T7 세포의 완전한 공식은 다음과 같습니다.
=IF (또는 (E7= "",F7= "",I7= """,IF(G7 & lt；; =EOMONTH (이번 달 결산일,-1), "마감됨", IF(I7= "매월", if (F7
기재 후 이번 달 각 대출의 이자 시작 날짜를 얻을 수 있다 (그림 5-84, 그림 5-84a 는 6 월의 예, 그림 5-84b 는 7 월의 예 참조).
그림 5-84 이번 달 이자일 공식 시작
(5) 이번 달 미지급이자 (K7:K 16 단위 면적).
이번 달 미지급이자는 이번 달에 실제로 지급되는 이자이며 다음과 같이 계산됩니다.
이번 달 미지급이자 = (대출 원금 × 연간 이자율 ⊏ 360) × 이자 기간 일수 = 일일 이자 × 이자 기간 일수.
이 섹션의 기본 가정과 전제 섹션의 정보를 바탕으로 그림 5-85 와 같이 대출 시작 및 종료 날짜에 따라 이자 기간이 달라질 수 있음을 알고 있습니다.
그림 5-85 이번 달 이자기간 동안의 다양한 상황
또한 다음을 고려해야합니다.
1) 이번 달 미지급이자에는 이번 달 결산일 이자 및 만기일 이자 지불이 포함됩니다.
이달의 이자일 지불은 이달의 이자일 결산된 이자를 말하며, 이달의 만기일 지불은 이달의 만기일로 인해 대출금 만기일에 지불된 이자를 가리킨다. (윌리엄 셰익스피어, 이자, 이자, 이자, 이자, 이자, 이자, 이자) 대출 만기일이 마침 이달 결산일인 경우, 우리는 이자일에 전체 이자를 지불한다고 가정한다.
2) 분기별 이자 모델 대출은 분기 말 이자일에만 이자를 지급한다.
이에 따라 우리의 설계 단계는 다음과 같습니다.
첫 번째 단계: 환경을 정리합니다.
그림 5-85 의 관련 정보에서 알 수 있듯이 대출 만기일과 이자 기간이 비어 있고 대출 만기일이 이번 달 전인 경우 (이달부터 이자일은' 마감' 으로 표시됨). 이번 달 미지급이자는 0 입니다.
K7 세포의 첫 번째 단계 공식은 다음과 같습니다.
=IF (또는 (G7= ",I7=", T7= ",0, 두 번째 단계로 이동)
두 번째 단계: 대출은 이번 달에 만기가 되어 이번 달에 이자를 지급한다.
대출 만기일이 이번 달인지 아닌지를 판단하는 것도 대출 만기일과 이달 결산일이 같은 해 같은 달인지 여부를 비교하는 문제다. 이달, 이번 달 이자에 대한 이자 기간은 이달 이자일 다음날부터 대출 만기일까지이다.
이자 기간의 시작 및 종료 일자가 모두 이자이므로 이자 일수를 계산할 때 두 일자를 뺀 후 1 을 추가해야 한다는 점에 유의해야 합니다.
예를 들어 이자 계산의 시작 일자는 20 14 14 1 이고 이자 계산의 종료 일자는 20 1 41입니다
K7 세포의 두 번째 단계 공식은 다음과 같습니다.
= If (12 * 년 (G7)+ 월 (G7) = 12 * 년 (이번 달 결산일)+월 (이번 달 결산일), 월
세 번째 단계: 이번 달 만기 대출은 이번 달에 이자를 지급한다.
만약 대출이 이달 이후에 만기된다면, 이번 달 미지급이자는 이자 주기 패턴에 따라 구분해야 한다. 대출은 분기별 이자 모델이며 이번 달은 분기 말 (AND(I7= "quarter ",mod (month (a3), 3) 이 아닙니다
K7 세포의 세 번째 단계 공식은 다음과 같습니다.
=IF(AND(I7= "분기", MOD (월 (이번 달 이자일), 3)
위 단계를 종합하는 공식.
K7 세포의 완전한 공식은 다음과 같습니다.
=IF(OR(G7= ",I7=", T7= ",마감됨), 0,if (12 * 년 (G7)+ 월 (G7)
기재한 후 이번 달 대출당 미지급이자를 낼 수 있다 (그림 5-86 참조).
편폭의 관점에서 볼 때, 이번 달에 이자를 지불하는 공식은 결코 복잡하지 않은 것 같다. 그러나 이번 달에 이자 시작 일자 (T7 셀) 가 없는 경우 이번 달 이자 공식에서 T 열과 관련된 모든 셀 매개변수는 이번 달 이자 시작 일자의 전체 공식으로 대체되어야 합니다. 이 경우 규모는 크지 않을 것이다.
에베레스트 등반과 마찬가지로, 우리는 도중에 몇 개의 베이스 캠프를 세워야 한다. 한 공식의 설정에 논리적 계층과 분기가 많다는 것을 알게 되면, 이달 미지급이자를 계산할 때 이번 달 이자 시작일이 중간 정보인 것과 같은 중간 정보를 베이스캠프로 즉시 활성화해야 합니다. 공식 설계를 점진적으로 간소화하는 역할을 합니다. 공식의 절정을 공략하는 과정의 뇌졸증을 피하기 위해서. 물론 이름을 정의하는 것도 베이스 캠프를 세우는 한 가지 방법으로 볼 수 있다.
그림 5-86 이번 달 미지급이자 공식
(6) 이번 달 결산일 (L7:L 16 단위 면적) 과 이달 만기일 (M7:M 16 단위 면적) 에 이자를 지급합니다.
지불 시간에 따라 이번 달의 미지급이자 지역을 구분해야 하는 이유는 보통 이달 결산일에 대량의 이자를 지불하기 때문이다. 따라서 우리는 결산일의 이자 지불을 면밀히 주시할 필요가 있다. 그리고 우리는 앞으로 며칠 동안 이자를 처리하는 기능을 실현할 때 이자 지불 시기를 구분해야 한다.
위의 관계에 따르면, 우리는 다음을 알 수 있습니다.
이번 달 이자일 이자+이번 달 만기일 이자 = 이번 달 미지급이자.
따라서 이번 달 미지급이자를 계산한 경우, 결제일 이자 및 만기일 이자 중 하나만 알면 되고, 다른 하나는 역산법을 통해 해결할 수 있다. (윌리엄 셰익스피어, 윈스턴, 이자, 이자, 이자, 이자, 이자, 이자, 이자, 이자, 이자, 이자) 이 경우, 우리는 결산일에 이자를 지불하는 공식을 분석하고 토론할 것이다.
이자 규칙에서 알 수 있듯이 이자 지불 관련 영향 요인은 그림 5-87 에 나와 있습니다.
그림 5-87 만기 일자와 만기 일자 마감 기간의 관계
그림 5-87 에는 6 가지 상황이 있지만, 이번 달 이자일은 실제로 3 가지 가능성밖에 없다. 이자가 없고, 이자액은 이번 달 미지급이자와 같고, 이자액은 이전 이자일 다음날부터 이달 이자일까지의 미지급이자와 같다. 위의 논리에 따라 설계 단계는 다음과 같습니다.
첫 번째 단계: 이번 달 이자일은 이자를 내지 않는다.
다음 중 하나가 발생할 경우 이번 달 이자일은 이자를 지불하지 않습니다.
1) 이번 달 미지급이자는 0 입니다.
2) 대출 만기일은 이번 달 결산일 이전입니다.
3) 분기별 이자 모드에서 이번 달은 분기 말에 해당하지 않습니다.
L7 세포의 첫 번째 단계 공식은 다음과 같습니다.
= 인 경우 (또는 (K7=0, G7< 이번 달 결산일 및 (I7= "분기", MOD (월 (이번 달 결산일), 3)
2 단계: 이번 달 이자일에 지급되는 이자는 이번 달 미지급이자와 같습니다.
만약 대출 만기일이 이번 달보다 늦는다면, 이번 달에는 만기지불이 발생하지 않는다는 것을 의미한다. 이에 따라 이달 결산일에 지급되는 이자는 이번 달 미지급이자와 같다. 그렇지 않으면 대출 만기일이 이달 결산일 이후, 다음 달 전이라고 밝혔다. 이 시점에서, 이번 달 이자일의 이자 지급액은 이전 이자일 다음날부터 이달 이자일까지의 미지급이자와 같다. (윌리엄 셰익스피어, 이자, 이자, 이자, 이자, 이자, 이자, 이자)
L7 셀의 두 번째 단계 공식은 다음과 같습니다.
= IF(G7 & gt；; EOMONTH (이번 달 결산일 0), K7, ROUND(D7*H7* (이번 달 결산일-T7+1)/360,2))
위의 공식을 결합하다.
L7 셀의 전체 공식:
= 인 경우 (또는 (K7=0, G7< 이번 달 결산일 및 (I7= "분기", MOD (월 (이번 달 결산일), 3)
이때 만기 일자에 대한 이자 지급 공식을 계산하고 설정합니다.
M7 세포의 공식은 =K7-L7 입니다.
기재 후 결제일 이자 및 만기일 이자를 받을 수 있습니다 (그림 5-88 참조).
그림 5-88 이자 일일 이자 공식 및 만기 일자 이자 공식
(7) 이번 달 미지급이자 (J7:J 16 단위 면적).
이번 달 이자는 이번 달에 대출금 원금의 합계를 내야 한다. 이 가운데 이번 달 미지급이자는 이미 K 에 기재되어 있으므로 문제의 관건은 이번 달에 원금을 지불하는 것이다.
만약 대출 원금을 이번 달에 지불해야 한다면, 대출이 이번 달에 만료된다는 의미이며, 이는 대출이 만료되는 연도가 그해와 같은지 아닌지를 다시 한 번 이용한다는 판단이다. (윌리엄 셰익스피어, 윈스턴, 원금, 원금, 원금, 원금) 만약 같으면 이번 달 이자는 대출 원금에 이번 달 미지급이자를 더한 것과 같다. 그렇지 않으면 이번 달 미지급이자만 고려된다.
N7 셀의 공식은 다음과 같습니다.
= if (G7 = ",0, if (12 * 년 (G7)+ 월 (G7) = 12 * 년 (이번 달 마감
기재한 후 이번 달의 미지급원이자를 받을 수 있다 (그림 5-89 참조).
그림 5-89 이번 달 미지급이자 공식
(8) 현재 총 대출 금액 (B3 단위).
현재 대출 총액은 아직 만료되지 않은 대출 원금의 합계이며, 계산 논리는 당연히 대출 만기일이' 오늘' 이후인지 여부를 판단하는 것이다. 이것은 당연히 SUMIF 함수에 달려 있다 (그림 5-90 참조).
B3 셀의 공식은 = sumif (G7: G 16, ">" & ampA3, D7:D 16) 입니다
그림 5-90 현재 대출 금액 공식
(9) 이번 달 미지급이자 (셀 C3) 와 이번 달 미지급이자 (셀 D3) 입니다. 이것은 합계 함수로 직접 들어간다.
C3 셀의 공식은 =SUM(K7:K 16) 입니다.
D3 셀의 공식은 =SUM(J7:J 16) 입니다.
물론, 이번 달 이자일 미지급이자 등의 공식을 설정하여 관리 요구를 충족시킬 수 있습니다. 여기서는 더 이상 군말을 하지 않습니다.
이로써 대출 대좌의 주요 정보 공식은 이미 설치되었다.
다음으로, 우리는 신용 원장의 미완성 항목을 완성하기 위해 돌아갈 것이다.
3. 신용 계정 공식 설계 방법 (2 부)
(1) 사용된 신용 한도 (D4:D8 커뮤니티).
사용된 한도는 관련 신용 계약에서 이미 취득하여 아직 돌려주지 않은 대출 금액입니다. 이 정의에는 두 가지 의미가 포함되어 있다. 첫째, 자신의 계약에 따른 대출을 인정하고, 다른 계약의 대출을 자신의 이름으로 계산할 수 없도록 해야 한다. 둘째, 관련 대출이 아직 사용 중인지 여부를 분명히 해야 한다. 이미 만기가 된 대출은 더 이상 신용 한도를 차지하지 않기 때문이다.
이렇게 우리는 사용된 한도가 다중 조건 합계 문제라는 것을 알 수 있다.
D4 세포 공식:
=SUMIFS (대출 원장! $D$7:$D$ 16, 대출 원장! $A$7:$A$ 16, A4, 대출 원장! $G$7:$G$ 16, "> & amp$D$2)
기재 후 각 신용 계약의 현재 사용 한도를 얻을 수 있습니다 (그림 5-9 1 참조).
그림 5-9 1 사용된 신용 한도 공식
(2) 사용 가능한 회로 (E4:E8 셀 영역).
사용 가능한 신용 한도로 우리에게 주는 첫인상은 당연히 총 신용 한도에서 사용된 신용 한도를 뺀 것이다. 그러나, 이것은 단지 첫 번째 느낌일 뿐, 우리의 두 번째 느낌은 바로 생각해야 한다. 이 논리의 전제는 신용 계약이 만료되지 않았다는 것이다. 이런 식으로, 우리의 공식은 논리적이다.
E4 결정포의 공식은 = IF(H4 & gt；; 2 달러, C4-D4, 0 달러)
열 채우기를 수행한 후 사용 가능한 할당량을 얻을 수 있습니다 (그림 5-92 참조).
그림 5-92 가용 신용 한도 공식
(3) 현재 사용 가능한 신용 한도 (G2 셀).
이 매개변수는 주로 사용 가능한 할당량을 요약하는 데 사용됩니다.
G2 셀의 공식은 =SUM(E4:E8) 입니다.
이로써 대변 상세 장부의 주요 정보 공식도 완전히 완성되었다 (그림 5-93 참조).
그림 5-93 신용 계정 주요 정보