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

IRR function in Excel - Usage and examples
FVSCHEDULE function - Returns the future value of an investment that has variable or adjustable interest in period 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!

Kareem Winters

Update 19 May 2020

You should read it

May be interested

GAMMA function - The function returns the gamma function value in Excel
gamma: the function returns the gamma function. support functions from excel 2013 onwards. syntax: gamma (number)
POISSON.DIST function - The function returns the Poisson distribution in Excel
poisson.dist function: the function returns the poisson distribution, the application function to predict the number of events in a specific time. support functions from excel 2013 onwards. syntax: poisson.dist (x, mean, cumulative)
WEIBULL function - The function returns the Weibull distribution in Excel
the function performs the calculation and returns the weibull distribution. based on this distribution to analyze reliability in theory such as calculating the average life of the device or used in the field of meteorology, hydrology and weather forecast.
QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 in Excel
quartile.exc function: the function returns the quartile of the data set, with percentile values from 0 to 1 excluding 0 and 1. the support function is from excel 2010 onwards. syntax: quartile.exc (array, quart)
PHI function - The function returns the value of the density function for a normal distribution in Excel
phi function: the function returns the value of the density function for a standard normal distribution. support functions from excel 2013 onwards. syntax: phi (x)
FORECAST function - The function returns a value along a linear trend in Excel
forecast function: the function performs the calculation or prediction of a future value by using current values using linear regression. in excel 2016 this function is replaced by forecast.linear function. syntax: forecast (x, known_ys, known_xs)
BETA.DIST function - The function returns the Beta distribution in Excel
beta.dist function: the function returns the beta distribution to study the variability of a number of things through a sample. support functions from excel 2010 onwards. syntax: beta.dist (x, alpha, beta, cumulative, [a], [b])
How to use the IFS function in Excel 2016
the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.
FTEST function - The function returns the result of an F-Test in Excel
the following article details how to use ftest - the function returns the result of an f-test. in statistical probability, the determination of the correctness and the deviation between two data samples is a regular and extremely important job.
GAMMA.INV function - The function returns the inverse of the gamma distribution in Excel
gamma.inv: the function returns the inverse of the gamma distribution, using this function to study their distribution variables that may be symmetrical. support functions from excel 2010 onwards. syntax: gamma.inv (probability, alpha, beta)