How to annualize returns in Excel

Rate of Return Formula (Table of Contents)

• Rate of Return Formula
• Rate of Return Calculator
• Rate of Return Formula in Excel (With Excel Template)

The Rate of return is return on investment over a period it could be profit or loss. It is basically a percentage of the amount above or below the investment amount. If the return of investment is positive that means there is a gain over investment and if the return of investment is negative that means there is a loss over investment. The rate of return is compared with gain or loss over investment. The rate of return expressed in form of percentage and also known as ROR. The rate of return formula is equal to current value minus original value divided by original value multiply by 100.

Here’s the Rate of Return formula –

Where,

• Current Value = Current value of investment.
• Original Value = Value of investment.

The rate of return over a time period of one year on investment is known as annual return.

Examples of Rate of Return Formula

Let us see an example to understand rate of return formula better.

Rate of Return Formula – Example #1

An investor purchased a share at a price of $5 and he had purchased 1,000 shared in year 2017 after one year he decides to sell them at a price of $10 in the year 2018. Now, he wants to calculate the rate of return on his invested amount of $5,000.

As we know,

Rate of Return = (Current Value – Original Value) * 100 / Original Value

Put value in the above formula.

• Rate of Return = (10 * 1000 – 5 * 1000) * 100 / 5 *1000
• Rate of Return = (10,000 – 5,000) * 100 / 5,000
• Rate of Return = 5,000 * 100 / 5,000
• Rate of Return = 100%

Rate of return on shares is 100%.

Now, let’s see another example to understand the rate of return formula.

Rate of Return Formula – Example #2

Amey had purchased home in year 2000 at price of $100,000 in outer area of city after sometimes area got develop, various offices, malls opened in that area which leads to an increase in market price of Amey’s home in the year 2018 due to his job transfer he has to sell his home at a price of $175,000. Now, let’s calculate the rate of return on his property.

As we know,

Rate of Return = (Current Value – Original Value) * 100 / Original Value

Put value in the above formula.

• Rate of Return = (175,000 – 100,000) * 100 / 100,000
• Rate of Return = 75,000 * 100 / 100,000
• Rate of Return = 75%

Rate of return on Amey’s home is 75%.

Annualize Rate of Return –

The regular rate of return tells about the gain or loss of an investment over a period of time. It is expressed in terms of percentage. The annualize rate on return also known as the Compound Annual Growth Rate (CAGR). It is return of investment every year. The annualized rate of return formula is equal to Current value upon original value raise to the power one divided by number of years, the whole component is then subtracted by one.

The formula for same can be written as:-

In this formula, any gain made is included in formula.

Let us see an example to understand it.

Rate of Return Formula – Example #3

An investor purchase 100 shares at a price of $15 per share and he received a dividend of $2 per share every year and after 5 years sell them at a price of $45. Now, we have to calculate the annualized return for the investor.

As we know,

Annualized Rate of Return = (Current Value / Original Value)(1/Number of Year)

Put value in the formula.

• Annualized Rate of Return = (45 * 100 / 15 * 100)(1 /5 ) – 1
• Annualized Rate of Return = (4500 / 1500)0.2 – 1
• Annualized Rate of Return = 0.25

Hence,

Annualized Rate of Return = 25%

So, Annualize Rate of return on shares is 25%.

Now, let us calculate the rate of return on shares.

Rate of Return = (Current Value – Original Value) * 100 / Original Value

Put value in formula.

• Rate of Return = (45 * 100 – 15 * 100) * 100 / 15 * 100
• Rate of Return = (4500 – 1500) * 100 / 1500
• Rate of Return = 200%

Now, rate of return is 200% for shares.

Rate of return is also known as return on investment. The rate of return is applicable to all type of investments like stocks, real estate, bonds etc.

Rate of Return Formula – Example #4

Suppose an investor invests $1000 in shares of Apple Company in 2015 and sold his stock in 2016 at $1200.

Then, the rate of return will be:

• Rate of Return = (Current Value – Original Value) * 100 / Original Value
• Rate of Return Apple = (1200 – 1000) * 100 / 1000
• Rate of Return Apple = 200 * 100 / 1000
• Rate of Return Apple = 20%

He also invested $2000 in Google stocks in 2015 and sold his stock in 2016 at $2800.

Then the rate of return will be as follows:-

• Rate of Return = (Current Value – Original Value) * 100 / Original Value
• Rate of Return Google = (2800 – 2000) * 100 / 2000
• Rate of Return Google = 800 * 100 / 2000
• Rate of Return Google = 40%

So, through the rate of return, one can calculate the best investment option available. We can see that investor earns more profit in the investment of Google then in Apple, as the rate of return on investment in Google is higher than Apple.

Significance and Use of Rate of Return Formula

Rate of return have multiple uses they are as follows:-

• Rate of return is used in finance by corporates in any form of investment like assets, projects etc.
• Rate of return measure return on investment like rate of return on assets, rate of return on capital etc.
• Rate of return is useful in making investment decisions.
• It is used in financial analysis by investors.

Rate of Return Calculator

You can use the following Rate of Return Calculator

Rate of Return Formula in Excel (With Excel Template)

Here we will do the same example of the Rate of Return formula in Excel. It is very easy and simple. You need to provide the two inputs i.e Current Value and Original Value

You can easily calculate the Rate of Return using Formula in the template provided.

Example #1

Example #2

Example #3

Example #4

Conclusion

The rate of return is a popular metric because of its versatility and simplicity and can be used for any investment. Return of return is basically used to calculate the rate of return on investment and help to measure investment profitability. If the investment rate of return is positive then it’s probably worthwhile whereas if the rate of return is negative then it implies loss and hence investor should avoid it. The higher the percentage greater the benefit earned. One thing to keep in mind is considering the time value of money. For simple purchase or sale of stock the time value of money doesn’t matter, but for calculation of fixed asset like building, home where value appreciates with time. So, the annualized rate of return formula is used. One can use rate of return to compare performance rates on capital equipment purchase while an investor can calculate which stock purchases performed better

Recommended Articles

This has been a guide to a Rate of Return formula. Here we discuss its uses along with practical examples. We also provide you with Rate of Return Calculator with downloadable excel template. You may also look at the following articles to learn more –

1. Debt to Income Formula
2. Formula for Capital Gains Yield
3. Guide to Bid-Ask Spread Formula
4. Formula for Capacity Utilization Rate
5. Annual Return Formula | How to Calculate? | Example