Google Sheets – Calculate Current Value of Compound Interest

formulasgoogle sheets

How can I calculate the current value of a sum deposited in a past date, with a fixed interest rate?

For example, if $1,000 were deposited on 1/1/2000 in an account which pays a 3% annual interest rate, how much would they be worth today?

Best Answer

COMPOUND INTEREST:

cell F6: =B6*(1+C6)^(YEARFRAC(A6,TODAY()))
cell G6: =B6*(1+C6/4)^(4*YEARFRAC(A6,TODAY()))
cell H6: =B6*(1+C6/12)^(12*YEARFRAC(A6,TODAY()))
cell I6: =B6*(1+C6)^(ROUNDDOWN(YEARFRAC(A6,TODAY()),0))
cell J6: =B6*(1+C6/4)^(4*ROUNDDOWN(YEARFRAC(A6,TODAY()),0))
cell K6: =B6*(1+C6/12)^(12*ROUNDDOWN(YEARFRAC(A6,TODAY()),0))

_______________________________________________________________

INTEREST MULTIPLIED BY YEARS:

up to today's date:
```
=B2 + C2/100*B2 * YEARFRAC(A2; TODAY())
```
up to to the start of new anual period (eg. from 01.01.2000 to 31.12.2018):
```
=B2 + C2/100*B2 * ROUNDDOWN(YEARFRAC(A2; TODAY()); 0)
```

Formula

=SUM(FILTER(C2:C,B2:B=D6,A2:A>=D4,A2:A<=D5))

Note

Apart from that, you need to change the end date from 7/20/2012 to 7/23/2012

Screenshot

Example

See example file I prepared: SUM with FILTER

Google-sheets – Scan row for value, get column of that match, apply column in another formula in a different cell

The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you wanted can now be interpreted using the indirect formula!

All together, you're looking at the following formula for B3 in your example:

=COUNTIF(INDIRECT("$A$2:"&CELL("address",INDEX($1:$2,2,MATCH(TODAY(),$1:$1)))),"Yes")

edit

I temporarily forgot about filter, which makes this task much easier: =COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")