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


ledger sheet templatepetty cash report formatcommon stockholders equityaccounting for revolving credit facilityeffective interest rate calculation in excelvoucher sample formatfifo and lifo in accountingaccrued vacation journal entryintangible assets balance sheet exampledefine debtorpv of perpetuity formulafiguring gross profitmandatory redeemable preferred stockmarkup calculator percentagedouble entry for provision for bad debtsmaterial quantity variance formulabalance sheet debtorspurchase return debit notepurpose of remittance adviceroyalty accounting notesexcel formula irrexamples of accruals in accountingcheque voucher templatejournal entry for creating provision for doubtful debtsthe balance of a control account in the general ledgerimprestsannualization calculatorwhat is remittance advice slipprovision for uncollectible accountsformula for pmtreceivables ledger control accountwarranty expense accountingpetty cash control accountjournal entry for advance paymentdefinition of gearing ratioimplicit calculatortable of future value annuity factorslifo reservegross profit formula calculatorcash receipt journal entrytrial balance dividendspresent worth exceljournal entries for revenue recognitionexample of npv calculation in excelexamples of bookkeeping spreadsheetsthe basic accounting equation issmall business excel spreadsheetexcel formula future valuecalculate activity based costing examplestandard chart of accounts for restaurantsprofitability index ruleledger examplesaccumulated depreciation is what type of accounthow to calculate profit markupwhat is the normal balance for accounts payablejournal entry for invoicehow to record collection of accounts receivablepetty cash claim formending inventory formula accountinga chart of accounts for a merchandising businessdeferrals in accountinguses of bank reconciliation statementthe amount of a promissory note is called theroyalty payment accountingwhat is objectivity principle in accountingdefine deferred incomewhat are retained earningequation for annuityshipping fob meanssteps in accounting cycleprofit percentage formula in excelfob basis meansannuity factor calculatorimpairment intangible assetsperpetuity growth model formula