I previously posted an article regarding cash flow planning and noticed that a lot of the visitors coming to this site are viewing this article. I decided to create a more thorough walk through of creating your own cash flow planning Microsoft Excel spreadsheet. Having learned to cash flow plan through Dave Ramsey’s Financial Peace University (FPU), I use my cash flow plan on a weekly basis, but I remember that I had plenty of questions when I first learned the concept.
Of course, if you’d prefer to just download my already-prepared spreadsheet rather than create your own, you can download it here: Cash Flow Plan
Creating a Cash Flow Plan Spreadsheet
Open Microsoft Excel. I’m using Microsoft Excel 2010, but any version of Excel will suffice, as we’re not using any of the advanced features.
In cell G1, enter your monthly net income. In my spreadsheet, I’ve entered $2,878.59, which equates roughly to an annual salary of $42,979.61, the average wage of a U.S. worker in 2011 according to the Social Security Administration.
In cell A2, type Category. This column will be filled with categories where you will allocate money (for example, savings, housing, etc.).
In cell B2, type Description. This column will be filled with the descriptions of items where you plan to spend your money (for example, rent, renter’s insurance, mortgage, etc.).
In cell C2, type Sub Total. This column will list the amount spend per each item listed in the description column.
In cell D2, type Total. This column will include the total amount allocated for a category.
In cell E2, type % Net. This column will contain calculations indicating how much of your net pay will be allocated per category.
In cell F2, type Actually Spent. This column will indicate how much you actually spent per item and will allow you to see where you underspend and overspend. Your spreadsheet should now look like this:
The next step is to consider major categories that your spending falls into. Examples might be charity, saving, housing, etc. Enter your first category in cell A3. Enter your next category in cell A6. Then enter all other categories, giving yourself a couple of cells of cushion for each. The spacing isn’t critical here, as you can simply cut and paste later in order to give yourself more room. Here is what my spreadsheet now looks like:
You’ll now want to fill in each spending item in column B. For example, under the Charity category, you may list Church, Catholic Radio, Franciscan Friars of the Renewal, or any of your favorite charities. Go through each category with the intention that all of your spending is classified (i.e., every penny you spend should fall under a category and sub-category). Here is what my spreadsheet now looks like:
Your next task is to determine how much to allocate for each item. Certain items are easy to place a number on, like rent, mortgage, and your cable bill. These bills are the same each month. Other items will be more difficult to come up with, as they fluctuate on a monthly basis. After tracking your spending for a few months, you’ll develop a better idea of how much you spend on these items. You can then update your spreadsheet accordingly. Here is my updated spreadsheet:
In cell D57, type the following: =SUM(C4:C54) This will provide a sum of all of the values in column C and will tell you how much you’ve allocated. This should match cell G1.
(Optional) I consider this step optional, but I like to do this so that I can see what proportion of my money I spend on each category. In cell E8, type =(SUM(C4:C7)/G1) This will tell you what percentage of your net monthly pay you have allocated toward the Charity category. If you aim at a 10% figure for tithing, this tells you that you need to allocate more for charity. Do this for each category of spending. My updated spreadsheet:
Next, we’ll want to enter formulas for calculating how much you *actually* spent. In cell F8, type =SUM(F4:F7) At the end of the month, after you go line by line in your cash flow plan and enter how much you have to your diocese, your parish, and so on, cell F8 will tell you how much you actually gave to charity. Do this for each category in your spreadsheet.
Finally, add up how much you actually spent for the entire month. In cell F56, type =SUM(F8,F14,F18,F24,F28,F36,F40,F43,F49,F53) Note that the cells in this formula are the summations of each category. If you added an item or took an item away, you’ll need to adjust your formula accordingly. My finished cash flow plan looks like this:
Your cash flow plan will be a work in progress, especially for the first few months that you practice using it. You’ll find categories to add, items to add, and things to rename. Good luck!
I periodically post finance-related verses from the Bible and the Catechism of the Catholic Church.
Today’s Mass reading from Acts 3:1-10 speaks of the worth of the graces and blessings Christ gives us:
Peter and John were going up to the temple area
for the three o’clock hour of prayer.
And a man crippled from birth was carried
and placed at the gate of the temple called “the Beautiful Gate” every day
to beg for alms from the people who entered the temple.
When he saw Peter and John about to go into the temple,
he asked for alms.
But Peter looked intently at him, as did John,
and said, “Look at us.”
He paid attention to them, expecting to receive something from them.
Peter said, “I have neither silver nor gold,
but what I do have I give you:
in the name of Jesus Christ the Nazorean, rise and walk.”
Then Peter took him by the right hand and raised him up,
and immediately his feet and ankles grew strong.
He leaped up, stood, and walked around,
and went into the temple with them,
walking and jumping and praising God.
When all the people saw him walking and praising God,
they recognized him as the one
who used to sit begging at the Beautiful Gate of the temple,
and they were filled with amazement and astonishment
at what had happened to him.
This verse prompts us to remember the graces and blessings Christ gives us on a daily basis and to give thanks for them. Through God’s grace we have life, health, family, friends, and many other signs of his love. Thanks be to God.
A recent Wall Street Journal article indicates that 28% of Americans have no confidence that they’ll have enough money saved to retire comfortably. In the same article, 57% of those surveyed indicate that they have less than $25,000 in savings and investments outside of their home values. Given that retirement calculators indicate you’ll need much more than $25,000 for retirement, the 57% cited in the Wall Street Journal article have a lot of ground to cover. Undoubtedly, many Americans are being squeezed by the current less-than-stellar economy, so how can a person save more for retirement when they’re already using their entire paycheck?
A Painless Solution
As an employee, one of the financial benefits I look forward to every year is an annual pay raise. The pay raise coincides with the new fiscal year at the organization where I work, so I can count on a pay increase at about the same time every year. One thing I have done recently is increase my 401(k) retirement contribution by 1% when my pay raise kicks in. The idea is that, if I have not gotten used to the larger paycheck yet, I won’t miss the 1% that automatically goes to my 401(k) account.
If every year you follow the pattern of saving 1% with each pay raise you receive, within several years you’ll have saved more for retirement without having missed the money. Ideally, you’ll reach a 401(k) savings rate of at least 10%, which will provide a substantial sum for retirement. In order to have a rough estimate of how much you’ll need saved for retirement, make sure to run through a retirement calculator or two, taking into account your expected retirement spending habits, retirement healthcare costs, and inflation.
Set it and Forget it
Some employers allow employees to sign up for automatic retirement contribution increases. If this is the case for you and you’re not saving enough, take advantage of the opportunity so that you have one less chore to do come pay raise time. As you approach retirement, you’ll be thanking yourself for making your life easier.
Vanguard investors who have less than $10,000 to invest purchase Vanguard’s entry-level Investor shares . Vanguard offers investors the ability to “upgrade” their shares by purchasing Admiral shares once investors accumulate a given amount within certain mutual funds. Admiral shares have lower expense ratios than their Investor shares counterparts, allowing investors to keep more of their money. From Vanguard’s web site, to qualify to purchase Admiral shares you need to meet one of the following criteria:
Invest $10,000 or more in most Vanguard index funds that offer Admiral Shares.
Invest $50,000 or more in Vanguard actively managed funds that offer Admiral Shares.
An example of the difference between Investor shares and Admiral shares: The Total Stock Market Index (VTSMX) mutual fund has an expense ratio of .18% while its Admiral shares equivalent, VTSAX, has an expense ratio of .06%.
If you invest with Vanguard and haven’t visited their web site in a while, you may be able to save yourself money by logging in and converting your Investor shares into Admiral shares. The conversion from Investor shares to Admiral shares is free, so it’s worth your time to check.
The new year will bring changes to 401(k) and IRA accounts. These changes will allow you to save more. Here’s a list of what’s changing:
Increased 401(k) contribution limit – The maximum you can save annually will increase from $17,000 to $17,500. These figures apply to 403(b) accounts, too.
401(k) fee notifications – Quarterly statements will include more information regarding fees you are paying in your 401(k) plan.
Increased IRA contribution limit – Workers who meet the income requirement will be able to contribute up to $5,500 annually, up from $5,000.
Increased Roth IRA income limits – Single people can contribute to a Roth IRA until they reach $112,000 in income, at which point contribution limits are decreased until income reaches $127,000. Couples can contribute to a Roth IRA until they reach $178,000 in income, at which point contribution limits are decreased until income reaches $188,000.
The 401(k), 403(b), Traditional IRA, and Roth IRA are great methods for growing your retirement nest egg. The tax advantages these retirement plans provide allow your money to grow more than in a taxable account. The 401(k) and 403(b) plans are provided by employers, but anyone with an earned income can open a Traditional IRA or Roth IRA.
A recent Wall Street Journal article indicates that an increasing number of parents and grandparents are being affected by student loan debt: http://blogs.wsj.com/bankruptcy/2012/10/29/soured-student-loans-bankrupt-parents-grandparents. From the article:
As young graduates and former students struggle to find work, their student-loan obligations are increasingly falling to the family members who agreed to back the debt in the event of default. Bankruptcy lawyers report that a growing number of student-loan co-signers, especially grandparents, are trying to get rid of the loan obligations using bankruptcy, hopeful that they’ll find a sympathetic judge or a lender who’s voluntarily willing to lower payments.
Unfortunately, borrowers usually don’t receive favorable rulings in bankruptcy court, so parents and grandparents who co-sign for their children’s’ and grandchildren’s student loans are often left footing the bill. This puts retired parents and grandparents in difficult predicaments due to being on fixed incomes.
My advice to parents and grandparents considering co-signing on student loans: Don’t do it, especially not if it’s for an undergraduate degree. If your prospective college student wants to attend a private undergraduate university but cannot afford it without sizable student loans, take a look at the many great public universities. Remember that while higher education can be financed via loans, you can’t take out a loan for retirement.
I periodically post finance-related verses from the Bible and the Catechism of the Catholic Church.
As I finished up mid-morning prayer from the Liturgy of the Hours yesterday, I came across scripture that relates directly to personal finance. From Romans 13:8-10 (or you can visit the Universalis web site for the full mid-morning prayer: http://www.universalis.com/20120924/terce.htm):
Avoid getting into debt, except the debt of mutual love. If you love your fellow men you have carried out your obligations. Love is the one thing that cannot hurt your neighbour; that is why it is the answer to every one of the commandments.
After spending seven and a half years paying off student loans and finally becoming debt free last year, I felt a weight lifted off my shoulders. For the five years I was in college plus the seven and a half years after, I had become accustomed to sending a check to Sallie Mae once a month. Also, during that time period, on numerous occasions I calculated how many years and months remained until I was payment-free and could cash flow that money elsewhere. While I incurred the debt in school, I didn’t realize that I was also adding stress to my life.
Needless to say, I agree with Romans 13:8-10 :) My experience with student loans taught me that debt involves more than making somebody else rich with all your interest payments. Debt also adds stress to your life and this stress is an intangible aspect of debt that one must account for when making financial decisions.