Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Apportioning interest rec'd (XP/2002)

    Hi All,

    I'm trying to figure out a way that I can write a formula to "split" an amount of income received in a single bank account between two "virtual accounts" that the account looks after. Throughout a period there will be deposits and withdrawals which can be ascribed to a particular "virtual account" and I want to apportion the interest received on the whole balance between the two "virtual accounts" according to balance (I suppose essentially on a daily basis).

    For example, at the end of a period (most probably quarterly) the account will receive an amount of interest (say $100) and the account has had the following transactions...

    Jan 1 Deposit $5000 (for virtual account 1)
    Jan 28 Deposit $3000 (for virtual account 2)
    Feb 16 Withdrawal $1000 (from virtual account 1)

    The end balance is $7000 (1=$4000 and 2=$3000) but account 1 has had more invested for longer than account 2. I could easily apportion the interest based on closing balance but if virtual account 2 deposited $100,000 on the last day before interest was calculated the resulting apportionment would be way out of kilter?

    Can anyone give me some guidance on a cell calculation I should use?

    Thanks,

    Stuart

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apportioning interest rec'd (XP/2002)

    <hr>this crude example<hr>
    crude, <img src=/S/nope.gif border=0 alt=nope width=15 height=15> NOT; genius, <img src=/S/yep.gif border=0 alt=yep width=15 height=15> yes
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apportioning interest rec'd (XP/2002)

    John,

    Thank you very very much. I've always wondered about the operation of SUMPRODUCT()... Those ("descriptor - take your pick") actuaries have used it on me so many times...

    Am I correct in saying that your column B (days elapsed) should be a decreasing series (in that the day before the "interest payment" will be "1")?

    Cheers,

    Stuart

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apportioning interest rec'd (XP/2002)

    Correct, see the values for John's example in the attached worksheet. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Apportioning interest rec'd (XP/2002)

    Hi folks,

    I think this is an interesting, but quite erroneous, use of SUMPRODUCT. The problem with it is that the intervals in column B should only be one day, not decreasing values starting at 31. A solution based on decreasing values would, I think, only be appropriate if the amounts each day were net deposits. What is represented, though, is account balances. Accordingly, the 'correct' interest apportionment would be based on the ratio of the average daily balances in the two accounts, and Account 1 in the example should thus receive 62.5% of the interest.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Apportioning interest rec'd (XP/2002)

    I'd like to see an example of what you mean. The problem with using an ADB is that it doesn't take into account the time that the deposits are earning interest.

    The reason that I said my method is crude relates to the issue that it doesn't take into account compounding over the period. However, daily compounding is rare, and at moderate and lower interest rates, will have a trivial effect.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Apportioning interest rec'd (XP/2002)

    As you see, Macropod doesn't agree with me, and I have responded by explaining the limitations and oversimplifications of my approach. But in turn I don't agree with him in using only ADB; it has to be ADB weighted by the period of time each sum of money is held. That is why I'm using the decreasing series, to take into account the number of days held to the end of the month. And I intend this only to be an apportionment approach, not an interest calculation; to use it an interst calculation would be very wrong. (Maybe that's what Macropod is thinking I intended.) One other limitation of my approach is that it assumes a constant interest rate for the period.

    Actuaries? Actuaries are people who wanted to be accountants but didn't have the personality. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> (I'm an accountant by training, I work closely with an actuary.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apportioning interest rec'd (XP/2002)

    Macropod is right.

    Consider the funds held on January 1 - they are credited for 31 days. Funds held on January 31 are credited for one day. If "Account A" placed $100,000 on deposit on January 1, then withdrew the amount on January 2 and had no further transactions for the month, the sunmproduct calculation would credit "A" for 31x100,000 = 3.1M $-days. If "Account B" placed the same amount of $100,000 on deposit on January 31, the sumproduct calculation would credit them for 1x100,000 = 100,000 $-days. In this case, the total on deposit would be 3.2M $-days, and we would apportion the interest as follows:
    <pre>to A: 3,100,000 / 3,200,000 = 96.875%
    to B: 100,000 / 3,200,000 = 3.125%
    </pre>

    This is obviously not correct - both accounts have had the same amount on deposit for the same length of time. The solution is to weight each day's ending balance (or average balance) by one day. The approach used works if, instead of applying weights to balances, we apply them to transactions - in this case, a deposit of $100,000 on January 1 weighted as 31 days worth - but if the balance doesn't change it is followed by 30 days of "zero transactions." In general, that is a more difficult way to do the calculations.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Apportioning interest rec'd (XP/2002)

    <P ID="edit" class=small>(Edited by JohnBF on 01-Oct-04 10:40. )</P>Edit. My original post has been convincingly shown to be incorrect. So I'm leaving the text in but deleting the example.

    Since you need to consider both the period held and amount you should apportion the total interest based on the sumproduct of the daily balances and the count of days of those balances, something like this crude example:

    <example withdrawn>
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Apportioning interest rec'd (XP/2002)

    Very well put Dean, a great example, and I see how my approach is wrong and Macropod is right. Unless we want to get into daily compounding ADB is going to be the best approach.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Apportioning interest rec'd (XP/2002)

    Stuart, my original post has been convincingly shown to be wrong. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> Apportion the interest by average daily balance. If you need help deriving ADB, post back.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apportioning interest rec'd (XP/2002)

    Don't know about Stuart, but I would love to see how to do it with ADB. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    New Lounger
    Join Date
    Jun 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apportioning interest rec'd (XP/2002)

    Hi Everyone,

    Thanks for all your input - after playing around with this stuff most of yesterday arvo (Sydney time) I think I came to realise that JohnBF's original calc didn't quite take into consideration net withdrawals.... Thanks Macropod for your confirmation of this.

    What I then did (I think) was come up with a formula for calculating the average daily balance - one of the problems I had was that I didn't necessarily want every day of every month represented by a row (as in real life there wasn't a deposit or withdrawal every day).

    So I've ended up with the attached spreadsheet calc.

    Effectively what I'm doing is working out the number of days (column J) which a "balance" applies for then moving to the next date and doing the same - at the end of a period (when an "interest income" transaction is found we sum the average balances and work out a percentage of income which is due to each virtual account.

    Can anyone find anything seriously wrong with this?

    ONe thing it doesn't take account of is changing interest rates but I can live with this.

    Cheers,

    Stuart

    P.S. If I take a while to reply to any replies it's only because I've got 2 tonnes of gravel to move today.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Apportioning interest rec'd (XP/2002)

    It's good.

    (I was initially confused by the way your day calculations look to the next date rather than the prior date, but once I understood that, they look correct.)

    If you wish to be so obsessive as to skip the intermediate calculations in columns K & L, we can bring SUMPRODUCT back to life! For April (unwrap the formulas):

    cell M10: =(SUMPRODUCT(H7:H9,$J$7:$J$9)/SUM($J$7:$J$9))/(SUMPRODUCT($G$7:$G$9,$J$7:$J$9)/SUM($J$7:$J$9))
    cell N10: =(SUMPRODUCT(I7:I9,$J$7:$J$9)/SUM($J$7:$J$9))/(SUMPRODUCT($G$7:$G$9,$J$7:$J$9)/SUM($J$7:$J$9))

    I sure hope I haven't <img src=/w3timages/censored.gif alt=censored border=0> up again. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Apportioning interest rec'd (XP/2002)

    Stuart, I found something minor; you are using 1 day when zero days have elapsed, and that slightly distorts the interest alloaction weighting. Also, since you were not trying to iteratively include joint costs in columns D & E, and interest is a joint item, I took the interest allocation out of columns D & E, and included it only in columns H & I as you had with joint costs. That gets us out of circular reference iteration, FWTW. See what you think of the attached.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •