MIRR Formula

Formula

MIRR = (FV +ve cash flows/-PV -ve cash flows )1/n - 1
Where
MIRR = Modified internal rate of return
FV = Future value of positive cash flows at the reinvest rate (r)
PV = Present value of negative cash flows at the finance rate (f)
n = number of periods

Use

The MIRR formula is used to calculate the rate of return for a project taking into account the finance cost (f) of the cash used to fund the project (negative cash flows), and the rate of return (r) on cash from the project (positive cash flows) reinvested elsewhere.

Excel Function

The Excel MIRR function can be used instead of the modified internal rate of return formula, and has the syntax shown below.

MIRR(Range of cash flows,f,r)

MIRR Formula Example

A business is evaluating a project which will have the cash flows shown in column 2 below at the end of the year shown in column 1. The business can borrow funds for the project at a rate of 6% (f), and can reinvest any funds received from the project at a reinvest rate of 3% (r).

To calculate the FV of the positive cash flows, each positive cash flow is compounded to the end of year five using the reinvest rate of 3% in the future value of a lump sum formula. For example, year 1 cash flow of 650 is compounded forward 4 years to the end of year 5 to give FV = 650 x (1+3%)4 =731.58. This is shown in column 3.

To calculate the PV of the negative cash flows, each negative cash flow is discounted back to the start of year 1 (today) using the finance cost rate of 6% (f) in the present value of a lump sum formula. This is shown in column 4.

Cash flows used in MIRR Formula
Yr Cash flow FV 3% PV 6%
0 -1,500.00 -1,500.00
1 650.00 731.58
2 525.00 573.68
3 480.00 509.23
4 450.00 463.50
5 -280.00 -209.23
2,277.99 -1,709.23

Using the totals of the FV and PV columns from the table, the MIRR formula is used to compute the modified internal rate of return as follows:

MIRR = (FV +ve cash flows/-PV -ve cash flows )1/n - 1
FV +ve cash flows at the reinvest rate (3%) = 2,277.99
PV -ve cash flows at the finance rate (6%) = -1,709.23
n = number of periods = 5 years
MIRR = (2,277.99 /-(-1,709,23) )1/5 - 1
MIRR = (2,277.99/ 1,709.23)1/5 - 1
MIRR = 5.9133%

The MIRR formula gives an value of 5.9133%, the same answer can be obtained using the Excel MIRR function as follows:

MIRR = MIRR(Range of cash flows,f,r)
f = 6%
r = 3% 
MIRR = MIRR({-1500;650;525;480;450;-280},6%,3%)
MIRR = 5.9133%

The MIRR formula is one of many used in time value of money calculations, discover another at the links below.

MIRR Formula November 6th, 2016Team

You May Also Like


Related pages


accounts ledgersjournal entry for retained earningsledger template excelpv table annuitywhat is reducing balance methodstraight amortizationmirr financenetbook valuewhat does fob destination meanlcm in accountingcash book reconciliation templateaccounting debits creditsperpetuity annuity formulaadjustment journal entrieswhat are temporary accounts in accountingfinancial leverage ratio definitioncost of finished goods manufactured formuladso ratioaccounts payable reconciliation templatefuture value of a perpetuity formuladebit and credit entries in accountspercentage of completion method of accountingunrealized loss journal entryallowance for doubtful accounts is a contra asset accountsmall business bookkeeping formsdouble entry for depreciationpmt rate nper pvwhat is three column cash bookaccruals journal entrypv calculation exceldepreciation ddbdefine reversing entriesliquidity ratio formula examplesthe balance in premium on bonds payablestandard chart of accounts for restaurantsmargin markupconsignee account numberprepaid expenses journal entryrental bookkeepinggross profit calculation in excelcapital lease accounting treatmentgearing formulafob accounting definitionwhat is fifo method in accountingpresent value annuity factor formulaformula for future value of an annuityunder applied overheadjournal entry stock dividendmarkup cost formulafob shipping meansleverage ratio formula for banksfuture value annuity chartexamples of ledgersformula for compounded continuouslydifference between markup and marginworksheet in accountingpresent value of lump sumpayroll entries general ledgerbookkeeping invoicedouble entry bookeepingaccrude expensecontra receivabledefinition of post closing trial balanceaccounting for damaged inventorywhat is uncredited chequehow to calculate cash discounthow to calculate margin and markupdebtors definition in accountingblank trial balance sheetoperating leverage factor formulasalary received journal entryrule of 78 sales calculator