Manuals/calci/MIRR

MIRR (ValuesArray,FinanceRate,ReinvestmentRate)

• $ValuesArray$ is the array of values.
• $FinanceRate$ is the finance rate.
• $ReinvestmentRate$ is the reinvestment rate.
  • MIRR(), returns the internal rate of return where positive and negative cash flows are financed at different rates.

Description

• This function gives the value of the modified internal rate of return for a particular cash flows.
• $MIRR$ is a modification of the internal rate of return.
• The internal rate of return (IRR) assumes the cash flows from a project are reinvested at the IRR, the modified IRR assumes that positive cash flows are reinvested at the firm's cost of capital, and the initial outlays are financed at the firm's financing cost.
• So $MIRR$ more accurately reflects the cost and profitability of a project.
• $MIRR(ValuesArray,FinanceRate,ReinvestmentRate)$, $ValuesArray$ is the array of cash flow values.
• $FinanceRate$ is the finance rate which is the interest rate we pay on the money used in the cash flows or it is the cost of capital.
• $ReinvestmentRate$ is the interest rate which we receive on the cash flows as we reinvest in to that.
• For $MIRR$ calculation involves two rates – finance rate, which is your weighted average cost of capital and reinvest rate.
• Also $MIRR$ calculation is having the following rules.

  1.The cash flows must occur at regular intervals, but do not have to be the same amounts for each interval.
  2.projects with MIRR greater the project's hurdle rate should be accepted; while in case of mutually exclusive projects, the project with higher MIRR should be preferred. 

• The formula for $MIRR$ is :

$\left({\frac {-NPV(rrate,values[positive])*(1+rrate)^{n}}{NPV(frate,values[negative])*(1+frate)}}\right)^{\frac {1}{n-1}}-1$ where $rrate$ is the reinvestment rate,$frate$ is the finance rate,$values[positive]$ is the positive values in the array only,$values[negative]$ is the negative values in the array only,$n$ is the number of periods and $NPV()$ is the CALCI $NPV$ function.

• This function will give the result as error when

  1.Any one of the argument is nonnumeric. 
  2.ar is not containing the atleast one positive value and one negative value.

Examples

Spreadsheet

	A	B
1	-250000	-8000000
2	20000	90000
3	30500	25000
4	25000	20000
5	37000	10000
6	28000	5000
7	11%	10%
8	12%	9%

1. MIRR(A1:A6,A7,A8)=-0.07
2. MIRR(B1:B6,B7,B8)=-0.52
3. MIRR([-8000000,90000,25000,20000,10000,5000],10%,9%) = -0.52

Related Videos

MIRR

See Also

• RATE
• XIRR
• XNPV

References

• Modified internal rate of return

• List of Main Z Functions

• Z3 home