Google Sheet Personal Finance Manager

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
jburke
Posts: 91
Joined: Thu Sep 04, 2008 10:47 am

Google Sheet Personal Finance Manager

Post by jburke » Fri Jul 27, 2018 12:22 pm

I created a personal finance workbook that I've decided to share with you. If you have Excel or Google Sheet experience, you can customize it to fit your personal specifications. All I ask is for you honest feedback and suggestions.

https://docs.google.com/spreadsheets/d/ ... sp=sharing

Some cool features:
I can see all my investments on one sheet and see if I'm on target with my asset allocation.
Stock and mutual funds are updated automatically.
I can specify an asset allocation that's aggressive in my younger years and conservative later on.
A social security calculator estimates payments based on past years and future income.
I can organize my expenses by category and see how I am spending long term.
FIRE Calculator built in. I can perform sensitivity analysis based on different growth and dividend inputs, inflation rates, and important dates such as retirement, social security and pension start dates, and date of death.
Last edited by jburke on Wed Aug 08, 2018 3:50 pm, edited 5 times in total.

User avatar
BolderBoy
Posts: 4050
Joined: Wed Apr 07, 2010 12:16 pm
Location: Colorado

Re: This personal finance workbook is great!

Post by BolderBoy » Fri Jul 27, 2018 3:53 pm

Gracious! Congratulations. You really have put a lot of work into this. Thank you.
"Never underestimate one's capacity to overestimate one's abilities" - The Dunning-Kruger Effect

User avatar
Sandtrap
Posts: 5324
Joined: Sat Nov 26, 2016 6:32 pm
Location: Hawaii😀 Northern AZ.😳 Retired.

Re: This personal finance workbook is great!

Post by Sandtrap » Fri Jul 27, 2018 4:27 pm

Excellent work.
Thanks for sharing it.
Aloha
j

TravelGeek
Posts: 2293
Joined: Sat Oct 25, 2014 3:23 pm

Re: This personal finance workbook is great!

Post by TravelGeek » Fri Jul 27, 2018 5:17 pm

Thank you for sharing this. I will take a more in depth look at it later. Looks like it could be very useful to me either as is or as inspiration for some features in existing spreadsheets I ave.

I will send you a PM as well.

AerialP
Posts: 160
Joined: Thu Apr 12, 2007 12:34 pm
Location: Central Kentucky

Re: This personal finance workbook is great!

Post by AerialP » Fri Jul 27, 2018 6:11 pm

First glance this looks excellent!! Could very well moving forward supercede a couple of my own spreadsheets, ongoing from the past 10 years. And we look to be about the same age so it's even more plug-and-play for me!
Thank you very much.

Miriam2
Posts: 2290
Joined: Fri Nov 14, 2014 11:51 am

Re: This personal finance workbook is great!

Post by Miriam2 » Fri Jul 27, 2018 7:36 pm

Impressive! Why do you need the number of shares? Is the spread sheet calculating your value based on price you put in & number of shares? Are you calculating this daily?

al_harrington
Posts: 4
Joined: Wed Jan 24, 2018 10:17 am

Re: This personal finance workbook is great!

Post by al_harrington » Sat Jul 28, 2018 12:06 pm

Not sure what kind of feedback you're looking for but I'll start by saying it's quite impressive. There's much of it I've not yet been able to unpack, but for someone else to use the workbook, it's not readily apparent how/where one would enter their differing asset types. In handling RE, you hard-code the references for value calculation which results in a somewhat rigid structure whereas with equities and bonds you leverage the sub-category. Doing the latter consistently might make the worksheet a bit more accessible and flexible so that all assets could be handled in the same way. The use of a named range or looking for the sub-category within a range of cells rather than hard coding the sub-category in your value calculation might provide even better flexibility.

For example, instead of "=sumif(E$4:E$13,"US All",H$4:H$14)" in H17, why not use "=sumif(E$4:E$13,A17,H$4:H$14)" and change the value in A17 to match the sub-category (or maybe a concatenation of category and sub-category)?

These kinds of workbooks can quickly loose coherency once one adds more and more features. You've done a good job with organizing the various data domains. I have my own that, while it serves my purposes, would like seem like a confusing jumble of data spread across multiple worksheets.

bgyt
Posts: 64
Joined: Sat Jun 16, 2018 8:20 am

Re: This personal finance workbook is great!

Post by bgyt » Sat Jul 28, 2018 3:13 pm

how do you call or fetch prices from google finance. I see the formula "=googlefinance(F5,"name")" but what else needs to be set to activate this function. It error's on me when I try it in excel.

IsItWinterYet
Posts: 6
Joined: Wed Mar 21, 2018 9:42 am

Re: This personal finance workbook is great!

Post by IsItWinterYet » Sat Jul 28, 2018 4:27 pm

One thing I like including on my purchase transaction log is which payment method I use (a specific credit card, Venmo, check, cash, etc.). This helps me filter out transactions when I check my credit card statements monthly. I also keep a Yes/No column to calculate bonus category spending.

