Skip to main content


Showing posts with the label Google Sheets

How to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio with Google Sheets

As a long-term investor, I need to know how to evaluate the performance of my stock portfolio. A simple return on investment calculation is not a good indicator for long-term investment because it does not take into account the holding duration, and cash flows involved during that period. A return on investment of 80% after 20 years is not as impressive as it sounds after 1 year. In this post, I explain the idea of using Google Sheets to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio. Identify cash flows Choose a discount rate Apply XIRR and XNPV functions of Google Sheets Interpret internal rate of return (IRR) and net present value (NPV) Conclusion Identify cash flows As mentioned in the introduction of time value of money, present value, future value, internal rate of return, net present value , to calculate the internal rate of return (IRR) and the net present value (NPV), it is necessary to firstly identify cash

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

I have been investing in the stock market for a while. I was looking for a software tool that could help me better manage my portfolio, but, could not find one that satisfied my needs. One day, I discovered that the Google Sheets application has a built-in function called GOOGLEFINANCE which fetches current or historical prices of stocks into spreadsheets. So I thought it is totally possible to build my own personal portfolio tracker with Google Sheets. I can register my transactions in a sheet and use the pivot table, built-in functions such as GOOGLEFINANCE, and Apps Script to automate the computation for daily evolutions of my portfolio as well as the current position for each stock in my portfolio. I then drew some sort of charts within the spreadsheet to have some visual ideas of my portfolio. However, I quickly found it inconvenient to have the charts overlapped the table and to switch back and forth among sheets in the spreadsheet. That's when I came to know the existen