Google Sheets – How to Calculate Compound Interest with Different Interest Rates

google sheets

Assume that I have the following different interest rates for different periods. I would like to calculate a compound interest rate based on these rates for lets say 200 periods. How can I do it elegantly instead of multiplying int_rate1 * int_rate2 * int_rate3 ...

Year    Month   Interest Rate
2014    1       8.04
2014    2       9.14
2014    3       10.06
2014    4       9.11
2014    5       9.20

Best Answer

You can use PRODUCT to provide the product of a range of values, eg:

=PRODUCT(C2:C201)

And if you wanted the compound interest effective at each month, then in row 2:

=PRODUCT(C$2:C2)

and fill down as far as required.

Related Solutions

Google Sheets – How to Refer to Two Different Columns in a COUNTIF Function

Use a combination of the ARRAYFORMULA, SUM and arithmetic operations

Here's the function to count all of the clicks by a 26 year old:

=ARRAYFORMULA(sum((A:A=26) * (C:C="true")))

There are 3 parts to this operation.

The ARRAYFORMULA takes care of looping over the specified range
The SUM manages counting all of the true results

Essentially, true is being converted to 1 and false is being converted to 0. Boolean logic is done by using arithmetic operations.

An AND operation uses multiplication:

(1 * 1) = 1 - (True && True) = True
(1 * 0) = 0 - (True && False) = False
(0 * 0) = 0 - (False && False) = False

An OR operation uses a combination of the *SIGN function and addition:

sign(1 + 1) = 1 - (True || True) = True
sign(1 + 0) = 1 - (True || False) = True
sign(0 + 0) = 0 - (False || False) = False

Note: The sign function is necessary because of the way boolean addition works differently than arithmetic addition. Basically in boolean addition 1 + 1 = 1, in arithmetic addition 1 + 1 = 2. Obviously, arithmetic addition will mess up the count so you need to run the results of the addition operations through a sign function. The sign function returns 1 if the value is positive, 0 if the value is 0, and -1 if the value is negative.

Lets say you wanted to count the clicks for all users between age 20-25:

=ARRAYFORMULA(sum(sign((A:A=20) + (A:A=21) + (A:A=22) + (A:A=23) + (A:A=24) + (A:A=25)) * (C:C="true")))

Google-sheets – Make dynamic array blank based on adjacent values in Google Spreadsheets

Direct answer

The following formula return the expected results for the three cases exposed in the question. It should be added to cell A2 and cells in A3:A13 should be empty.

=ArrayFormula(
  IF(
    NOT(
      (ROW(B2:B13)-1=1)
      *
      (B2:B13="$")
    )
    *
    (
      (B2:B13=">")
      +
      (B2:B13="$")
      +
      (ROW(B2:B13)-1>=Vlookup("$",{B2:B12,ROW(B2:B12)},2,0))
    )
    *
    NOT((B1:B12=">")
    *(B2:B13="$")),
    ".",
    " "
  )
)

Explanation

In this case the use of ArrayFormula function together with references to B2:B13 will return an array of 12 rows and 1 columns with the expected values.

The comparison of a range or array with a scalar value returns an array of the same size of the range or array. All the tests should return an array of the same size.

AND and OR functions could not work with arrays, but Google Sheets interpret TRUE as 1 and FALSE as 0, so, instead of using AND function use * and instead of OR use +.

As + and * take precedence over comparison operators each test should be enclosed in parentheses.