Google Sheet - Cash Flow App

This is a Google Sheet that you can use to project your cash flow and portfolio position.

Video Tutorial

Instructions

This spreadsheet is designed to project your cash flow and portfolio position over the next 36 months. It can help you plan for future investments, big purchases, paying down debt, etc.

The spreadsheet generates two main statements:

Cash Flow Statement: Tracks how your cash changes month-to-month by taking the starting cash, adding your cash-in items (income), and subtracting your cash-out items (expenses) to get the expected month-end cash.

The Statement of Cash Flows is one of the three main financial statements along with the Income Statement and Balance Sheet. It tracks how cash changes month-to-month. We are using the "Direct Method" for the Cash Flow Statement, where we see exactly how cash comes into and out of the portfolio for each item.

Because we are directly using the Statement of Cash Flows, you need to consider your income and expenses on an after-tax basis. We are looking at what actually comes into and out of your accounts.
Portfolio Position Statement: A summary of your assets (Liquid and Illiquid) and liabilities, giving you a view of your portfolio position or net worth at the end of each period.

This is a kind of "pseudo-Balance Sheet." I prefer this view over a traditional Balance Sheet because it simplifies thinking about my personal portfolio.

For example, with a traditional Balance Sheet, you would see the value of your house in the assets section and the balance of your mortgage in the liabilities section, but it might not explicitly state your exact equity in the equity section. In this Portfolio Position Statement view, I only show your equity in a property. The only true liabilities I show are "free-standing" loans, meaning they are not associated with a specific property.

Key Notes

In general, gray cells are meant for data entry. Non-gray cells contain formulas and should be left alone.
On the top Menu or Banner on the spreadsheet, you will see Financial Tools. This is what makes this spreadsheet really useful for you! Clicking Financial Tools will bring up some options. The first option is Open Sidebar, which will give you these same functions in an easy-to-use sidebar menu. I recommend this during initial setup of your financials. You can also use all of the functions directly within that dropdown. I will go over each of those functions below. The first time you use these buttons, you will need to allow the App Scripts to run, which will add the necessary rows and sections.

Getting Started

Put today's date in cell A1 on the "Date" tab.
Put the current value of your cash and securities (Liquid Assets) in cell E6 on the "CFPlanning" tab.
Fill in your income and expense items.
- You can add new lines using the "Basic Cash In/Out" buttons.
- Each line uses an initial value and a growth rate to populate the projection. For your income, you enter your after-tax income, then a percentage that you expect it to grow by each year. You do similarly for expenses, like rent for example. Enter the current rent and the rate you expect it to increase by next year. If you pay a mortgage, you will enter your payment in the Property Module detailed below, not here.
- You can manually adjust the values in any month if you know it will be different from the projected amount, and there are Manual Cash In/Out lines for one-off items.
Adjust the Expected Return of your liquid assets stored as securities, and put in some percentage of your liquid assets that you will hold as cash at the start of the Portfolio Position Statement.
- These items define how much of your liquid assets you expect to hold in securities, stocks, or bonds, and define what kind of returns we expect on those securities.
- The sheet defaults to 5% for Expected Return, which is conservative. You might use 8.5% for average stock returns. If you have primarily CDs or other debt securities, you can put that specific interest rate.
- I have 40% set as my Cash % Allocation. I think this is a reasonable assumption. You can also adjust this percentage over time.
Add modules based on your non-stock investments. Each module will create a line in the Portfolio Statement and corresponding lines in the Cash In/Out sections. The actual asset model for the module will appear at the bottom of the sheet as a blank template.

Module Options

Here is a list of the available modules:

Property: Built for your house or an investment property. You can input the asset value, loan terms, planned improvements (CAPEX), expected income, rental operating expenses, and the eventual sale of the property.

I imagine that this module will get the most use. You can input the property name in the gray cell under the word Property. Here is a step-by-step explanation of all of the inputs.

Purchase Date: If you have not yet purchased this property, put the expected purchase date into the Purchase Date cell. Otherwise, leave that blank.

Starting Market Value: In the Starting Market Value cell, input the current expected market value of the property. You can research this with Zillow or similar sites if you do not know a good value. This value will be reflected on the property value line. You can manually adjust this property value on the property value line if you expect it to increase. You can also show planned improvements (CAPEX) that will cost cash but increase the property's value in the CAPEX line.

