Google Spreadsheet for tracking progress to financial freedom

Guhan Ramanan has developed a Google Spreadsheet to track our progress to financial freedom (retirement and four other financial goals). He has generously agreed to share it with us. Do ignore the opening sentence, though 🙂

-=-=-=-=-=-=-=-=-=-

I always knew Pattu was super-smart – I recently learnt he is also very brave – he invited me to write again for his blog! 🙂

After my last post about my journey to financial freedom: A Force Awakens!, I began to think about how I could track where really was I was w.r.t to my financial goals. I mean, once you’ve gotten your investment plan in place, and promised yourself not to check markets as often as your email (yeah, right!) – how do you really know where you are?

So I decided to build a dashboard that links my financial freedom goals to my assets in a simple way and lets me keep track of it online. Yawn, one more financial dashboard? Spare me, you say? Oh well, let me show you what it looks like.

financial-freedom-dashboard-1

Interested?

As you can see, it’s very simple and direct– it tells you where you are % wise on your journey; also shows you each of your goals are doing on your path to financial freedom.

Here are 3 more reasons why I like it:

  1. Allows me to measure progress toward financial freedom as a visual function of goal achievement rather than just numbers.
  2. A market crash/bull run that changes asset value, can now be seen in correct perspective – translated into how it affects goals, rather than as an event by itself.
  3. Helps me see “big” picture clearly as that is what one needs to be focusing on to get to financial freedom

But wait, there is more! You can allocate every financial asset you own to goals in simple line by line format! Now, read that again – yes, you can do % asset allocation to each goal. For ex. You can say all the stock I own in Asian Paints, will contribute to Goal 1.

I hope I have your attention now! J

There are 4 main tabs:

financial-freedom-dashboard-2

#1 Dashboard:

  1. Fill in the current values of your goals, inflation %, and how years away. This will calculate the target corpus for the goal.

For retirement, use the Low Stress Retirement Calculator

financial-freedom-dashboard-3

The Investment Mapping Tab, is where the actual asset allocation happens. You can tie each Financial asset to a particular goal in any % allocation you choose.

In the example below: I’ve allocated 50% of Asian Paints to Retirement, 20% to Goal 1, 20% to Goal 2 and 10% to Goal 4 (nothing to Goal 3). Colgate-Palmolive is allocated 20% evenly across all goals.

financial-freedom-dashboard-4

Likewise for Mutual Funds + any other assets I own.

So, how do I enter what all I own?

Varies by asset class – I’ve explained for Equity and MF – which is where you should be putting your money if you are reading this blog! J For others, I’m sure you can extrapolate!

Equity:

This is easy. You just have to get the Stock code from Google Finance for the stock you are interested in and enter the quantity you own.

financial-freedom-dashboard-6

How to find your stock’s code in Google?

  1. Go to google.com/finance.
  2. Enter the stock name – wait for the drop down (see below)

financial-freedom-dashboard-73. Copy the stock code (in yellow) for BSE and if you want NSE prices, copy the first value (ASIANPAINT)

4. Paste/Type value in Symbol column of the Investment data\

5. The Instrument column will auto-populate and should show you the name of the stock (sanity check to make sure your stock code is right)

Mutual Funds

Finding your MF’s Scheme Code

1, 2: Go into the Current_NAV tab, press Ctrl+F and find your Fund – Look closely and pick the exact one – Direct, Dividend, Growth etc.

financial-freedom-dashboard-8

3: And when you get the scheme code, copy it into Column C in the Investment Mapping Tab.

financial-freedom-dashboard-9

Getting your NAV:

All you have to do is click on MF Tools > Update NAV to get latest NAV values. It will auto update the last NAV known (from AMFI)

financial-freedom-dashboard-10

Other Assets

Real Estate: Things like Real Estate, Gold etc don’t really have an online place to get value reliably. So you can fill in investment value and the current value by yourself.

