For simplicity's sake, suppose I have an investment that earns 1% daily return on average.
The following day, that 1% earning is immediately rolled over into an account that earns 10% yearly compounding daily.
So suppose I had $1000 as an initial investment, by day two I would have $1010. That $10 would then be rolled into the 10% APY account. This cycle would repeat every day.
How would I calculate the ending yield and total overall APY % if I plugged it into a spreadsheet?
"suppose I had $1000 as an initial investment, by day two I would have $1010. That $10 would then be rolled into the 10% APY account. This cycle would repeat every day."
It sounds like you are effectively putting $10 into a 10% (let's say daily) account, every day.
a = 10
r = 0.1
Totalling up the balance x over days. (Assuming the $10 would be transferred at the end of the day, after accumulating from the $1000.)
start x = 0
day 1 x = x*(1 + r) + a = 10
day 2 x = x*(1 + r) + a = 21
day 3 x = x*(1 + r) + a = 33.1
In a mathematical formula
n = 3
(a ((1 + r)^n - 1))/r = 33.1
In Excel
A1 = 10
A2 = 0.1
A3 = 3
=A1*(POWER(1+A2,A3)-1)/A2
There is a Future Value of Annuity function built into excel. You can set it up with the following example of 10% APY compounding daily, 5 days of compounding, and 1% of the $1000 added every single day
A1 = 0.1/365
A2 = 5
A3 = 1000 * 0.01
=FV(A1, A2, -A3)
This will give a final value of $50.03 in your account after 5 days which you can easily check against your spreadsheet.
Note 1: A3 has to be negative to receive a positive answer. I always view it as if you are putting in a positive $10 you are paying someone else. If you are putting in a -$10 someone is paying you. (In this case it is you who is paying you).
Note 2: This is excel using a pure math function which means it will carry more decimals than your financial institution will have, so you will have some rounding error.
