OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calc formula to divide interest earned by 2 owners by %

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
larsk3
Newbie
Newbie


Joined: 17 Mar 2010
Posts: 1

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

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
Back to top
View user's profile Send private message
keme
Moderator
Moderator


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

PostPosted: Thu Mar 18, 2010 2:47 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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