Buzz
The MIRR function in Excel calculates the adjusted internal rate of return of a series of periodic cash flows. MIRR also considers investment costs and interest received when reinvested.
Example of the MIRR function
Exploring Excel: Embrace the MIRR Function
Learn How to Use the MIRR Function in Excel
* Download Excel 2013
* Download Excel 2010
* Download Office 2007
* Download Office 2003
Syntax:MIRR(values, finance_rate, reinvest_rate)
In this context:
- Values: Refer to cells containing numerical values (payments - negative values and incomes - positive values), a mandatory parameter
+ Values must encompass both negative and positive values for the adjusted internal rate of return to be computed. Otherwise, the MIRR function reports #DIV /0! error.
- Finance_rate: The interest rate paid for the amount used in cash flows
- Reinvest_rate: The interest rate received from reinvested cash flows
Considering an Example:
Given the spreadsheet below, with data entered corresponding to the parameters of the MIRR function in Excel
Requirements:
- Calculate the rate of return on investment after 5 years
- Calculate the rate of return on investment after 3 years
- Calculate the rate of return after 5 years based on a reinvestment rate of 14%
- To compute the rate of return on investment after 5 years. Enter the formula in cell C14 =MIRR(C6:C11,C12,C13). The result is as shown below:
- To compute the rate of return on investment after 3 years. Enter the formula in cell C15 =MIRR(C6:C9,C12,C13). The result is as shown below:
