A Comprehensive Guide to Calculating Accounting Rate of Return in Excel

The accounting rate of return (ARR) is an important financial metric used to evaluate the profitability of an investment. It measures the net profit or return expected on an investment compared to its initial cost. Calculating ARR is crucial for businesses and investors to make informed decisions about potential investments.

What is ARR?

ARR, or Accounting Rate of Return, is a financial ratio that measures the average annual profit earned from an investment as a percentage of the initial investment cost. It is also known as the Average Accounting Rate of Return. ARR helps in assessing the profitability of an investment and comparing it to other investment options.

What is the Accounting Rate of Return formula?

The formula for calculating ARR is:

ARR = Average Annual Profit / Initial Investment Cost

ARR is expressed as a percentage, representing the average annual profit as a proportion of the initial investment cost.

How to calculate ARR

To calculate ARR, follow these steps:

1. Determine the average annual profit: Calculate the net profit earned from the investment over its useful life and divide it by the number of years.
2. Calculate the initial investment cost: Determine the total cost of acquiring the asset or making the investment.
3. Apply the ARR formula: Divide the average annual profit by the initial investment cost and multiply by 100 to get the percentage.

ARR Calculation – an example

Let's consider an example to illustrate the calculation of ARR. Assume a company invests $100,000 in a project and expects to generate an average annual profit of $20,000 over a period of 5 years. The ARR would be calculated as follows:

ARR = $20,000 / $100,000 x 100 = 20%

In this example, the accounting rate of return is 20%, indicating that the investment is expected to generate a 20% return on the initial investment cost.

How to calculate Accounting Rate of Return in Excel?

Calculating ARR in Excel is simple and efficient. Excel provides various functions and formulas that can be used to perform the necessary calculations. Here are the steps to calculate ARR in Excel:

1. Enter the necessary data: Create a new Excel spreadsheet and enter the required data, including the average annual profit and the initial investment cost.
2. Use the formula: In a cell, enter the formula =AVERAGE_ANNUAL_PROFIT / INITIAL_INVESTMENT_COST to calculate the ARR.
3. Format the result: Format the result cell as a percentage to display the ARR value properly.

By following these steps, you can easily calculate the accounting rate of return in Excel.

We can help

If you're looking to automate the way you get paid and improve payment processing for your business, GoCardless can help. With over 85,000 businesses already using GoCardless to get paid on time, it's a reliable and efficient solution for payment collection. Learn more about how GoCardless can improve your payment processing today.

Conclusion

Calculating the accounting rate of return (ARR) is essential for evaluating the profitability of an investment. It provides valuable insights into the expected return on investment and helps businesses and investors make informed decisions. Using Excel to calculate ARR simplifies the process and allows for efficient analysis. By following the steps outlined in this guide, you can confidently calculate ARR in Excel and make informed investment decisions.