How to calculate individual share?

[karthick87]

We are 4 in number, and we used to share the expense end of the every month. Total Monthly Rent for the accomodation is 20,000. We do have miscellaneous expense every month, for example User1 is spending 12000 for the miscellaneous expense for the current month. User3 is spending 6000 for the miscellaneous expense for the current month and User3 is Paying Rent Rs.20000 in advance and finally user4 is spending only 800rs for the miscellaneous expense.

We need to calculate everybody's equal share, and share it with the person who has shared more money. I have shared the sample excel file with the required fields. Could somebody help me?

 

[Faseeh]

Hi Karthick,

By Equal Share you mean Corresponding Share? If yes then formula is simple:

A person's Share = (Person's Expense) / Total Expense

 

[karthick87]

No for example, consider the fixed amount (rent) is 1000. User1 & User2 has spend rs100 for some miscellaneous expense, user3 has given the rent in advance (Rs.1000) and user4 has not spend anything for the current month.

Total Rent Amount = 1000 / 4 = 250
So now it should be.
User1 should give 150rs+50rs =200rs to User3
User2 should give 150rs+50rs =200rs to User3
User4 should give= 250(Rent)+User1 (100/4) + User2 (100/4) =300 to User3
User3 need not give any amount to anybody. (His individual share 250+50 =300, as he has already given the rent amount which is 1000. He will get the remaining amount 700 from other three users.

 

[karthick87]

Can anybody help me on this?

 

[Faseeh]

How will you achieve this in this table, can you fit the example that you have provided into the sample sheet you have uploaded. When i just tried it, i found that it should be like a a table, plotted people against people and showing transaction. Can you show how does it fit in your sheet?

 

[karthick87]

Hello Faseeh,

I have attached an sample excel sheet with this message for your reference. Please let me know what better could be done on the excel.