FDs: Most FDs will show you maturity value, so pick that up from the bank and put that in Current Value. If you want to get really fancy, you can write up a compound interest formula based on start, end dates, interest % and compounding frequency. It would have meant 3 more columns in my investment sheet – so didn’t do it!

Putting it all together in a chart to create the graph we are looking for!

financial-freedom-dashboard-1

While this dashboard tells you where you are, it does not tell you 2 things:

  1. It is not a returns calculator – when you want to see where you are, what % returns you got doesn’t really matter. When you see the car odometer to see distance travelled, avg. speed doesn’t really matter! Physics example, since its Pattu’s blog! J
  2. It does not factor the “time-to-goal” angle to goals in its visual representation. Frankly, it was getting too complicated to build! J

In simple terms, this dashboard helps you understand if your favourite MF falls 10%, and it is allocated 25% to a goal, then it has only a 2.5% impact on that goal. And if you have 5 goals in life, it basically means 0.5% (half-a-percent!) of your path to financial freedom is impacted.

Is 0.5% drop in your path to goal, worth getting worried and losing sleep over? Instead, if you can focus on how you get that 0.5 % back by investing more, or elsewhere, it will put you in a much better place!

Easier still, re-allocate to your goals as you need them, so it need not really affect a goal you care more for! It is a spreadsheet after all! And your spreadsheet at that How is that for perspective?

 

-=-=-=-=-=-=-=-=-=-

Do thank me in thanking Guhan for sharing this spreadsheet with us. If you are comfortable with Google sheets, feel free to add features to this. I shall be happy to publish your work for the benefit of the DIY community. Sharing is learning!

Pranav Date has added as asset allocation graph to the above spreadsheet.

Get a copy of the Google spreadsheet portfolio tracker V 4 (Dec 25 2017)

Want to conduct a sales-free "basics of money management" session in your office?
I conduct free seminars to employees or societies. Only the very basics and getting-started steps are discussed (no scary math):For example: How to define financial goals, how to save tax with a clear goal in mind; How to use a credit card for maximum benefit; When to buy a house; How to start investing; how to invest for and after retirement etc. depending on the audience. If you are interested, you can contact me: freefincal [at] Gmail [dot] com. You need to only cover my travel fare for the session.

Connect with us on social media


Do check out my books


You Can Be Rich Too with Goal-Based InvestingYou can be rich 243x300 - Google Spreadsheet for tracking progress to financial freedom

My first book is now available at a 35% discount for Rs. 258. It comes with nine online calculators. Get it now.  The Kindle edition is only Rs. 199.

Gamechanger: Forget Startups, Join Corporate & Still Live the Rich Life You Want

Cover pink - Google Spreadsheet for tracking progress to financial freedom My second book is now only Rs 199 (Kindle Rs. 99) Get it or gift it to a young earner

The ultimate guide to travel by Pranav Surya

Travel-Training-Kit-Cover This is a deep dive analysis into vacation planning, finding cheap flights, budget accommodation, what to do when travelling, how travelling slowly is better financially and psychologically with links to the web pages and hand-holding at every step.  Get the pdf for ₹199 (instant download)

Create a "from start to finish" financial plan with this free robo advisory software template


Free Apps for your Android Phone

All calculators from our book, “You can be Rich Too” are now available on Google Play!
Install Financial Freedom App! (Google Play Store)
Install Freefincal Retirement Planner App! (Google Play Store)
Find out if you have enough to say "FU" to your employer (Google Play Store)

About Freefincal

Freefincal has open-source, comprehensive Excel spreadsheets, tools, analysis and unbiased, conflict of interest-free commentary on different aspects of personal finance and investing. If you find the content useful, please consider supporting us by (1) sharing our articles and (2) disabling ad-blockers for our site if you are using one. We do not accept sponsored posts, links or guest posts request from content writers and agencies.

Blog Comment Policy

Your thoughts are vital to the health of this blog and are the driving force behind the analysis and calculators that you see here. We welcome criticism and differing opinions. I will do my very best to respond to all comments asap. Please do not include hyperlinks or email ids in the comment body. Such comments will be moderated and I reserve the right to delete the entire comment or remove the links before approving them.

