Different in calculating annualized monthly return

Hi

I calculated the annualised return as tab "UU Return" (row 1484).
In the tab, I get the data from the respective month ie from 311016 (column H) and I insert it in the column E.
While I am thinking the source data is correct, however, the column D in tab 311016 (and also for the other monthly tab) showed a different average figure.
For example, in the month of October, the annualised return is 2.87% (tab UU return, row 1484), however the average return showed 3.21 (tab 311016, row 40).
I think the number shall be at least equal or will have a very minimal difference i.e. +-0.10
I am unable to explain the difference.

Please help me.

Thank you in advance for your help

Originally Posted by wanmuhd

For example, in the month of October, the annualised return is 2.87% (tab UU return, row 1484), however the average return showed 3.21 (tab 311016, row 40).
I think the number shall be at least equal or will have a very minimal difference i.e. +-0.10
I am unable to explain the difference.

For brevity, I will refer to the 'UU Return' worksheet as simply UU.

You are asking a math question. Off-hand, I do not see any error in the Excel formulas. But mathematically, you are making many mistakes, to wit:

1. You are comparing a simple annualized rate with a compounded annualized rate.
2. You are comparing an arithmetic average daily rate with a geometric average rate.
3. You are comparing one daily rate (e.g. C9/B9) with a different daily rate (e.g. H9/H8-1) in the 311016 worksheet.

Although all 3 mistakes can be significant, depending on the magnitude of the daily rates, #3 is the primary culprit in the difference that you see, to wit: about 3.21% in 311016!D40 v. about 2.87% in UU!F1484.

The formula in UU!F1484 is effectively =(1+F1483)^(365/31)-1, where F1483 is a monthly rate. That is equivalent to (1+F1483)^(1/31)^365-1, where (1+F1483)^(1/31) is the geometric average daily rate.

F1483 is effectively calculated by the array-entered formula {=PRODUCT(1+E1439:E1469/E1438:E1468)-1}, where E1440:E1469/E1439:E1468 is the same as H9:H38/H8:H37 in 311016, and E1439/E1438 is the same as 311016!H8/300916!H7 (Oct1/Sep30).

H9/H8-1 is a daily rate (ratio), and (H9/H8)^365-1 is the compounded annualized rate.

In contrast, 311016!D40 is an arithmetic average, effectively calculated by the array-entered formula {=365*AVERAGE(C8:C38/B8:B38)}.

C9/B9 is a daily rate (ratio), and 365*C9/B9 is the simple annualized rate.

You also multiply the annualized rate by 100, which is ill-advised. Just format it as Percentage.

In effect, you seem to expect 365*C9/B9 to be comparable to (H9/H8)^365-1, for example. But 365*C9/B9 is about 3.23%, whereas (H9/H8)^365-1 is about 2.89%.

That is source of the significant difference between 311016!D40 and UU!F1484.

Off-hand, I see no relationship between the daily rate underlying column D, where D[i] = C[i]/B[i], and the daily ratio H[i]/H[i-1]-1 underlying the amounts in column H, where H[i] = H[0] + Sigma(C[k]/B[k], k=1,...,i), where H[0] = 300916!H37 (Sep30).

So the difference between 311016!D40 and UU!F1484 does not surprise me.