Calc formula to divide interest earned by 2 owners by %

[larsk3]

Hi,
Can some calc wizard help me with the following?

A simple savings account jointly held by 2 people, interest paid monthly.

One party has 381,333 in the account and the other party has 78,556 in the account. The funds are co-mingled to get the highest interest rate step, which requires a balance of over 400,000.

I need instructions on how to create a spreadsheet where I am able to input the interest earned each month and apportion the interest earned to the corresponding percentage of each owners participation.

Something like: 3000.00 interest earned, split to 'Owner A' $2400 for having 80% of the account balance and to 'Owner B' $600 for having 20% of the account balance, and totaling the new ownership balance and new percentage of ownership every month, of course.

Also, some way to reduce either owners balance for their personal withdrawals before the apportionment of interest calculation, strictly on a "day before interest was paid" basis, not by the exact date of withdrawal, which is too complicated and fussy.

I need to go through 5 years of monthly statements.... please help!

Thank you.
_________________
Lars Kindem

[keme]

If movements in the account are fairly regular and/or relatively small, the final balance for each month should provide a reasonable basis for the dividing. Then, to find interest earned for each account holder, you can multiply the interest earned by each holder's balance, and divide by the overall balance on the account.

Assuming...
... earned interest for the month is in cell A1,
...balance for account holder A is in cell D1
...balance for account holder B is in E1
Formulas:
=A1*D1/SUM($D$1:$E$1) will give the interest earned for acct. holder A
=A1*E1/SUM($D$1:$E$1) will give the interest earned for acct. holder B

Note that Calc behaves in a strictly mathemathical way, which in some cases can lead to inconsistencies when doing accounting calculations. Calc calculations will be performed to approx. 14 valid digits, while monetary transactions are generally rounded to the nearest penny (i.e. two decimals). Rounding to two decimals can give an "extra penny" here and there unless you take steps to cater for that (occurs if the split is exactly on half a penny). This accumulated rounding error will not amount to much with montly calculations for 5 years, so I wouldn't bother to adjust the formulas for it. I mentioned it so you know why when you see that there's a small difference in some sum.

Also, make sure you keep earned interest and capital separate for the correct periods, so the compound interest does not introduce any error. (I believe annual capitalising of interest is the usual.)