[재테크][IT] Excel VBA로 수익 복리 효과 자동으로 계산해주는 프로그램 만들기 (feat. 스노우볼 효과 자동 계산기)

 

parsimony.tistory.com/3

당장 "투자"를 시작해야하는 이유 (feat. 스노우볼, 복리의 마법)

지난 포스팅에서 투자를 당장 시작해야하는 이유를 얘기하면서 복리 효과를 직접 계산해보았다.

 

이번에는 투자금액, 이율, 기간 등 매개변수가 바뀔 때마다 계산기 다시 두들길 필요 없이 입력값에 따라 자동으로 복리 효과를 계산해주는 프로그램을 만들어 볼 것이다.

 

"준비물은 Excel 딱 하나만 있으면 된다."

 

Excel에는 VBA(Visual Basic for Application)라고 엑셀 자체에 내장되어있는 프로그래밍 툴이 있다.

이것을 활용하면 복잡한 수식을 계산하는 함수를 만들거나, 유용한 기능을 자동화 할 수 있다.

 

 

투자금액, 이율, 기간 등을 설정하면, 불어나는 자산금액을 자동적으로 계산해주는 기능을 프로그래밍 해볼 것이다.

 

작업의 결과물부터 보자면, 다음과 같다.

 

 

첫 투자금액, 연수익률, 투자 기간 그리고 임금이 상승한다는 가정하에 그 임금상승분까지 포함하여 계산할 것이다.

다음 방법을 차근차근 따라하면 된다.

 

 

<VBA 프로그래밍으로 복리 계산기 만들기>

0. 우선 엑셀을 켜서 아래와 같이 표를 작성해준다. 프로그래밍에 앞서 입력 및 출력에 사용될 셀의 위치를 특정시키키 위함이다. 여기서는 C2, C3, C4, C5가 입력으로 사용되고, C7, C8, C9가 출력으로 사용된다.

1.  Alt + F11을 눌러 VBA 에디터 창을 연다.

 

2. VBAProject에 마우스 오른쪽 버튼 누르고 새 모듈을 생성한다.

 

3. 다음과 같이 코드를 작성한다.

-- 간략한 코드 설명 --

(1) "Dim"은 VBA에서 사용되는 변수 선언문이다.

Dim 뒤에 나오는 단어는 '변수(variable)'가 되어서 '값(Value)'을 넣을 수 있다. 

 

(2) '변수'뒤에 나오는 As는 자료형을 지정할 수 있게 한다. "integer"는 정수형이고, "Double"은 실수형이다.

     (자료형에 대한 자세한 내용은 별도 포스팅)

 

(3) "String"은 배열 자료형을 의미한다. "ReDim"을 이용해서 배열의 크기를 지정할 수 있다.

     (배열에 대한 자세한 내용은 별도 포스팅)

 

(4) 엑셀에서 반복문은 이와 같이 작성한다.

    For i = 1 To y
        something
    Next i

 반복 횟수 체크를 하는 변수 i와 끝을 가리키는 변수 y로 For문을 시작하여

 something에는 반복하고자 하는 코드를 작성하고,

 끝으로 "Next i"를 작성하면 끝.

 

(5) "Cells(a, b)"는 변수처럼 사용가능해서 다른 변수로부터 값을 받아오거나 줄 수 있다. Cells가 좌변에 있으면 출력이 되고, Cells가 우변에 있으면 입력이 된다. 

 

변수, 자료형, 배열, 반복문 등 문법에 대한 자세한 내용은 별도로 포스팅 할 것이다.

지금 잘 이해가 안 가는 분들은 그냥 아래 코드를 복사해서 붙여넣어주면 된다.

 

