Submitted To:- Submitted By:-
Ms Smriti Saloni
M.Com 1
Roll.no. 8440
INTRODUCTION
• The Modified Internal Rate of Return (MIRR) is a function in Excel that takes
into account the financing cost (cost of capital) and a reinvestment rate
for cash flows from a project or company over the investment’s time horizon.
• The standard Internal Rate of Return (IRR) assumes that all cash flows received
from an investment are reinvested at the same rate. The Modified Internal
Rate of Return (MIRR) allows you to set a different reinvestment rate for cash
flows received. Additionally, MIRR arrives at a single solution for any series
of cash flows, while IRR can have two solutions for a series of cash flows that
alternate between negative and positive.
WHAT IS THE MIRR FORMULA IN
EXCEL?
• The MIRR formula in Excel is as follows:
• =MIRR(cash flows, financing rate, reinvestment rate)
• Where:
• Cash Flows – Individual cash flows from each period in the series
• Financing Rate – Cost of borrowing or interest expense in the event of
negative cash flows
• Reinvestment Rate – Compounding rate of return at which positive cash flow is
reinvested
AN EXAMPLE OF MIRR
• Below is an example that provides the most clear-cut example of how MIRR
differs from standard IRR.
• In the example below, we imagine two different projects. In both cases the
total amount of cash received over the investment’s life is the same – the only
difference is the timing of those cash flows.
• Here are the key assumptions:
• Initial investment: $1,000 (same in both projects)
• Major positive cash flow: $1,750 (same in both cases)
• Timing of major cash flow: last year in Project 1; first year in Project 2
• Reinvestment rate for MIRR: 0%
• As you can see in the image above, there is a major difference in the return
calculated by MIRR and IRR in Project #2. In project #1, there is no
difference.
• Let’s break down the reasons why.
MIRR PROJECT #1
• In Project #1, essentially all cash flow is received at the end of the project, so
the reinvestment rate is irrelevant.
• It’s important to show this case to clearly illustrate that reinvestment doesn’t
matter when a project only has one final cash flow. Examples would be a
zero-coupon bond or a Leveraged Buyout (LBO) where all cash flow is used to
service debt until the company is sold for one large lump sum.
MIRR PROJECT #2
• In Project #2, by contrast, essentially all of the cash flow is received in year
one. This means that the reinvestment rate is going to play a big role in the overall
IRR of the project.
• Since we set the reinvestment rate for MIRR to 0%, we can make an extreme
example to illustrate the point.
• The life of the investment is 7 years, so let’s look at what each result is saying.
• MIRR is saying that, if you invested $1,000 at 8% for 7 years you would have
$1,756 by the end of the project. If you sum up the cash flows in the example, you
get $1,756, so this is correct.
• So, why is the IRR result of 75% saying?
Clearly, it’s not saying that if you
invested $1,000 at 8% for 7 years you would have $50,524.
• Recall that IRR is the discount rate that sets the Net Present Value (NPV) of an
investment to zero. So, what the IRR case is saying is simply that discounting
the $1,750 cash flow in year one needs to be discounted by 75% to arrive at
an NPV of $0.
WHAT IS THE DATA BASE
FUNCTION?
• The DB Function is an Excel Financial function. This function helps in calculating
the depreciation of an asset. The method used for calculating depreciation is
the Fixed Declining Balance Method for each period of the asset’s lifetime.
• Formula
• =DB(cost, salvage, life, period, [month])
HOW TO USE THE DB
FUNCTION IN EXCEL?
• Assume we wish to calculate the depreciation for an asset with an initial cost
of $100,000. The asset’s salvage value after 5 years is $10,000.
• We will calculate the depreciation for all five years. The formula used is:
• The month argument was left blank, so the function would assume it as 12. For
each year, we will only change the period, as shown below:
• The DB function uses the fixed-declining balance method to compute the
asset’s depreciation at a fixed rate. The formula used by DB to calculate
depreciation for a period is:
• =(Cost – Total depreciation from prior periods) * Rate
