엑셀의 숨은 보석, 가상 분석(What-If Analysis)
마이크로소프트 엑셀이 사랑받는 가장 큰 이유 중 하나는 방대한 함수 목록입니다. 그런데 이러한 함수들의 활용도를 한층 끌어올려 주는 숨은 기능들이 있다는 사실을 아시나요? 그중에서도 의외로 많은 사용자가 놓치고 있는 도구가 바로 가상 분석(What-If Analysis)입니다.
엑셀의 가상 분석 도구는 세 가지 핵심 요소로 구성되어 있습니다. 이번 글에서 소개할 것은 그중에서도 특히 강력한 목표 찾기(Goal Seek) 기능입니다. 목표 찾기를 사용하면 수식의 결과에서 거꾸로 거슬러 올라가, 특정 셀에 원하는 출력값이 나오도록 하는 데 필요한 입력값을 자동으로 계산해 줍니다.
목표 찾기 예제: 대출 이자율 계산하기
주택 구입을 위해 모기지 대출을 받으려는 상황을 가정해 보겠습니다. 대출 이자율이 연간 상환액에 어떤 영향을 미치는지 궁금하시죠? 대출 금액은 100,000달러이며, 상환 기간은 30년입니다.
엑셀의 PMT 함수를 사용하면 이자율이 0%일 때 연간 상환액이 얼마인지 손쉽게 계산할 수 있습니다. 스프레드시트는 대략 다음과 같은 형태가 됩니다.
여기서 A2 셀은 연 이자율, B2 셀은 대출 기간(년), C2 셀은 대출 금액을 나타냅니다. D2 셀에 입력된 수식은 다음과 같습니다.
=PMT(A2,B2,C2)
이 수식은 이자율 0%, 30년 만기, 100,000달러 모기지 대출의 연간 상환액을 계산합니다. D2의 값이 음수로 표시되는 점에 유의하세요. 엑셀은 상환금을 재무 상태에서 빠져나가는 마이너스 현금 흐름으로 간주하기 때문입니다.
하지만 현실적으로 어느 대출기관도 0% 이자로 100,000달러를 빌려주지는 않습니다. 계산을 해보니 연간 모기지 상환액으로 6,000달러까지 지불할 수 있다고 가정해 봅시다. 그렇다면 "연간 지불액이 6,000달러를 넘지 않으려면 최대 몇 %의 이자율까지 감당할 수 있을까?"라는 질문이 자연스럽게 생깁니다.
많은 사람들이 이런 경우 A2 셀에 여러 숫자를 일일이 입력해 가면서 D2 값이 약 6,000달러에 도달할 때까지 시행착오를 반복합니다. 그러나 가상 분석의 목표 찾기 도구를 사용하면 엑셀이 이 작업을 대신 처리해 줍니다. 핵심은 엑셀이 D2의 결과에서부터 역방향으로 계산을 진행하여, 연간 최대 지불액 6,000달러 조건을 충족하는 이자율을 스스로 찾아내도록 하는 것입니다.
목표 찾기 실행 단계
먼저 리본 메뉴에서 데이터 탭을 클릭하고, 데이터 도구 섹션에 있는 가상 분석(What-If Analysis) 버튼을 찾습니다. 버튼을 클릭한 후 메뉴에서 목표 찾기(Goal Seek)를 선택합니다.
작은 창이 열리면 단 세 가지 변수만 입력하면 됩니다.
- Set Cell(수식 셀): 수식이 들어 있는 셀을 지정합니다. 이 예제에서는 D2입니다.
- To Value(목표값): 분석 종료 시 D2 셀이 갖길 원하는 값입니다. 우리의 경우 -6,000입니다. 엑셀은 지불금을 음수 현금 흐름으로 인식한다는 점을 잊지 마세요.
- By Changing Cell(변경할 셀): 100,000달러 대출의 연간 비용이 6,000달러가 되도록 엑셀이 찾아낼 이자율이 위치할 셀입니다. 즉, A2 셀을 지정합니다.
확인(OK) 버튼을 클릭하면 엑셀이 각 셀에서 일련의 숫자들을 빠르게 시험해 가며, 반복 계산(iteration)을 통해 최종 값에 수렴하는 과정을 거칩니다. 이 예제의 경우 A2 셀에는 약 4.31%가 표시됩니다.
결과 해석하기
이 분석 결과는 30년 만기 100,000달러 모기지 대출에서 연간 지불액을 6,000달러 이하로 유지하려면, 이자율이 최대 4.31% 이하인 조건으로 대출을 받아야 한다는 의미입니다. 가상 분석을 계속 활용하고 싶다면 다양한 숫자와 변수 조합을 시도해 보면서, 유리한 모기지 금리 조건을 폭넓게 탐색해 볼 수 있습니다.
마무리
엑셀의 가상 분석 목표 찾기 도구는 스프레드시트의 다양한 함수와 수식을 보완하는 강력한 도구입니다. 셀 안의 수식 결과에서 거꾸로 거슬러 올라가 계산하기 때문에, 계산에 포함된 여러 변수를 훨씬 명확하게 파악하고 실험할 수 있습니다. 대출 상환 계획, 판매 목표 설정, 손익분기점 분석 등 실무에서 다양하게 활용해 보시기 바랍니다.