Author Topic: Share your financial spreadsheets!  (Read 10306 times)

MilStachian

  • 5 O'Clock Shadow
  • *
  • Posts: 94
  • Location: New England
Share your financial spreadsheets!
« on: October 25, 2013, 11:01:36 PM »
I've come across several post where authors mention tracking investments/finances with their own Excel spreadsheets, and I'm curious to see what they look like.

So, here's my call to the community; if you're an Excel wizard, please post and share your work with the rest of the world.  If you don't have a place to upload them, send me a PM and I'll hang them up on the web and post the links here.


SnackDog

  • Handlebar Stache
  • *****
  • Posts: 1260
  • Location: Latin America
Re: Share your financial spreadsheets!
« Reply #1 on: October 26, 2013, 12:12:11 AM »
I have been meaning to share mine but it is not yet flexible enough for general use. I have over-customized it for my particular case. I would heartily encourage everyone to build their own in order to get a grip on their future. Just whack in columns for years and start adding rows for income, appreciation, spending, etc. be sure to define variables like inflation, starting balances, retirement age, etc. I like one page with all the variables and a collection of graphs to see how changes in, say, retirement date affect future account values.

CDP45

  • Pencil Stache
  • ****
  • Posts: 509
Re: Share your financial spreadsheets!
« Reply #2 on: October 26, 2013, 12:37:26 AM »
Balance sheet and income statement. You know who takes moneys seriously? Businesses. Treat your finances like a business would and make a plan for every dollar that comes in.

MilStachian

  • 5 O'Clock Shadow
  • *
  • Posts: 94
  • Location: New England
Re: Share your financial spreadsheets!
« Reply #3 on: October 26, 2013, 07:21:02 AM »
Balance sheet and income statement. You know who takes moneys seriously? Businesses. Treat your finances like a business would and make a plan for every dollar that comes in.

CDP45, you make a great point, but I'd still like to see how other folks manage their money.  Why don't you post your IS and BS for those that may not have seen one before.

ichangedmyname

  • Bristles
  • ***
  • Posts: 418
    • Luxe Frugality
Re: Share your financial spreadsheets!
« Reply #4 on: October 26, 2013, 07:28:22 AM »
I'll be interested too. Thanks!

thelamb

  • 5 O'Clock Shadow
  • *
  • Posts: 95
  • Age: 44
  • Location: Columbus, OH
Re: Share your financial spreadsheets!
« Reply #5 on: October 26, 2013, 10:56:06 AM »
I've recently been playing with personal capital, which (assuming the same for mint) is a great way to get a quick snapshot with little work.  That being said, I've long kept a personal set of sheets, and, like others have said, I'm of the opinion that this is the best way to firmly grasp your given situation.  Mine are extremely personalized, moderately complex, and take up a few hours a month to maintain.  You could argue that I spend too much time on them, but I enjoy it and it helps me stay focused.  Due to how personalized they are, I won't share the actual sheets, but will attempt to layout in this crude format what they look like.  If I ever get time, I may try to templatize and share.  I apologize, this is going to be insanely long. 

Sheet 1 - Bills
This one is a comparison of the current amount of cash in my checking accounts versus each major bill that will go out.  It's somewhat inaccurate as certain bills have already been paid and accounted for in the current cash level, but it still works to give a quick shot of input versus output.  I have two checking accounts, one for the majority of living expenses, the other for my mortgage, HELOC, and other remodeling expenses.  The columns look like this:

Checking 1, Bill1, Bill2, etc., Checking 1 Expenses (sum), Checking 1 Remaining,
Checking 2, Mortgage, HELOC, etc., Checking 2 Expenses, Checking 2 Remaining,
Total Beginning (c1 + c2), Total Expenses, Total Remaining,
Debt Expenses, Living Expenses,
TH (take home) – Total Expenses, TH – Living Expenses
Then there are various columns to calculate retirement amounts based on current living expense amounts.  Like the living expenses * 12 * 25 gives a retirement amount.  The amount being saved * 12 to get yearly savings.  Then the first divided by the second gives years till retirement.  This is a stupidly crude calc since current savings and interest aren’t calculated at all. 

Sheet 2 – Equity
Simply a monthly snapshot of every account balance.  I don’t worry about the fact that the checkings are usually about to be depleted.  It’s just a snapshot.  The columns are:

Checking 1, Checking 2, IRA, Roth IRA, 401k, Money Market, Total Liquid Assets, Debt, Total Equity

Sheet 3 – Debt
Similar to the equity, this is a snapshot of every debt account balance.  This won’t include my day-to-day cc unless I’m carrying over a balance.  I’m carrying some remodel debt and I split things between short term (have to pay off in less than a year), mid term (stuff people would typically carry over longer or things that are hedged against house equity), and finally mortgage.  I add some complexity to calc the amount that should be reduced every month based on a desired payoff date.  The columns are:

