I started an Acorns account several months ago to use as a glorified savings account. I don't make contributions on purchases, but I contribute 10% of my paycheck to it every two weeks (Richest Man In Babylon, anyone?). I'm trying to calculate the average daily return based on account value at the start of the day vs end of the day.
Background: I initially started with my account portfolio settings set to Moderately Aggressive, thinking it might yield better gains, but after a few weeks I noticed that the losses pretty much canceled out the gains. Since then, I've started keeping track of my account value at the start of the day and at the end of the day, taking contributions into account. I've changed my portfolio risk level each month since then, lowering it each time to see if I get better returns (so far, Moderately Conservative seems to yield better returns than more aggressive allocations, FYI). I'm trying to determine which portfolio yields the best daily and theoretical yearly return.
I have an Excel file with several months worth of opening and closing account values. I've been subtracting the opening value from the closing value to get the daily gain or loss, and then dividing that by the account's opening value for that day to get the %change in terms of change/initial amount.
What is the standard way to calculate the average return of data like this? I've read that simply adding all the percents together and dividing by the number of data points doesn't create a very accurate picture. I've also read that using
[(1+return1) * (1+return2) * ...]^1/n -1 is a more accurate way to calculate the average, but when I try this, neither excel nor my calculator will give me a real answer (because of the negatives?). How should I convert my history of gains and losses into a statistically likely daily gain or loss?
Also, once I have the average daily gain, can I use the standard
Annual Return = [(Daily Gain +1)^365]-1 to get the theoretical annual gain (not taking future contributions into account, obviously)?