YTD ( Year-To-Date) Google Sheets Formula

Last Updated on 13/06/2023 by Ndanileka

WHAT IS THE YEAR-TO-DATE (YTD) RETURN?

YTD (Year-to-Date) refers to the cumulative performance of a stock from the beginning of the calendar year to the current date.
It provides an overview of the stock’s performance over the year, taking into account all the price fluctuations and changes that have occurred during that period.
By considering the YTD return, investors can assess the stock’s performance relative to the start of the year and gain insights into its overall trend and performance trajectory.

YEAR-TO-DATE CALCULATION

The calculation of stock price YTD (Year-to-Date) requires two price points:
The previous year’s closing price and the current price.
By using the closing price of the previous year instead of the opening price of the new year, we can avoid potential price gaps that may occur between the closing and opening prices.

To illustrate the calculation, let’s consider an example where a stock closed the year trading at 24.44 and now it’s trading at 20.49, we would calculate YTD as per the example below.

YTD = ( ( Current Price – Last Year Close Price) / Last Year Close Price ) * 100
= ( (20.49 – 24.44) / 24.44) *100
= – 16.16%

This calculation shows that the stock has experienced a negative YTD return of 16.16% since the start of the year.

Candle stick chart illustrating Year-to-date calculation
YTD calculation chart illustration



RETRIEVING THE PREVIOUS YEAR’S END DATE

In this section, we’ll go over the process of getting the date of the previous year-end. We’ll use this date to get the closing price of the previous year and build our Year-to-Date (YTD) formula.

To achieve this, we will utilize the following functions: DATE(), YEAR(), and TODAY().

The DATE() function takes three arguments – year, month, and day – and converts them into a date format. The YEAR() function returns the year specified by a given date, while the TODAY() function retrieves the current date.

To construct the formula for obtaining the previous year’s date, we can use the following expression:

=DATE(YEAR(TODAY()) - 1, 12, 31)

This formula utilizes the DATE() function to create a date by combining the previous year (obtained using YEAR(TODAY()) – 1), the month of December (represented by 12), and the last day of the month (31st).


YEAR-TO-DATE GOOGLE SHEETS FORMULA

In our example, Cell B1 = “COUR ” (Coursera Inc)

Step 1. Request Current Price.

=GOOGLEFINANCE(B1)
Current price google sheets formula
Current Price Formula

Step 2. Request Last Year’s Close Price

Step 2.1
Request close prices from the previous 5 Days.

In scenarios where the last day of the year falls on a non-trading day, it is necessary to incorporate buffer days when requesting close prices. This can be achieved by subtracting “- 5” from the start_date and adding “+1” to the end_date argument.

The purpose of this adjustment is to account for the behavior of the GOOGLEFINANCE() function, which retrieves data up until the day before the specified end_date. By subtracting “- 5” from the year-end date formula, we extend the start_date to include additional days prior to the year-end. Additionally, by adding “+1” to the end_date, we ensure that the desired end_date is included in the data retrieval.

=QUERY(GOOGLEFINANCE(B1,"Close",DATE(YEAR(TODAY())-1,12,31)-5,DATE(YEAR(TODAY())-1,12,31)+1))
Previous Year Close Price Formula, With Buffer Days
Previous Year Close Price Formula, With Buffer Days

Step 2.2
Request the last traded price as a single value.

To retrieve the last traded price as a single value and remove the column labels, we can utilize the Query() function. By selecting Col2 (Close Prices) and ordering the data by Col1 (Dates) in descending order, we ensure that the most recent price is returned. To limit our request to only one value, we specify a limit of 1. Additionally, we set the header argument to 0, which removes the column labels from the output:

=QUERY(GOOGLEFINANCE(B1,"Close",DATE(YEAR(TODAY())-1,12,31)-5,DATE(YEAR(TODAY())-1,12,31)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1",0)
Previous Year Close Price Formula
Previous Year Close Price Formula

Step 3. Complete YTD Formula
To simply our calculation, we will express as YTD equation as follows :

YTD = ( Current Price / Last Year Close Price – 1) *100
YTD = ( Step 1 / Step 2.2 -1) *100

=TO_PERCENT(GOOGLEFINANCE(B1)/QUERY(GOOGLEFINANCE(B1,"Close",DATE(YEAR(TODAY())-1,12,31)-5,DATE(YEAR(TODAY())-1,12,31)+1),"SELECT Col2 ORDER BY Col1 DESC LIMIT 1",0)-1)

Year-to-date google sheets formula
Year-to-date Formula

YEAR-TO-DATE PRICE ANALYSIS

YTD (Year-to-Date) is a valuable tool for comparing stocks within the same sector, analyzing the performance of underlying holdings in an index, or tracking the overall market. It allows us to filter and identify the best and worst-performing stocks based on their performance since the beginning of the calendar year.

The bar chart below provides a visual representation of how YTD can be utilized as a stock monitor to assess the general direction of the market. By plotting the YTD returns of various stocks or indices, we can quickly identify trends and gain insights into the relative performance of different investments. This helps investors make informed decisions and allocate their resources effectively.

S&P 100 YTD Bar Chart in Google Sheets
S&P 100 YTD Bar Chart

4 comments

  1. I get this error when plugging your formula into Google Sheets “ERROR: Function Quote parameter 3 expects number values. But 31/12/21 is a text and cannot be coerced to a number”. Any suggested fix?

    1. I think it has to do with google sheets locale settings.
      1) Try to change the date format “31/12/21 -(DD,MM,YYYY) ” to your locale format. For example, USA would be MM/DD/YYYY.
      To view your locale on the spreadsheet go to File > Settings, General.

      2) I have my locale set to United Kingdom, changing yours to the same should make the formula work.

      1. Changing to UK did the trick, however the YTD returns are incorrect. Using VOE (an ETF) as an example, the YTD returns differ from those shown on the Vanguard website. Perhaps dividend payments have something to do with it. thanks again

  2. It could be dividend payments, different data source or calculation method.
    Also Yahoo Finance has the YTD at – 2.91%

    Quick calculation, data from Google Finance:
    Last year’s close 150.33
    Last close 145.11

    YTD = ((145.11- 150.33) / 150.33 ) x 100 = – 3.47%

Leave a Reply