19 thoughts on “Google Spreadsheet for tracking progress to financial freedom

  1. Thank you Pattu for sharing and Guhan for creating a FINformative dashboard. This will help track our financial progress from wherever we want to glance at it or review, when time permits…

  2. Thanks for sharing the sheet, I am trying to map the MF part, but the MF Tools > Update NAV , Is not working, unable to get the auto update of instrument name and current NAV, If any one already tried out, please share it,

    1. Hi, when you run it for the first time, Google will ask you for permissions, that you need to grant.

      Also, in the Current_NAV tab, if you can see NAV date as last working day -1, that means it works!

      Despite this if it still won’t work, could you let me know the error you get?

  3. Thanks for the sheet.

    I thought that Fetching data for Stocks and MF to Google Sheets was tough (which actually is). Now that I have access to that data, can improve the sheet as and when time permits.

    For goals which are very long in future, the percentage shown in Dashboard is too small, the goal is inflation indexed while the current value is not. So I think that it need to be fixed in some way.

    Anyways, awesome sheet to work on.

    Regards,
    Kishan

    1. Why should current value be inflation indexed? The target is inflation indexed and that is the actual amount one should have after that many years. So naturally the % completed will be small!

      1. Pattu Sir,

        I agree with you.

        Even I also don’t know what I want. 😀 Disappointed with the zero point something percentage which I have. 😀
        So, I modified the sheet a little to make it inflation adjusted. 😀
        At least it’s 1. something now.

        My thinking:
        If I am investing for 30 year goal, I should have 100/30 = approx 3% complete after 1 year.

        Pattu Sir,

        Can you make a Goal cum Portfolio Tracker (which is in Excel) in Google sheet? I guess it would be too much of an effort though.

        Regards,
        Kishan

  4. Thanks, was looking for such sheets, if there is any easy way to input UNITS of MF SIP every month would be awesome.

  5. Awesome information.. i am super exited so that i can do away with my excel sheet.. Thanks a ton Pattu and Guhan..

  6. Awesome work Guhan and Pattu for sharing on this site.
    Works as advertised (!) and really gives a visual angle to the net worth part! Loving it and will be using it daily!

  7. Wonderful sheet. As usual, freefincal helps in our planning so much.

    I have my own customized sheet and I keep improving it, as and when I get tips from AIFW or freefincal. This time I added the percentage met.

    My two cents to improvise this: I found that for percentage (of target corpus) met does not consider the current available value (for the goal) to be remaining invested until the target is met.

    Example, current amount = x, target amount after nyrs is y, Interest Rate expected kept at the least say 6%, then the current amount remains invested even if there is no future investment and hence apply simple ROI on x, that results in an amount after n years. Only this amount has to be divided by y to check the remaining investment needed for financial independence.

    My financial independece goal table has the following columns right now.

    SI NO
    Goal
    No of years
    Where the amount is invested
    Interest Rate
    Required Amount as on date
    Amount per month to achieve my goal
    Current Amount
    Inflation
    Target Amount
    In Short of/higher amount for target, assuming the amount remains invested
    %Met (the way I explained above)

    I am sure that it will give a better feel about my investments and a better real picture of future with my current investements.

  8. Hi Guhan,

    Some of my near term goals are mapped to RDs, how can we capture this as part of the sheet ?

    Really appreciate the awesome work, will help me a lot.

    Rgds,
    Som

  9. Hi

    Could not fetch the number of units for more than six different Mutual Funds using (=SUMIFS(mfInvestmentUnitRange,mfInvestmentNameRange,B10)). Formula in C10 cell in Investment,Mapping sheet is not able to fetch sum of units from MF Investment sheet if we add more than 6 unique Mutual Funds.

    Can you please help.

    THanks,
    Ramkumar.

Your feedback is valuable. Do let us know what you think about the article and help us improve