How to Calculate CAGR in Microsoft Excel

To maintain monitor of an funding equivalent to a mutual fund, you seemingly need to calculate the CAGR (compound annual progress charge). You are able to do so in Microsoft Excel utilizing the RRI operate and it’s a lot simpler than you would possibly assume.

Let’s stroll by means of establishing your knowledge and utilizing the RRI operate to calculate the CAGR in Excel.

Arrange the Funding Particulars

All you might want to calculate the CAGR are the intervals and the values for every interval. Record the years within the first column and quantities within the second as proven beneath.

Periods and amounts for an investment in Excel

Enter the System for the RRI Operate

Subsequent, choose the cell the place you need to calculate the CAGR. That is the place you’ll enter the formulation for the RRI operate.

RELATED: The Fundamentals of Structuring Formulation in Microsoft Excel

The syntax for the operate is RRI(intervals, current worth, future worth) the place all three arguments are required.

Utilizing our knowledge above, we’d use this formulation to search out the CAGR with the RRI operate:

=RRI(A7,B2,B7)

A7 incorporates the variety of intervals within the funding, B2 incorporates the current worth, and B7 incorporates the long run worth.

Formula for the RRI function in Excel

To format the outcome as a share, go to the Dwelling tab and both click on the P.c Model button or select “Proportion” within the Quantity Model drop-down field.

Format CAGR as a percentage

In its place, you possibly can insert the precise values within the formulation as an alternative of the cell references:

=RRI(5,50,400)

RRI formula with constants instead of cell references

Calculating CAGR With a Forumla

Whereas the RRI operate does a superb job of calculating the CAGR of an investment, you possibly can all the time do the maths by hand or enter that formulation into Excel to double-check the RRI formulation outcome.

RELATED: Defining and Making a System

To calculate CAGR, you first divide the long run worth by the current worth. Then, increase the outcome to an exponent of 1 divided by the variety of intervals (years). Lastly, subtract one from the outcome.

Right here is the formulation for CAGR utilizing our above instance and cell references in Excel:

=(B7/B2)^(1/A7)-1

As you possibly can see within the screenshot beneath, this formulation confirms the results of the RRI operate’s formulation.

Formula to calculate CAGR in Excel

Microsoft Excel is a stable software for retaining monitor of investments and family funds. So in case you’re taken with further methods it might probably assist you to, check out these finances features you need to use in Excel.

Leave a Reply

Your email address will not be published.