Author Topic: One sheet to rule them all...  (Read 51912 times)

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
One sheet to rule them all...
« on: June 12, 2015, 07:17:07 PM »
In a few older threads (this one and this one), there were financial spreadsheets shared and lots of great nerd-ery ensued. In one thread in particular, I shared my personal finance dashboard and got so much interest I've scrubbed the numbers and provided instructions.

For your viewing pleasure, I've attached a sample of the dashboard.



The master copy can be found here.

FI Tracker v3

If you want to use this for yourself, go to File->Make a Copy. If you have questions, post them here! I hope some of you gain some value from this.

Edit 23-May-2016: As this thread has grown, fellow forum member and Google Sheets nerd Weinerdog has taken the original sheet and made some very useful tweaks and optimizations. His FI Tracker v3 (and prior to that, v2) is a branch that allows copy/paste from Mint, reduces visual clutter, and provides more automation than the original. I think it's a better version, quite frankly. It was such an improvement that it's now the recommended link above.

Links to v2 and v1 for historical purposes.
« Last Edit: May 23, 2016, 08:03:05 AM by IndyPendent »

CashFlowDiaries

  • Stubble
  • **
  • Posts: 178
  • Location: Indianapolis, IN
  • Follow me on my journey to Financial Freedom!
    • Cash Flow Diaries
Re: One sheet to rule them all...
« Reply #1 on: June 12, 2015, 08:02:12 PM »
That is a sweet spreadsheet!! I really like the dashboard but it must be a pain in the butt entering all that data.

I keep track of my net worth as well but not an awesome spreadsheet like yours.  Mine is super simple and just keeps track of current month and last month for comparison reasons.
Passive Income earner.  Creator of  www.cashflowdiaries.com

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #2 on: June 12, 2015, 08:15:16 PM »
Yep, it's work to enter but the result is worth the effort! It won't be for everyone, for sure.


Sent from my iPhone using Tapatalk

Cookie

  • Guest
Re: One sheet to rule them all...
« Reply #3 on: June 12, 2015, 10:39:23 PM »
Thanks! This is way better than mine.

deborah

  • Magnum Stache
  • ******
  • Posts: 4840
Re: One sheet to rule them all...
« Reply #4 on: June 12, 2015, 11:07:47 PM »
Thanks very much! I have changed the colours around in the net worth dial on the dashboard, as I think the greatest net worth should be green, and the least red.

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #5 on: June 13, 2015, 05:38:20 AM »
Thanks very much! I have changed the colours around in the net worth dial on the dashboard, as I think the greatest net worth should be green, and the least red.

Great idea! I didn't really pay attention to the colors, but you're right. Green definitely belongs toward the end of the progress. :)

Edit: I've now updated the master copy to flip the colors. Nice suggestion! I also added a couple of additional details about the savings rates conditional formatting.
« Last Edit: June 13, 2015, 05:55:00 AM by IndyPendent »

wienerdog

  • Bristles
  • ***
  • Posts: 364
Re: One sheet to rule them all...
« Reply #6 on: June 26, 2015, 11:44:22 PM »
Indy thanks for the great spreadsheet!  I have found it very useful and well put together.  I have been tinkering with it a little and made a few changes.  If you don't mind I will share the edited Home Payoff sheet that calculates the loan from the terms.  I followed Indy's lead and left boxes that you fill in non colored.  I put a little bit of instructions in as I had a loan in time that I knew the value and I didn't want all the previous months cluttering the sheet up.  The instructions explain it good enough I hope.  You can just as well start from month one.

The loan balance and equity should auto update at the beginning of the month.  All you need to do is put the extra payment in if you pay additional principal each month.  You can put that value in ahead of time if you pay a fixed amount.   I did some other updates where it will pull the value for principal and the additional payment into each month's tab to save you the labor.  I can share those changes but I want to smooth them out a little more.

Anyway thanks to Indy for sharing this great spreadsheet as I just made a few minor tweaks.  If you want me to take the sheet down let me know and I will delete it.