Sub calculator()
    
    '입력
    Dim First As Double '첫투자금액
    First = Cells(2, 3)
    Dim Inc As Double '  임금상승률
    Inc = 1 + Cells(3, 3) / 100
    Dim r As Double '          이율
    r = 1 + Cells(4, 3) / 100
    Dim y As Integer '     투자년수
    y = Cells(5, 3)
    
    Dim Principal() As String '원금
    ReDim Principal(y)
    Dim Earning() As String '  이득
    ReDim Earning(y)
    Dim Asset() As String '  총자산
    ReDim Asset(y)
    
    Dim i As Integer  '    반복횟수
    
    '초기화
    Asset(0) = First
    Earning(0) = 0
    Principal(0) = First
    
    '계산 실행
    '반복문으로 해마다 쌓인 원금, 이득, 자산을 각 배열에 저장
    For i = 1 To y
        Asset(i) = Asset(i - 1) * r + First * Inc ^ i
        Principal(i) = Principal(i - 1) + First * Inc ^ i
        Earning(i) = Asset(i) - Principal(i)
    Next i
    
    '출력
    Cells(7, 3) = Principal(y)
    Cells(8, 3) = Earning(y)
    Cells(9, 3) = Asset(y)

End Sub

 

4. 입력 셀에 값을 넣고 실행 버튼(or F5)을 누른다.

 

① 첫 투자금액, 임금상승률, 연 수익률, 투자 기간을 각각 입력한다.

② VBA 창에서 "실행 - Sub/사용자 정의 폼 실행" 또는 "F5"를 눌러서 실행한다.

③ 프로그램이 계산한 금액이 출력 셀에 출력된다.

 

 

 

"...이걸 계속 눌러줘야 해?"

 

근데 만들고 보니 이 계산 기능을 쓸 때마다 VBA 창을 열어서 F5키를 눌러줘야하는 게 꽤 번거로운 일이다. 우린 엑셀에서 Function을 사용하여 실시간으로 값을 딱딱 반영해주는 것에 적응되어 있기 때문에 실행 때마다 VBA창 열어서 실행 버튼을 누르는 것은 은근 고역이다.

 

그럼 VBA 창을 열지 않고 셀에 값을 입력할 때마다 계산기가 자동으로 동작하게 할 순 없을까?

 

다행히 VBA에는 Worksheet 자체 프로시저가 존재해서 Excel에서 특정 셀 값이 변경될 때 동작하는 매크로를 설정할 수 있다.

 

자동으로 계산기가 동작하게 하기 위해서 다음 방법을 따라한다.

 

5.  Microsoft Excel 개체 - Sheet1(또는 현재 사용 중인 워크시트) 을 더블클릭한다.

 

6. (일반)을 worksheet로 바꾸고, (선언)은 Change를 선택한다. 

Selection Change 부분은 지워준다.

 

7. 아래 코드를 작성한다. 

 

-- 간략 코드 설명 --

 

(1) If 로 시작하는 문장은 조건문이며, 첫 줄이 조건 식이고 그 다음에 따라오는 문장은 조건에 맞아들었을때 동작하는 코드가 된다. 조건문의 끝은 "End If" 로 끝난다.

 

(2) "Intersect"는 단어가 내비치는 표현 그대로 겹치는 범위를 돌려주는 기능을 한다.

 

(3) "If Not Intersect( ) Is Nothing Then"은 이중 부정문 = 긍정문이다. 즉, Range("C2:C5")와 Target이 겹치는 부분이 있다면, 이 조건문은 참이되어 다음 문장이 동작하게 된다. Target은 셀 값이 변하는 영역(두꺼운 사각형)을 가리킨다. 따라서 Range안에서 어떤 값이 변하게 되면, 이 조건은 참이 되어 다음 코드가 동작하게 된다.

 

(5) Call은 다른 프로시저를 호출한다. 

"Call Module1.calculator" 라고 쓰면, 모듈1에 있는 Calculator라는 프로시저를 호출하는 것이다.

 

 

문법에 대한 상세한 내용은 별도로 포스팅 할 것이다.

지금 잘 이해가 안 되면 그냥 다음 코드를 따라치면 된다.

 

(!) 이 코드는 반드시 Worksheet_Change 안에서 작성해야 된다.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Range("C2:C5"), Target) Is Nothing Then
        Call Module1.calculator
        
    End If
    
End Sub

저장 후 창 닫기.

 

8. 완성

투자액, 임금상승률, 연 수익률, 투자기간을 차례로 입력해보자.

N년 후 당신의 자산이 얼마나 불어나는 지 실시간으로 볼 수 있다.

파라미터 값을 바꿔가면서 살펴보자.

 

이율 10%에 투자년수가 15년이 넘어가면 이자가 원금을 앞지른다.