Monthly IRR discounts cash flows to the wrong NPV (not zero)

I want to prove ((1+IRR(range))^12)-1 is an accurate formula to produce an annualized IRR of monthly cash flows. To do so I used it to discount the cash flows to achieve a result of zero ...but got a different answer.

Here's the number set, beginning in cell A1:

A1 (100)
A2 10
A3 12
A4 14
A5 16
A6 18
A7 20
A8 22

This formula ((1+IRR(A1:A8,31%))^12)-1 results in 35.73%. I should be able to use this for my discount rate in this formula NPV(30.95%/12,A2:A8)+A1 to produce a NPV of 0.00. Instead, the only discount rate that works is 30.95%.

Why?

(Note - You may notice I tried to "guess" a result of 31% and trick it into getting closer to the "right" result.)

Incidentally, NPV(30.95%/12,A1:A8) also works, though this is against Microsoft instructions to leave the amount for Period 1 outside the parentheses. Odd.

You had calculated an annualized IRR for your cash flows which comes out to be 35.73%
The periodic IRR is 2.578%

And when you are calculating NPV you are making an error in using the discount rate

NPV for the cash flows will be zero at the periodic IRR of 2.578%

So to get this periodic IRR of 2.578% from the annualized IRR of 35.73%, you would have to de-annualize it
If cell A10 contains your IRR formula then the following formula for NPV would get you an NPV of zero

=NPV((1+A10)^(1/12)-1,A2:A8)+A1

Originally Posted by pullchalks

This formula ((1+IRR(A1:A8,31%))^12)-1 results in 35.73%. I should be able to use this for my discount rate in this formula NPV(30.95%/12,A2:A8)+A1 to produce a NPV of 0.00. Instead, the only discount rate that works is 30.95%.

Why?

(Note - You may notice I tried to "guess" a result of 31% and trick it into getting closer to the "right" result.)

Guess is irrelevant here you could have skipped it and it would still get you the same IRR

30.95% is the annual IRR that is calculated by multiplying periodic IRR by 12

2.578% x 12 = 30.94%

Thus your formula of NPV(30.95%/12,A2:A8)+A1 would result in an NPV of zero since 30.95%/12 = 2.578%

Annualized IRR is not the same as Annual IRR

Annualized IRR = (1+IRR)^12-1
Annual IRR = IRR x 12

Originally Posted by pullchalks

Incidentally, NPV(30.95%/12,A1:A8) also works, though this is against Microsoft instructions to leave the amount for Period 1 outside the parentheses. Odd.

NPV(30.95%/12,A1:A8) is the net present value of an ordinary annuity where the first cash flow is discounted at time period 1

NPV(30.95%/12,A2:A8)+A1 is the net present value of an annuity due where the first cash flow is discounted at time period 0

Both these would result in an NPV of zero at the IRR of 2.578%

IRR for an ordinary annuity with end of period cash flows and IRR for an annuity due with start of period cash flows is the same

Try this for annuity due with start of period cash flows where first cash flow occurs at time period 0

A1 (100)
A2 10
A3 12
A4 14
A5 16
A6 18
A7 20
A8 22

=IRR(A1:A8)
IRR = 2.578%

Now we shift the first cash 1 period to the right at time period 1 to make it an ordinary annuity with end of period cash flows where first cash flow occurs at time period 1

A1 0
A2 (100)
A3 10
A4 12
A5 14
A6 16
A7 18
A8 20
A9 22

=IRR(A1:A9)
IRR = 2.578%

It has the same IRR as annuity due

Excel NPV function only permits calculation of net present value of an ordinary annuity that has end of period payments meaning first cash flow is discounted at time period 1

You can work around it by skipping the first cash flow to the NPV function and adding back the first cash flow to the results of NPV function

To address the limitation of Excel's NPV function, you could instead try out tadNPV function that is part of TADXL Excel add-in for finance

tadNPV permits you to specify whether the cash flows make start of period or end of period payments. It further lets you select the type of compounding for interest whether discrete or continuous

Thanks, very helpful.

I don't understand why the IRR is the same regardless of whether the first payment is made at the end of Period 0 or Period 1. If each period is one month, I should think 30 days matters, whether at the beginning of the string of numbers or at the end.

Originally Posted by pullchalks

Thanks, very helpful.

I don't understand why the IRR is the same regardless of whether the first payment is made at the end of Period 0 or Period 1. If each period is one month, I should think 30 days matters, whether at the beginning of the string of numbers or at the end.

This needs a bit of clarification.


T = 0 refers to the time at present as of NOW
T = 1 refers to the end of the period where period may refer to end of day, end of week, end of month

Let me further elaborate with examples


When you open a savings account you make deposits at start of period such as the first day you opened the account. Here the first deposit is recorded at Time Period 0. This is an annuity due with start of period payments. Another example of annuity due is the house rent one pays at the 1st of each month. Thus making such stream of payments an annuity due

When you take a loan on house or car, you receive the loan amount immediately from the lender. The repayments begin 1 period from T = 0 such as end of month periodic repayments. This is an ordinary annuity with end of period payments.

Just to add couple more cents to what has already been stated. The internal rate of return calculated with Excel's IRR function assumes interest is compounded discretely. Discrete compounding refers to per period compounding such as daily, weekly, fortnightly, biweekly, monthly, quarterly, or annually. Yet in finance and banking applications, interest is compounded continuously in a good number of situations such as with Savings account.

tadIRR Excel function is an alternative IRR function that calculates internal rate of return for both cases when interest is either compounded discretely or compounded continuously. See the two different IRR values below for your data. With continuous compounding of interest, IRR is lesser than the IRR value with discrete compounding

=tadIRR(A1:A8,,0)
Periodic IRR = 2.5782215%
Annualized IRR = 35.73%

=tadIRR(A1:A8,,1)
Periodic IRR = 2.5455458%
Annualized IRR = 35.21%

For your cash flows of
-100 10 12 14 16 18 20 22

NPV of annuity due at IRR of 2.5455458% using continuous compouding of interest

Year Payment PVIF @ 2.5455458% Present Value
0 -100.00 1.000000 -100.00
1 10.00 0.974866 9.75
2 12.00 0.950363 11.40
3 14.00 0.926477 12.97
4 16.00 0.903190 14.45
5 18.00 0.880489 15.85
6 20.00 0.858359 17.17
7 22.00 0.836785 18.41
Net Present Value (Annuity Due) T= 0 to N-1 -0.00

NPV of ordinary annuity at IRR of 2.5455458% using continuous compouding of interest
Year Payment PVIF @ 2.5455458% Present Value
1 -100.00 0.974866 -97.49
2 10.00 0.950363 9.50
3 12.00 0.926477 11.12
4 14.00 0.903190 12.64
5 16.00 0.880489 14.09
6 18.00 0.858359 15.45
7 20.00 0.836785 16.74
8 22.00 0.815753 17.95
Net Present Value (Ordinary Annuity) T= 1 to N -0.00

Excel's NPV function too has the same assumption of discrete compounding of interest. tadNPV Excel function extends the functionality and permits finding net present value not only for ordinary annuity but also for annuity due. It also permits you to specify compounding of interest.