Last Updated on 21/05/2023 by DEUS Harvest
WHAT IS A MONTH TO DATE RETURN (MTD)?
Month-to-date (MTD) return measures the percentage change in the stock price from the beginning of the current month to the current date.
It is calculated by subtracting the previous month’s close from the current price and then dividing the sum by the previous month’s close.
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-DATE GOOGLE SHEETS FORMULA
The Month-to-Month tutorial provides detailed information about making date requests for parsing the last day of the month in Google Sheets. To gain an understanding of this topic, it is advised to refer to the “GOOGLE SHEETS MONTH-END DATE PARSING” section and read through it.
For 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