WHAT IS A MONTH TO DATE RETURN (MTD)?
Month-to-date (MTD) stock price return is the percentage change in the stock price from the beginning of the current month to the current date. It is calculated by taking the difference between the stock price at the current date and the stock price at the previous month’s close and dividing that amount by the stock price at the previous month’s close.
For example, IBM’s December Close was 140.89, and the current price is 140.11; we can use the two price points to calculate the MTD as follows.
MTD = ( ( Today’s Price – Last Month Close Price) / Last Month Close Price ) * 100
= (( 97.25 – 84.00 ) / 84.00) x 100
= 15.77 %
To simply our equation, we can express the above sum as,
= ( (97.25 / 84 ) -1) *100
= 15.77%
MONTH-TO-MONTH GOOGLE SHEETS FORMULA
Google Sheets date request were discussed in Month-to-Month tutorial ; read through the “GOOGLE SHEETS DATE REQUESTS” to understand the method used for parsing the last day of the month date.
In the below example, Cell B1 = ” AMZN” ( Amazon.com, Inc. )
Step 1 Request Close Price ( Last Month)
Step 1.1 Request The Last 5 Days
=QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-1)-5,EOMONTH(today(),-1)+1))
Step 1.2 Return Close Price as a Single Value
Use the QUERY() function to select and sort the “close prices” by date in descending order, limit the request to one value, and remove the column label.
=QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-1)-5,EOMONTH(today(),-1)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1LABEL Col2 ''")
Step 2 Request Today’s Current Price
=GOOGLEFINANCE(B1,"PRICE")
Step 3 Complete the MTD formula
MTD = (Step 2 / Step 1.2) -1 ) format as percentage
= (GOOGLEFINANCE(B1,"PRICE")/QUERY(GOOGLEFINANCE(B1,"CLOSE",EOMONTH(today(),-1)-5,EOMONTH(today(),-1)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1LABEL Col2 ''"))-1