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


define petty cash booktable pvifaprojected balance sheet templatevalue of a perpetuity formulacalculate the contribution margin ratiojournal entry deferred revenueannuity excel formulalabour rate variancenet profit sales ratiorecording depreciation expensegross profit margin formula excelprovisioning accountingaccount payable formulapv excel formulabasic accounting tutorialwhat is transposition in accountingrestaurant accounting chart of accountsallowance for doubtful accounts ratioperpetual inventory templatestraight line method of depreciation examplebad debt expense adjusting entryaccrued expenses journal entry examplehow to calculate operating leveragecash conversion period formuladouble entry records for depreciationbookkeeping skills testsample statement of retained earningspayback period formula uneven cash flowscreditor definition accountingannuity fv calculatorwhat is imprest fundliquidity ratio equationasset retirement obligation journal entry examplesundry payments definitiondepreciation ddbimprest accountlabour rate varianceaccounting for accrued vacationexcel npv calculationequation for depreciationcalculating declining balance depreciationcalculator present value of annuityexamples of cash receipts journalpremium bonds payabledouble entry records for depreciationhow do you find ending inventoryaccounting adjusting entries rulesbookkeeping ledger templateaccounts payable debit balanceskillcheck online testing answersmarkup accounting formulaaccounts receivable debit or credit normal balancegaap chart of accounts numberingcalculating pay back periodposting journal entries to t accountswhat is efficiency ratiosbookkeeping process flowdoes accumulated depreciation go on the balance sheetsuspense paymentcogs and inventorydefinition of accrued expensesannuity factor table excelannuity pv tableunits of production calculatortemplate for petty cashaccounts payable salary ukpvifa tablesaccrued expencemonthly bookkeeping templatepayment chart templaterecording payroll journal entriesaccounting quizzeswhy accumulated depreciation is credittotal variable costs formulaperpetual accounting journal entriesspreadsheets for small businesstypes of subsidiary ledgersnoninterest bearing noteannuity calculation in excelwhy do you prepare a trial balanceaccruals examples