Try this spreadsheet today for your financial planning!

Google spreadsheet link
https://docs.google.com/spreadsheets/d/1Mfi...dit?usp=sharing

Features
- Summary of your expenditure and savings per month, throughout the year.
- Net worth summary which plucks the numbers from your spreadsheet to help you track your investments allocation.
- Link your previous/next spreadsheet to bring forward the calculations across your spreadsheets.
- Upgrade your spreadsheet to the template's latest version to enjoy new features.

Sample summary:


What does it mean to have a good plan?
The sheet contains various conditional formatting to tell you whether there's something good/bad. In general, I use the traffic light colouring: red is bad, yellow is "almost there", green is good. Let's look at some of the details below.

Summary > ending balance
You may notice red/yellow indicators.
Red means you have not enough for the next month. The sheet tracks your budget monthly and that strictly means from first day to last day of the month, e.g. 1-29 February 2020, 1-30 June 2021 etc. Having that said, it does not have the level of detail to tell you how much you're spending by day; this sheet cannot tell you how much you will spend on 20 June, and how much you will have left by the end of that day. What this sheet does however, is that it assumes all your expenses are clocked in on day 1 of the month, and your salary comes in much later. This means, in order to have a healthy plan, you need to have enough ending balance on the month before, to cover for the next month, e.g.
Income = 10k
Jan ending balance = 2k
Feb expense = 5k
Result > Jan ending balance is shown as red, as it is NOT enough to cover for Feb expenses. It assumes that Feb's income will not come on time.
Note:
- investments are not calculated as part of the expenses. This sheet assumes that your investments will be done AFTER your pay day of the month, so that in case you have extra expenses, the first outflow you can cut is your investment, without affecting the your life comforts i.e. the essentials and nonessentials. This is to provide more flexibility for you to manage for when you get your huge bonus for the month, that you want to do the investment in the same month itself, without hurting your KPI directly (i.e. red ending balance indicators even though you know it's fine).

Yellow means you have too much idle cash; the current threshold/formula = your income * 2. This sheet intends to facilitate investments/savings. The ending balance is meant to kept at a healthy "just enough" level, while allowing you to slowly "plant your money tree" to save for the rainy days. As you start your journey in financial planning, investment will eventually come into the picture because money depreciates; you need to let the power of compounding help cushion the fall. Meaning, you need to let your money generate more money for you. But it does not have to be risky investments that do not fit your appetite; that is all up to you, and it is completely up to you to decide where you will keep your extra cash -- all you have to do is jot that down under Investment tab. I have yet to figure out a good way to visualise extraction your invested money to cover for sudden shortfalls.

Summary > buying power
This answers "how much can I splurge this year?" Notice the value never exceeds a certain point even though your ending balance is much higher -- this is because the amount you can splurge is not simply the ending balance, but it also takes account of:
- how much you're supposed to invest up to that month
- how much needed for subsequent month expenses
Note: having 3,000 buying power on March, but 2,000 on April, means that you're NOT supposed to YOLO spend all 3,000 on March itself. Because, in doing so, you will end up in 1,000 deficit once you enter April, as indicated by the red ending balance if you add a 3,000 expenditure.

Summary > Target savings
Dictated by your % target based on your income.
Green = target reached
Yellow = almost there
Red = far from target
This is completely up to you. You ought to balance this out with your expenses and buying power, so that you can have a comfortable life for yourself, while not forgetting to save. The sheet guides you by telling you result of your configurations and inputs, but ultimately, you're the one deciding how much you should save, or spend.

Summary > Debit/Credit weightage
Visualises your debit (money in) vs. credit (money out) to see distribution of your expenses throughout the year. Ending balance is counted towards credit weightage to balance DR/CR. When balance is negative, it is displayed as red to the left.

Developer's note
This is my little personal project to publish it for everyone to use, for free. Please feel free to PM me for any question/support regarding this spreadsheet, or financial planning in general, to manage your expenditures better. Note that I am not qualified and do not provide investment advice.

My motivation comes from wanting to improve my own sheet, since I'm the #1 user after all! However, I do want this to work for more people too, so I want to know what you think about my spreadsheet, and whether you would like to use it:
If "not yet", what's stopping you?
If "no", why not?

Eventually, if time+passion+effort allows, I hope to come up with more formal guidelines (like YouTube videos) and channels to support this project.


This post has been edited by Takudan: Jul 1 2023, 08:50 PM

Sheets
Updates are in blue.

Start - introduction
Setup - spreadsheet configurations
Changelog - list of version changes, mainly to help track your document version vs. template

Upgrade - configuration to upgrade your spreadsheet via macros. The script will copy template sheets, then match by sheets of the same name, to migrate pre-mapped values as configured in this sheet from your existing to the new sheets.

Input sheets (blue, by default): all your money inflow and outflow per month goes here. 4 sheets:
1. Essentials - necessary expenses
2. Non-essentials - expenses that can be cut down in times of need
3. Income - net income (amount that reaches you).
4. Investment - outflow that goes to savings/investments
- Values can be approximate to ease your tracking effort, and I recommend you to round up your values so you "overestimate" your spending. This gives you more spare cash when your spending is lower than planned.

EF - Emergency funds planning that supports any type of short term investments/savings. In each section, you need to specify:
- Previous balance - amount carried forward from previous year(s)
- Interest rate (% p.a.), compounding (or not) and frequency - the sheet automatically calculates your earned interest per month, taking account of whether it is a compounding back into principal, as well as its frequency.
Non-compounding interests are recorded back into Income sheet.
Placements and withdrawals are recorded back into Investment sheet.
Finally, you can track your emergency funds target and progress at the topmost rows. Configure your desired number of months to sustain, and give a custom multiplier to the minimum amount needed if you want a more comfortable unemployment/zero-income period.

Summary - here is where you plan your savings %. You can see how it reflects immediately onto your overall budget.
There are 2 main charts for overview:
- Summary chart: visualises income and expenses, each with two sub-categories.
- Cumulative summary: separated starting balance for clarity.

Net Worth Summary - track your money tree. There is limited calculations for now as it is new... idk how to calculate annualised returns and others yet...
I haven't figured out what if you extract your investments for usage...

Income Tax - Insurance - jot down your insurance premiums and their %, for tax relief calculation

Income Tax - Relief - works as a checksum for your tax relief. Also extracts some information directly from spreadsheet, e.g. PRS, insurance. Table follows year of assessment 2022. Also extracts EPF, PRS, Charity (check LHDN for tax-exemption status), Life and Medical insurance tax relief, easing your income tax filing.

Template - contains sections available for copy to the input sheets and EF.
For example, in category "Default (adv)" there are 4 template rows you can copy:
Weekly category:
1. Column R > Configure amount.
2. Provided formula will fill the total monthly amount accordingly.

Monthly category:
1. Fill amount in the month where the payment starts/changes.
2. Provided formula will fill the subsequent months.
3. To revert #1, replace by pasting (Ctrl+C > Ctrl+V) formula from any column D:O.

Bimonthly category:
1. Column R > Configure amount.
1b. [Optional] Column B > Tick to count on even months (Feb, Apr...)
1c. [Optional] Override with manual input in any month.
2. Provided formula will fill all/subsequent months.
3. To revert #1c, replace by pasting (Ctrl+C > Ctrl+V) formula from any column D:O.

Quarterly category:
1. Column R > Configure amount.
1b. [Optional] Column B > Change starting month by replacing default 1 (Jan) with any month number.
1c. [Optional] Override with manual input in any month.
2. Provided formula will fill all/subsequent months.
3. To revert #1c, replace by pasting (Ctrl+C > Ctrl+V) formula from any D:O column.


Data - for spreadsheet formulas, please keep it hidden and untouched.

Latest update:
This post has been edited by Takudan: Jan 3 2025, 12:19 AM

In case anyone's interested to know my motive, here's my background story:
About me


History



This post has been edited by Takudan: Jun 16 2021, 03:09 PM

Awesome doc. Appreciated that you included changelog.

Thanks man. I realised there's more to write about the fine details because they might look pretty cryptic to someone who stumbles upon the sheet for the first time... So I'll need your/the public's help to identify those haha

You can befriend polarbearz soon!

thanks for the effort.

Thanks a lot

polarbearz also create and share useful templates? (genuine question, not sarcasm)

Yup. Hers is more focused on investment returns and is the one I use extensively to keep track of my investment return.

https://forum.lowyat.net/index.php?showtopi...&#entry98487565

https://ringgitfreedom.com/resources/my-exc...tfolio-summary/

Omg, it's almost like I'm shadowing her footsteps HAHA. And I was totally going to ask, why Excel and not Google spreadsheet, and she answered it in her blog lol.

I was wondering if there's possibility of linking to hers but... Honestly mine is more on general management and much less details on the investment portion, there won't be anything worth porting. Two very different spreadsheets serving very different purposes, both grew from very docile creature to a monstrous blob full of dirty codes in the background.

Excellent! Thanks man.

(also added to first post)

What does it mean to have a good plan?
The sheet contains various conditional formatting to tell you whether there's something good/bad. In general, I use the traffic light colouring: red is bad, yellow is "almost there", green is good. Let's look at some of the details below.

Summary > ending balance
You may notice red/yellow indicators.
Red means you have not enough for the next month. The sheet tracks your budget monthly and that strictly means from first day to last day of the month, e.g. 1-29 February 2020, 1-30 June 2021 etc. Having that said, it does not have the level of detail to tell you how much you're spending by day; this sheet cannot tell you how much you will spend on 20 June, and how much you will have left by the end of that day. What this sheet does however, is that it assumes all your expenses are clocked in on day 1 of the month, and your salary comes in much later. This means, in order to have a healthy plan, you need to have enough ending balance on the month before, to cover for the next month, e.g.
Income = 10k
Jan ending balance = 2k
Feb expense = 5k
Result > Jan ending balance is shown as red, as it is NOT enough to cover for Feb expenses. It assumes that Feb's income will not come on time.
Note:
- pay day configured in FD is only to help you decide when you should set your monthly FDs up to spread your maturity dates across the month, to have a healthy "money ready at any point of the month" plan. Putting it at day 1 does not change the assumption that you still need to have ending balance more than next month's expense, disregarding your income. Again, I admit this is an over-engineered feature and you may ignore the calendar configuration if it is not applicable to you.
- investments are not calculated as part of the expenses. This sheet assumes that your investments will be done AFTER your pay day of the month, so that in case you have extra expenses, the first outflow you can cut is your investment, without affecting the your life comforts i.e. the essentials and nonessentials. This is to provide more flexibility for you to manage for when you get your huge bonus for the month, that you want to do the investment in the same month itself, without hurting your KPI directly (i.e. red ending balance indicators even though you know it's fine).

Yellow means you have too much idle cash; the current threshold/formula = your income * 2. This sheet intends to facilitate investments/savings. The ending balance is meant to kept at a healthy "just enough" level, while allowing you to slowly "plant your money tree" to save for the rainy days. As you start your journey in financial planning, investment will eventually come into the picture because money depreciates; you need to let the power of compounding help cushion the fall. Meaning, you need to let your money generate more money for you. But it does not have to be risky investments that do not fit your appetite; that is all up to you, and it is completely up to you to decide where you will keep your extra cash -- all you have to do is jot that down under Investment tab. I have yet to figure out a good way to visualise extraction your invested money to cover for sudden shortfalls.

Summary > buying power
This answers "how much can I splurge this year?" Notice the value never exceeds a certain point even though your ending balance is much higher -- this is because the amount you can splurge is not simply the ending balance, but it also takes account of:
- how much you're supposed to invest up to that month
- how much needed for subsequent month expenses
Note: having 3,000 buying power on March, but 2,000 on April, means that you're NOT supposed to YOLO spend all 3,000 on March itself. Because, in doing so, you will end up in 1,000 deficit once you enter April, as indicated by the red ending balance if you add a 3,000 expenditure.

Summary > Target savings
Dictated by your % target based on your income.
Green = target reached
Yellow = almost there
Red = far from target
This is completely up to you. You ought to balance this out with your expenses and buying power, so that you can have a comfortable life for yourself, while not forgetting to save. The sheet guides you by telling you result of your configurations and inputs, but ultimately, you're the one deciding how much you should save, or spend.

Version 17
Start: new "Clear all data" macro to let you start your own without the noise from sample data.
Upgrade script: now supports migration of
1) protected sheet/range information.
2) Input sheets grouping and hidden columns settings
(no more known issue as of now, but hey let me know!)

