Financial Spreadsheet Samples

sheet1 (jpg)

Download

sheet2 (jpg)

Download

Why You Need to Create and Understand Your Net Worth Spreadsheet.

Before making financial decisions I used to rely on the advice of others, including financial and other banking professionals on a potential buy or sell of an equity, real estate purchase, or even on the decision to open or close a particular account or service.


This uncertainty changed years ago when I made a complete inventory of all of my assets, liabilities and income streams. I did this in excel because I liked having full control of the calculations and planning assumptions. Yes, you can use online calculators, but customizing your own sheet is in my opinion a better option. This spreadsheet grew to forecast all of my assets that I checked monthly and yearly- This was a key shift for me.


Once I could see my future balances five, ten, fifteen years ahead for my current investments, the decisions to buy or sell anything became much easier. The sheet I use forecasts an extraordinary life span, why not factor in calculations to your 100th year; You never know. This one sheet is broken into two images. 


Basic assumptions

-This sheet is one many you can find on the internet. However this sheet is a template sample of the one I use as my primary gauge to determine my net worth. I factor in a standard 7% growth on equity accounts, and smaller percentages for CD's and online savings accounts.


-The key with these sheets is that you can always check the top row to see your forecastesd assets by age. Having a clear financial picture of each asset by age tells you where you will be financially, and furthermore motivates you to make changes to increase those asset amounts.


For instance, are you paying high fee’s on one investment account- you will see the impact of this higher fee account long term when you compare it to another lower fee cost account in the row below. 


Calculation Sample Formula: 

All of these calculations are very simple to find, my basic excel formula for calculating compounded gain looks something like this:


For Column C6 

C5= $100

=(C5*.07)+C5+100

-The calculation will calculate a value in column C6 with the following:

The dollar value of column C5 (for example $100 dollars) times a 7% percent gain plus the original C5 value, plus another $100 dollars.


The result is column C6 = $207 dollars with each succeeding column to the right compounding the previous columns calculated values. Compounding interest is mathematically impressive.


-Also, I assume that you and your spouse both have IRA's, 401k or SEP Accounts, Savings Accounts, One Pension and Three Rental Properties along with a Primary Residence under your ownership. 

-Note that sheet 1 shows a range of year columns from age 40 to 76. 


Sheet #1: This is the first sheet showing all retirement brokerage and savings accounts by horizontal row; each row is a unique brokerage or savings account, and each column represents a year or your age. Once you maintain your investments and savings this way, planning out any purchase or sale becomes easier to understand. 


Think of a scenario when you consider liquidating your CD's, with a simple sheet like this you can see the actual loss you will incur if you liquidating the account now versus purchasing new higher rate CD's and allowing them to mature for additional time. 


Sheet #2: This is the second sheet showing all real estate mortgages, values and actual equity. Note that for real estate, some do not account their primary residence as part of their assets when calculating total net worth- (you can decide if you too are aligned with Robert Kiyosaki's view of asset definition and exclude your primary residence). 


This sheet also includes post tax brokerage accounts. As with sheet one, before consider selling, re-renting or purchasing a property, you'll need to se a forecast of the current properties. How much of the principal is being reduced each year, which is another way of saying how much is your net worth going to grow each year. 


So you are thinking about selling your primary residence and pocketing the cash tax free cash through IRS provision 121, as you have lived their three years- Does this make sense? Well, that depends. How much will this primary residence be valued in ten years. Are you better off renting it and allowing a renter to pay down your principal another 10 years. 


Additional Tools:

Personal Capital:

Note that I still use the online service personal capital to show me brokerage account fee's, transactions, cash flow, account performance, savings goals, etc. This free savings calculator is also another important tool to use that keeps your online financial transactions tracked and viewable within a easy to read dashboard.


My personal capital provided share link that will earn you $20 dollars if you download the free personal capital application.


 https://share.personalcapital.com/x/89GZJp 


Morningstar:

I also use Morningstar's portfolio tracker to research individual stocks, ETF's and other market positions that I organize into separate portfolios, including: 


Core - These are my primary long term positions I own. 

Research- stocks on my radar, recent IPO's, all of which I may consider for a future purchase.

Dividend- current dividend stocks growing in my portfolio

High Risk- highly speculative companies I am watching

Kid's- the positions I have purchased on behalf of my child, yes I teach my kids how to research and track equities as well


https://www.morningstar.com/portfolio.html


Free percentage calculator:

Another free calculator that I use every week is the basic percentage calculator that let's me quickly calculate percentage amounts and changes to percentages. This tool should always be in front of you on your phone or in your bookmarks bar on your browser so that you can quickly calculate three simple percentages such as, (i) what is 16.5% of $8,635 dollars,  and (ii) what $3,018 is what percent of $77,254, and (iii) my favorite example formula, what is the percentage increase from $1,005 dollars $7,000 dollars. 


 https://percentagecalculator.net/ 



Your own brokerage account tools:

If you are using established brokerages to purchase equities, they too also include good tracking and dashboard tools that link directly to your accounts. Each brokerage I use maintains similar retirement planning toolsets that allows you to link your other brokerage accounts as well. 


Why you must think about your assets and liabilities

Back to my spreadsheet- This one very simple sheet example broken down into two parts is an absolute critical starting point and tool for anyone interested in understanding the financial impacts of their decisions. In my opinion you must understand and appreciate your net worth, (assets minus liabilities) by age to make sounds financial decisions. 


Speaking from direct experience, once you create a simple sheet like this you will be able to better understand impacts and risks with a calendar view into your future. This basic sheet has basically simplified my financial planning, I expect my simple example can help you as well.


Thank You, 



Financial Sombrero



Building Wealth Knowledge Every Day