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?

1 Answer 1



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




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


  • 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".
    – Ohad
    Commented Jan 18, 2019 at 11:36
  • yup, C2 was custom formatted with @% mask
    – user0
    Commented Jan 18, 2019 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
    – Ohad
    Commented Jan 18, 2019 at 11:59
  • @Stacko answer updated
    – user0
    Commented Jan 18, 2019 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.
    – Ohad
    Commented Jan 20, 2019 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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