View previous topic :: View next topic 
Author 
Message 
larsk3 Newbie
Joined: 17 Mar 2010 Posts: 1

Posted: Wed Mar 17, 2010 5:34 pm Post subject: Calc formula to divide interest earned by 2 owners by % 


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 comingled 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 

Back to top 


keme Moderator
Joined: 30 Aug 2004 Posts: 2910 Location: Egersund, Norway

Posted: Thu Mar 18, 2010 2:47 am Post subject: 


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.) 

Back to top 




You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group
