2

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?

0

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))

2

_______________________________________________________________

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)

4

9
  • 1
    Many thanks! Just one difficulty: I had to use 3 (and not 3%) under annual rate in order to get to the same result. Or delete the "/100".
    – Stacko
    Jan 18 '19 at 11:36
  • yup, C2 was custom formatted with @% mask
    – user0
    Jan 18 '19 at 11:40
  • hmmm... actually the formula doesn't answer my question, as it only calculates the interest * years. It does not calculate the compound interest. For the example above, the result should have been $1757 and not $1571
    – Stacko
    Jan 18 '19 at 11:59
  • @Stacko answer updated
    – user0
    Jan 18 '19 at 13:18
  • Thanks again. The answer now indeed enables an accurate calculation of the compound interest. I still didn't mark it as solved with a faint hope someone comes with a Google Sheets function that calculates current value of compound interest.
    – Stacko
    Jan 20 '19 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.