https://docs.google.com/spreadsheets/d/1lDEIs0ODvVqOW9FtB8o2Jvott4jF2ms2PJbXREBguHk/edit?usp=sharing


IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #7 on: June 27, 2015, 06:47:34 AM »
Indy thanks for the great spreadsheet! 
[...]
Anyway thanks to Indy for sharing this great spreadsheet as I just made a few minor tweaks.  If you want me to take the sheet down let me know and I will delete it.
[...]

Right on! No need to remove it, I think it's great if folks find your version useful. A couple of notes:

1) Cell E8 is static, which for me was a bit confusing until I copy/pasted the formula from another cell. The values down-line were borked until I did that.
2) Cell C1 should (presumably) be white, as the user should update that periodically
3) If you really wanted to be fancy-pants, you could have an amortization schedule calculator factored into your monthly amounts--technically the principle and interest amounts will change monthly, especially when you are making large extra payments.

The original is definitely more basic--I check my payoff amount manually each month and update the value. For me, updating the payoff amount is actually a nice little shot of dopamine and a reward for my frugality! :)

I've added a link to your fancy-pants version in the original template, in case others want your version--nice work!

wienerdog

  • Bristles
  • ***
  • Posts: 364
Re: One sheet to rule them all...
« Reply #8 on: June 27, 2015, 02:24:38 PM »
Indy,

Thanks for the feedback.  I tried to cut down on the confusion with the instructions on the right.  After I looked at it again I figured there was a better way to do a certain point in time or start the table somewhere that is not at the beginning of the loan.  I updated the spreadsheet with your feedback.  I think it is more complete now and I took in account for the added payment in the principal calculation.

Thanks again for the original and the great feedback!

Hummer

  • 5 O'Clock Shadow
  • *
  • Posts: 73
Re: One sheet to rule them all...
« Reply #9 on: June 27, 2015, 06:01:39 PM »
I'm not the most tech savy. How do I get a copy of it downloaded?
Canada seems expensive. Is 20k American MMM spending equivalent to 30k Canadian?

If anybody has a converter that takes into account the cost of living, please PM me so I can set some realistic goals for myself.

wienerdog

  • Bristles
  • ***
  • Posts: 364
Re: One sheet to rule them all...
« Reply #10 on: June 27, 2015, 08:37:31 PM »
I'm not the most tech savy. How do I get a copy of it downloaded?

Hummer you should have a google account and be logged in.   The link in the first post will direct you to the shared copy of the sheet which you can't edit.  Under the File menu option select "make a copy..." and you will save a copy to your google drive that you can edit.

Hummer

  • 5 O'Clock Shadow
  • *
  • Posts: 73
Re: One sheet to rule them all...
« Reply #11 on: June 27, 2015, 09:20:34 PM »
Figured it out. Thanks
Canada seems expensive. Is 20k American MMM spending equivalent to 30k Canadian?

If anybody has a converter that takes into account the cost of living, please PM me so I can set some realistic goals for myself.

Hummer

  • 5 O'Clock Shadow
  • *
  • Posts: 73
Re: One sheet to rule them all...
« Reply #12 on: June 27, 2015, 10:13:53 PM »
For some reason, I don't have a pie chart or a needle on my dashboard page. I don't think I accidentally deleted anything.
Canada seems expensive. Is 20k American MMM spending equivalent to 30k Canadian?

If anybody has a converter that takes into account the cost of living, please PM me so I can set some realistic goals for myself.

swick

  • Global Moderator
  • Magnum Stache
  • *****
  • Posts: 2860
Re: One sheet to rule them all...
« Reply #13 on: June 27, 2015, 11:01:33 PM »
Awesome resource!  Pinned :)

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #14 on: June 28, 2015, 05:19:04 AM »

For some reason, I don't have a pie chart or a needle on my dashboard page. I don't think I accidentally deleted anything.

