How to annualize monthly returns?

pammyap

New Member
Joined
Aug 25, 2017
Messages
1
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!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top