MIRR function - The function returns the adjusted internal rate of return in Excel

Sometimes in the process of investing in securities you wonder and do not know when you can payback or how to calculate whether the next investment is profitable or not. The following article shows how to use the MIRR function to help you answer the above question.

MIRR function - The function returns the adjusted internal rate of return in Excel Picture 1

Instructions for using the MIRR function

Description: Perform the calculation of the internal rate of return adjusted for periodic cash flow. Not only that, but MIRR allows calculating the cost and interest if reinvested.

Syntax: MIRR (values, finance_rate, reinvest_rate) .

Inside:

- values: Include payments, including negative values. This includes initial expenses, loans, etc.

- finance_rate: Interest paid on loans in the cash flow.

- reinvest_rate: Interest received when reinvesting.

Example: Give the following data table:

MIRR function - The function returns the adjusted internal rate of return in Excel Picture 2

Calculate:

- Calculate the interest rate of the investment after a period of 3 years.

- Calculate the interest rate of the investment after a period of 5 years.

- Calculate the interest rate of the investment after a period of 5 years and use the reinvestment with the interest rate of 12%.

Perform the problem:

1. Interest rate of the investment after 3 years

In the cell to calculate enter the formula: = MIRR (D5: D8, D11, D12) .

MIRR function - The function returns the adjusted internal rate of return in Excel Picture 3

Result:

MIRR function - The function returns the adjusted internal rate of return in Excel Picture 4

So after 3 years, the interest rate = -6.6% => The investment after 3 years has no interest.

2. Interest rate after 5 years

In the cell to calculate enter the formula: = MIRR (D5: D10, D11, D12) .

MIRR function - The function returns the adjusted internal rate of return in Excel Picture 5

Results after calculation:

MIRR function - The function returns the adjusted internal rate of return in Excel Picture 6

So the investment after 5 years is profitable and the interest rate is 11% of the capital.

3. Interest rate received after 5 years with reinvestment receives interest rate of 12%

In the cell to calculate enter the formula: = MIRR (D5: D10, D11.12%) .

MIRR function - The function returns the adjusted internal rate of return in Excel Picture 7

Result:

MIRR function - The function returns the adjusted internal rate of return in Excel Picture 8

The interest rate after 5 years of reinvestment with the interest rate of 12% = 9% <11% => The effective reinvestment is not high, you should consider.

The above is the calculation and estimation of interest rates after investment and reinvestment. Hope you consider carefully before deciding to invest.

Good luck!