Bal1, Bal2, etc, Short Term Bal, Bal3, Bal4, etc., Mid Term Bal,
Short + Mid Bal, Amt Pd in Month, Mnths to Payoff Date (has a fun formula:  =(YEAR($L$1)-YEAR(A11))*12+MONTH($L$1)-MONTH(A11)),
Need to pay (how much should reduce to stay on track),
Mortgage, Mortgage Reduction, MR %,
Total Debt, Total Reduction, Avg Total Reduction,
Then, since I did a remodel and hope to have decent equity in my house, I have a whole series of columns to calc estimated sale, proceeds and subtract out all the debt, etc… 

This has rows for each month that has occurred but also has projections for the future to help gauge if my plan will actually work. 

Budget
This is a whole separate spreadsheet with multiple worksheets, is the newest to be added, and is by far the most complex.  It’s not a complete view of my budget but just my one cc that is used for all living expenses like groceries, gas, entertainment, dining, etc.  It is also the cc I use for company business expenses, mainly travel, which get reimbursed.  I don’t budget these but simply filter them out since they’re not my expenses.  There is a worksheet for each monthly cc statement.  It’s simply the entire statement and I add a column for category and literally go through and type in a category value. 

Then there is a summary worksheet.  It is divided into a top half and bottom half with some summary and goal values in the middle. 

The top half calcs the expenditures for each month for each category.  There are implicit groupings for a higher categorization.  For instance, the cats Dining, Booze, Entertainment can be lumped together as Stupid/Fun, while the cats Food, Clothes, Gas are lumped as Living.  The columns are:

Yearly (Tot – Bus * 12), Tot – Bus (Business), Cat1, Cat2, etc., Business, Total,
Then the higher level cats:  Stupid/Fun, Living, Random, Bills
Then the higher level cats again as percentages of the total. 

The middle has four rows that correspond to the top columns:  Average, Yearly Average, Budgeted, Yearly Budgeted. 

The bottom half then compares the expenses per category against the amount budgeted for each category.  The columns are all the same.  The value for each column is the amount budgeted minus the amount spent, giving either the amount left or the amount over.

For the current month, whereby I will grab the statement periodically, usually weekly, I calc some things to give me an idea of where I’m at.  For this, I have the following additional columns:
Days Left, % Days Left, % Money Left, Average Amount Spent Per Day, On Pace to Spend, Amount Can Spend Remaining Days








senecando

  • Bristles
  • ***
  • Posts: 480
  • Age: 34
  • Location: Madison, Wi
Re: Share your financial spreadsheets!
« Reply #6 on: October 26, 2013, 11:17:50 AM »
I do a simple month by month and use YNAB or Mint for the rest.
« Last Edit: January 17, 2014, 11:58:52 AM by senecando »

Cecil

  • Bristles
  • ***
  • Posts: 301
  • Age: 39
  • Location: Vancouver, Canada
Re: Share your financial spreadsheets!
« Reply #7 on: October 26, 2013, 11:57:01 AM »
Mine's full of custom data that I'd rather not post publicly, but I'll describe it. I use MS Money to record and categorize all my transactions, then copy the summary over to a spreadsheet on a monthly basis:

Sheet 1: Monthly balance statement. Account balances by account, total assets, total liabilities, net worth, change from previous month, and year-over-year percentage gain. With a graph.

Sheet 2: Invested $ by date, with an XIRR calculation for a cross-account annualized rate of return. Also keeps track of which accounts need to be funded next based on my yearly limit.

Sheet 3: Asset Allocation. Balance by index fund type, current percentages, and a calculation of what to sell and what to buy on the next rebalance.

Sheet 4: Detailed monthly breakdown of stats I find interesting. Total Income, Total Expenses. Monthly savings rate, rolling 12-month savings rate, all-time savings rate, total investment portfolio, total dollars contributed, total investment income to date, expected investment income this month (based on 8% gain), allowable withdrawal this month (at 4% SWR), years of annual expenses saved, and a calculation of expected years to FI. With charts graphing all of the above.

Sheet 5: Monthly expenses broken down into regular and one-offs. Graphs of monthly expenses on a year-by-year basis.

Sheet 6-7: A custom lifelong balance sheet forecaster that includes increased future expenses and pensions.

Milehimama

  • 5 O'Clock Shadow
  • *
  • Posts: 14
  • Location: Lone Star State
    • Milehimama
Re: Share your financial spreadsheets!
« Reply #8 on: October 26, 2013, 01:16:19 PM »
I've been lurking around this site for a while, but here is a sample mockup of the monthly budgeting sheet I use.
https://docs.google.com/spreadsheet/ccc?key=0Apo2IKl-ZAyjdHF1RXdvTjJZTnZKNVBFNkppb2Zna0E&usp=sharing

