Understanding and Tracking Your Finances with a Spreadsheet

Let’s say you want to start investing, creating an emergency fund, making provisions for current and future expenses, saving towards a goal, understanding your current financial position, and planning for the future. How do you keep track of your finances? How do you identify your expense pain points? While there are applications that can help with this, it’s important to have a thorough understanding of your financial standing through a spreadsheet that you create yourself. Everyone’s situation is different, but there is a foundation that we can all start from.

First, try treating yourself like a company and build a financial statement for yourself. This includes your liabilities (what you owe), assets (the total resources you have access to and control), and equity (what you own). Based on the accounting equation, we know that assets equal equity plus liabilities. When you rearrange the equation, you get equity equals assets minus liabilities. This is important because our focus will be on equity – what you own. This will determine your financial standing. If your equity is negative, it means you are in a net debt/expense position. If it’s positive, it means you don’t have net debt and have enough to cover expenses to the degree of the magnitude of the positive amount. So, on your spreadsheet, your equity column should be formulated to subtract your total liabilities from your total assets (Assets – Liabilities).

Next, create schedules for the asset and liability sides of your financial standing. The asset side is usually straightforward – simply input the cost of the asset or its current market value, depending on the type of asset. You may need to create schedules if you have different assets or asset types on a particular platform and need to accurately depict their valuation. For example, if you have assets on a platform that includes both Naira and Dollar-denominated assets, it makes sense to convert any foreign currency since you’ll be representing the total values of your standing in a particular currency.

For the liability side, whether it’s a loan or a short-term payable to a friend or relative, they also deserve their own schedule. If it’s a bank loan, you’ll likely receive a repayment schedule that details how much you’ll be paying monthly, weekly, or at any other interval depending on the agreement between you and the bank. For loans from family and friends, there may not be a formal agreement, but there should be a verbal understanding of when you’ll repay the loan and whether interest will be charged. In this case, I would advise creating a schedule that allows for easy repayment. For instance, if someone lends you 100,000 Naira and expects repayment in one month’s time, aim to repay the loan weekly by dividing 100,000 by 4 to get 25,000 Naira. Make sure to set aside this amount at the end of each week so that by the fourth week you have the full amount to repay the person you owe. To do this successfully, create another money market fund account or savings bank account separate from the one you created for your emergency fund.

Expenses that you know you “must” pay for, such as rent, yearly health insurance, Netflix subscription, and internet subscription should be part of your financial planning structure. The methodology for this is not to simply input the amount of these expenses (especially the year-long ones) into your spreadsheet or financial management app, but to amortize the amounts over a period. This is like the methodology we applied to the loan from family and friends.

The important points to remember and note are as follows:

1. Everyone’s situation is different – people are unique and have different habits, so that means the financial plan for Mr. A won’t necessarily work for Mr. B. Mr. B and Mr. A must understand themselves first and their cash flow process and let that determine how they would plan their finances.

2. This is not following an accounting standard, nor would I call it proper accounting even though we are following some principles. Some rules have been modified to fit our objectives.

3. Equity = Assets – Liabilities

4. Equity is what you own, Assets are the total resources you have access to, and control and Liabilities are what you owe.

• Be pessimistic with the amount of your assets and liabilities – slightly reduce your asset amount where possible and slightly increase your liability amounts depending on certain conditions.
• If you have assets in foreign currency like USD, use a lower rate than the current market rate when converting back to your currency – a 5-10% reduction will work just fine. For bonds and stocks, instead of marking to market, you can slightly reduce the market price by 5-10%. Since money in bank accounts or money market accounts is liquid, you can leave them as is.
• For liabilities, certain conditions apply when you have liabilities in foreign currency or loans with variable interest rates. You can slightly increase them by 5-10%.
• If a 5-10% increase is not comfortable for you, use the inflation rate to decrease asset values and increase liability values.
• Forget about accrued interest on the asset side and only recognize interest/dividends received as assets when they have been deposited into your bank account, mutual fund account or reinvested in your asset (e.g., a mutual fund or fixed deposit where you opt for reinvestment).
• On the liability side, recognizing interest depends on the agreement between you and the entity or person who lent you money.
• Non-current and illiquid assets such as cars, land, clothes, shoes, phones, etc., should not be included in your spreadsheet. Only record the sale of any of these assets when the proceeds have been deposited into your bank account under “bank account”. Other assets such as loan receivables from friends and family should not be recorded either. Cryptocurrencies, bonds, and stocks can be included based on your judgment – as a rule of thumb, only bonds and “some” stocks meet the criteria for inclusion in your spreadsheets, cryptocurrencies, not so much due to their volatility. Remember that an important criterion for an asset to be included in your spreadsheet is liquidity.

6. When saving for an emergency fund, remember that this money should be set aside for situations such as losing your job or source of income. As a rule of thumb, you should hope that you never find yourself in a situation where you need to use your emergency fund. For example, if you know that on average you spend 4 million Naira yearly on expenses (including monthly expenses, rent, childcare/school fees, health insurance, etc.), set this amount aside in a money market fund and leave it there as your emergency fund. This savings should be separate from what you normally save for actual yearly expenses such as rent, school fees, HMO fees, etc. (These can also be kept in a money market fund but should be separate from where you saved your emergency fund).