I am trying to estimate the value of an investment into the future with minimum and maximum APY compounded monthly in Google Sheets, but I'm getting frustrated; FV, PV, RATE all seem to expect different information than I have available.

For example, let's say I can put $1000 a month into a 5% APY investment, compounded monthly. How would I calculate what the investment value would be at the end of the year? At the end of 10 years?


In fact FV is what you want here. In your example:

=-FV((1.05^(1/12)-1), 120, 1000, 0)

will yield the final value of a 5% APY investment of a $1000 monthly after 10 years.

The 1st argument to FV is the monthly compounding rate calculated down from the 5% APR.

Um, what you want probably isn't a function, because it's too simple to be a specialized function. It doesn't matter if it's compounded weekly, monthly or quarterly, if you want an APY of an amount X at a rate R after an integer number of years N, the formula for that would be =X*(1+R)^N.

For a fractional number of years, just calculate the same for the applicable number of periods at the fractional period rate.

Here is the list of all Google Spreadsheets functions, if you narrow down by Financial you will see that they mostly deal with more complicated scenarios involving vectors of payments, dates, variable rates etc.: https://support.google.com/docs/table/25273?hl=en&ref_topic=3105398

| improve this answer | |
  • That is not what I am asking for. The formula you provided does not take into account the monthly installments I specified. – Jeremy Holovacs May 10 '17 at 17:23
  • Sorry, I missed that. Then in fact it's FV() that you want. I will reformulate my answer. – ttarchala May 10 '17 at 19:28

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.