# Google Sheet formula for annualized returns - doubts

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