How to Create a Personal Financial Statement in Excel

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will show you how to create a personal financial statement in Excel.


Download Practice Workbook

Download this workbook and practice while going through the article.


Introduction to Personal Financial Statement

Personal Financial Statements are summary reports which contain the state of the financial situation of an individual. They include the reports of the balance sheet and income statement. They are the written documents or records that the company performs throughout the year.

Balance Sheet

A Balance sheet summarizes the financial position after a certain period. It is also known as a Statement of Financial Position/condition. In general, a balance sheet contains 3 portions. And, these are Assets, Liabilities, and Owners’ equity. Balance sheets for an individual are organized according to the equation:

Assets = Liabilities + Equity/Net Worth

Assets: These are the main resources owned by individuals. They can be classified into many types. Like Current and Fixed assets, Tangible, and Intangible assets, etc.

Liabilities: They are things that the individual owes to a person or a company like cash, loans, etc.

Equity: For a personal balance sheet, this is the difference between assets and liabilities.

Income Statement

It reports the profit and loss of a company over a certain period. So, the income statement is made of 3 parts.

  • Revenue
  • Expense
  • Net Income.

2 Steps to Create a Personal Financial Statement in Excel

In this article, I will create a personal financial statement in Excel. The entire process is divided into 2 main parts. They are

  • Preparing Balance Sheet
  • Preparing Income Statement

Let’s prepare them steps by steps.


Step 1: Prepare Balance Sheet

The first step is to prepare a balance sheet. For your convenience, I have divided the task into 3 parts.


Create Outline

The first task is to create an outline. There is no fixed rule for doing so. I have given a sample here.

Preparing balance sheet to create a personal financial statement in excel


Write Down Information

The next task is to put the correct information in the right account. This varies from person to person. I have the following information.


Calculate Assets, Liabilities, and Equity

The next part is the calculation. I will calculate total assets, total liabilities, and equity one by one.

To calculate total assets,

  • Go to C15 and write down the formula
=SUM(C9:C14)

Preparing balance sheet to create a personal financial statement in excel

  • Then, press ENTER to get the output.

  • Similarly, calculate total liabilities.

Preparing balance sheet to create a personal financial statement in excel

Equity is the difference between total assets and total liabilities. To calculate it,

  • Go to F15 and write down the following formula
=C15-F13

  • Then, press ENTER to get the output.

Preparing balance sheet to create a personal financial statement in excel

Read More: How to Prepare Financial Statements from Trial Balance in Excel


Step 2: Create Income Statement

The next step is creating an income statement. For your convenience, I have divided this step into 3 parts. Let’s see what they are.


Create Outline

The first task is to create an outline. There is no fixed rule for doing so. I have given a sample here.

Preparing income statement to create a personal financial statement in excel


Write Down Information

The next task is to put the correct information in the right account. This varies from person to person. I have the following information.


Calculate Total Revenues, Expenses, and Net Income

The final part is to calculate the total revenue, expense, and net income.

To calculate the total revenue,

  • Go to D10 and write down the formula
=SUM(D8:D9)

Preparing income statement to create a personal financial statement in excel

  • Then, press ENTER to get the output.

  • Similarly, calculate the total expense.

Preparing income statement to create a personal financial statement in excel

  • Finally, I will calculate the net income. This is the difference between total revenue and expense. To calculate it
  • Go to D21 and write down the formula
=D10-D19
  • After pressing ENTER, you will get the output.

Preparing income statement to create a personal financial statement in excel

Read More: How to Automate Financial Statements in Excel (with Easy Steps)


Things to Remember

  • An income statement calculates the profit or loss of an individual/ a business over a period of time.
  • A balance sheet illustrates the financial position of an individual/ a business at a specific point in time.

Conclusion

In this article, I have explained how to create a personal financial statement in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo