Separating interstest in one fund with subaccounts

[all4money]

I want just one savings account with several subaccounts that I will keep up with in Excel. How do I allocate the interest earned in the entire account between the subaccounts? Is there a calculator out there that I can use or is there something in Excel I could formulate? How do you guys keep up with it? I don't want to have several different savings accounts at the bank but would like to keep the interest separate based on what amounts I have in the subaccounts. I'm thinking that I'll have to do a pro-rata share sort of thing but thought I'd survey you to see if there's an easier way...

Thanks.

[Ima saver]

That is an interesting question and I never thought about it. I have one large account with several subaccounts. I just keep up with it on paper since I know nothing about computers.
I just have a sub account marked interest! I don't allocate it to the different sub accounts, but that is a good idea.

[cptacek]

First of all, Interest = Principal * rate * time. Usually, the rate is yearly, but the interest is figured daily. So for every day the money is in there, the formula is Interest = Principal * rate * 1 / 365. For a week, it is Interest = Principal * rate * 7 / 365.

I would create an excel spreadsheet. I am going to assume you are splitting the money into 2 pots (you can have more pots...you just need more columns) and that you are getting 5.15% interest.

Here are your headings and the cells they go in:
A1 - Date
B1 - Debit / Credit
C1 - Interest
D1 - Pot A
E1 - Debit / Credit
F1 - Interest
G1 - Pot B
H1 - Total

In A2, put today's date, in D2 put the portion of the money you are putting in Pot A and in G2, put the portion of the money you are putting in Pot B. In H2, put the formula =D2+G2. This should equal the total amount of money you have in that savings vehicle.

Now, let's say that you add $200 to the account tomorrow. $150 of that will go to Pot A and $50 will go to Pot B. In the Date column, A3, enter the date of the transaction. In the Debit / Credit column, B3, enter $150. In the Interest Column enter the following formula: =D2*.0515*(A3-A2)/365 In the Pot A column, enter the following formula: =D2+B3+C3.

Do the same thing for the next pot. $50 in the Debit / Credit column, E3, the Interest column should have =G2*.0515*(A3-A2)/365, and the Pot B column should have =G2+E3+C3.

The total column should have =D3+G3.

Now, just copy these formulas down the columns. Every time you add money to the account (or subtract money...just put in a negative number), add whatever portion that goes to that pot to the Debit/Credit section for that pot. If you want all the money of a deposit to go to one pot, then just enter 0 in for the other one.

One word of caution...you may find that your interest deposits will be off by a cent here or there. It may be a rounding difference between what you are using and what the bank is using, so if they deposit something slightly different, you can put the exact number in instead of the formula you used. Also, I've had loans that are figured on a "360 day year" so when I figured up an amortization table I had to take that into account.

[Lori63]

I never thought about it. Two of my son's (18 and 15) have money in my savings acct-I usually just keep the interest. It's such a small amount, and their money is in savings because they know they'll spend it if it isn't.

[MonkeyMama]

I don't keep a lot of sub-accounts, but I wouldn't take the time to allocate interest (which is why I don't have a lot of subaccounts either). All the interest in my short-term savings is just an extra slush fund, and in my big savings account it just all goes to the efund. Just easier that way. I could start allocating it to our subaccounts and all that stuff, but I just don't see the point.

But if you do see the point, excel is probably easiest - good post cptacek

[LuxLiving]

I just keep a separate page in my log book for interest income, when it goes over $500, then I allocate it to a line item.

[anonymous_saver]

I guess I don't understand the question. Because I have subaccounts with Emigrant and interest accrues in each subaccount individually. So for example, I could earn $2 interest in my emergency fund, but $20 interest in my down payment fund (for example).

Do you have subaccounts with a place that doesn't seperate the interest for you?

[ktmarvels]

I think the OP is like us and only has one account, but the money in that one account is earmarked for different things. So the interest is earned on the total amount of the account, but she wanted to know how to divide up the interest into each category.

[all4money]

Thanks, ktmarvels... that's exactly my scenario. I wouldn't make such a big deal about it but the situation is this... I have $19,000 that I need to invest in my short term savings thru Dec 07 (for various reasons, I can't put it in a CD or anything like that). I don't want to have to open up a brand new account just to close it in Dec, but I would like to know how much of the monthly interest on the entire account would "belong" to the $19,000 and how much would be on the regular amount that I would have in the account that is normally designated as my "freedom" account.

Thanks for everyone's input!

[RJB1180]

Quick and dirty is to divide the amount of interest by the ratio of that savings.

$100 interest for may

Balance : $26,000

short term $19,000 or 73% of the account

short term interest for the month $73

just my 2 cents

[pearlieq]

For our smaller, immediate account (for irregular short-term expenses) I usually just allocate the interest to the line item that "needs" it the most. Our cars are getting older, so I've been allocating the $1.24 each month to the car account. I'm sure that will make all the difference when the time comes...

As for our larger accounts, I usually allocate the interest to the emergency fund. I think of it as my hedge against inflation.