One of the common and ideal applications of Excel is in management of personal finances. I created one for my personal use a couple of years ago and I have found it very helpful and have been using it regularly. I thought I will take that concept, put a friendlier user interface, make things automated and share it here.
DOWNLOAD
Please download the version that fits your needs.
- Excel 2013
- Indian Rupeeas currency:Personal Finance Manager Excel template
- USD $ as currency:Personal Finance Manager Excel Template (USD)
- No currency formatting:Generic version
- Indian Rupee with sample data:Personal Finance Manager (sample data).
- Excel 2010
- Indian Rupee as currency:Personal Finance Manager Excel template - 2010
- USD $ as currency: Personal Finance Manager Excel Template (USD) - 2010
- No currency formatting:Generic version - 2010
 |
| Personal Finance Manager Excel Template - Monthly Summary |
This is a simple template that focuses on making it easy for you to know what's happening with your financial situation especially when you have multiple bank accounts, credit card accounts and cash. It also helps you set budgets and see how you are actually doing against your budget. With simple data entry, the template provides you instant access to actionable information in a consumable form that can answer key questions regarding your personal financial situation. Specifically, the template helps you in knowing the following:
- What's my net balance right now?
- How much money is in my different bank accounts?
- How much do I owe on credit cards?
- On what items am I spending my money on?
- Am I exceeding my monthly budget? If so, in which categories?
- How are my expenses trending over time?
- Am I spending more on any specific expense category over time?
- How much am I saving every month? How does that add to my net balance?
Please view the video for a demo of the template.
The template has 3 worksheets: 1)Settings 2)Transactions and 3)Report.
Before we get into how to use the template, we need to talk about the3 types of transactions.In this template, a financial transaction is one of three things.
- Income: It's an income transaction when the money is coming to you. Examples: a) your salary, b) you may be owning a property and your tenant pays you rent. c) you may get interest on your savings balance from your bank.
- Expense: These are transactions where you are spending money. Examples: Phone bills and grocery bills.
- Transfer: These are transactions where money moves from one of your accounts to another of your accounts. Your credit card payment is an example. You may take the money from your bank account and pay your credit card balance. In such cases, the money is leaving your bank account and goes into your credit card account. Another example of Transfer is ATM withdrawal where you are transferring the bank balance to cash in your hand. It's neither an income nor an expense transaction. It's a transfer.
HOW TO USE THE TEMPLATE:
Step 1: Enter information in Settings worksheet
 |
| Personal Finance Manager Excel Template - Settings |
- Set your starting balances of accounts
- Create categories & Sub categories (trust me, this helps)
- Set your monthly budgets
Step 2: Entering transactions in the Transactions worksheet:
- When you open the template, there will be only one record in the Transactions worksheet (as shown in the image below). Start entering your own transactions by replacing this record with your data.
 |
| Personal Finance Manager Excel Template - Enter new transactions |
- And when you finish entering your first transaction, enter your second. There are at least a couple of ways to do that. a) You could hitTabkeyfrom the Comments field in the first record. b) You could also type information in the row below the first record and hitEnterkey. Please see the video below about drop down menus, where this is demonstrated.
- Types of Transactions:
- Income and Expense: By default, all the Income and Expense transactions should be entered as positive amounts.
- Special case (Refund): If you purchased an item at a store, you would enter an Expense transaction with positive amount. If, a few days later, you returned the item to the store for some reason and get a refund, then you should enter the refund as a new Expense transaction with negative value.
- Transfer: When money is transferred from one account to another, create two records
- 'Transfer' type with negative amount from the account you are taking the money from.
- 'Transfer' type with positive amount for the account you are depositing the money into. Examples of Transfers are Credit Card Payment (transfer from Bank account to Credit Card account) and ATM withdrawal (transfer from Bank account to Cash)
 |
| Drop Down menus for easy data entry |
- After you enter your transactions, the Transactions worksheet would look like this image below.
 |
| Personal Finance Manager Excel Template - Sample Transactions |
Step 3: View ‘Report’ worksheet
- Since there are pivot tables and charts, please refresh the data by going to Data ribbon and refresh all. This updates the charts with your new transactions.
 |
| Personal Finance Manager Excel Template - Refresh Data |
- The report has four pages.
- 1) Current financial summary
- Summary of your current financial status
- Current balance in each of your accounts
- You can find balances for any period in each of your accounts
- This can be helpful when your bank statements and credit card statements actually have their billing cycles different from calendar months. This allows you to compare your statements with the data you have in this template and confirm that you have not missed any transactions.
- 2) Monthly summary (Please choose one month at a time)
- Total Income, expense and savings
- Expense categories where money was spent that month
- Comparison of expenses to budget
- Categories that exceeded budget will be highlighted in red
- Expenses by sub categories
- 3) Trends of expenses, savings and net balance
- Trend of expenses over time and comparing against monthly budget
- Trend of savings and net balance over time
- 4) Trends of transactions by Categories and Sub Categories
 |
| Personal Finance Manager Excel Template - Current Financial Summary |
 |
| Personal Finance Manager Excel Template - Monthly Summary |
 |
| Personal Finance Manager Excel Template - Budget Trends |
 |
| Personal Finance Manager Excel Template - Transaction Details |
I hope you find this template useful in managing your personal finances easily.In the next version, I plan to include support for otherfinancial instruments such as fixed deposits and other features such as settingfinancial goals. Your feedback will be very helpful for me to know whichfeatures are worth adding. Do you find this useful?