Feel free to drop me a PM or post here if you require any support on using this spreadsheet, or even your expenditures management (please only share what you think is okay to share to a stranger). Note that I am not capable and will not provide investment advice.

Hope this gives you a peace of mind knowing how you're doing financially, to navigate better in these hard times.

This post has been edited by Takudan: Jul 5 2021, 01:25 AM

Just saw this post by chance!! Didn't get the @tag notification

Must say this is indeed a very comprehensive personal finance tracker, covering almost all the important aspects to manage ones' personal finance. Mine is all over the place as I use different app/sheets for different purpose

Thank you! Soooo what's stopping you trying out the sheet? I'd love to hear what you have to see what more I can add onto the sheet.

lol for me, the scripts ain't so bad, but what's scary is the cell formulas that I have to cram everything into that one multi-line text box that doesn't have easy indentations, especially conditional formatting that can fit a whole formula in it but they only give you a single line text box to work with.

I scared to use so many macros. Mine is structured like this:
-a ledger sheet of inflows and outflows
-a sheet to reconcile ledger to bank statements and investments
-pivot tables to summarise the ledger cash flow & net worth by month & year
-one sheet for each category of investment so that I can record the latest market value each month

Oh, the macros are only used for upgrading/migration, i.e. when you want to take the latest changes from template. If you only take the current version and don't want to use macros to upgrade in the future, you can just delete the buttons haha (right click, else left clicking will execute the macro instead).
Sounds like your system mainly tracks past records in detail, unfortunately that is not what this sheet is good at... I'm curious to know though, how do you plan for your future budget?

I don't have huge purchases planned, so my future budget I just use this for next 50 years:
https://networthify.com/calculator/earlyretirement

thanks TS