2

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!

Improve this question

1 Answer 1

Reset to default
0

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.

Improve this answer
8
  • The formula as written returns an answer in the quintillions of dollars.
    – glibdud
    Commented Feb 24, 2021 at 14:13
  • @glibdud see my edit. (A single character can make all the difference...)
    – RonJohn
    Commented Feb 24, 2021 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
    Commented Feb 24, 2021 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
    Commented Feb 24, 2021 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
    Commented Feb 24, 2021 at 15:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .