Skip to main content

Posts

Showing posts with the label Google Sheets tutorials

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.

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

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

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

How to convert column index into letters with Google Apps Script

Although Google Sheets does not provide a ready-to-use function that takes a column index as an input and returns corresponding letters as output, we can still do the task by leveraging other built-in functions ADDRESS , REGEXEXTRACT , INDEX , SPLIT as shown in the post . However, in form of a formula, that solution is not applicable for scripting with Google Apps Script. In this post, we look at how to write a utility function with Google Apps Script that converts column index into corresponding letters. With the solution in the form of a formula , we don't even need to understand how column index and letters map each other. With apps script, we need to understand the mapping to come up with an algorithm. In a spreadsheet, columns are indexed alphabetically, starting from A. Obviously, the first 26 columns correspond to 26 alphabet characters, A to Z. The next 676 columns ( 26*26 ), from 27th to 702nd, are indexed with 2 letters. [AA, AB, ... AY, AZ], [BA, BB, ... BY, BZ],

How to convert column index into letters with Google Sheets

In Google Sheets, rows are indexed numerically, starting from 1, but columns are indexed alphabetically, starting from A. Hence, it is pretty straightforward to work with rows and trickier to work with columns as we need to convert between column index and corresponding letters. For example, what are the letters of column 999th in Google Sheets? In this post, we will look at how to convert a column index into its corresponding letters by using the built-in functions of Google Sheets. What are letters of the column 999th in a spreadsheet? Unfortunately, Google Sheets does not provide a ready-to-use function that takes a column index as an input and returns corresponding letters as output. However, there is a little trick of combining other available built-in functions to do the task. In Google Sheets, there is the ADDRESS function that returns the cell reference, according to the specified row index (first parameter) and column index (second parameter) in the input. For example,

How to copy data in Google Sheets as HTML table

I often need to extract some sample data in Google Sheets and present it in my blog as an HTML table. However, when copying a selected range in Google Sheets and paste it outside the Google Sheets, I only get plain text. In this post, I explain how to copy data in Google Sheets as an HTML table by writing a small Apps Script program. Concept Implementation Source Code Demo HTML table code HTML table visualization Getting Started Conclusion Concept On a spreadsheet, users select a range that they want to copy as HTML table. With the selected range, users trigger a command Copy AS HTML table . The command can be added to the toolbar, or to the contextual menu, or accessed via a keyboard shortcut. The command is executed to transform the selected range into HTML code for table. The HTML code can be added to the clipboard or can be displayed somewhere so users can copy it manually. The HTML table must consist of all displayed cells of the selected range and the widths

Compare stock portfolio to compound interest saving account

In investing, it is important to define the target that we want to achieve. Many investors like to compare their portfolios with a market index, but is outperforming the market index a good target? It is not a quantitative target to be measurable as market indexes fluctuate daily. I, personally, aim only for 10% growth a year. If at the end of the year, I see my portfolio grows 10%, I am pleased with that result no matter that S&P500 grows 50% the same year. To see if my stock portfolio grows in line with the 10% target, I choose to compare it to a saving account with the same interest. In this post, I will show you how to use a fictive saving account with daily compound interest as the target to benchmark a stock portfolio. Idea Computation Visualization Evolution of target saving account with 5% annual compound interest Evolution of target saving account with 10% annual compound interest Evolution of target saving account with 15% annual compound interest Conclusion