150 엑셀 목표값찾기 재무함수 pmt

150_엑셀 초급 강좌 – 데이터 분석 (1)

엑셀에서 PMT 재무 함수는 가장 널리 쓰이는 함수라고 생각되고 저도 가끔 집 대출금을 계산할 때 엑셀을 실행 시켜 볼 정도로 유용한 함수 입니다. PMT함수는 대출금의 원금에 대해 연 이자율과 상환금액을 바탕으로 월 납부 금액을 계산해 주는 함수 입니다. 여러 재무 함수들이 있지만 가장 보편적인 함수이고 좀 특별한 대출일 경우 이 함수는 적용할 수 없습니다. (위키피디아에 회계 부분을 참조 바랍니다. ) 이 재무 함수는 특히 엑셀의 분석 도구인 목표값 찾기와 같이 쓰이면 그 효과가 배가 됩니다. 이번 포스팅에서는 먼저 목표값 찾기가 어떤 기능인지 알아보고 PMT함수에 적용 해서 어떻게 유용하게 이용되는지 소개 하겠습니다.

이번 포스팅은 리본 데이터 탭의 가상 분석에 관한 내용입니다.

 

150연습파일.xlsx

150연습파일_완성본.xlsx

1. 목표 값 찾기

   다들 학창 시절 시험을 몇 개 망치고 남은 시험을 몇 점 받으면 평균 90점을 받을 수 있을까 열심히 고민 하셨던 기억이 있을 것 입니다. 이때 사용 할 수 있는 기능이 바로 엑셀의 목표 값 찾기 입니다. 내가 지금까지 친 시험은 이미 평균으로 구해 놓았을 테고 남은 시험의 점수를 해 구하기로 찾는 것이 아니라 (이 경우는 SOLVER라고 합니다) 엑셀이 여러가지 가상의 데이터를 빠르게 대입하여 원하는 목표 값을 찾아 줍니다. 다음 포스팅에 해 찾기에 대해서 소개 하겠지만 약간은 다릅니다. 목표 값 찾기는 내가 수식을 써서 얻어 놓은 값이 다른 데이터를 하나 바꾸어서 얻어 놓은 값을 원하는 값으로 바꾸는 기능입니다. 평균을 예제로 들면 쉬운데 이렇게 말로 설명하면 내가 해놓고도 어렵네요.

    

   이렇게 내가 시험 친 점수를 바탕으로 이미 평균을 구했습니다. 앞으로 체육을 몇 점 받으면 평균 90이 될까요?

   

    

   자 일단 수식이 있어야 목표값 찾기가 됩니다. 우리는 기존에 친 시험 평균을 수식으로 구해 놓았죠? OK

   그 다음 조건, 목표값 >> 평균 90점 OK

   바꾸는 셀 >> 체육 점수 OK , 체육 점수가 빈셀이 아니고 다른 데이터가 있어도 목표값 찾기는 성립됩니다. 셀의 데이터를 하나 바꾸는 것 이니까요

    

   엑셀 데이터 탭으로 가셔서 목표값 찾기를 클릭 합니다.

   

    

    

   지금 설명 드렸던 대로 셀을 지정 하시면 됩니다. 수식 셀을 우리가 이미 쓴 평균, 찾는 값이 목표값 즉 90, 값을 바꿀 셀이 체육 점수 셀 입니다.

   

    

   목표값 찾기를 처음 실행 하실 때 수식 셀에 커서를 가져다 두고 실행하면 편합니다.

    

   체육 점수를 100점 맞아야 평균 90점이 되네요. 쉽죠?

   

    

2. 재무 함수 PMT

   내가 은행으로부터 연이율 5프로에 100만원을 빌려서 36개월 상환으로 값는다. >> 재무 함수 PMT 입니다.

   함수 활용법

   =PMT(연이자/12,납부개월수,원금)은 월 납부 금액을 반환 합니다.

   여기서 연이자를 12로 나누는 것은 1년에 12개월이 있기 때문입니다. 만약 6개월 이자라고 하면 6으로 나누면 되는데 일반적인 경우는 아닙니다.

    

   만약 100만원을 빌려서 5프로의 이율로 3년에 걸쳐서 상환하면 월 얼마를 갚아야 하는지 계산하고 싶습니다.

   

    

    

   PMT함수 안에 인수를 직접 숫자로 입력 하셔도 되고 이렇게 셀에 있으면 셀 주소를 넣어도 됩니다.

   

    

    

   100만원을 빌리면 29971원씩 매달 납부해야 합니다.

   

    

   여기서 값이 –가 나오는 것은 PMT함수는 100만원에서 일정 금액을 차감해서 0원을 만드는 함수 이기 때문입니다. 간단히 인수를 –로 바꾸면 양수로 나옵니다.

    

   이렇게 셀을 –로 바꾸면 됩니다.

   

    

    

   29971원이 이제 양수로 얻어 졌습니다.

   

    

3. PMT 함수 목표값 적용하기

   한달에 30000원 가까이 되는 부담스럽지만 은행 이율을 바뀌는게 아니죠. 그렇다면 내가 한달에 20000원을 갚기 위해서는 얼마의 원금을 빌려야 할까요?

   목표값 찾기의 조건이 완성되었습니다.

   PMT 함수로 함수를 적용함 >> 수식 셀

   한달 상환금액 >> 목표값

   원금을 바꾼다 >> 값을 바꿀 셀

    

   역시 수식 셀에 커서를 두고 목표값 찾기를 실행 하면 편합니다.

   

    

    

   한달에 20000원씩 납부하기 위해서는 667314원을 빌려야 하네요.

   

    

    

   셀 수식 때문에 소수점 자리가 보이지 않는 것이지 엑셀은 최대한 정확한 값을 계산 합니다. 경우에 따라 소수점이 중요하다고 하면 당황하지 말고 셀 서식만 바꿔주시면 됩니다.

   

    

   재무 함수는 참 유용한 함수 이지만 내가 원하는 대출 원금이나 이율을 계산할 때 이 목표값 찾기와 같이 쓴다면 그 효율은 배가 됩니다.