What web browser are you using?


Sent from my iPhone using Tapatalk

Hummer

  • 5 O'Clock Shadow
  • *
  • Posts: 73
Re: One sheet to rule them all...
« Reply #15 on: June 28, 2015, 07:48:02 AM »
Chrome but I'm not using a browser. After I made a copy, I downloaded it as an excel worksheet. I've been using it from my desktop.
Canada seems expensive. Is 20k American MMM spending equivalent to 30k Canadian?

If anybody has a converter that takes into account the cost of living, please PM me so I can set some realistic goals for myself.

deborah

  • Magnum Stache
  • ******
  • Posts: 4840
Re: One sheet to rule them all...
« Reply #16 on: June 28, 2015, 02:23:35 PM »
I don't think excel has the needle graph type. It does, however, have pie charts.

Hummer

  • 5 O'Clock Shadow
  • *
  • Posts: 73
Re: One sheet to rule them all...
« Reply #17 on: June 28, 2015, 03:39:21 PM »
Shoot. That never even occurred to me. I assumed the master copy was an excell spreadsheet. Now I see it is a google spreadsheet.
Canada seems expensive. Is 20k American MMM spending equivalent to 30k Canadian?

If anybody has a converter that takes into account the cost of living, please PM me so I can set some realistic goals for myself.

Tieke

  • 5 O'Clock Shadow
  • *
  • Posts: 60
  • Location: New Zealand
Re: One sheet to rule them all...
« Reply #18 on: June 28, 2015, 06:12:38 PM »
Oh my.... Indy, I think I love you.  I am just starting off spreadsheeting my finances in full for the first time and am only the most basic of Excel users, so this is going to save me a ton of trouble.  Thank you!!

Chippewa

  • Magnum Stache
  • ******
  • Posts: 2860
  • Location: In the Poppy Fields
    • stupid debt
Re: One sheet to rule them all...
« Reply #19 on: June 28, 2015, 08:56:42 PM »
Love it! Finally cleaned it up with all my digits. And incorporated my 'annual snapshot of planned expenses' sheet into it from my old spreadsheet. Now I got everything I need umbrella'd into one spreadsheet. Thanks!

mickeyj

  • 5 O'Clock Shadow
  • *
  • Posts: 98
Re: One sheet to rule them all...
« Reply #20 on: June 29, 2015, 02:30:01 AM »
Thanks for this. It's freaking awesome!

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #21 on: June 29, 2015, 06:01:39 PM »

Thanks for this. It's freaking awesome!

You, and everyone else, are certainly welcome! I would love to hear how people are using this and how we could potentially improve the template!

Rosy

  • Pencil Stache
  • ****
  • Posts: 891
Re: One sheet to rule them all...
« Reply #22 on: July 05, 2015, 09:56:29 AM »
@Indy THANK YOU!
 I only have open office so I hope I can make it work. I really would love to have an all encompassing spreadsheet with color graphics. Hope I can figure it out. Spreadsheets are not my strength, glad you gave Hummer the instructions for the google download.

Zamboni

  • Handlebar Stache
  • *****
  • Posts: 1654
Re: One sheet to rule them all...
« Reply #23 on: July 06, 2015, 11:42:07 PM »
Awesome!  Following and will surely enjoy.

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #24 on: July 07, 2015, 05:06:24 PM »
@Rosy, I've not tested any versions in Open Office, but I would be interested in your feedback. What works and what doesn't?

Thegoblinchief

  • Walrus Stache
  • *******
  • Posts: 6686
  • Age: 32
  • Location: SE WI
  • Voluntarist sheepdog
    • The Goblin Chief
Re: One sheet to rule them all...
« Reply #25 on: July 11, 2015, 07:02:58 AM »
Interesting sheet. Not sure if I'll use it or not versus what I'm doing now, but thanks for sharing :)

