Is there anyway to calculate the daily percentage return? I would like to use it to calculate the correlation and construct a optimized portfolio. Or daily equity also be fine. Thanks!
Yes, if you have the total return and the number of days.
[ADDITION: see my response below for the simpler way of computing this.]
In Excel, you can do it 2 ways, one by solving for a root, the other by repeatedly guessing, using the right formula.
Given that I’m too lazy to look up the root function, here is one that works off the top of my head:
Let’s say your total return is 50%, (add 1 to that and) in cell B1 type: 1.5
In cell B2, type the number of days, for example: 75
In cell B3, type: =(1+B4)^B2
In cell B4, try to guess the daily return. I started with: .004
The result in cell B3 was too low (below 1.5)
So in cell B4 I tried: .005
Still too low, I tried: .0055
Too high, so I spent 30 seconds trying higher and lower numbers until I got the answer: .00542
Then cell B3 said 1.5, just as cell B1 said,
In that example, the answer was 0.542% daily return
You may also have to set the number formats and adjust the decimal places to solve your math problem.
Hi QuantitativeModels, thanks for your answer. Your formula is for calculating geometry average. But I am looking for actual daily return, or equity value.
Try Timesheet.
https://collective2.com/c2explorer_help/html/ff5c67cf-1ee5-43b0-bc45-b1bf8f71a9be.htm
I think it is what you are seeking for.
The formula for computing the daily average return is below. i guess you are asking for a list of your daily balances, which is something different
OK, let’s try an example: 75% return in 25 days. The daily percentage return is obviously not 3%.
The formula is:
=((1+total return)^(1/number of days))-1
in Excel type: =(1.75^(1/25))-1
Daily return is 2.263%.
Thanks, Bob. That’s exactly what I was looking for.
