How to annualize monthly returns?

[pammyap]

I have a series of prices from Jan 2012 to Jul 2017 for the iShares Short Treasury Bond ETF.

I calculated monthly returns using (Month 2 Price / Month 1 Price)-1.

Then, I used PRODUCT(1+RANGE)^(MIN(1,12/COUNT(RANGE)))-1 to calculate the 1 year and 5 years annualized return.

Are the above steps right? The output does not match what is reflected on the ETF's website.

For example, I am getting a 5-year annualized returns of 0.02% as of 30 June 2017, when the website states 0.12% for the same period. Help please!

 

[joeu2004]

I have a series of prices from Jan 2012 to Jul 2017 for the iShares Short Treasury Bond ETF. I calculated monthly returns using (Month 2 Price / Month 1 Price)-1. Then, I used
PRODUCT(1+RANGE)^(MIN(1,12/COUNT(RANGE)))-1
to calculate the 1 year and 5 years annualized return. Are the above steps right?

Yes, assuming that you array-enter the PRODUCT formula (press ctrl+shift-Enter instead of just Enter); and assuming that COUNT(RANGE) is 12 and 60 respectively, which means that the last ratios in the ranges are Month13/Month12 and Month61/Month60 respectively.

But if your Excel file has the raw data (monthly prices), you could simply calculate Month13/Month1 - 1 for 1yr and Month61/Month1 - 1 for 5yr.

The output does not match what is reflected on the ETF's website.

One likely difference is: the website calculates total return, whereas you are calculating daily price returns, I presume. The difference is: total return incorporates dividends as well as NAV.

 

[joeu2004]

Errata....

But if your Excel file has the raw data (monthly prices), you could simply calculate Month13/Month1 - 1 for 1yr and Month61/Month1 - 1 for 5yr.

For the annualized 5yr return, the second expression should be (Month61/Month1)^(1/5) - 1