Tools & spreadsheets for managing money

jordanjim

What tools do other people use to help them manage their money? I'm thinking of programs, spreadsheets etc.

I've just spent all morning so far trying to put together a spreadsheet that will predict my net worth and tax liability at the end of each year, including available tax reliefs and accounting for if I ever go into the higher tax bracket. While I'm reasonably comfortable with Excel, I'm still pretty much a novice at using the various equations available and I'm sure there must be a better designed system or template out there that would save time and give better results. Does anyone have any suggestions?

george4064

For my ISA portfolio and various bank accounts, I just maintain a spreadsheet with all the information in it. Seems to work for me.

Kendall80

I cant help you with a template or other tool as my workbook is homemade and tailored to my requirements. However, for my tax equation I have a sheet which collates interest earned each month across mine and MrsKs many accounts. I then use an IF equation to display 20% tax on any amount over 1000 for each of us. If under 1000 at years end it displays 0.


Not sure if the equation will help you but here it is anyway. Q10 is my total interest in this tax year cell. This is in Kingsoft Office not excel as its free and i'm cheap. I believe its fully compatible though.


IF((Q10-1000)*1.2-(Q10-1000)>0,(Q10-1000)*1.2-(Q10-1000),0)


It essentially means if my earned interest is greater than 1000 please would you kindly display 20% of it - otherwise leave me alone and just display 0.

AndyT678

Kendall80 wrote: »

IF((Q10-1000)*1.2-(Q10-1000)>0,(Q10-1000)*1.2-(Q10-1000),0)


It essentially means if my earned interest is greater than 1000 please would you kindly display 20% of it - otherwise leave me alone and just display 0.

Crikey that's a convoluted thing!

How about =IF(Q10>1000,(Q10-1000)*0.2,0)

Linton

Or even shorter =MAX((Q10-1000)*0.2,0)

jimjames

Microsoft Money is still available and is free. That's the software I use.

Kendall80

AndyT678 wrote: »

Crikey that's a convoluted thing!

How about =IF(Q10>1000,(Q10-1000)*0.2,0)

It is a bit isn't it. Shows my level of excel capability I guess.

You should see the size of the equation for my monthly P2P interest.

That sounds just about the nerdiest sentence i've ever written.

System

For what it's worth I have found that it can be useful to allocate cells to variables that are liable to change. It can save getting under the bonnet and changing formulas later on.

In this case it would be pretty quick to change a single formula, but if you have a spreadsheet that is number crunching more complicated affairs for tax or whatever then I find it worth planning all the variables at the start.

Using your case as a simple example I'd set it up like this:

Cell A1 = total interest earned
Cell A2 = annual tax free allowance (enter £1,000)
Cell A3 = tax rate (enter 20%)
=IF(A1<A2,0,(A1-A2)*A3)

Cells A2 and A3 are then variables you can change each time the Chancellor appears with the red box.

darkidoe

wneil wrote: »

For what it's worth I have found that it can be useful to allocate cells to variables that are liable to change. It can save getting under the bonnet and changing formulas later on.

In this case it would be pretty quick to change a single formula, but if you have a spreadsheet that is number crunching more complicated affairs for tax or whatever then I find it worth planning all the variables at the start.

Using your case as a simple example I'd set it up like this:

Cell A1 = total interest earned
Cell A2 = annual tax free allowance (enter £1,000)
Cell A3 = tax rate (enter 20%)
=IF(A1<A2,0,(A1-A2)*A3)

Cells A2 and A3 are then variables you can change each time the Chancellor appears with the red box.

Bump. We need a good thread to educate spreadsheet novices like me on the most commonly used equations on spreadsheets to track interest, net worth etc.. Currently I am just recording 'raw' data. Figures monthly and adding them up and subtracting from the previous months to get an idea of monthly savings net of interest, pay, expenses etc..

AndyT678

wneil wrote: »

For what it's worth I have found that it can be useful to allocate cells to variables that are liable to change. It can save getting under the bonnet and changing formulas later on.

In this case it would be pretty quick to change a single formula, but if you have a spreadsheet that is number crunching more complicated affairs for tax or whatever then I find it worth planning all the variables at the start.

Using your case as a simple example I'd set it up like this:

Cell A1 = total interest earned
Cell A2 = annual tax free allowance (enter £1,000)
Cell A3 = tax rate (enter 20%)
=IF(A1<A2,0,(A1-A2)*A3)

Cells A2 and A3 are then variables you can change each time the Chancellor appears with the red box.

Using parameters like this is a really useful piece of advice.

I'd also consider going a bit further an using named ranges to identify the variables so that A1 is called Interest, A2 Tax_Allowance, A3 is Tax_Rate etc...

That way you can use the names as references in formulae throughout the sheet and it's instantly obvious what you're referencing. It makes the sheet easier to troubleshoot and simpler for other users to understand.

Speculator

I started with Lotus123 when I first started saving when I got my University grant cheque and have been using Excel since 1999.