When you download monthly return data from Morningstar, it places the data in column B starting in row 6, so I will use B6:B225 as the monthly return data in my examples. Show
Note that Morningstar gives you the data as whole numbers, meaning 5.42039 instead of 0.0542039, which before you can use the data you must divide by 100. There are at least three options. Option 1: Future ValueThe main formula for an annualized rate of return is: The quotient of the ending value divided by beginning value raised to the exponent of the quotient of one divided by the number of years minus one. The Excel function FVSCHEDULE calculates the future value of its first input when grown according to the array in its second input. In this way, you can use FVSCHEDULE(1,B6:B225) to calculate the ending value of a $1 investment. The next step in the formula for annualized return is the ending value of the investment divided by the beginning value of the investment. Since we used a beginning investment value of $1, we have already completed that step. The next step is to raise this ratio to the exponent of 1 divided by the number of years and then subtract 1. Because we have a number of months in COUNT(B6:B225), to make this the number of years we would use 12/COUNT(B6:B225) This makes the final formula for returns expressed as decimals:
If your numbers are expressed as whole numbers, you will need to divided the return numbers by 100 to get the right answer. To do this, you will Array Enter (Ctrl-Shift-Enter) the following formula, which includes the step of dividing by 100:
Option 2: ArrayAnother way to annualize a return is to use the product of, for each month in turn, one plus the month’s return. This can be achieved with the array-entered formula:
This formula assumes you need to divide by 100 to get your returns into decimals. If you do not, you can use:
Option 3: Large TableSometimes, you may find that you are already in need of creating a table where you grow the assets by the monthly amounts. If that is the case, set up the table with a starting value and then multiply the cell above by that month’s monthly return divided by 100. For example, the first cell might be:
where C5 has a starting value of say $10,000. Then after growing all the returns all the way down to growing C224 by B225 into a C225, you can calculate the annualized return by using ending value divided by beginning value, like so:
This has the added benefit that you can count the annualized return over time, easily calculate a rolling return, or create a “Growth of the Assets Over Time” graph without any additional effort. However, it uses a lot of cells to accomplish something you could have done in one cell with one of the options above. Hopefully, these three strategies empower you to run some of your own investment analysis. If you enjoy running investment analysis in Microsoft Excel, you may want to consider applying for a job at Marotta Wealth Management! We are an independent, fee-only, comprehensive wealth management firm offering a complete range of investment management and financial planning services. The work we do for clients is valuable, fun, and fulfilling. As fee-only fiduciaries, our only concern is to help clients meet their goals. And as comprehensive wealth managers, we are always seeking to advance the financial planning profession. You can read more about the benefits of working for us here. Photo by Tyler Franta on Unsplash Related Articles
We have no secret ingredient at Marotta Wealth Management. Instead, we openly and publicly publish our strategies as articles on our website. This article is talking about ways to calculate the Average Annual Growth Rate (AAGR) and Compound Annual Growth Rate (CAGR) in Excel.
Calculate Compound Annual Growth Rate in ExcelTo calculate the Compound Annual Growth Rate in Excel, there is a basic formula =((End Value/Start Value)^(1/Periods) -1. And we can easily apply this formula as following: 1. Select a blank cell, for example Cell E3, enter the below formula into it, and press the Enter key. See screenshot: =(C12/C3)^(1/(10-1))-1 Note: In the above formula, C12 is the cell with end value, C3 is the cell with start value, 10-1 is the period between start value and end value, and you can change them based on your needs. 2. In some cases, the calculation result may not format as percentage. Please keep selecting the calculation result, click the Percent Style button Calculate compound annual growth rate with XIRR function in ExcelActually, the XIRR function can help us calculate the Compound Annual Growth Rate in Excel easily, but it requires you to create a new table with the start value and end value. 1. Create a new table with the start value and end value as the following first screen shot shown: Note: In Cell F3 enter =C3, in Cell G3 enter =B3, in Cell F4 enter =-C12, and in Cell G4 enter =B12, or you can enter your original data into this table directly. By the way, you must add a minus before the End Value. 2. Select a blank cell below this table, enter the below formula into it, and press the Enter key. =XIRR(F3:F4,G3:G4) 3. For changing the result to percentage format, select the Cell with this XIRR function, click the Percent Style button Quickly save the CAGR table as a mini template, and reuse with only one click in futureIt must be very tedious to refer cells and apply formulas for calculating the averages every time. Kutools for Excel provides a cute workaround of AutoText utility to to save the range as an AutoText entry, which can remain the cell formats and formulas in the range. And then you will reuse this range with just one click. Free Trial 30 Days Now! Buy Now!
Calculate Average Annual Growth Rate in ExcelTo calculate the Average Annual Growth Rate in excel, normally we have to calculate the annual growth rates of every year with the formula = (Ending Value - Beginning Value) / Beginning Value, and then average these annual growth rates. You can do as follows: 1. Besides the original table, enter the below formula into the blank Cell C3 and, and then drag the Fill Handle to the Range C3:C11. =(C4-C3)/C3 2. Select the Range D4:D12, click the Percent Style button 3. Average all annual growth rate with entering below formula into Cell F4, and press the Enter key. =AVERAGE(D4:D12) Up to now, Average Annual Growth Rate has been calculated and shown in the Cell C12. Demo: calculate average/compound annual growth rate in ExcelKutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now! Related articles:
Average range with rounding in Excel
Running total /average in Excel
Average rate of change in Excel
Weighted average in Excel
Moving/rolling average Excel The Best Office Productivity ToolsKutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Read More... Free Download... Purchase... Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Read More... Free Download... Purchase...
Oldest First Sort comments by Oldest First Newest First Comments (22) No ratings yet. Be the first to rate! Manoj about 6 years ago #16319 This comment was minimized by the moderator on the site Hi Bro, Your Formula for CAGR: [quote]=((End Value/Start Value)^(1/(Periods - 1)) -1 [/quote] highlighted in blue is incorrect. The correct CAGR formula is: = ((FV/PV)^(1/n)) - 1 Where, FV: Future Value PV: Present Value n: Number of years Reply 0 0 triple triple 6997r6 Manoj about 2 years ago #16320 This comment was minimized by the moderator on the site ga ga ga gu gu gu gu gu gay Reply 0 0 Dan about 6 years ago #17367 This comment was minimized by the moderator on the site Agree with MANOJ - the formula is wrong. it is not n-1 Reply 0 0 ramsy foss about 5 years ago #18070 This comment was minimized by the moderator on the site I need the amount $20,000 @ 8% & 10% would be after 30 years compounded growth please. Thank you, Ramsy Reply 0 0 Andrew about 5 years ago #18096 This comment was minimized by the moderator on the site As noted above, this formula is INCORRECT and misleading (especially since it's the first Google result). Please correct! Reply 0 0 Daniel about 5 years ago #18207 This comment was minimized by the moderator on the site The original formula is CORRECT, but its explanation is wrong; It confuses the number of dates shown with the number of periods. In the example, there are ten dates listed but they extend over only 9 years (periods). That is why there is a minus 1.Try it yourself by calculating each year's increase at the CAGR of 6.5257% and you will get the correct ending answer. Reply 0 0 HB Daniel about 3 years ago #18208 This comment was minimized by the moderator on the site thank you. Strange only two others seem to have noticed this. I'd like to know why doesn't the formula just say to put in the actual number of periods, such as the 9 periods shown instead of ten minus one? Actually gave the correct formula n=number of periods) which is smart, because then eveeryone can use it for say 4/1/xx to 9/31/xx and actually list the number of periods in years with a decimal to represent the part of the year related to thte number of months between the beginning of April to the end of Sept Reply 0 0 Daniel about 5 years ago #18209 This comment was minimized by the moderator on the site The formula is actually CORRECT, but the explanation is incorrect. There are 10 dates that represent 9 periods. It should say that "n-1" means "dates-1", not "periods - 1". You can verify it yourself by increasing each year by this example's n-1 CAGR and you will get the final result. Reply 0 0 Shailender about 5 years ago #18564 This comment was minimized by the moderator on the site In the CAGR formula, why we are using -1 at the end. I am using two formulaes 1) (I5/I4)^(1/(25-1))-1 2)(I5/I4)^(1/(25)-1) which one is correct..? Please help me out on this thank you. Reply 0 0 Sergio Shailender about 5 years ago #18565 This comment was minimized by the moderator on the site The first formula you are using is the correct one! Reply 0 0 McKing about 5 years ago #19492 This comment was minimized by the moderator on the site There is a new tool that will fit to your planning software. MS Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Thus, it can do Bookkeeping/Accounting, Budgeting (Existing Year and Long Term) and Data Analysis. Reply 0 0 Wayne Hurt about 5 years ago #21476 This comment was minimized by the moderator on the site Can someone help with this problem using excel? Reply 0 0 Tang Kelly Wayne Hurt about 5 years ago #21477 This comment was minimized by the moderator on the site Hi Wayne, Reply Report 0 0 Robert about 5 years ago #21680 This comment was minimized by the moderator on the site Hi, Reply Report 0 0 Robert Robert about 5 years ago #21681 This comment was minimized by the moderator on the site FY12 - 2653 Reply Report 0 0 Tang Kelly Robert about 5 years ago #21682 This comment was minimized by the moderator on the site Hi Robert, Reply Report 0 0 DQ about 4 years ago #26226 This comment was minimized by the moderator on the site Hi - I'm trying to work backwards to find the highest price I can buy a share at when I have a total expected return. Are you able to please help me by reverse engineering the formula to work this out. Using your example - I'm trying to work out what the "3" should be 2.43443 =(3200/x)^(1/(40-8))-1 Reply 0 0 pp about 3 years ago #28762 This comment was minimized by the moderator on the site Your period is wrong in CAGR formula ? Reply 0 0 HB pp about 3 years ago #28763 This comment was minimized by the moderator on the site I agree, there's only 9 periods from 1/1/11 to 1/1/20. Ten periods would be 1/1/11 to 12/31/20. Please explain Reply 0 0 John Taylor about 3 years ago #30973 This comment was minimized by the moderator on the site You can also just use the RATE() formula with setting PMT to 0. RATE(9,0,-549,987) = 6.73%. Built right into Excel already. How do you calculate annual rate of return over multiple years?To calculate the CAGR of an investment:. Divide the value of an investment at the end of the period by its value at the beginning of that period.. Raise the result to an exponent of one divided by the number of years.. Subtract one from the subsequent result.. Multiply by 100 to convert the answer into a percentage.. How do I calculate CAGR in Excel for 4 years?Note: in other words, to calculate the CAGR of an investment in Excel, divide the value of the investment at the end by the value of the investment at the start. Next, raise this result to the power of 1 divided by the number of years. Finally, subtract 1 from this result.
How do you calculate rate of return over years?Annual Rate of Return: Definition & Formula
To calculate a 1-year annual return, take the end-of-year investment value, deduct the value from the beginning of the year, and then divide it also by the beginning-of-year value.
How do you calculate growth rate between two years in Excel?Percentage Growth Rate = (Ending value / Beginning value) -1
AAGR is calculated by dividing the total growth rate by the number of years.
|