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

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

예를 들어 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