Edit: I suppose I should ask - is there a faster way to "reset" the sheets by clearing out data other than manually going through and deleting cell values?
« Last Edit: July 11, 2015, 07:47:20 AM by Thegoblinchief »
Presenter at Camp Mustache Canada 2017

Read my urban homesteading adventures here: https://forum.mrmoneymustache.com/journals/food-forest-lagomorphs-and-tiny-dinos-in-the-city/

“The greatest change we need to make is from consumption to production, even if on a small scale, in our own gardens. If only 10% of us do this, there is enough for everyone. Hence the futility of revolutionaries who have no gardens, who depend on the very system they attack, and who produce words and bullets, not food and shelter.” - Bill Mollison

wienerdog

  • Bristles
  • ***
  • Posts: 364
Re: One sheet to rule them all...
« Reply #26 on: July 11, 2015, 08:34:00 AM »
Probably the best would be to edit Jan how you like it.  (expenses, investment accounts etc)  Then copy that one to duplicate it and rename for the rest of the months.

Edit:  Actually I am not sure if that won't break something else as I haven't done it that way.  I just modified the section I wanted on June.  Then copied each section to each month.
« Last Edit: July 11, 2015, 08:35:33 AM by wienerdog »

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #27 on: July 12, 2015, 07:33:52 AM »

Probably the best would be to edit Jan how you like it.  (expenses, investment accounts etc)  Then copy that one to duplicate it and rename for the rest of the months.

Edit:  Actually I am not sure if that won't break something else as I haven't done it that way.  I just modified the section I wanted on June.  Then copied each section to each month.

Yep, that's pretty much how I do it too.

z6_esb

  • 5 O'Clock Shadow
  • *
  • Posts: 27
Re: One sheet to rule them all...
« Reply #28 on: July 15, 2015, 08:07:27 AM »
1. For each month, I created a new row that captures employer 401K match (F34, G34, H34, I34) and then added that sum to the formula in F11.  Is there any reason to not do this?

2. I also created a net income row (B6 and C6=-(F37+F38+F39)) since I think of my budgets in terms of take-home on a day-to-day basis.

Thanks for putting this together !

wienerdog

  • Bristles
  • ***
  • Posts: 364
Re: One sheet to rule them all...
« Reply #29 on: July 17, 2015, 10:25:00 AM »
1. For each month, I created a new row that captures employer 401K match (F34, G34, H34, I34) and then added that sum to the formula in F11.  Is there any reason to not do this?

I did the same.  I then take the total and use it under C12 and I labeled that row 401k match.  I then added it to the sum under C14 as I consider that spent on investments. 

One other thing I plan to do is to automatically populate all the spending areas toward the bottom left.  Right now I just log a daily spreadsheet of every item I spend and categorize it.  At the end of the month I will just copy that sheet to a blank on the FI Tracker and then have a script total each category and place it in the proper slot for the month.

I have also been looking at certain apps that makes entering the expenses easier with an export feature.  Might have to roll my own eventually.  Just want it simple where you enter the amount and select the category.  It enters the current date or lets you change to the date the expense occurred.

Can't thank Indy enough for providing an excellent base to customize.

Stash Engineer

  • Stubble
  • **
  • Posts: 172
Re: One sheet to rule them all...
« Reply #30 on: July 21, 2015, 06:46:12 AM »
Nice work on the spreadsheet!  I'm playing with it now.
Chuck Norris is so fast, he can make a fire by rubbing two ice cubes together.

wienerdog

  • Bristles
  • ***
  • Posts: 364
Re: One sheet to rule them all...
« Reply #31 on: July 21, 2015, 09:25:53 PM »
I got another turn of Indy's spreadsheet if anyone is interested in taking it for a spin. Best if used in Google Sheets as that is all I have used.  I gave his first spreadsheet a little twist and added the Mad Fientist's FI sheet in as I found that a good tool to look at what expenses cost in time and calculate when FI should arrive.  Of course I put my little spin on some parts of each but most credit goes to Indy and Mad Fientist.  (Not sure if he/she posts here?)  http://www.madfientist.com/

