=PMT(B2/12,B3*12,-B1)은 B1의 대출을 B2의 연이율로 B3년 동안 갚을 때의 월 상환액을 반환합니다. 상환이 매달 이뤄지므로 이율은 12로 나누고 연수에는 12를 곱합니다.
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Monthly payment | $1,580.17 |
| 6 | Total paid | $568,861.22 |
| 7 | Total interest | $318,861.22 |
250,000을 6.5%로 30년 빌리면 매달 1,580.17이 들고, 전체 기간의 이자는 대출액보다 많은 318,861.22입니다. B3을 15로 바꾸면 상환액은 2,177.77로 오르지만 총이자는 절반 아래로 떨어집니다.
PMT 구문
=PMT(rate, nper, pv, [fv], [type])
rate: 기간당 이율입니다. 연이율로 매달 상환한다면rate/12를 쓰세요.nper: 상환 횟수입니다. 30년 동안 매달 상환하면30*12, 즉 360입니다.pv: 현재 가치, 즉 빌린 금액입니다.fv(선택): 마지막에 남는 금액입니다. 완전히 갚는 대출이면 기본값인 0이고, 저축 계획이면 저축 목표입니다.type(선택): 대부분의 대출처럼 각 기간 말에 상환하면 0(기본값), 월세나 리스처럼 각 기간 초에 내면 1입니다.
PMT는 고정 이율과 같은 상환액을 가정합니다. 대출 기관이 상환액에 더하는 세금, 보험료, 수수료는 포함하지 않습니다.
PMT가 음수를 반환하는 이유
엑셀의 재무 함수(PMT, PV, FV, NPV, IRR)는 숫자의 부호로 돈의 방향을 나타냅니다. 받는 돈은 양수, 내는 돈은 음수입니다. 대출은 받는 돈이므로 =PMT(B2/12,B3*12,B1)은 나가는 상환액인 -1,580.17을 반환합니다.
그래서 이 페이지의 수식에는 -B1이 있습니다. 대출액이 음수로 들어가고 상환액이 양수로 나옵니다. =-PMT(B2/12,B3*12,B1)도 같습니다. 같은 부호 규칙이 fv도 정하므로 한 방식을 골라 어디서나 쓰세요. 받게 될 저축 목표는 현재 가치 0과 함께 음수 미래 가치로 넣습니다.
대출 기간 비교하기
기간을 한 열에 두고, 금액과 이율은 절대 참조로 고정하고, 수식을 아래로 채우면 상환액과 총비용을 나란히 비교할 수 있습니다.
| A | B | C | |
|---|---|---|---|
| 1 | Amount | $28,000 | |
| 2 | Rate | 7.9% | |
| 3 | Years | Monthly | Total interest |
| 4 | 3 | $876.13 | $3,540.58 |
| 5 | 4 | $682.25 | $4,747.92 |
| 6 | 5 | $566.40 | $5,984.00 |
| 7 | 6 | $489.56 | $7,248.66 |
대출 기간을 3년에서 6년으로 늘리면 상환액은 876.13에서 489.56으로 줄지만, 이자는 3,540.58에서 7,248.66으로 늘어납니다. $ 기호는 절대 참조에서 더 알아보세요.
매달 얼마를 저축해야 하나
pv를 0으로 두고 미래 가치를 주면 PMT는 반대 질문, 즉 목표에 도달하려면 매달 얼마를 모아야 하는지에 답합니다. 목표는 받게 될 돈이므로 납입액이 양수가 되도록 음수로 넣습니다.
| A | B | |
|---|---|---|
| 1 | Goal | $20,000 |
| 2 | Annual rate | 4% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly deposit | $301.66 |
4%로 60개월 동안 매달 301.66을 넣으면 20,000이 됩니다. 납입액의 합은 약 18,100이고 나머지는 이자가 채웁니다. B2를 0%로 바꾸면 납입액은 정확히 20,000을 60으로 나눈 값이 됩니다.
이자와 원금: IPMT와 PPMT
상환액마다 일부는 이자를, 일부는 대출금을 갚습니다. IPMT는 한 번의 상환액 중 이자 부분을, PPMT는 원금 부분을 반환하며, 둘을 합치면 PMT가 됩니다. 두 함수 모두 두 번째 인수로 회차를 받으므로 아래로 채우면 상환 스케줄이 됩니다.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Payment | Interest | Principal | Balance |
| 2 | 1 | $1,580.17 | $1,354.17 | $226.00 | $249,774.00 |
| 3 | 2 | $1,580.17 | $1,352.94 | $227.23 | $249,546.77 |
| 4 | 3 | $1,580.17 | $1,351.71 | $228.46 | $249,318.31 |
| 5 | 4 | $1,580.17 | $1,350.47 | $229.70 | $249,088.61 |
| 6 | 5 | $1,580.17 | $1,349.23 | $230.94 | $248,857.67 |
1개월 차에는 상환액 1,580.17 중 1,354.17이 이자이고 226.00만 잔액을 줄입니다. 원금 부분은 매달 조금씩 늘어납니다. 실제 통합 문서에서는 앞의 시트들처럼 이율, 기간, 금액을 셀에 넣고 $로 참조하세요.
직접 해 보기: 자동차 대출 상환액
| A | B | |
|---|---|---|
| 1 | Loan | $32,000 |
| 2 | Annual rate | 6.9% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly payment |
직접 해 보세요: B5에서 B1의 대출을 B2의 연이율로 B3년 동안 갚을 때의 월 상환액을 구하세요. 양수로 표시하세요.
힌트: 이율과 상환 횟수가 모두 월 단위여야 합니다.
흔한 실수: 월 상환에 연이율 쓰기
이율과 nper는 같은 기간을 써야 합니다. 개월 수를 세면서 이율을 연 단위로 두면 매달 6.5%를 물리게 되어 상환액이 약 10배 높게 나옵니다:
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Wrong | $16,250.00 |
| 6 | Right | $1,580.17 |
틀린 수식은 매달 16,250.00을 요구하는데, 이것은 월 6.5%의 이자일 뿐입니다. 같은 실수의 두 번째 형태는 이율을 6.5%나 0.065 대신 6.5로 입력하는 것으로, 이는 연 650%를 뜻합니다. 상환액이 터무니없어 보이면 먼저 이율의 단위를 확인하세요. 연 단위로 상환한다면 연이율과 연수를 그대로 쓰세요.
자주 묻는 질문
엑셀에서 대출 월 상환액은 어떻게 계산하나요?
=PMT(rate/12, years*12, -amount)를 씁니다. 예를 들어 250,000을 6.5%로 30년 빌리면 =PMT(6.5%/12,30*12,-250000)이고, 매달 약 1,580.17을 반환합니다.
엑셀에서 PMT가 음수로 나오는 이유는 무엇인가요?
엑셀의 재무 함수는 부호로 돈의 방향을 나타냅니다. 받는 돈은 양수, 내는 돈은 음수입니다. 대출금은 들어오는 돈이므로 나가는 상환액은 음수입니다. 상환액을 양수로 보려면 대출액 앞(-B1)이나 PMT 앞에 빼기 기호를 붙이세요.
엑셀에서 대출의 총이자는 어떻게 계산하나요?
상환액에 상환 횟수를 곱하고 대출액을 뺍니다: =PMT(B2/12,B3*12,-B1)*B3*12-B1. 첫해에 낸 이자는 =-CUMIPMT(B2/12,B3*12,B1,1,12,0)을 쓰세요.
PMT, IPMT, PPMT는 어떻게 다른가요?
PMT는 상환액 전체입니다. IPMT는 한 번의 상환액 중 이자 부분, PPMT는 원금 부분이며, 둘을 더하면 PMT가 됩니다. 초기 상환액은 대부분 이자입니다.
엑셀에서 매달 얼마를 저축해야 하는지 계산하려면 어떻게 하나요?
PMT에 현재 가치 대신 미래 가치를 줍니다. =PMT(4%/12,5*12,0,-20000)은 연 4%로 5년 뒤 20,000이 되는 월 납입액입니다.
