I'm trying to setup a calculator in google sheets where I calculate the future value of investing for example:

  • a principal of 10.000
  • into which I deposit 1.000 every month
  • the investment doesn't give steady monthly returns but we can know an annualized average of 20% (say stocks or ETFs)

Would it be correct to use the formula:

FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

FV(20, 12, 1000, -10000)

The main doubt is also: when estimating the compounding of a volatile monthly market, should we consider the interest compounded monthly or yearly, given the example of an annualized 20% return?

Thanks everybody!


Would it be correct to use the formula: FV(20, 12, 1000, -10000)

EDIT: both you and I overlooked percentage. The first parameter should be 20% not 20.

Yes. (I'd write the formula as -FV(20%/12, 12, 1000, 10000). Mathematically they are identical, and I interpret -10000 as a negative amount of money. However, maybe trained analysts use -10000 and I'm the wrong one.)

The main doubt is also if I should consider the interest compounded monthly or yearly?

That's up to you. However, since it says "annualized average", I'd stick with 12 periods.

  • The formula as written returns an answer in the quintillions of dollars.
    – glibdud
    Feb 24 at 14:13
  • @glibdud see my edit. (A single character can make all the difference...)
    – RonJohn
    Feb 24 at 14:27
  • Hey thanks for sharing - I'm basically trying to replicate this thing here (thecalculatorsite.com/finance/calculators/…) and if I change the parameter to monthly or yearly compound it makes a LOT of difference. I can't really grasp what's happening?
    – rudyStock
    Feb 24 at 14:29
  • Shouldn't it be 20%/12? The formula knows nothing about the length of the periods involved; I think it expects rate per period, not annualized. Also, in your -FV formula you also need to flip the sign on the third argument.
    – glibdud
    Feb 24 at 14:31
  • @rudyStock I need more coffee. -FV(20%, 12, 1000, 10000) is for one $1000 contrib per year for 12 years. -FV(20%/12, 12, 1000, 10000) is for one contrib per month for one year.
    – RonJohn
    Feb 24 at 15:09

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.