Google Sheet formula for annualized returns - doubts

Question

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!

RonJohn · Accepted Answer

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.

Google Sheet formula for annualized returns - doubts

One Answer

Add your own answers!

Ask a Question