# Calculating daily yields with a different compounding interest

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?

• "This cycle would repeat every day." What exactly repeats every day? – RonJohn Jun 9 at 0:13
• Earn 1% initial account, take 1% earned to transfer to 10% account. – eComEvo Jun 9 at 0:42
• I'd break it down into pieces: cell A1=364; cell A2 = \$1000; cell A3=A2*1.01; cell A4=A3*1.10*(A1/365). That tells you how much you'd make on the first \$1000. – RonJohn Jun 9 at 0:56
• Do the same in row B, except cell A2 = 363, since it's the next day. Repeat 361 more times. Then sum up column 4. – RonJohn Jun 9 at 0:58
• This presumes the banks are open 365 days per year, and it's not a leap year. Adjust that as you see fit. – RonJohn Jun 9 at 0:59

"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
``````
• you assume A3 and A2 to be in years, but the poster wanted a 10% APY. you will need to do something like `A2 = 0.1/365` (for compounding every day of the year) or `A2 = 0.1/252` (for stock market trading days) for the poster to get a more accurate description of what they want. – rhavelka Jun 9 at 14:39
• @rhavelka Indeed, `r` and `A2` should be `0.1/365` or `1/252`. `n` and `A3` are in days though, as intended. – Chris Degnen Jun 9 at 15:05
• Sorry, I made an edit to my comment and accidentally lost that A3 was in days. – rhavelka Jun 9 at 15:08

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.