Formula and 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.
| 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.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Double Entry Bookkeeping. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a degree from Loughborough University.