[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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

Joined: 30 Aug 2004
Posts: 2867
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.)
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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