Aug 6 2014, 09:58 PM
QUOTE(j.passing.by @ Aug 6 2014, 07:21 PM)
Annualized return or CAGR (Compound Annual Growth Rate)
I've finally changed the formula in the excel file; to use the proper CAGR formula instead of simply dividing the returns by the number of days or years holding the fund.
For those interested, here's the formula:
=(POWER(K3/H3,1/(F3/365))-1)*100
K3 is the current value of the fund.
H3 is the initial value of the fund. Some would called it the purchased value or invested value.
F3 is the different between the Purchased date and the Current date.
The whole formula is multiply by 100. You can delete this *100 if you displayed the cell in %.
Please note that my file looks similar to the what is seen in the Account Enquiry page in PMO, except that I have some extra fields like Purchased Date.
-------------------
I did use this formula quite same time, it is only correct for a single transaction. If you hv few rounds of buy/sell transaction, the result will not be correct anymore. I would suggest use excel formula "XIRR", it can accurately calculate the annualized return. I've finally changed the formula in the excel file; to use the proper CAGR formula instead of simply dividing the returns by the number of days or years holding the fund. For those interested, here's the formula:
=(POWER(K3/H3,1/(F3/365))-1)*100
K3 is the current value of the fund.
H3 is the initial value of the fund. Some would called it the purchased value or invested value.
F3 is the different between the Purchased date and the Current date.
The whole formula is multiply by 100. You can delete this *100 if you displayed the cell in %.
Please note that my file looks similar to the what is seen in the Account Enquiry page in PMO, except that I have some extra fields like Purchased Date.
-------------------
For a single transaction, above formula and XIRR will give the same result.
Quote
0.0615sec
0.75
7 queries
GZIP Disabled