대출기간 산출

일단 수식을 도출한 후 엑셀함수를 걸도록 합시다. 수식 도출은

 

대출금을 b, 이자율을 r, 매년 상환액을 a라고 두고, 대출 받자 마자 1회 상환을 하는 걸로 합시다. (1년 후 첫 상환을 한다면 아래를 참고하여 적절히 계산할 수 있을 겁니다.) 그럴 때

 

0년 후(대출 받았을 때) 잔액 : b-a

1년 후 잔액 : (b-a)(1+r) -a

2년 후 잔액 : (b-a)(1+r)^2 - a(1+r) -a

3년 후 잔액 : (b-a)(1+r)^2 - a(1+r)^2 - a(1+r) - a

n년 후 잔액 : (b-a)(1+r)^(n-1) - a(1+r)^(n-1)-...-a(1+r) - a

 

등비수열의 합 공식 a+ar+ar^2+...ar^(n-1) = a(r^(n-1)-1)/(r-1)을 적용하여 정리하면

 

n년 후 잔액

=(b-a)(1+r)^(n-1) - a((1+r)^(n-1)-1)/(1+r-1)

=(b-a)(1+r)^(n-1) - a((1+r)^(n-1)-1)/r

=(1+r)^(n-1)(b-a-a/r) + a/r

 

이제 n년 후 잔액이 0이 되는 n을 구하면 되므로

(1+r)^(n-1)(b-a-a/r) + a/r = 0

a/r = (1+r)^(n-1)(a+a/r-b)

(1+r)^(n-1) = (a/r)/(a(1+r)/r -b)

(n-1)log(1+r)=log((a/r)/(a(1+r)/r -b))

n=1+(log((a/r)/(a(1+r)/r -b)))/log(1+r)

 

	B	C
2	대출금	5,000,000
3	금리	0.54%
4	상환금	515,017
5	기간	9.94

 

C5 =1+(LOG((C4/C3)/(C4*(1+C3)/C3 -C2),2))/LOG(1+C3,2)

 

월 금리 6.5%/12 = 0.54%를 입력하면 10개월이 C5에 나옵니다.

 

(2008.11.13 이건 PMT 함수의 사촌인 NPER 함수를 설계한 로직일 것임. 나중 다룰 일이 있을 듯)