I am open for feedback if anyone sees something wrong or something isn't clear.  I removed Indy's dashboard sheet and the investment sheet as those need cleaned up yet.  When I get time I will add them back in.  I am also open to feedback if you got an idea to make it better.  I tried to include plenty of different types of accounts and plenty of expenses so you can use what what you need.  Just leave them blank or if you want shorten the sheet up by deleting the rows you don't need under the expenses that should work.  (You will have to do it on each monthly sheet and the FI sheet so they all match.  You will only use the delete row feature and the formulas should all move up.  Be sure to keep the totals at the bottom of each expense bundle.)

A bunch of fields update automatically or don't appear until after the first of the month.  Your expenses won't get averaged into the FI calculation until the first of the next month but the way it calculates it should take a running average for you over the year.  At the end of the year you should be able to save it as a new sheet and update just the Jan 1, 2015 date to Jan 1, 2016 and the rest of the monthly sheets and the FI sheet will update for you.

At the first of the month if you update the accounts under the NW sheet then it should update your total net worth for you.  On the FI sheet there are two True and False fields.  In the Mad Fientist's FI sheet he/she included home equity in the calculation.  To me that didn't seem right since it isn't liquid unless your plan is to move and to use that cash then.  I put an easy way to go back and forth so not sure if that is useful to everyone?  The equity gets pulled automatically from the mortgage page each month.  If you set it to False it won't calculate the principal into the monthly savings rate.  You can make true or false the field that uses the budget for the FI calculation.  You then can adjust the budget numbers on the FI sheet and set it to true to see how the FI time differs from your real expenses or costs compared to a proposed budget.

Link to sheet:  FI Tracker V2

ToDos yet:

  • Get Indy's Dashboard back in
  • Get Indy's investment sheet back in
  • Make it easier for people to start out by replicating Jan to all the months.  (Cut and paste isn't bad now? Any feedback on this?)
  • Get a way to automatically update expenses each month from an export from programs like Mint

Edit: For updates to the sheets.

Most of the dash is back.  Just need allocation pie charts once investment sheet is in.[/list]
« Last Edit: July 23, 2015, 04:51:20 AM by wienerdog »

wienerdog

  • Bristles
  • ***
  • Posts: 364
Re: One sheet to rule them all...
« Reply #32 on: July 21, 2015, 09:34:04 PM »
I forgot one other item I would like to get added in is to track over time on how much dividend/interest income starts to come from the investments.  Of course the ones in tax advantaged accounts would be easy as you just need to update totals but ones in taxed accounts will need to have some kind of taxes taken out of them.  Maybe just tracking the totals so you know where they are at and more than likely the dividends are reinvested so they would get reflected in each accounts update at the beginning of the month.

The Fake Cheap

  • Stubble
  • **
  • Posts: 170
  • Location: Canada
    • The Fake Cheap
Re: One sheet to rule them all...
« Reply #33 on: July 22, 2015, 06:06:46 AM »
I'm going to try the first spreadsheet out on my next NW update.   Hopefully I can get it to work as I am a bit tech deficient, and don't have much (any) experience with Google Docs.  Thanks!

MoonShadow

  • Magnum Stache
  • ******
  • Posts: 2544
  • Location: Louisville, Ky.
Re: One sheet to rule them all...
« Reply #34 on: July 22, 2015, 11:23:05 PM »
Just tagging this thread, so I won't lose it.  Thanks for the great tool!

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #35 on: July 23, 2015, 01:30:08 PM »
I got another turn of Indy's spreadsheet if anyone is interested
[...]

Excellent work! I like a lot that you've done--you've cleaned up a lot of the things I had on my to-do list like simplifying the copy/paste work month to month, auto-referencing the mortgage sheet for equity, etc.

A couple of comments based off my experience with my version:

1) In the mortgage sheet, you auto-calculate the payoff balance, but I don't see any place where you take amortization into account. If I'm paying extra on the principle, doesn't that impact the principle to interest ratio on the remainder of the loan? Meaning, wouldn't I have a great proportion of principle in my next payments that is unaccounted for in the formula? This has been my experience, but perhaps I'm missing something. That's why I kept that sheet simple and didn't try to auto-calculate the balance. Instead I just check my balance (by phoning the bank) once per month and update it.
2) Nice catch on the second paycheck deduction table. Clearly I am a one income household. :)
3) I like your shortened months on the tabs.
4) Years to FI is a great stat!
5) YTD total savings is great too.

