메뉴
실무 위키›응용 공식›대출 원리금 균등 상환표 공식

대출 원리금 균등 상환표 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

차량 할부의 회차별 상환액을 이자와 원금으로 나누고 남은 대출 잔액을 계산합니다.

예제파일 다운로드
대출 원리금 균등 상환표 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=-IPMT ( 연이율 / 12, 회차, 기간(개월), 대출금 )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
연이율필수대출에 적용하는 연이율이 들어 있는 셀입니다. 매달 상환하므로 수식에서 12로 나누어 월이율로 바꾼 값입니다.
회차필수이자와 원금을 계산할 현재 상환 회차가 들어 있는 셀입니다. 1부터 기간(개월)까지 순서대로 적는 값입니다.
기간(개월)필수전체 상환 횟수를 월 단위로 적어 둔 셀입니다. 예제에서는 4개월 동안 네 번 상환하는 조건입니다.
대출금필수상환할 차량 대출 원금이 들어 있는 셀입니다. PMT 함수, IPMT 함수, PPMT 함수는 양수 대출금을 넣으면 계산 결과를 음수로 반환하므로 예제에서는 수식 앞에 -를 붙인 것입니다.
B2상환액=-PMT($H$2/12,$H$3,$H$1)
C2이자=-IPMT($H$2/12,A2,$H$3,$H$1)
D2원금=-PPMT($H$2/12,A2,$H$3,$H$1)
E2잔액=MAX(0,$H$1-SUM($D$2:D2))
E3=MAX(0,$H$1-SUM($D$2:D3))
E4=MAX(0,$H$1-SUM($D$2:D4))
E5=MAX(0,$H$1-SUM($D$2:D5))
A
B
C
D
E
F
G
H
I
1
회차
상환액
이자
원금
잔액
대출금
4,060,401
2
1
1,040,604.01
40,604.01
1,000,000
3,060,401
연이율
12%
3
2
1,040,604.01
30,604.01
1,010,000
2,050,401
기간(개월)
4
4
3
1,040,604.01
20,504.01
1,020,100
1,030,301
5
4
1,040,604.01
10,303.01
1,030,301
0
6
상환액은 매달 1,040,604.01로 같지만 잔액이 줄면서 이자는 감소하고 원금 납입액은 늘어납니다. 4개월 조건이라 마지막 회차의 잔액은 0이며 MAX 함수로 음수 잔액을 막습니다.
이 공식이 사용하는 함수
PMT 함수 IPMT 함수 PPMT 함수 SUM 함수 MAX 함수

동작 원리

PMT 함수가 매달 같은 상환액을 정하고 IPMT 함수와 PPMT 함수가 이자와 원금을 나누며, SUM 함수와 MAX 함수가 누적 원금을 반영한 잔액을 구합니다.

예제 시트에서 첫 회차를 계산하는 B2:D2 셀과 마지막 잔액을 구하는 E5 셀의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

PMT 함수가 매달 낼 상환액을 정합니다

연이율 12%를 12로 나눈 월이율 1%와 4개월 조건을 사용합니다. 계산 결과는 음수로 나오므로 수식 앞의 -로 부호를 바꿔 매달 상환액 1,040,604.01을 얻습니다.

=-PMT ( $H$2 / 12, $H$3, $H$1 )
= 1,040,604.01
2

IPMT 함수가 첫 회차 이자를 계산합니다

첫 회차에는 대출금 4,060,401의 1%인 40,604.01이 이자로 배분됩니다.

=-IPMT ( $H$2 / 12, A2, $H$3, $H$1 )
= 40,604.01
3

PPMT 함수가 첫 회차 원금을 계산합니다

같은 회차의 전체 상환액에서 이자를 뺀 1,000,000이 원금 납입액입니다. 다음 회차부터 잔액이 줄어 이자는 감소하고 원금은 늘어납니다.

=-PPMT ( $H$2 / 12, A2, $H$3, $H$1 )
= 1,000,000
4

누적 원금을 빼 마지막 잔액을 구합니다

D2:D5에 계산된 원금은 모두 4,060,401입니다. 이를 대출금에서 빼면 0이며, MAX 함수가 계산 결과의 하한을 0으로 제한해 잔액이 음수가 되지 않습니다.

=MAX ( 0, $H$1 - SUM ( $D$2:D5 ) )
= 0
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료