엑셀 이자계산 수식 (특이한 이자 지급 구조)

포스트 목차.

• 01. 문제
• 02. 문제 해결 방법
• 03. 예제

 

본 포스트에서는 빌린 기간별로 이자계산 방식이 다른 경우 이자 계산을 위한 엑셀 수식을 작성하는 방법을 다룬다.

 

01. 문제

 

Q. 엑셀수식 문의드립니다. 만약 100만원을 빌렸다는 가정하에 15일 이내에 상환하면 월 0.5%의 이자 한달치를 16일 ~ 30일 이내에 상환하면 월 1%의 이자 한달치를 31일 이후부터는 월1%의 이자를 일할로 계산할 때 엑셀수식을 어떻게 하면 좋을까요?

 

조금 특이한 구조의 이자 계산법에 대한 질문이다.

 

문제를 정의하면 다음과 같다.

 

1. 15일 이내에 상환할 경우 월 0.5% 이자 한달분을 내야한다.  (원금 * 0.5%에 해당하는 5,000원을 내야한다.)

2. 16일 ~ 30일 사이에 상환할 경우 월 1.0% 이자 한달분을 내야한다. (원금 * 1.0%에 해당하는 10,000원을 내야한다.)

3. 31일 이후 부터는 월1%의 이자를 일할로 계산한다. (월이 30일이라고 가정하면 하루에 10,000/30 원 만큼의 이자를 내야하고, 이 값에 빌린 일수만큼을 곱하면 된다.)

 

02. 문제해결 방법

(1) 이자 계산

다른 문제들과 이 문제의 차이는 이자의 표기 방식이 연이자가 아니라 월이자라는 것이다. 즉 연이자로 환산하면 12를 곱해야 한다. (예. 월이자 1% = 년이자 12%)

 

(2) 구간별 다른 이자 계산법

 - 15일 이내에 상환할 경우 월  0.5% 이자 한달분을 내야 한다.

15일 이내에 상환할 경우 원금의 0.5%를 내라는 소리다.

 

 - 16일~30일 사이에 상환할 경우 월 1% 이자 한달분을 내야한다.

16일에서 30일 사이에 상환할 경우 원금의 1%를 내라는 소리다.

 

- 31일 이후부터는 월 이자 1%로 일할계산

여기서부터는 우리가 알고 있는 정상적인 이자계산법을 사용하면 된다. 

 

(3) 중첩 IF함수만 사용하면 어렵지않게 계산이 가능할 것 같다.

 

 

03. 예제

(1) 시트 구성

 

시트 구성은 위와 같다.

셀F2에 빌린 기간을 입력하면, 셀F3에 빌린 기간동안 내야하는 총 이자가 계산되는 방식이다.

 

D2~D4에는 계산의 기준이되는 이자 값이 채워질 예정이다.

 

 

(2) 이자 계산 수식 세우기

 - 15일 이내에 상환할 경우 월  0.5% 이자 한달분을 내야 한다.

셀D2에는 다음과 같은 수식을 채우자

=F1*C2

원금과 이율을 곱한 값이다. 이 구간에서는 어느날에 갚던지 한달치 이자가 붙기 때문에 원금과 월이율만 곱하면 된다.

 

 - 16일~30일 사이에 상환할 경우 월 1% 이자 한달분을 내야한다.

셀D3에는 다음과 같은 수식을 채우자

=F1*C3

원금과 이율을 곱한 값이다. 이 구간에서는 어느날에 갚던지 한달치 이자가 붙기 때문에 원금과 월이율만 곱하면 된다.

 

- 31이후 부터는 월1%이자를 일할계산해야 된다.

셀D4에는 다음과 같은 수식을 채우자.

=F1*C4/30

이 값은 하루에 붙는 이자를 나타낸다. 월이자를 30으로 나눈값이다.

 

마지막으로 셀 F3에는 다음과 같은 수식을 채우자.

=IF(F2<=15,D2,IF(F2<=30,D3,D4*F2))

 

빌린 기간의 값에 따라 D2, D3, D4*빌린기간의 값이 반환되는 IF함수를 사용했다.

 

(3) 이자 계산 예제

 

- 10일 빌린 경우

 

10일 빌린 경우 월 0.5%에 해당하는 5,000원이 이자로  계산되는 것을 확인할 수 있다.

 

- 20일 빌린 경우

 

20일 빌린 경우 월 1.0%에 해당하는 10,000원이 이자로  계산되는 것을 확인할 수 있다.

 

- 50일 빌린 경우

 

50일 빌린경우 월이자 1% (일이자 1/30%)로 50일을 빌린 16,667원이 계산되는 것을 확인할 수 있다.