These are all really good tweaks to the sheet. I'm wondering if many of them don't belong in the master copy in some form of merge.

wienerdog

  • Bristles
  • ***
  • Posts: 364
Re: One sheet to rule them all...
« Reply #36 on: July 23, 2015, 05:42:44 PM »
Thanks for the feedback Indy.  See replies below:

1)  Yea when you mentioned that the last time I didn't calculate it right.  I didn't understand how it exactly worked and I wasn't using the current value of the loan left for each interest payment. (Not an accountant!  LOL)  I have calculated all that in now and double checked it with a financial calculator and it all calculates correct.  Took me a bit but hey I understand it now. :>) If you want to play around I added another sheet that shows the complete table instead of only showing up to the current month.  You can put different values in and that data won't be used anywhere else in the spreadsheet so don't worry about screwing anything up.  Just a calculator to see what different values do.

What I did in the principal formula is the upper right hand table calculates principal and interest for the loan values you enter.  That value stays constant over the life of the loan.  For the current principal value I take the month before's loan balance and multiply it by the interest rate and divide that by 12.  That gives me the interest for that month.  I then subtract that from the principal and interest value in the upper right table to get the principal.  If you then add additional principal it gets added to the principal and then that value is subtracted from last months loan value to get the current months loan value.

I hope that I explained that correctly and thanks for the feedback as the very first version I did was wrong.

2) I am a single income household also but figured it would be helpful for others!  I moved it to the bottom as I didn't think people would update it that much and it should make it easier to insert or delete rows in the expenses since nothing is to the right.

3) Thank you.

4 & 5)  Credit for these should go to Mad Fientist as that is all their doing.  I just incorporated it into your sheet as I thought it was helpful and a great tool towards calculating when FI will hit.  I probably should contact them to make sure they are okay with using it.  Anyone know them or do they post here?

Probably should wait for some more feedback to see if it is easy for others to use?  I am trying to make it easier so it isn't so much effort at the start but so far I think it is worth the trouble if you just take a little time and update the expenses that work for you.  Then over time the actual expense can get entered.  Thanks again for sharing the original!


ThePFBCoach

  • 5 O'Clock Shadow
  • *
  • Posts: 52
  • Location: Ohio
Re: One sheet to rule them all...
« Reply #37 on: August 06, 2015, 01:24:37 PM »
4 & 5)  Credit for these should go to Mad Fientist as that is all their doing.  I just incorporated it into your sheet as I thought it was helpful and a great tool towards calculating when FI will hit.  I probably should contact them to make sure they are okay with using it.  Anyone know them or do they post here?

He gives it away for free on his site, so I don't see him having a problem with it especially since you gave him a shout out.  I used the Mad Fientist sheet as the skeleton for my FI Tracker.  I thought my previous sheet was great until I found his site.  And seeing the sheet Indy created has given me even more ideas for things I would like to add to mine.

Keep up the great work guys!
Beer taste on a champagne budget.

mickeyj

  • 5 O'Clock Shadow
  • *
  • Posts: 98
Re: One sheet to rule them all...
« Reply #38 on: August 21, 2015, 12:27:58 AM »
Can I ask a quick question?

I've been stashing money into my discretionary fund and family emergency fund and they are saving goals in the spreadsheet.

Now I need to tap into those stash for some ad-hoc expenses. How do I account for those expenses in my monthly expenses spreadsheet?

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #39 on: August 21, 2015, 05:12:41 AM »

Can I ask a quick question?

[...] How do I account for those expenses in my monthly expenses spreadsheet?

If you are referring to the net worth asset columns, whenever I have something that I have to use my emergency fund for, I simply deduct it from that asset in the net worth row for that month.

You can also make a note in the monthly spreadsheet in the miscellaneous spending area, but if you use cash sources other than income it will be a wash to your monthly savings.

Example: this upcoming September I know I will have some medical bills that I will use my HSA savings for. I have a net worth asset column that has my HSA balance. Once I pay that bill, I'll update the row on the net worth sheet to reflect my new HSA balance.

In the September sheet I will add two line items to the miscellaneous spending area:  1) for the bill itself 2) for the incoming transfer of cash from my HSA. These two cancel each other out, and as I'm not using my monthly income to pay that bill it doesn't impact the September savings. Make sense?

mickeyj

  • 5 O'Clock Shadow
  • *
  • Posts: 98
Re: One sheet to rule them all...
« Reply #40 on: August 21, 2015, 07:09:39 PM »

Can I ask a quick question?

[...] How do I account for those expenses in my monthly expenses spreadsheet?

If you are referring to the net worth asset columns, whenever I have something that I have to use my emergency fund for, I simply deduct it from that asset in the net worth row for that month.

You can also make a note in the monthly spreadsheet in the miscellaneous spending area, but if you use cash sources other than income it will be a wash to your monthly savings.

Example: this upcoming September I know I will have some medical bills that I will use my HSA savings for. I have a net worth asset column that has my HSA balance. Once I pay that bill, I'll update the row on the net worth sheet to reflect my new HSA balance.

In the September sheet I will add two line items to the miscellaneous spending area:  1) for the bill itself 2) for the incoming transfer of cash from my HSA. These two cancel each other out, and as I'm not using my monthly income to pay that bill it doesn't impact the September savings. Make sense?

That makes sense, but how do you record incoming transfer of cash from your HSA? As income?

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #41 on: August 22, 2015, 05:30:15 AM »
I would not record it as income, as the appropriate time to do that would be when I initially earned or received the cash that I socked away in my HSA. The recording of the transfer takes place in the net worth tab--that assets value is reduced by the amount you transfer out.

mickeyj

  • 5 O'Clock Shadow
  • *
  • Posts: 98
Re: One sheet to rule them all...
« Reply #42 on: August 22, 2015, 08:45:23 PM »
Ok I probably sound like an idiot here, but how do you 'transfer into' the monthly expense sheet? I always saw the monthly expense sheet as a balance sheet (income - expenses = savings/excess spending). Does that make sense?

mickeyj

  • 5 O'Clock Shadow
  • *
  • Posts: 98
Re: One sheet to rule them all...
« Reply #43 on: August 24, 2015, 11:28:47 PM »
Here's what I did.

I took money off Net Worth, and transferred that money into Unallocated Cash to balance off the negative spending.

That allowed me to keep the additional expenditure in check, while not affecting the gross & net saving.

Let me know if this made sense

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #44 on: August 25, 2015, 06:01:34 AM »
That works just fine! I think you did right there.

freemdln

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #45 on: October 07, 2015, 11:12:11 PM »
I got another turn of Indy's spreadsheet if anyone is interested in taking it for a spin. Best if used in Google Sheets as that is all I have used.  I gave his first spreadsheet a little twist and added the Mad Fientist's FI sheet in as I found that a good tool to look at what expenses cost in time and calculate when FI should arrive.  Of course I put my little spin on some parts of each but most credit goes to Indy and Mad Fientist.  (Not sure if he/she posts here?)  http://www.madfientist.com/

