How to Create a Personal Financial Statement in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Personal Financial Statement in Excel


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.

How to Create a Personal Financial Statement in Excel: 2 Steps

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


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.

Download Practice Workbook

Download this workbook and practice while going through the article.


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.


Related Articles


<< Go Back to How to Create Financial Statements in Excel | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo