How to calculate annualized return from monthly returns in Excel
Excel formula
=PRODUCT(1+B2:B10)^(12/COUNT(B2:B10))-1Compound the monthly returns and scale to 12 months: =PRODUCT(1+B2:B10)^(12/COUNT(B2:B10))-1.
- Result on the example
- 7.66% in
D2 - Works in
- Every current version
- Google Sheets
- Different formula, see below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
1+B2:B10 turns each return into a growth factor (1.012, 0.996, …), and PRODUCT multiplies them into the growth over nine months. Raising that to the power 12/9 stretches it to a year, and subtracting 1 turns it back into a rate. Google Sheets needs ARRAYFORMULA for the 1+B2:B10 part.
=PRODUCT(1+B2:B10)^(12/COUNT(B2:B10))-1Result0.0766103347
B2:B109 rows × 1 col: 1.2%, -0.4%, 2.1%, 0.8%, …A range: 9 cells in one column.
1+B2:B10{9 rows × 1 col} 1.012, 0.996, 1.021, 1.008, …The + operator adds.
PRODUCT(1+B2:B10)1.056924362PRODUCT: Multiplies all the numbers.- number1
1+B2:B10{9 rows × 1 col} 1.012, 0.996, 1.021, 1.008, …
- number1
COUNT(B2:B10)9COUNT: Counts cells that contain numbers.- value1
B2:B109 rows × 1 col: 1.2%, -0.4%, 2.1%, 0.8%, …
- value1
12/COUNT(B2:B10)1.333333333The / operator divides.
PRODUCT(1+B2:B10)^(12/COUNT(B2:B10)1.076610335The ^ operator raises to a power.
PRODUCT(1+B2:B10)^(12/COUNT(B2:B10))-10.0766103347The - operator subtracts.
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(PRODUCT(1+B2:B10)^(12/COUNT(B2:B10))-1). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =AVERAGE(B2:B10)*12 | 7.47%Different result | Twelve times the average month is a rough estimate that ignores compounding. A rough 7.47%. |
| =PRODUCT(1+B2:B10)-1 | 5.69%Different result | The total return over the nine months, without annualizing. Total for nine months: 5.69%. |
Common mistakes
- Annualizing a few months of returns projects them onto a whole year. Treat short periods with care.
- Returns must be decimals (1.2% or 0.012), not 1.2.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate annualized return from monthly returns
D2, convert the nine monthly returns in B2:B10 into an annualized return.Questions
How do I annualize from a start and end value?
Use the dates: =(end/start)^(365/(end_date-start_date))-1.
How do I annualize daily returns?
Use 252 trading days: =PRODUCT(1+B2:B200)^(252/COUNT(B2:B200))-1.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.