ChipAside.com » Tools

Following is a small tutorial of how you can use excel (or similar spreadsheet program) to plan yor financial future.

1. Determine your income and spendings

Before we start, a little work from your part is required. You should know (more or less) what you earn and spend monthly because this data will be used to plan for the future. The earnings shouldn’t be a big problem as most of us know where the money comes from and how much! What could be a problem, is to determine and categorize your spendings.

Example:

Income:

Work: 2000$
Investments: 150$
Freelance: 500$

Spendings:

Rent: 800$
Bills (water, sewer, electricity, telecom.): 270$
Car Loan: 130$
Car Gas: 150$
Education (school): 120$
Food: 400$
Other: 450$

It’s a good idea to add 10-15% margin to your variable spendings like food, gas and other. We want to be very conservative in our calculations and plan for “the worst case”. You can always adjust these later on (if you see that you spend more/less than planned).

2. Excel structure

In our excel spreadsheet the columns will represent the months and rows will be the cashflow concepts.

Financial planning with excel

On the beginning of the first month (example: mar 07) we put our starting capital (bank accounts + cash). Then we add income, subtract spendings and the result is the end balance of this month. The starting capital of the second month is the end capital of the first. And so on. In our example the spreadsheet would look like this:

Cashflow Spreadsheet Example

I have used different colors to make the sheet more expressive. You can also freeze the month and concept cells so that they always stay above. You can do this by placing the cursor on B2 cell and clicking “Freeze Panes” in the “Window” menu.

3. Add functions

In the spreadsheet above I have already added some functionality:

Change - the change cells calculate capital change during the month. It is just the sum of income minus the sum of spendings using this formula (place in B14): =SUM(B3:B6)-SUM(B7:B13)

End capital - is the capital after applying the month’s change (place in B15): =B2-B14

Start capital - is the end capital of the previos month. I our example the start capital in abril 07 would be the end capital of march 07 (place in C2): =B15

Months - the following month is approx. the current month plus 31 days (place in C1): =B2+31

That’s it! In order to not write the functions again and again you can copy now the cells C1:C15 and paste them in following columns for as many months/years as you want.

What now?

In real life you don’t have same spendings each month. The data we have entered is just an approximation of most important cashflow. You should consider adding all non-monthly-recurring income and spendings to your plan. For example:

You know that each July you spend 1200$ more for holidays. You can add this spending in “Other” (or create new row/concept: “Holidays”) in July of each year.
You pay for insurance every 3 months.
You are planning to buy a $10,000 car on September 08

You should also personalize and add new concepts to your income/spendings like insurance, mortgage, rent income, etc. Depending on your needs.

After you have finished your plan, you will have a complete overview of your financial future and be able to plan months or even years ahead:

You will always know how much you can spend,
whether to take an additional financing in 4 months,
cut on costs
or be able to put $100 extra into your savings account monthly for the next x months without getting short on cash.

The spreadsheet we just made is a basic structure. In a more advanced version you could add investment dividends with re-investing functions, for example. If you have a solid undersanding of excel, you can play around and design a personalized cashflow spreadsheet that fits your needs.

You can download our cashflow example here.