Apportioning interest rec’d (XP/2002)

Topic

New Reply

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

Reply | Quote

Replies

(Edited by JohnBF on 01-Oct-04 10:40. )

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:

Reply | Quote

[indent]

---

this crude example

---

[/indent]
crude, NOT; genius, yes

Reply | Quote

[indent]

---

this crude example

---

[/indent]
crude, NOT; genius, yes

Reply | Quote

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

Reply | Quote

Correct, see the values for John’s example in the attached worksheet. –Sam

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

Correct, see the values for John’s example in the attached worksheet. –Sam

Reply | Quote

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. (I’m an accountant by training, I work closely with an actuary.)

Reply | Quote

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 31×100,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 1×100,000 = 100,000 $-days. In this case, the total on deposit would be 3.2M $-days, and we would apportion the interest as follows:

to A:  3,100,000 / 3,200,000 = 96.875%
to B:    100,000 / 3,200,000 =  3.125%

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 31×100,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 1×100,000 = 100,000 $-days. In this case, the total on deposit would be 3.2M $-days, and we would apportion the interest as follows:

to A:  3,100,000 / 3,200,000 = 96.875%
to B:    100,000 / 3,200,000 =  3.125%

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.

Reply | Quote

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. (I’m an accountant by training, I work closely with an actuary.)

Reply | Quote

Stuart, my original post has been convincingly shown to be wrong. Apportion the interest by average daily balance. If you need help deriving ADB, post back.

Reply | Quote

Don’t know about Stuart, but I would love to see how to do it with ADB. –Sam

Reply | Quote

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.

Reply | Quote

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 up again.

Reply | Quote

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 up again.

Reply | Quote

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.

Reply | Quote

Hi John,

By the time I got back to Woody’s after my initial post, which stirred the pot soemwhat, you folk had pretty much sorted things out and had implemented an ADB solution without any more ‘help’ from me. FWIW, attached is my re-working of Sammy’s workbook, showing another way of using SUMPRODUCT to calculate the ADBs.

One might argue whether interest should be calculated for an amount on the day it is depositied into or withdrawn from an account, and I’d expect different banks have different rules for this. Still, it’s only likely to matter when frequent large transactions are involved.

My re-working also still relies on an intermediate ‘days’ column, which you’ve shown how to do without.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi All,

Sorry for the delay in my reply (most rude of me).

Thanks heaps for all your help – I think I’ve settled on the calc in JohnBF’s last post – one thing I’m still pondering is macropod’s thought on whether interest is rec’d on a deposit on the day it is deposited (my brain is still hurting and I’ve chucked in the towel).

Cheers,

Stuart

Reply | Quote

Hi John,

By the time I got back to Woody’s after my initial post, which stirred the pot soemwhat, you folk had pretty much sorted things out and had implemented an ADB solution without any more ‘help’ from me. FWIW, attached is my re-working of Sammy’s workbook, showing another way of using SUMPRODUCT to calculate the ADBs.

One might argue whether interest should be calculated for an amount on the day it is depositied into or withdrawn from an account, and I’d expect different banks have different rules for this. Still, it’s only likely to matter when frequent large transactions are involved.

My re-working also still relies on an intermediate ‘days’ column, which you’ve shown how to do without.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Don’t know about Stuart, but I would love to see how to do it with ADB. –Sam

Reply | Quote

Stuart, my original post has been convincingly shown to be wrong. Apportion the interest by average daily balance. If you need help deriving ADB, post back.

Reply | Quote

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

Reply | Quote

(Edited by JohnBF on 01-Oct-04 10:40. )

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:

Reply | Quote