엑셀 대출 이자 계산기 만들기, 원리금 상환 계획표 자동 생성 방법

내 집 마련이나 투자를 위해 대출을 고민하다 보면, 매달 나가는 원금과 이자가 정확히 얼마인지 계산해 보는 과정이 꼭 필요합니다. 은행 사이트 계산기도 좋지만, 직접 엑셀로 상환 계획표를 만들어두면 중도 상환 계획을 세우거나 자산 관리를 할 때 훨씬 유연하게 활용할 수 있습니다.

오늘은 엑셀의 재무 함수를 활용해 대출 금액, 이자율, 기간만 입력하면 매달 상환액을 자동으로 계산해 주는 대출 계산기 제작법을 공유합니다.

1. 대출 계산기에 꼭 필요한 재무 함수

엑셀에는 복잡한 대출 계산을 도와주는 전용 함수가 있습니다. 가장 대표적인 것이 PMT 함수입니다.

• PMT 함수: 매달 갚아야 할 원금과 이자의 합계(원리금)를 계산합니다.
• 사용법: =PMT(월이율, 총상환횟수, 대출원금)

이 함수 하나만 알면 매달 은행에 내야 할 돈을 1초 만에 구할 수 있습니다.

2. 상환 계획표를 한 번에 만드는 매크로

함수 입력도 좋지만, 대출 기간이 길어지면 수백 줄의 행을 일일이 채우기 번거롭죠. 아래 매크로를 이용하면 대출 정보 입력 시 전체 기간의 상환 스케줄을 자동으로 그려줍니다.

Alt + F11을 눌러 모듈을 추가하고 아래 코드를 복사해서 넣어보세요.

Sub CreateLoanSchedule()
    Dim Principal As Double ' 대출 원금
    Dim AnnualRate As Double ' 연 이자율
    Dim Term As Integer ' 대출 기간(개월)
    Dim MonthlyRate As Double ' 월 이율
    Dim MonthlyPayment As Double ' 매월 상환액
    Dim i As Integer
    
    ' 1. 사용자로부터 기본 정보를 입력받습니다.
    Principal = InputBox("대출 원금을 입력하세요 (예: 100000000)", "대출 금액")
    AnnualRate = InputBox("연 이자율을 입력하세요 (예: 0.04)", "이자율")
    Term = InputBox("대출 기간을 개월 수로 입력하세요 (예: 36)", "대출 기간")
    
    MonthlyRate = AnnualRate / 12
    ' PMT 함수와 동일한 로직을 VBA로 구현
    MonthlyPayment = WorksheetFunction.Pmt(MonthlyRate, Term, -Principal)
    
    ' 2. 시트 정돈 및 제목 작성
    Cells.Clear
    Range("A1:D1").Value = Array("회차", "원리금 상환액", "이자액", "납입원금")
    
    ' 3. 반복문을 통해 상환 계획표 자동 생성
    For i = 1 To Term
        Cells(i + 1, 1).Value = i ' 회차
        Cells(i + 1, 2).Value = MonthlyPayment ' 매월 고정 상환액
        Cells(i + 1, 3).Formula = "=B" & (i + 1) & "-D" & (i + 1) ' 이자 계산
        ' 상환 원금 계산은 엑셀 내장 PPMT 함수 활용
        Cells(i + 1, 4).Value = WorksheetFunction.Ppmt(MonthlyRate, i, Term, -Principal)
    Next i
    
    MsgBox "상환 계획표 생성이 완료되었습니다.", vbInformation, "작업 완료"
End Sub

3. 실무 및 재테크 활용 팁

위 매크로를 실행하면 회차별로 원금과 이자가 어떻게 구성되는지 한눈에 볼 수 있습니다. 원리금 균등상환 방식은 초기에는 이자 비중이 높고 나중에 원금 비중이 높아지는데, 이 표를 보면 중도 상환 시점을 언제로 잡는 것이 유리한지 판단하는 데 큰 도움이 됩니다.

또한, 이 시트에 지난번 알려드린 '하이라이트 매크로'를 연결하면 특정 금액 이상의 이자가 나가는 구간을 시각화할 수도 있습니다.

4. 마치며

자산 관리의 첫걸음은 내 돈의 흐름을 정확히 파악하는 것에서 시작합니다. 오늘 공유해 드린 엑셀 대출 계산기를 통해 더 꼼꼼하고 스마트한 재테크 계획을 세워보시길 바랍니다.