I do it this way so I can account for cashflow as well as expenses.  The top part is basically fixed expenses, the lower part the variable expenses.  I use the cash envelope system for variable expenses, such as groceries, and this also tells me how much cash I need out and WHEN I can take it out.

This is the spreadsheet I use when helping friends with budgeting, especially those who live paycheck to paycheck.  The "days we get paid" vs. the "days bills are due" are particularly important to us, as we are paid hourly (variable income, not salary), and are paid every two weeks, so paydays are on different days each month.  This also actually gives us 2 extra "paydays" a year - two months we'll get 3 paychecks- which go directly into savings. 

This mock budget is helpful with the variable income.  This sample budget is set at an income of $4500k per month, for example.  Overflow goes into a basic savings account that is connected to the checking account.  If there is a lot of overtime, for example, and the monthly income is actually $5600, than we move $1100 to savings automatically (in addition to the the "monthly overage" savings shown below.  If it's a short week, such as Thanksgiving (we don't get paid holidays, so if the business is shut down Thurs-Sun it will be a 30 hour week, for example), and the paycheck is say, $1600 instead of $2250 as budgeted, then we will pull $650 from savings so that the income becomes the standard $2250.  (Obviously if this became a common occurrence, we'd revisit the budget.)

Anyway, we and most of my friends are variable income and also have kids which can mean variable expenses, so this works for us.

DaKini

  • Bristles
  • ***
  • Posts: 415
  • Location: Germany, Munich area
Re: Share your financial spreadsheets! (RebalancingTool)
« Reply #9 on: April 28, 2014, 07:55:08 AM »
Hi there,
i want to share my RebalancingTool with you.
It was made with OpenOffice Calc, so i cannot guarantee it works with Excel (it should however).

Short HowTo for SETUP:
This Rebalancing tool assumes that you have split your assets in two main categorys "Risky" and "Riskless". The Categorys are then subdivided in subcategorys. Risk control is exercised by balancing riskful assets against the riskless ones. You may also balance the individual categorys shifts.

First, you need to enter your overall configuration into the top left box ("Risk control"):
- The "riskless portion" defines how much you desire to have in non volatile assets like bonds etc. The riskful portion is automatically calculated.
- You define your rebalancing bands. Those are used to highlight the cells which deviate more than the percent value defined.

Next, you define your subcategorys and their allocation:
- Choose names for the brighter cells. I choosed the colors to make some kind of "sorting" and clarification but the colors are meaningless otherwise.
- Above the names you see the percent allocation of the respective category in the darker colored cell. There you enter your desired allocation for that subcategory respective to the entire portfolio part. At the leftmost side you can check that you have exactly 100% allocated (it is highlighted if not so you can adjust).

Now enter the assets you own in the "Depot / Product" lines. Enter the current value below the corresponding subcategory.
You can add Lines as you like.
Once you are finished, the spreadsheet should have calculated all deviations. Your overall Balancing of Riskful against riskless is shown in the topmost right box.

Short HowTo for REBALANCING:
After updating all your values of all your products, you should first check (top-right box) wehter the overall risk allocation is in its boundarys.
If its out of whack (determined by the "global rebalancing band") it will be highlighted and you need to rebalance to make the deviation  go away.
Each portfolio part has a "(Notes)"-Line at its bottom where you can enter balances that are exempt from caluclation. You can use this to note down planned adjustments.
When you are done making rough plans, you can use the "Planned adjustments" line to look what the final outcome of your actions would be. Once you are satisfied, you have your "sell this, buy that" orders that need to be executed.


I hope this is helpful. If there are errors in the formulas, please let me know (especially so i dont do dumb investments because i use this spreadsheet for my real investments).
Probably it is also helpful to do a playthrough with the examples to get a feeling for how the spreadsheet works.

For me its mostly the following exercise:
- Update investment values
- Update available cash, this brings "cash" position out of whack
- Shift the cash surplus to the most lacking asset; priority at riskless assets; riskful only if portfolio is out of whack
- Once a year rebalance with other assets if needed
« Last Edit: April 28, 2014, 07:57:54 AM by DaKini »

CDP45

  • Pencil Stache
  • ****
  • Posts: 509
Re: Share your financial spreadsheets!
« Reply #10 on: April 28, 2014, 08:35:06 PM »
Ok i'll contribute:

Simple Budget Sheets

I think if you say make a copy then you can save one yourself, otherwise this is open for helpful additions.
« Last Edit: April 28, 2014, 08:56:29 PM by CDP45 »

 

Wow, a phone plan for fifteen bucks!