Interest distribution

[Ufoo]

Hello excel experts,

The attached excel sheet shows contributions made by different people towards an investment. The investment started in July 2017. By May 2018 an interest has been accrued (say 5,000,000); and is supposed to be distributed among shareholders based on 2 factors:

i) The amount of money that a person has invested. This is easy.
ii) The span of time that someone's money has been in the investment. So that Emanuel (the last person in the excel sheet) who joined in January 2018 will not be in the same footing with those who started. I have struggled to get this formula. Someone please help.

Thanks @Hui for your help. However, I still cannot get the formula right.

Cheers,
U4

 

[AlanSidman]

I have assumed a 5% per annum interest rate. You can change this as needed.

Look at the attached and see if that does what you are looking for.

 

[Ufoo]

Thank you so much Allan. This would be perfect if the interest rate was constant. However it is not. We contributed money and put it in a business. Now we have a lump sum interest which should be distributed to each member according to the factors that I have discribed. I will appreciate further assistance

 

[Haz]

Check the attached file.

 

[Ufoo]

Thanks a lot.