Spreadsheet to manage multiple virtual accounts

mister_t

Does anyone have a spreadsheet to manage multiple 'virtual' accounts within a single savings accounts that will work out the approx interest due for each virtual account?

I like to have multiple accounts to ring fence our savings into different pots (tax, holiday, kids education, general, etc.). I used to do this by opening multiple accounts but that was too much of a faff when needing to move accounts to chase the best rates. I have now put all money into a single account and have a spreadsheet to manage which bits of money belong to which virtual account. That is the easy bit

The problem is that when I get paid interest (be it monthly/yearly) I would like to apportion that interest across the virtual accounts according to their balances over the interest term. Apportioning the interest based on their relative balances on the day the interest is paid would be too simplistic though.

Does anyone have such a spreadsheet to save me reinventing the wheel? I was hoping to do it just with excel formulas but if I have to resort to writing a macro then so be it.

RayWolfe

Wow!
If you WANT that level if detail, then I can only guess that you will actually LIKE writing a macro to do it.

mister_t

You're right, I should get out more.

I can't be the only one that likes their money divided up into different pots, though I guess it's the perfectionist in me that wants to dived up the interest as well.

I found the answer to my problem in the end and it turned out to be pretty simple.

jem16

mister_t wrote: »

You're right, I should get out more.

I can't be the only one that likes their money divided up into different pots,

You're not - I like to do that too. HBOS web savers were perfect for me but they now offer a poor interest rate. Not many others offer the ability to do different pots so perhaps one account and a spreadsheet would be a good idea.

though I guess it's the perfectionist in me that wants to dived up the interest as well.

Not as important but might be nice.

I found the answer to my problem in the end and it turned out to be pretty simple.

You wouldn't like to share your spreadsheet would you?

RayWolfe

mister_t wrote: »

I can't be the only one that likes their money divided up into different pots, though I guess it's the perfectionist in me that wants to dived up the interest as well.

Lots of people like the pot concept ... it comes from the jam jars behind the clock on the mantelpiece concept. And as you know, any simple spreadsheet will accomplish that. What blew my socks off was the NEED to allocate interest to each pot ... though it will stop you going out and beating up old ladies!
A thought, why not have an interest pot?
No? OK, go on, get your macro writing hat on. Good luck!

sloughflint

RayWolfe wrote: »

No? OK, go on, get your macro writing hat on. Good luck!

I don't think a macro is needed. I have a spreadsheet too ( without interest) and I don't think it would be too difficult to alter to take interest into consideration.

isofa

You definitely don't need macros, just ensure you have the correct forumlae dividing up the interest in the correct pots and for the correct period of time.

mister_t

The thinking behind dividing up the interest is this. If all the interest goes into an interest pot then all the other pots are effectively depreciating over time. So if I am going to split the interest across all pots then the question becomes how do I do it with some degree of accuracy.

In the end I opted for working out the average daily balance of each pot over the month and applying those relative proportions to the interest figure. I guess there are a number of ways of doing it with varying degrees of accuracy and complexity. I reckon this way is probably close enough.

Still, it keeps the brain ticking over.