Starting Equity: Enter the amount of your down payment or your current equity into the Starting Equity cell. The loan balance of the property will be calculated based on the Market Value less the starting equity.

Loan Details: The Monthly Payment, Escrow Items, and Interest rate are all interconnected. Input the interest rate you have on the property, or an estimate of the rate you will have. Next, enter your current required monthly payment, or if you are purchasing this property, use a tool to estimate your required payment. Then, specify the portion of this payment that is an Escrow item. This structure simplifies how principal is paid down each month. There is also a line in the model for you to input any Additional Payments you intend to make to reduce the loan principal faster.

Rental Property Inputs: These inputs are for if you plan to rent out this property. Input a date for when you expect to start collecting rental income next to Rent Start Date. In the cells next to Income, enter the expected rent payments your tenants will pay and how you expect this payment to grow over time. Again, Zillow or similar services can be helpful for research. I recommend entering 2% for the income growth rate. Next, enter the expected expenses to maintain the property, i.e., Operating Expenses (OPEX). I recommend making this at least 25% of the expected income and setting its growth rate to 2%.

Finally, if you expect to sell this property, there is a Sale Date input that will model an exit from the property.
Annuities (In/Out): Meant to help you model level payments for various contractual agreements.

The term "annuity" is certainly finance jargon, but it is quite straightforward for our purposes. An Annuity is simply modeled as a specified number of payments of a certain amount. For example, someone might pay you $250 for the next 10 months.

We model this by inputting the number of Payments Remaining and the Payment Size. If you have not yet started making or receiving payments, then enter a Start Date.

An Annuity Cash In is designed for situations where you are receiving payments from someone, while an Annuity Cash Out is for situations where you are making payments to someone else. On the Portfolio Position Statement, the Cash In version is shown as a positive value equal to the number of payments multiplied by the payment size, and the Cash Out version shows this number as negative.
Loan (In/Out): Represents loans made to you or loans you make to others. You define a loan value, interest rate, and balance to project payments and the future loan balance.

The loan module is for loan obligations where you desire more control than the annuity module provides. The Loan Cash In module is used when you loan money to another person, resulting in "Cash In" payments for you. Conversely, the Loan Cash Out module is for loans you owe, and it will appear as a negative value in the Portfolio Position Statement.

You can enter a Starting Balance. If there is no starting balance, or if you plan to borrow or loan more at a later date, you can input that amount in the Issued line.

Mandatory Payments function similarly to loan payments in the property module: you either need to use a tool to estimate the payment or input your actual payment. This design simplifies the sheet. The Interest Rate is entered, and there is a dedicated line where any additional payments can be recorded.
Custom Asset/Liability: A fully flexible module for modeling anything else. It has "Issued" and "Distributed" lines for cash moves that adjust the balance, and periodic "Cash In" and "Cash Out" lines for income or expenses.

The Custom Asset/Liability modules are built for complete flexibility. The only initial input is a Starting Balance. You can add to or subtract from the Balance by using the Issued and Distributed lines. Any cash in or out that does not affect the balance can be modeled in the Periodic Cash In/Out lines.

This module is designed to be entirely flexible, allowing the user to model whatever is needed. I have been involved in some collateralized debt investments with unusual interest structures, almost a "Loan to Own" scenario. The Custom module is how I would model such scenarios.
Passive Opportunity: A spot for you to allocate cash where you expect a given return percentage outside of your main securities portfolio.

Finally, the Passive Opportunity module is built as a very basic model for an asset returning a given percentage. This could be used to show where part of your cash is being separated from the Liquid Assets section, for example, if you are putting it into a high-yield savings account and do not intend to touch it for a while. This module could also model your retirement accounts. It could also serve as an easy proxy for an investment where you are not entirely sure what its return will be, but you want it separated from your main portfolio.

The only initial inputs for this module are a Beginning Balance and an Expected Return. The module is designed to provide a cash flow on the balance based on the expected return for every period. There are lines to Issue (add to) the balance of the asset as well as Distribute (reduce) the balance.