카테고리 없음

엑셀로 투자 수익 분배 쉽게하기

RichFebru 2025. 11. 10. 07:28
반응형

가끔 친구/지인과 투자를 할 때가 있죠?

예를 들어 5명이 있고 수익·분배율이 각각 다를 때, 개인 패널티를 합산하면 전체 패널티 풀이 나옵니다. 그 금액을 분배율의 총합으로 나눠 개인 분배율을 곱하면 각자 가져갈 배당분이 계산됩니다. 마지막으로 **원래 내 몫(수익×분배율)**을 더하면 총수익이 됩니다.
핵심은 “패널티는 모두 모아서, 분배율 비례로 돌린다”는 점입니다.

엑셀은 어떤 개념이나 계산방법에 대한 정확한 정의를 바탕으로 함수를 사용해야 합니다.

핵심

  • 한 셀로 끝내는 공식: 총수익 = 수익×분배율 + (전체 패널티 합)×(내 분배율/분배율 합)
  • 엑셀 범위가 **B11:B15=수익, C11:C15=분배율(%)**일 때, D11에 아래 수식 입력 후 아래로 복사하면 자동 계산 완료.
  • =B11*C11 + SUMPRODUCT($B$11:$B$15*(1-$C$11:$C$15))*C11/SUM($C$11:$C$15)
  • 분배율은 반드시 % 형식으로 입력(60% 등). 텍스트 “60%”가 아니어야 함.

엑셀함수 - SUMPRODUCT

왜 이 공식이 맞는가 (근거)

  1. 개인 패널티 = 수익×(1−분배율). 분배율이 100%가 아니면 그만큼이 미수분(패널티)입니다.
  2. 전체 패널티 풀 = 각 개인 패널티의 합 = SUMPRODUCT(수익범위, 1−분배율범위).
  3. “차감된 패널티 금액을 나머지가 나눠 갖는다”는 규칙을 분배율 비례 배분으로 해석하면 가장 일관성 있고 투명합니다. 즉, 내 몫 = 전체 패널티×(내 분배율/분배율 합).
  4. 최종 총수익 = 원래 내 몫(수익×분배율) + 패널티 배당분.

실무 적용: 셀 기준 예시

  • 범위: B11:B15=수익, C11:C15=분배율(%), D11=총수익 시작 셀
  • 입력 수식(한 줄):
=B11*C11 + SUMPRODUCT($B$11:$B$15*(1-$C$11:$C$15))*C11/SUM($C$11:$C$15)
  • 아래로 복사하면 각 행(각 사람)의 총수익이 즉시 계산됩니다.

(선택) 표(테이블)로 더 견고하게

B10:C15를 Ctrl+T로 표로 만들고 표 이름을 T로 지정했다면:

=[@[수익]]*[@[분배율]] + SUMPRODUCT(T[수익]*(1-T[분배율]))*[@[분배율]]/SUM(T[분배율])

행이 늘어나도 자동 추적되어 유지보수가 편합니다.

숫자로 감 잡기(간단 검증)

예를 들어 5명이 있고 수익·분배율이 각각 다를 때, 개인 패널티를 합산하면 전체 패널티 풀이 나옵니다. 그 금액을 분배율의 총합으로 나눠 개인 분배율을 곱하면 각자 가져갈 배당분이 계산됩니다. 마지막으로 **원래 내 몫(수익×분배율)**을 더하면 총수익이 됩니다.
핵심은 “패널티는 모두 모아서, 분배율 비례로 돌린다”는 점입니다.

다른 배분 규칙이 필요한 경우(대안 수식)

상황에 따라 괜찮은 변형이 있습니다. 아래 괄호 부분만 바꾸면 쉽게 전환됩니다.

  1. 균등배분(모두 같은 금액 수령)
=B11*C11 + SUMPRODUCT($B$11:$B$15*(1-$C$11:$C$15))/COUNTA($B$11:$B$15)
  1. 분배율 100%만 보상(100%인 사람끼리 균등)
=B11*C11 + IF(C11=1, SUMPRODUCT($B$11:$B$15*(1-$C$11:$C$15)) / COUNTIF($C$11:$C$15,1), 0)
  1. 수익 비례 배분(분배율 대신 ‘기여 수익’ 기준)
=B11*C11 + SUMPRODUCT($B$11:$B$15*(1-$C$11:$C$15))*B11/SUM($B$11:$B$15)

표시·반올림 팁

  • 10원 단위 반올림
  • =B11*C11 + MROUND(SUMPRODUCT($B$11:$B$15*(1-$C$11:$C$15))*C11/SUM($C$11:$C$15),10)
  • 소수 첫째자리 반올림: ROUND( … , 1)로 감싸기.
  • 통화 서식은 “표시 형식 > 회계/통화”로 맞추면 깔끔합니다.

오류 방지 체크리스트

  • 분배율은 % 서식으로 입력(예: 60%). “문자열 60%”면 계산 실패 가능.
    • 불가피할 땐 VALUE(SUBSTITUTE(C11,"%",""))/100로 변환.
  • 합계가 0이 되는 특수 상황(분배율 합=0 등)에서는 IFERROR(… ,0)로 방어.
  • 범위 고정은 **절대참조($)**로 묶기: $B$11:$B$15, $C$11:$C$15.

마무리

이 글의 핵심은 한 줄 수식으로 실무 규칙을 명확히 모델링하는 것입니다.

  • 규칙이 분배율 비례 재배분이라면 맨 위 수식이 가장 간단하고 투명합니다.
  • 운영 정책이 바뀌면 괄호만 바꿔 대안 수식으로 즉시 전환 가능합니다.
  • 표(테이블)를 쓰면 행 추가·삭제에도 자동으로 따라와 유지보수 비용이 거의 들지 않습니다.

수익분배.xlsx
0.01MB