Saturday, December 26, 2015

Personal Finance Planner Spreadsheet

Introduction



Personal Finance Planner is a tool designed to track monthly expenses and build savings plan. The tool is designed to manage multiple debit accounts (savings / current accounts) and credit accounts (credit cards) while keeping funds allocated for monthly expenses and savings separated. There is also a analysis tool which helps to analyze monthly expenses.

The tool is designed with the following rules:
    1. Balance at month end should be 0. Remaining funds for each month are deposit into savings.
    2. Income and money received is recorded as positive value.
    3. Expenses is recorded as negative value.
    4. Savings fund is managed separately from monthly expenses.
    5. Fund is reserved to pay every single cent spent on credit card by month end.
    6. Each expenses is tracked on net value spent / received.
    7. No overdraft. Overspend amount is deducted from savings.
    Download

        Using Personal Finance Planner

         

        Overview


        Expenses Tracking Sheet
        Expenses Tracking Sheet
        • Fund Balance: Remaining funds available on hand for current month expenses.
        • Cash Balance: Cash on hand.
        • Daily Expenses: Non categorized personal expenses.
        • Daily Limit: Planned daily expenses (maximum).
        • Exp / Day: Average expenses per day since last calculated day.
        • Debit Accounts: Savings / Current accounts and Cash.
        • Credit Accounts: Credit cards transaction.
        • Credit Limits: Credit limits for each card.
        • Savings (Deposit - Cash): Total savings in debit accounts (Cash and bank account).
        • Savings Allocations: Savings allocated in other area, such as Fixed Deposit, Investment and Equity.
        • Reference: Transaction reference number for future reference.

          Analysis Sheet

          Analysis Sheet
          • Nett Income: Income - Tax.
          • Savings %: Percentage of net income allocated as savings.
          • Fund: Amount allocated for monthly expenses.
          • Expenses: Total expenses.
          • Balance: Remaining funds available till month end.
          • Savings - Income: Savings from monthly salary.
          • Savings - Others: Savings obtained from other source such as FD Interest, dividend and etc.
          • Savings - Nett: Total savings per month after deducted expenses from savings (SavExp).
          • Target Savings: Target savings amount by end of the year.

            User Guide


            Previous Balance

            First line of expenses tracking sheet bring forward balance of each accounts and credit card expenses from previous month. Fund balance shall be zero before beginning of each month (Rule No. 1).


            Income


            Incoming funds is recorded as Income for gross salary whereas income tax deducted is recorded as TAX. Nett income is calculated as Total Income - Total TAX. Example above shows nett income of 3,420 is available for current month expenses which also reflected in analysis sheet.

            TIPS: Click Refresh All button to update the contents of analysis sheet.


            Savings
            As mentioned in Rule 4, savings is managed separately from monthly expenses. Image above shows that 1,500 is moved from available fund to savings. Total fund remaining is now 1,920 after 1,500 is moved into savings. Cash deposit for savings increased from 1,820 to 3,320.


            Expenses

            Expenses Analysis (Click to Enlarge)
            All kind of expenses / payment in the forms of cash / credit card / wire transfer is recorded in expenses tracking sheet with negative value. Take a look on expenses record for Grocery, Petrol and Electricity where all transaction is recorded with negative value which represent money spent. Reference columns is added to keep track of receipt number or reference number for online transaction.

            In analysis sheet, amount spent on each category on each month is summarized for further analysis to identify spending habit and monitor spending trends.


            Daily Expenses

            Daily Expenses is created to track all the small amount we spent daily on food, drinks and even candy. All these spending have to be recorded else the accounts will not balance.
            All these minor expenses is tracked as Daily Expenses as CASH - Cash Balance, where CASH is remaining fund in cash account whereas Cash Balance is remaining cash on hand.Exp / Day on the other hand calculate average Daily Expenses spent since last summarize date. It's recommended to sum up total amount spent as Daily Expenses once per week.

            Reset Daily Expenses Calculation

            To balance up different between Cash Balance and CASH account, all you need to do is add an entry of Misc - Daily Expenses to reset the calculation of Daily Expenses and Exp / Day. Don't forgot to set the Last date to date where you add this entry to reset No. Days.


            Withdraw / Deposit Funds

            Withdraw, deposit, transfer funds between debits account does not cost you additional cents (assume that no service charge imposed) or earn you extra cents. According to Rule 6, these kind of transaction is neither an income or expenses since the net value for these kind of records is 0.


            Example above shows a record that 2,000 transferred from Bank A to Bank B with no service charge.


            Credit Card Expenses
             
            Credit card normally have 30 days payment terms where expenses on credit card is required to pay one month later. To avoid over expenses, an identical amount in Fund Balance is deducted on every single expenses spent on credit card (Rule 5). This is to ensure we have sufficient fund to pay credit card bill and prevent overspent.


            Credit Card Billing

            Since transaction on credit card did not appear immediately on your next billing statement. An additional column is created on each Credit Card account to keep track on which transaction had been billed and which have not. This is also a tool to verify each transaction on your credit card statement before pay.

            For each transaction that appear in your card statement, simply put a "x" in the Billed column to have transaction added to the PAYMENT DUE column as shown above.


            For credit card which give cash rebate on expenses, rebated cash shall recorded as positive value in credit card account. Besides, payment to credit card account is also a positive value on card account which "restore" the credit limits on respective card shown above. Also note that a transaction of 115.00 on car service is not billed in current statement.

            Total Usage in credits account is a total expenses spent on each credit card through the year. If you are holding a card which required minimum spending per year to achieve certain status or rewards, this could be handy.


            Savings Expenses (SavExp)

            Expenses from savings account, a.k.a savings expenses is similar transaction type as Expenses except that the transaction must be record as SavExp and spent amount must be deducted from deposit account. In such, the spent amount will be deduct from savings instead of Fund Balance pool.

            If the transaction for savings expenses was not done properly, the balance cell in analysis sheet will shown as red indicate that there is error in calculation where balance in expenses tracking sheet does not match balance calculated in analysis sheet.


            Savings Relocation
            Savings Relocation (click to enlarge)

            To relocate savings to other investment or savings tool, the amount is deducted from both debits account and savings account resulting zero expenses transaction with fund deducted from savings. Columns on the right of Deposit CASH are columns added funds allocated to others savings / investment instrument. Total savings fund is calculated as sum of Deposit CASH and funds allocated in all other savings tool.


            Overspent

            As stated in Rule 7, overdraft is strongly not recommended to maintain a good spending habits. Hence, whenever Fund Balance for a particular month fall below negative values, the different shall be cover back from savings fund by moving fund from Savings to monthly fund.

            --- END OF DOCUMENTATION ---

            Appreciation

            Do you like our work? Appreciate if you could help to buy us a cup of coffee. Thank you.