Date	USD (PM)
11/19/20	1857.35
11/18/20	1876.1
11/17/20	1889.05
11/16/20	1885.6
11/13/20	1890.9
11/12/20	1874.85
11/11/20	1860.95
11/10/20	1878.7
11/9/20	1867.3
11/6/20	1940.8
11/5/20	1938.45
11/4/20	1900.15
11/3/20	1908.3
11/2/20	1889.9
10/30/20	1881.85
10/29/20	1870.3
10/28/20	1869.95
10/27/20	1905.7
10/26/20	1898.45
10/23/20	1903.65
10/22/20	1900.95
10/21/20	1924.15
10/20/20	1898.4
10/19/20	1905.6
10/16/20	1905.05
10/15/20	1891.9
10/14/20	1910.05
10/13/20	1891.3
10/12/20	1925.5
10/9/20	1923.25
10/8/20	1887.45
10/7/20	1884.5
10/6/20	1913.4
10/5/20	1909.6
10/2/20	1903.05
10/1/20	1902
9/30/20	1886.9
9/29/20	1883.95
9/28/20	1864.3
9/25/20	1859.7
9/24/20	1861.75
9/23/20	1873.4
9/22/20	1906
9/21/20	1909.35
9/18/20	1950.85
9/17/20	1936.25
9/16/20	1961.8
9/15/20	1949.35
9/14/20	1958.7
9/11/20	1947.4
9/10/20	1966.25
9/9/20	1947.2
9/8/20	1910.95
9/7/20	1928.45
9/4/20	1926.3
9/3/20	1940.45
9/2/20	1947.05
9/1/20	1972.35

This dataset contains gold spot prices recorded at the daily frequency. You will need to convert this into monthly returns in Excel. a. To do this, use IF function to build a new column that takes the value of 1 if the date is the last day of the month and 0 otherwise. For example, 2020/9/30 should be denoted by 1, and 2020/9/29 should be 0. (Note that figuring out how to write this formula using IF is part of the challenge. You will find month() function very helpful in writing this formula.)

b. Then use the Filter function under Data Tab to only keep the month-end dates.

c. Now you are ready to calculate the monthly returns of gold using the HPR formula. For example, the monthly return in Sep 2020 should be based on the prices at the end of Aug 2020 and Sep 2020.