Skip to main content

Posts

Create a dividend income tracker with Google Sheets by simply using pivot tables

As my investment strategy is to buy stocks that pay regular and stable dividends during a long-term period, I need to monitor my dividends income by stocks, by months, and by years, so that I can answer quickly and exactly the following questions: How much dividend did I receive on a given month and a given year? How much dividend did I receive for a given stock in a given year? Have a given stock's annual dividend per share kept increasing gradually over years? Have a given stock's annual dividend yield been stable over years? In this post, I explain how to create a dividend tracker with Google Sheets. Manage stock transactions with Google Sheets Create dividend tracker with Google Sheets Track annual dividend amount of stocks Track dividend amount by month and by year Track annual dividend per share of stocks Track annual dividend yield of stocks Demo Conclusion References Manage stock transactions with Google Sheets I use a spreadsheet on Goo
Post a Comment
Read more
Recent posts

Demo how to use XIRR and XNPV functions of Google Sheets to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio

I have explained the idea of using Google Sheets functions to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio . The process consists mainly of three steps: Identify cash flows from transactions managed in a Google Sheets spreadsheet Choose a discount rate based on personal preferences Apply XIRR and XNPV functions of Google Sheets In this post, I demonstrate step-by-step how to apply this process to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio at 3 levels.
Post a Comment
Read more

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 Series: how to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio in Google Sheets
Post a Comment
Read more

Create dividend income tracker with Google Data Studio

With transactions registered, it is easy to create a dividend income tracker with Google Sheets. However, a dividend income tracker in Google Sheets is not interactive. Instead of having different pivot tables and switching forth and back among them, I can create an interactive dividend income tracker with a single-page report on Google Data Studio. In this post, I explain how to create a dividend income tracker with Google Data Studio. Manage stock transactions with Google Sheets Create a report in Google Data Studio and connect to Transactions data sources in the spreadsheet Use Time series chart to track annual dividend amount Use Pie chart to visualize the contribution of dividend among stocks Use Pivot table to track annual dividend per share and annual dividend yield of stocks Use Pivot table to track dividend amount by month and by year Demo Conclusion References
Post a Comment
Read more

Time value of money, Present Value (PV), Future Value (FV), Net Present Value (NPV), Internal Rate of Return (IRR)

Why do I use my current money to invest in the stock market? Because I expect to have more money in the future. Why do I need more money in the future than now? Because of many reasons, the same amount of money will have less purchasing power than today. Therefore my investment needs to generate more money than today to protect my purchasing power in the future. That is the main concept of the time value of money where one dollar today is worth more than one dollar in the future. Present Value (PV), Future Value (FV) Net Present Value (NPV) Discount rate Internal Rate of Return (IRR) Conclusion Series: how to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio in Google Sheets Present Value (PV), Future Value (FV) At 10% annual growth rate, an investment of 1000$ will be worth 1000 * 110% = 1100$ after 1 year, and will be worth 1000 * 110% * 110% = 1210$ after 2 years. The future value of 1000$ after 2 years at the
Post a Comment
Read more

Stock Correlation Analysis With Google Sheets

Correlation is a statistical relationship that measures how related the movement of one variable is compared to another variable. For example, stock prices fluctuate over time and are correlated accordingly or inversely to one another. Understanding stock correlation and being able to perform analysis are very helpful in managing a stock portfolio investment. In this post, we will look at how to perform stock correlation analysis with Google Sheets. Understanding correlation and its applications in stock investing Stock correlation analysis with Google Sheets Getting started User guide Conclusion Understanding correlation and its applications in stock investing The most familiar correlation measure is the Pearson product-moment correlation coefficient . The strength of the relationship between two variables is expressed numerically between -1 and 1. For example: Two stocks are positively correlated when their prices always go up or go down together. Their coefficient
Post a Comment
Read more