엑셀 PPMT,IPMT함수로 대출상환시 원금과 이자 구하기

엑셀함수는 PMT는 대출을 받고 난 후 대출금을 상환할 때 매달 상환해야 할 금액(원리금)이 얼마인지 계산할 때 사용하는데,

  • PPMT함수를 이용하면 상환할 원리금(원금+이자)중에서 원금이 얼마인지 계산할 수 있고
  • IPMT함수를 이용하면 이자가 얼마인지 계산할 수 있다.

결과적으로 PPMT함수로 계산한 원금과 IPMT함수로 계산한 이자를 더하면 PMT함수로 구한 원리금과 같은 금액이 된다(PPMT + IMPT = PMT).

 

[ PMT함수에 대해서는 아래 링크 참조 ]

엑셀 PMT함수로 대출 상환액, 목표금액을 모으기 위한 월저축액 구하기

 

PPMT함수 – 원리금 중에서 원금 계산하기

구문(Syntax)
PPMT(rate, per, nper, pv, [fv], [type])

고정 금리(rate)가 적용되는 대출(pv)을 받아서 정해진 기간(nper)동안 상환해야 할 때 매기간 상환해야 할 원리금(원금+이자)중에서 원금이 얼마인지 구해준다.

인수 :

rate : 기간별 이자율(고정 금리).
per : 납입 회차.
nper : 총 납입 기간 수.
pv : 현재가치(현재 시점의 현금잔액)
fv : 생략가능, 미래가치를 말하며 미래시점에 남아 있는 현금잔액.
type : 생략가능, 납입 시점을 나타낸다. 0은 기간말, 1은 기간초.

 

사용예 :

대출의 월 상환액 중 원금 구하기(원리금 균등상환 기준)
  • 고정금리 3.5%가 적용되는
  • 1억원 대출을 받고
  • 2년 동안 상환해야 할 때

매월 동일한 금액으로(균등상환) 상환해야 원리금(원금+이자)중에서 1회차에 납입해야 할 원금이 얼마인지 PPMT함수로 알아보자.

=PPMT(C13/12,C14,C15*12,C16,C17,C18)

엑셀PPMT함수로 대출상환원리금중 원금구하기

수식이 정상적으로 입력되었다면 대출상환액(원리금)중에서 원금은 -4,028,606원이 구해진다.

상환 원금은 매월 내가 갚아야 하는 돈(현금의 유출)이므로 음수로 구해지는 점을 유의하자.

엑셀PPMT함수로 대출상환원리금중 원금구하기

주의 : 이자율(rate)과 납입기간수(nper)를 지정할 때는 동일한 단위를 사용해야 한다. 연 이자율 3.5%의 대출금을 매월 상환한다면 연 이자율을 12로 나누어서 이자율을 적용해야 하고, 납입기간수는 월단위로 바꿔주어야 한다(년수*12).

 

 

IPMT함수 – 원리금 중에서 이자 계산하기

구문(Syntax)
IPMT(rate, per, nper, pv, [fv], [type])

고정 금리(rate)가 적용되는 대출(pv)을 받아서 정해진 기간(nper)동안 상환해야 할 때 매기간 상환해야 할 원리금(원금+이자)중에서 이자가 얼마인지 구해준다.

인수 :

rate : 기간별 이자율(고정 금리).
per : 납입 회차.
nper : 총 납입 기간 수.
pv : 현재가치(현재 시점의 현금잔액)
fv : 생략가능, 미래가치를 말하며 미래시점에 남아 있는 현금잔액.
type : 생략가능, 납입 시점을 나타낸다. 0은 기간말, 1은 기간초.

 

사용예 :

대출의 월 상환액 중 이자 구하기(원리금 균등상환 기준)
  • 고정금리 3.5%가 적용되는
  • 1억원 대출을 받고
  • 2년 동안 상환해야 할 때

매월 동일한 금액으로(균등상환) 상환해야 할 원리금(원금+이자)중 1회차에 납입해야 할 이자가 얼마인지 IPMT함수로 알아보자.

=IPMT(C13/12,C14,C15*12,C16,C17,C18)

엑셀 IPMT함수로 대출상환원리금중 이자구하기

수식이 정상적으로 입력되었다면 대출상환액(원리금)중에서 이자는 -291,667원이 구해진다.

상환 이자는 매월 내가 갚아야 하는 돈(현금의 유출)이므로 음수로 구해지는 점을 유의하자.

엑셀 IPMT함수로 대출상환원리금중 이자구하기

주의 : 이자율(rate)과 납입기간수(nper)를 지정할 때는 동일한 단위를 사용해야 한다. 연 이자율 3.5%의 대출금을 매월 상환한다면 연 이자율을 12로 나누어서 이자율을 적용해야 하고, 납입기간수는 월단위로 바꿔주어야 한다(년수*12).

 

PPMT함수와 IPMT함수를 이용하여 대출상환 스케줄만들기

PPMT함수와 IPMT함수는 상환 회차별로 대출상환원금과 이자를 계산해 주므로 다음과 같이 대출상환스케줄을 만들 수 있다.

계산수식은 실습용 엑셀파일을 다운로드해서 확인해 보자.

<대출 1억원(고정금리 3.5%)을 2년 동안 상환해야 할 때 스케줄>

*납입회차별 원금과 이자가 다른 이유 : 원금을 갚아 나가면 대출잔액이 줄어들고 줄어든 잔액에 이율을 적용하기 때문에 이자금액이 조금씩 줄어들고 원리금 균등이므로 이자금액이 줄어들면 상환할 원금은 조금씩 늘어나게 된다.

 

[ 엑셀 재무 함수 목록 ]

 

Leave a Comment