Math Function: Compounding: Periods Required to Get 100% APY

user_190925 0
Viewed 14

I'm trying to find or derive a function for Google Sheets, that will return the number of periods (eg days) required to reach a specified APY (eg 100%), given the interest rate per period.

I started with a basic APY function:

r = rate per period
n = number of periods
APY = (1 + r) ^ n - 1

Example:

r = 5% (per period of a day)
n = 14.21 (number of periods, ie days)
APY = (1 + 5%) ^ 14.21 - 1
    = 100.03%

I'm stuck trying to reverse the function, so I can determine n (the number of periods), if the APY is given as 100%.

Any suggestions would be most appreciated.

math
1 Answers

You need the (1+r)-log to reverse the power-to function:

r = log(APY + 1) / log(1 + r)

Examples:

r = log(1,0003 + 1) / log(1 + 0,05) = 14,209
r = log(1 + 1) / log(1 + 0,05) = 14,207

Should not matter which log you use, the log-10 or the natural log (ln), as long as you use the same function both times.

user_28461380
Related