I am open for feedback if anyone sees something wrong or something isn't clear.  I removed Indy's dashboard sheet and the investment sheet as those need cleaned up yet.  When I get time I will add them back in.  I am also open to feedback if you got an idea to make it better.  I tried to include plenty of different types of accounts and plenty of expenses so you can use what what you need.  Just leave them blank or if you want shorten the sheet up by deleting the rows you don't need under the expenses that should work.  (You will have to do it on each monthly sheet and the FI sheet so they all match.  You will only use the delete row feature and the formulas should all move up.  Be sure to keep the totals at the bottom of each expense bundle.)

A bunch of fields update automatically or don't appear until after the first of the month.  Your expenses won't get averaged into the FI calculation until the first of the next month but the way it calculates it should take a running average for you over the year.  At the end of the year you should be able to save it as a new sheet and update just the Jan 1, 2015 date to Jan 1, 2016 and the rest of the monthly sheets and the FI sheet will update for you.

At the first of the month if you update the accounts under the NW sheet then it should update your total net worth for you.  On the FI sheet there are two True and False fields.  In the Mad Fientist's FI sheet he/she included home equity in the calculation.  To me that didn't seem right since it isn't liquid unless your plan is to move and to use that cash then.  I put an easy way to go back and forth so not sure if that is useful to everyone?  The equity gets pulled automatically from the mortgage page each month.  If you set it to False it won't calculate the principal into the monthly savings rate.  You can make true or false the field that uses the budget for the FI calculation.  You then can adjust the budget numbers on the FI sheet and set it to true to see how the FI time differs from your real expenses or costs compared to a proposed budget.

Link to sheet:  FI Tracker V2

ToDos yet:

  • Get Indy's Dashboard back in
  • Get Indy's investment sheet back in
  • Make it easier for people to start out by replicating Jan to all the months.  (Cut and paste isn't bad now? Any feedback on this?)
  • Get a way to automatically update expenses each month from an export from programs like Mint

Edit: For updates to the sheets.

Most of the dash is back.  Just need allocation pie charts once investment sheet is in.[/list]

Sweet spreadsheet! One issue I am running into... I entered the mortgage info and when it refreshed, month 2+ has now cleared.  What did I do wrong? I am a bit new to google sheets... Thanks.

wienerdog

  • Bristles
  • ***
  • Posts: 364
Re: One sheet to rule them all...
« Reply #46 on: October 20, 2015, 10:03:34 AM »
I haven't been on much so I just saw the mortgage question.  The mortgage sheet is only designed to show the current month after the 1st of the month occurs.  The mortgage calculator shows the total payment layout.  So if you look at the example sheet you see the loan started on in May of 2012.  You can scroll down and it only shows Oct 2015.  If you check it again after Nov 1 it will then show November.  I did it that way as it sheet takes the last value in the column and adds it into the NW and a few other calculations so it updates for you each month.

Does that help?  If you can give me some more details on what you entered I can check it.

I have updated the investment page that Indy had so it now updates the value of the stock automatically.  I will see if I can get some time this week to push that page to the posted version.
« Last Edit: October 20, 2015, 10:10:56 AM by wienerdog »

freemdln

  • 5 O'Clock Shadow
  • *
  • Posts: 2
Re: One sheet to rule them all...
« Reply #47 on: October 23, 2015, 01:17:10 PM »
uh...duh! that makes sense.  Thanks Wiener... if I may be so bold... ;)
I just signed on to personal capital, against my inner voices... and this spreadsheet is a good supplement to it.


Fairviewite

  • 5 O'Clock Shadow
  • *
  • Posts: 14
Re: One sheet to rule them all...
« Reply #48 on: December 02, 2015, 12:03:38 PM »
Thanks for the tool Indy and WD!

IndyPendent

  • 5 O'Clock Shadow
  • *
  • Posts: 82
Re: One sheet to rule them all...
« Reply #49 on: January 05, 2016, 03:07:02 PM »
The blank FI tracker has been updated for 2016!

I've updated the formula on the YMOYL tab to use the 2016 months in the Net Worth tab.

I also fixed one bug in cell C13 on the YMOYL sheet.