2

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?

Improve this question
8
  • "This cycle would repeat every day." What exactly repeats every day?
    – RonJohn
    Jun 9, 2021 at 0:13
  • Earn 1% initial account, take 1% earned to transfer to 10% account.
    – eComEvo
    Jun 9, 2021 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, 2021 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, 2021 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, 2021 at 0:59

2 Answers 2

Reset to default
1

"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
Improve this answer
3
  • 1
    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, 2021 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, 2021 at 15:05
  • Sorry, I made an edit to my comment and accidentally lost that A3 was in days.
    – rhavelka
    Jun 9, 2021 at 15:08
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .