# Would someone please have a look at my simple npr / irr calc and let me know if it seems correct?

#### jc65046

##### New Member
Hello!

I've read through several posts on here that I found by searching google and I believe I've properly applied the excel NPV / IRR formulas to my cash flow equation. But would someone please confirm?

I couldn't figure out how to import my excel screen, so here are the details and what I calculated.

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. I've seen some other spreadsheets where they take the excel IRR formula and multiply by 12. Why would they do this?

Thank you!!!
Alex

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Because the rate is 10% per period, and the period is a month, so the annual rate is ...

117%?!?!?! The actual IRR was 9.74%, so I multiplied by 12 to get the 117

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.

Last edited:
This was incredibly helpful. Much appreciated. My boss isn't very familiar with excel and I'm not very familiar with financial models, so it was just a misunderstanding. I really appreciate your time and help!

Thanks!!!

Replies
8
Views
11K
Replies
2
Views
5K
Replies
0
Views
879
Replies
0
Views
1K

### Forum statistics

1,196,274
Messages
6,014,394
Members
441,818
Latest member
itsfaisalkhalid ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back