Annual rate = 15%
Monthly rate = 1.25%
Term = 24 months
Initial cash outlay = $550
Monthly cash in = $60 (total $1,440 after 24 months)
NPV = $687.45
IRR = 10%
My boss said this IRR is way too low and something isn't correct.
Your "boss" is incorrect: there is nothing wrong with an IRR of 9.74% (approximately). That is indeed the correct
monthly IRR.
She simply neglected to tell you -- or you neglected to note -- that she wants the
annual IRR.
There is nothing sacrosanct about an annual IRR. That is, an IRR can be monthly or annual -- or any other period, for that matter -- just as the discount rate can monthly or annual, like your data above.
However, the monthly IRR can be annualized two different ways: multiplying by 12, as you did ("simple" annual IRR); or compounding, as Excel XIRR does effectively.
Both methods are valid. For example, a loan APR is the annual IRR of the cash flows. In the US et al, the APR is the "simple" annual IRR. In the UK and other EU countries, the APR is the compounded annual IRR.
See the difference below.
| A
| B
| C
|
|
1
| annl rate
| 15.00%
|
|
|
2
| mon rate | 1.25% |
| B2: =B1/12 |
3
| term | 24 | months |
|
4
| init outflow | 550.00 |
|
|
5
| mon inflow | 60.00 |
|
|
6
| NPV | 687.45 |
| B6: =-B4-PV(B2,B3,B5) |
7
| mon IRR | 9.74%
|
| B7: =RATE(B3,B5,-B4) |
8
| annl IRR | 116.83% | simple | B8: =B7*12 |
9
|
| 204.91% | compounded | B9: =(1+B7)^12-1 |
<tbody>
</tbody>
Note that NPV and monthly IRR can be directly. But you might have created the following cash flow table (in E1:E25). By adding arbitrary dates in F1:F25, that also allows us to use Excel XIRR to confirm the compounded annual IRR derived from the monthly IRR.
| E
| F
| G
| H
|
|
1
| -550.00
| 12/1/2014 | NPV | 687.45 | H1: =E1+NPV(B2,E2:E25) |
2
| 60 | 1/1/2015 | mon IRR | 9.74% | H2: =IRR(E1:E25) |
3
| 60 | 2/1/2015 | XIRR (annl) | 204.92% | H3: =XIRR(E1:E25,F1:F25) |
|
|
|
|
|
|
24
| 60 | 11/1/2016 |
|
|
|
25
| 60 | 12/1/2016 |
|
|
|
<tbody>
</tbody>
Note: The Excel XIRR is not always so close to the annualized monthly IRR. For these numbers, Excel XIRR can return between 203.24% and 207.04% (approximately), depending on the initial date. I chose an initial date that yields a close XIRR, by coincidence.
117%?!?!?! The actual IRR was 9.74%, so I multiplied by 12 to get the 117
You or your "boss" calculated the monthly discount rate (1.25%) by dividing the annual rate (15%) by 12.
So it should be no surprise that you might annualize the monthly IRR by multiplying by 12 ("simple" annual IRR).
But did your "boss" give you the monthly discount rate or tell you to divide the annual rate by 12? Or was that just an assumption that you made?
In either case, it would be prudent to ask your "boss" if she wants the compounded annual IRR instead of the "simple" annual IRR.
And if she does, perhaps the monthly discount rate should be =(1+B1)^(1/12)-1 instead of =B1/12, for consistency.