User avatar
whodidntante
Posts: 4051
Joined: Thu Jan 21, 2016 11:11 pm

Re: This personal finance workbook is great!

Post by whodidntante » Sun Jul 29, 2018 3:15 pm

Cool.

Rosa
Posts: 23
Joined: Mon May 07, 2018 9:58 am

Re: This personal finance workbook is great!

Post by Rosa » Sun Jul 29, 2018 7:29 pm

Oh jburke, it's the greatest table!!! I wish I had this facility to make a similar table like that. THANK YOU SO MUCH for giving it to us. Very generous of you. Because I only have CDs, MMs, Savings and an IRA though, I have them spread all over in banks and credit unions and am just listing them in only one column on one of my email pages. However, if I knew how to do your table I'd certainly would want to do it that way. :)

User avatar
dwickenh
Posts: 1347
Joined: Sun Jan 04, 2015 9:45 pm
Location: Illinois

Re: This personal finance workbook is great!

Post by dwickenh » Sun Jul 29, 2018 8:35 pm

thanks jburke,

Just starting playing with this and it appears to be very comprehensive.

Best to you,

Dan
The market is the most efficient mechanism anywhere in the world for transferring wealth from impatient people to patient people.” | — Warren Buffett

jburke
Posts: 91
Joined: Thu Sep 04, 2008 10:47 am

Re: This personal finance workbook is great!

Post by jburke » Mon Jul 30, 2018 2:34 am

New Version Coming Soon.
Last edited by jburke on Wed Aug 08, 2018 2:50 pm, edited 2 times in total.

jburke
Posts: 91
Joined: Thu Sep 04, 2008 10:47 am

Re: This personal finance workbook is great!

Post by jburke » Wed Aug 01, 2018 10:34 am

New Version Coming Soon.
Last edited by jburke on Wed Aug 08, 2018 2:49 pm, edited 1 time in total.

User avatar
munemaker
Posts: 3464
Joined: Sat Jan 18, 2014 6:14 pm

Re: Google Sheet Personal Finance Manager

Post by munemaker » Wed Aug 01, 2018 1:54 pm

Thanks - I just took a glance at it and...very impressive.

I have my own SS tailored to my own needs. I am going to either adapt yours to my needs, or steal some ideas from your and incorporate into mine.

Thanks for posting!

jburke
Posts: 91
Joined: Thu Sep 04, 2008 10:47 am

Re: Google Sheet Personal Finance Manager

Post by jburke » Wed Aug 08, 2018 2:54 pm

New Version:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

I've added the following capabilities:

1. You can differentiate between pre-tax and after tax retirement accounts
2. You can reinvest taxable dividends for cost-basis inclusion
3. Social Security Calculator will estimate your social security retirement benefit if you were born after 1959. Otherwise, you'll have to customize it.
3. User input cells are shaded blue for ease of use

To edit, make a copy of the file.
Last edited by jburke on Wed Aug 08, 2018 3:16 pm, edited 1 time in total.

Phika
Posts: 21
Joined: Mon Mar 24, 2014 11:47 am

Re: This personal finance workbook is great!

Post by Phika » Wed Aug 08, 2018 3:16 pm

bgyt wrote:
Sat Jul 28, 2018 3:13 pm
how do you call or fetch prices from google finance. I see the formula "=googlefinance(F5,"name")" but what else needs to be set to activate this function. It error's on me when I try it in excel.
To fetch a price from google finance, the formula is

=GOOGLEFINANCE("tickerprice") so if I want VTSAX, it would be =GOOGLEFINANCE("VTSAX")

Loik098
Posts: 644
Joined: Mon May 30, 2016 9:29 pm

Re: Google Sheet Personal Finance Manager

Post by Loik098 » Wed Aug 08, 2018 6:38 pm

jburke wrote:
Fri Jul 27, 2018 12:22 pm
FIRE Calculator built in. I can perform sensitivity analysis based on different growth and dividend inputs, inflation rates, and important dates such as retirement, social security and pension start dates, and date of death.
Thanks for all of you work on this sheet. Really appreciate it. Going to steal the work above and add to my own sheet....once I figure it out :-)

rgs92
Posts: 2167
Joined: Mon Mar 02, 2009 8:00 pm

Re: Google Sheet Personal Finance Manager

Post by rgs92 » Wed Aug 08, 2018 6:45 pm

Thanks for this.

jburke
Posts: 91
Joined: Thu Sep 04, 2008 10:47 am

Re: Google Sheet Personal Finance Manager

Post by jburke » Wed Aug 08, 2018 6:54 pm

Steal away! And if you have anything that you think could improve my sheet, please share.

User avatar
Bradley37
Posts: 26
Joined: Tue Oct 03, 2017 3:30 pm

Re: Google Sheet Personal Finance Manager

Post by Bradley37 » Thu Aug 09, 2018 5:25 pm

Thanks a lot for sharing. :sharebeer

Post Reply