Hello all,
I have a problem with a discrepancy between two different IRRs (calculated
on the same cash flow but in two different ways) that, in my opinion should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will send over
the excel sheet with the problem. Please note that a basic understanding of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.

"Ben" wrote:
I have a problem with a discrepancy between
two different IRRs (calculated on the same cash
flow but in two different ways) that, in my opinion
should be exactly the same.

I responded in email. But if you comment here, it might help others as well.

What are the two different methods that you used? What are the two results
that you got?

If we knew that, I suspect we can offer educated "guesses" that might
explain the difference.

How many data points are we talking about?

If they are a few dozen or less, perhaps you could simply post the data in a
response. Alternatively, perhaps you can demonstrate __a__ discrepancy
(albeit not the same one) with a lot less data.

Most of the time, that is sufficient for us to explain discrepancies.


----- original message -----

"Ben" wrote:
Hello all,
I have a problem with a discrepancy between two different IRRs (calculated
on the same cash flow but in two different ways) that, in my opinion should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will send over
the excel sheet with the problem. Please note that a basic understanding of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.

I believe Ben's problem is a financial math question, not an Excel question,
having looked at his worksheet. I could use help in trying to answer the
financial math question.

The problem can be described as follows. I have changed some numbers
because with Ben's original numbers, the values were so close that their
difference might be mistaken for computer arithmetic anomalies, which they
are not.

Consider two loans, started at the same time.

Loan A for $50,000 (A1) at 10% (A2) annual rate for 120 (A3) monthly
payments. The monthly rate is A2/12 (A4), and the monthly payment (A5) is
PMT(A4,A3,-A1).

Loan B for $200,000 (B1) at 5% (B2) annual rate for 240 (B3) monthly
payments. The monthly rate is B2/12 (B4), and the monthly payment (B5) is
PMT(B4,B3,-B1). The remaining balance (B6) after loan A is paid off is
FV(B4,A3,B5,-B1).

Questions:

1. What is the average monthly rate of the combined loans, if both loans run
to term?

2. What is the average monthly rate of the combined loans if loan B is paid
off early when loan A is paid off?

3. #1 = #2 when interest rates are equal (A2 = B2). Why is #1 < #2 when A2
B2, and why is #1 #2 when A2 < B2?

Ben already answered #1 and #2 correctly. The remaining question is #3.

Ben uses IRR to answer #1. If E1 is =-A1-B1, E2:E121 is =$A$5+$B$5, and
E122:E241 is =$B$5, then IRR(E1:E241,0.5%) in B8 is the average monthly
rate.

Is there a better way?

The answer to #2 can be computed by RATE(A3,A5+B5,-A1-B1,B6) in B9. Ben
also uses IRR, presumably to confirm the RATE formula. If F1 is =E1, F1 is
copied through F120, and F121 is =E121+B6, then IRR(F1:F121,0.5%) in B10 is
the same as the RATE result, as expected.

When A2 and B2 are both 10%, B8 and B9 are both about 0.8333%. But when A2
is 10% and B2 is 5%, B8 is about 0.4709% and B9 is about 0.4822%. And when
A2 is 5% and B2 is 10%, B8 is about 0.7817% and B9 is about 0.7725%.

Why are B8 and B9 unequal when A2 and B2 are unequal, and how would we
predict which average rate (B8 or B9) is higher?


----- original message -----

"Ben" wrote in message
...
Hello all,
I have a problem with a discrepancy between two different IRRs (calculated
on the same cash flow but in two different ways) that, in my opinion
should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will send
over
the excel sheet with the problem. Please note that a basic understanding
of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.

Clarification....

I wrote:
1. What is the average monthly rate of the combined loans,
if both loans run to term?

I got ahead of myself. I probably should use the term "effective monthly
rate" or "rate of return", as Ben did, instead of "average monthly rate".

Also, I should note that there is a good chance that this might be the
purpose of a homework assignment, although Ben claims that he ran "this
problem by my instructor as well as had a class discussion about it, and WE
could not come up with a suitable explanation".

So if you are skittish about the possibility of handing someone the answer
to an assignment, feel free to email your ideas to me directly. The problem
has piqued my curiosity, too ;-). My email address is joeu2004 "at"
hotmail.com.


Discussion....

Mathematically, it does not surprise me that the IRR for scenario #1 (full
term) and #2 (early payoff) would be the same when the individual interest
rates are the same. And I believe I can explain why the IRR would differ
when the interest rates are not the same.

But I am still at a loss to explain this in common-sense non-mathematical
terms based on time-value-of-money concepts.


For scenario #1 (both loans run to term), the monthly IRR can be computed by
solving for r1 in:

0 = -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i,
i=nA+1,nB}

for nB nA and prin = prinA+prinB.


And for scenario #2 (early payoff of loan B):

0 = -prin + SUM{(pmtA+pmtB)/(1+r2)^i, i=1,nA} + fvB/(1+r2)^nA

where fvB denotes the remaining balance of loan B after nA payments.


But note that fvB is simply the PV of the remaining payments. Thus:

fvB = SUM{pmtB/(1+rB)^i, i=1,nB-nA}

where rB denotes the monthly interest rate for loan B.


In the scenario #1 formula, if rA = rB (same interest rates), then r1 = rB.
I believe we can prove that rigorously by separating the cash flows; or we
can simply demonstrate that empirically.

So, starting with the scenario #1 formula:

0 = -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i,
i=nA+1,nB}

= -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i,
i=1,nB-nA} /(1+r1)^nA

= -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+rB)^i,
i=1,nB-nA} /(1+r1)^nA

= -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + fvB/(1+r1)^nA

The last formula is in the same form as the formula for scenario #2. So r1
= r2.


However, when rA < rB, we expect r1 < rB in scenario #1. So we cannot
simplify the formula for scenario #2. But we can put it into a
more-conventional IRR form, to wit:

0 = -prin + SUM{(pmtA+pmtB)/(1+r2)^i, i=1,nA} + fvB/(1+r2)^nA

= -prin + SUM{(pmtA+pmtB)/(1+r2)^i, i=1,nA-1} + (pmtA+pmtB+fvB)/(1+r2)^nA

It should be clear that r1 < r2, if for no other reason than there are
fewer cash flows and the last cash flow does not equate to the "tail" of the
scenario #1 cash flows.

I think this form can also explain how whether r1 < r2 or r1 r2 depends
not on the relationship between rA and rB (or r1 and rB, which I believe is
related). But I'm still working on how to explanation that clearly.


----- original message -----

"Joe User" <joeu2004 wrote in message
...
I believe Ben's problem is a financial math question, not an Excel
question, having looked at his worksheet. I could use help in trying to
answer the financial math question.

The problem can be described as follows. I have changed some numbers
because with Ben's original numbers, the values were so close that their
difference might be mistaken for computer arithmetic anomalies, which they
are not.

Consider two loans, started at the same time.

Loan A for $50,000 (A1) at 10% (A2) annual rate for 120 (A3) monthly
payments. The monthly rate is A2/12 (A4), and the monthly payment (A5) is
PMT(A4,A3,-A1).

Loan B for $200,000 (B1) at 5% (B2) annual rate for 240 (B3) monthly
payments. The monthly rate is B2/12 (B4), and the monthly payment (B5) is
PMT(B4,B3,-B1). The remaining balance (B6) after loan A is paid off is
FV(B4,A3,B5,-B1).

Questions:

1. What is the average monthly rate of the combined loans, if both loans
run to term?

2. What is the average monthly rate of the combined loans if loan B is
paid off early when loan A is paid off?

3. #1 = #2 when interest rates are equal (A2 = B2). Why is #1 < #2 when
A2
B2, and why is #1 #2 when A2 < B2?

Ben already answered #1 and #2 correctly. The remaining question is #3.

Ben uses IRR to answer #1. If E1 is =-A1-B1, E2:E121 is =$A$5+$B$5, and
E122:E241 is =$B$5, then IRR(E1:E241,0.5%) in B8 is the average monthly
rate.

Is there a better way?

The answer to #2 can be computed by RATE(A3,A5+B5,-A1-B1,B6) in B9. Ben
also uses IRR, presumably to confirm the RATE formula. If F1 is =E1, F1
is copied through F120, and F121 is =E121+B6, then IRR(F1:F121,0.5%) in
B10 is the same as the RATE result, as expected.

When A2 and B2 are both 10%, B8 and B9 are both about 0.8333%. But when
A2 is 10% and B2 is 5%, B8 is about 0.4709% and B9 is about 0.4822%. And
when A2 is 5% and B2 is 10%, B8 is about 0.7817% and B9 is about 0.7725%.

Why are B8 and B9 unequal when A2 and B2 are unequal, and how would we
predict which average rate (B8 or B9) is higher?


----- original message -----

"Ben" wrote in message
...
Hello all,
I have a problem with a discrepancy between two different IRRs
(calculated
on the same cash flow but in two different ways) that, in my opinion
should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will send
over
the excel sheet with the problem. Please note that a basic understanding
of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.

I have a problem with a discrepancy between two different IRRs
(calculated on the same cash flow but in two different ways) that, in my
opinion should be exactly the same.


Hi. I'll try my best here.

For #1, all payments after time 120 were brought back to time 0 at the
unknown irr rate. The higher rate for the Home Equity Loan was spread
out from 10 years, to 20 years. Hence, the IRR for IRR #1 should be lower.

Mathematically, all payments from 121 to 240 were brought back to time
120 at the IRR rate, and then brought back to time 0 at the IRR rate also.

However, for #2, you used the remaining loan balance at time 120. You
brought all payments after 120 to time 120 at the known mortgage rate,
and then brought it to time 0 at the IRR rate. The high Equity Loan is
not part of any calculations after time 120 as in the first example.
These two differences are why they are different.

Hope I said this right. :)

Dana DeLouis



On 12/23/09 7:10 PM, Ben wrote:
Hello all,
I have a problem with a discrepancy between two different IRRs (calculated
on the same cash flow but in two different ways) that, in my opinion should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will send over
the excel sheet with the problem. Please note that a basic understanding of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.

To summarize:

a. In scenario #1, both loans run full term.

b. In scenario #2, the shorter loan runs full term, and the longer loan is
paid off early at the same time.

c. Empirically, we find that when the loan interest rates are equal, the
IRRs for scenarios #1 and 2 are equal.

d. Empirically, we find that when the loan interest rates are different, the
IRRs for scenarios #1 and 2 are different.

Previously, I offered mathematical proofs to explain of #c and #d. But they
depended on assertions that I left unproven. To clean up the sloppiness
in the previous proofs....


In the scenario #1 formula, if rA = rB (same interest rates), then
r1 = rB.

This seems to be the most intuitive assertion, yet oddly I am having trouble
with the proof.

0 = -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA}
+ SUM{pmtB/(1+r1)^i, i=nA+1,nB}

= -(prinA + prinB) + SUM{pmtA/(1+r1)^i, i=1,nA}
+ SUM{pmtB/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i, i=nA+1,nB}

= -prinA + SUM{pmtA/(1+r1)^i, i=1,nA} +
-prinB + SUM{pmtB/(1+r1)^i, i=1,nB}


Note the following formulas, which are fundamental to loan arithmetic (the
loan amount is the NPV of the payments discounted at the interest rate):

[1] 0 = -prinA + SUM{pmtA/(1+rA)^i, i=1,nA}

[2] 0 = -prinB + SUM{pmtB/(1+rB)^i, i=1,nB}


Since rA = rB, then r1 = rB = rA is at least one solution for the scenario
#1 formula, since 0 = 0 + 0.

Arguably, that is only a "sufficient" proof, not a "necessary and
sufficient" proof. Theoretically, some IRR formulas can have more than one
solution. But I believe we should have only one IRR solution in each of
these scenarios since the sign of the cash flows changes only once.

BTW, I am talking about mathematical equality. There can always be
infinitesimal inequalities that are artifacts of floating-point
computations.


when rA < rB, we expect r1 < rB in scenario #1.

Proof by contradiction; that is, assume r1 = rB, and prove that contradicts
the requirement rA < rB.

0 = -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA}
+ SUM{pmtB/(1+r1)^i, i=nA+1,nB}

= -(prinA + prinB) + SUM{pmtA/(1+r1)^i, i=1,nA}
+ SUM{pmtB/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i, i=nA+1,nB}

= -prinA + SUM{pmtA/(1+r1)^i, i=1,nA} +
-prinB + SUM{pmtB/(1+r1)^i, i=1,nB}

= -prinA + SUM{pmtA/(1+r1)^i, i=1,nA} +
-prinB + SUM{pmtB/(1+rB)^i, i=1,nB}

Based on [2] above, the 2nd line is zero. So:

0 = -prinA + SUM{pmtA/(1+r1)^i, i=1,nA}

Since that has the same form as [1] above, then r1 = rA; and since r1 = rB,
then rA = rB. That contradicts our requirement (rA < rB). Ergo, r1 < rB
when rA < rB.


when rA < rB [....]
It should be clear that r1 < r2

Proof by contradiction; that is, assume r1 = r2, and prove that contracdicts
the requirement rA < rB.

The scenario #1 and 2 formulas are both equal to zero. So:

-prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i, i=nA+1,nB}
= -prin + SUM{(pmtA+pmtB)/(1+r2)^i, i=1,nA} + fvB/(1+r2)^nA

-prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i, i=nA+1,nB}
= -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + fvB/(1+r1)^nA

SUM{pmtB/(1+r1)^i, i=nA+1,nB}
= fvB/(1+r1)^nA

SUM{pmtB/(1+r1)^i, i=1,nB-nA}/(1+r1)^nA
= SUM{pmtB/(1+rB)^i, i=1,nB-nA}/(1+r1)^nA

That is true only if r1 = rB. But that contradicts our requirement
(rA < rB), since we proved above that if rA < rB, then r1 < rB
(and the contrapositive: if r1 = rB, then rA = rB). Ergo, r1 < r2
when rA < rB.


----- original message -----

"Joe User" <joeu2004 wrote in message
...
Clarification....

I wrote:
1. What is the average monthly rate of the combined loans,
if both loans run to term?

I got ahead of myself. I probably should use the term "effective monthly
rate" or "rate of return", as Ben did, instead of "average monthly rate".

Also, I should note that there is a good chance that this might be the
purpose of a homework assignment, although Ben claims that he ran "this
problem by my instructor as well as had a class discussion about it, and
WE could not come up with a suitable explanation".

So if you are skittish about the possibility of handing someone the answer
to an assignment, feel free to email your ideas to me directly. The
problem has piqued my curiosity, too ;-). My email address is joeu2004
"at" hotmail.com.


Discussion....

Mathematically, it does not surprise me that the IRR for scenario #1 (full
term) and #2 (early payoff) would be the same when the individual interest
rates are the same. And I believe I can explain why the IRR would differ
when the interest rates are not the same.

But I am still at a loss to explain this in common-sense non-mathematical
terms based on time-value-of-money concepts.


For scenario #1 (both loans run to term), the monthly IRR can be computed
by solving for r1 in:

0 = -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i,
i=nA+1,nB}

for nB nA and prin = prinA+prinB.


And for scenario #2 (early payoff of loan B):

0 = -prin + SUM{(pmtA+pmtB)/(1+r2)^i, i=1,nA} + fvB/(1+r2)^nA

where fvB denotes the remaining balance of loan B after nA payments.


But note that fvB is simply the PV of the remaining payments. Thus:

fvB = SUM{pmtB/(1+rB)^i, i=1,nB-nA}

where rB denotes the monthly interest rate for loan B.


In the scenario #1 formula, if rA = rB (same interest rates), then r1 =
rB. I believe we can prove that rigorously by separating the cash flows;
or we can simply demonstrate that empirically.

So, starting with the scenario #1 formula:

0 = -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i,
i=nA+1,nB}

= -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+r1)^i,
i=1,nB-nA} /(1+r1)^nA

= -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + SUM{pmtB/(1+rB)^i,
i=1,nB-nA} /(1+r1)^nA

= -prin + SUM{(pmtA+pmtB)/(1+r1)^i, i=1,nA} + fvB/(1+r1)^nA

The last formula is in the same form as the formula for scenario #2. So
r1 = r2.


However, when rA < rB, we expect r1 < rB in scenario #1. So we cannot
simplify the formula for scenario #2. But we can put it into a
more-conventional IRR form, to wit:

0 = -prin + SUM{(pmtA+pmtB)/(1+r2)^i, i=1,nA} + fvB/(1+r2)^nA

= -prin + SUM{(pmtA+pmtB)/(1+r2)^i, i=1,nA-1} +
(pmtA+pmtB+fvB)/(1+r2)^nA

It should be clear that r1 < r2, if for no other reason than there are
fewer cash flows and the last cash flow does not equate to the "tail" of
the scenario #1 cash flows.

I think this form can also explain how whether r1 < r2 or r1 r2 depends
not on the relationship between rA and rB (or r1 and rB, which I believe
is related). But I'm still working on how to explanation that clearly.


----- original message -----

"Joe User" <joeu2004 wrote in message
...
I believe Ben's problem is a financial math question, not an Excel
question, having looked at his worksheet. I could use help in trying to
answer the financial math question.

The problem can be described as follows. I have changed some numbers
because with Ben's original numbers, the values were so close that their
difference might be mistaken for computer arithmetic anomalies, which
they are not.

Consider two loans, started at the same time.

Loan A for $50,000 (A1) at 10% (A2) annual rate for 120 (A3) monthly
payments. The monthly rate is A2/12 (A4), and the monthly payment (A5)
is PMT(A4,A3,-A1).

Loan B for $200,000 (B1) at 5% (B2) annual rate for 240 (B3) monthly
payments. The monthly rate is B2/12 (B4), and the monthly payment (B5)
is PMT(B4,B3,-B1). The remaining balance (B6) after loan A is paid off
is FV(B4,A3,B5,-B1).

Questions:

1. What is the average monthly rate of the combined loans, if both loans
run to term?

2. What is the average monthly rate of the combined loans if loan B is
paid off early when loan A is paid off?

3. #1 = #2 when interest rates are equal (A2 = B2). Why is #1 < #2 when
A2
B2, and why is #1 #2 when A2 < B2?

Ben already answered #1 and #2 correctly. The remaining question is #3.

Ben uses IRR to answer #1. If E1 is =-A1-B1, E2:E121 is =$A$5+$B$5, and
E122:E241 is =$B$5, then IRR(E1:E241,0.5%) in B8 is the average monthly
rate.

Is there a better way?

The answer to #2 can be computed by RATE(A3,A5+B5,-A1-B1,B6) in B9. Ben
also uses IRR, presumably to confirm the RATE formula. If F1 is =E1, F1
is copied through F120, and F121 is =E121+B6, then IRR(F1:F121,0.5%) in
B10 is the same as the RATE result, as expected.

When A2 and B2 are both 10%, B8 and B9 are both about 0.8333%. But when
A2 is 10% and B2 is 5%, B8 is about 0.4709% and B9 is about 0.4822%. And
when A2 is 5% and B2 is 10%, B8 is about 0.7817% and B9 is about 0.7725%.

Why are B8 and B9 unequal when A2 and B2 are unequal, and how would we
predict which average rate (B8 or B9) is higher?


----- original message -----

"Ben" wrote in message
...
Hello all,
I have a problem with a discrepancy between two different IRRs
(calculated
on the same cash flow but in two different ways) that, in my opinion
should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will send
over
the excel sheet with the problem. Please note that a basic understanding
of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.

"Dana DeLouis" wrote:
Mathematically, all payments from 121 to 240 were brought back to time 120
at the IRR rate, and then brought back to time 0 at the IRR rate also.

However, for #2, you used the remaining loan balance at time 120. You
brought all payments after 120 to time 120 at the known mortgage rate, and
then brought it to time 0 at the IRR rate.

But if the longer (mortgage) loan rate is the same as the IRR in the second
case (early payoff of the longer loan), "all payments from 121 to 240 are
brought back to time 120 at the IRR rate, and then brought back to time 0 at
the IRR rate also", just as they are in the first case (both loans run to
term).


The high Equity Loan is not part of any calculations after time
120 as in the first example.

These two differences are why they are different.

But under some conditions, the two IRRs are the same, not different, despite
the fact that "the equity loan is not part of any calculations after time
120" in the second case.


I think you are assuming what it is that we want to explain namely: (a) the
longer (mortgage) loan rate is not the same as the second IRR, and (b) the
two IRRs are different, (c) both under some conditions to be specified.

Of course, I have "explained" all of this with rigorous math proofs. So we
know that the #a and #b are indeed true, and we know that #c is whenever the
loan interest rates are different.

But I am sure that everyone, myself included, would prefer a more
common-sense explanation, along the lines of yours, that does not already
make these assumptions.


----- original message -----

"Dana DeLouis" wrote in message
...
I have a problem with a discrepancy between two different IRRs
(calculated on the same cash flow but in two different ways) that, in my
opinion should be exactly the same.


Hi. I'll try my best here.

For #1, all payments after time 120 were brought back to time 0 at the
unknown irr rate. The higher rate for the Home Equity Loan was spread
out from 10 years, to 20 years. Hence, the IRR for IRR #1 should be
lower.

Mathematically, all payments from 121 to 240 were brought back to time 120
at the IRR rate, and then brought back to time 0 at the IRR rate also.

However, for #2, you used the remaining loan balance at time 120. You
brought all payments after 120 to time 120 at the known mortgage rate, and
then brought it to time 0 at the IRR rate. The high Equity Loan is not
part of any calculations after time 120 as in the first example.
These two differences are why they are different.

Hope I said this right. :)

Dana DeLouis



On 12/23/09 7:10 PM, Ben wrote:
Hello all,
I have a problem with a discrepancy between two different IRRs
(calculated
on the same cash flow but in two different ways) that, in my opinion
should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will send
over
the excel sheet with the problem. Please note that a basic understanding
of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.

I have a problem with a discrepancy between two different IRRs
(calculated on the same cash flow but in two different ways)
that, in my opinion should be exactly the same

I understand the question is "why are they different?"

They are different because in #1, you just used an IRR for all periods
1-240.

In #2, look at the equation in M122.

You used: ...NPV(B6/12,M123:$M$242)...

You used the Mortgage rate (B6) for perionds 121-240, and an IRR rate
for 1-120. This is one reason why they are different.

The NPV from above is just another way of returning the remaining
mortgage balance at time 120.

= = = = = = = = = =
HTH :)
Dana DeLouis



On 12/25/2009 6:10 PM, Dana DeLouis wrote:
I have a problem with a discrepancy between two different IRRs
(calculated on the same cash flow but in two different ways) that, in my
opinion should be exactly the same.


Hi. I'll try my best here.

For #1, all payments after time 120 were brought back to time 0 at the
unknown irr rate. The higher rate for the Home Equity Loan was spread
out from 10 years, to 20 years. Hence, the IRR for IRR #1 should be lower.

Mathematically, all payments from 121 to 240 were brought back to time
120 at the IRR rate, and then brought back to time 0 at the IRR rate also.

However, for #2, you used the remaining loan balance at time 120. You
brought all payments after 120 to time 120 at the known mortgage rate,
and then brought it to time 0 at the IRR rate. The high Equity Loan is
not part of any calculations after time 120 as in the first example.
These two differences are why they are different.

Hope I said this right. :)

Dana DeLouis



On 12/23/09 7:10 PM, Ben wrote:
Hello all,
I have a problem with a discrepancy between two different IRRs
(calculated
on the same cash flow but in two different ways) that, in my opinion
should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will
send over
the excel sheet with the problem. Please note that a basic
understanding of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.

"Dana DeLouis" wrote:
They are different because in #1, you just used an IRR for all periods
1-240.
[....]
You used the Mortgage rate (B6) for perionds 121-240, and
an IRR rate for 1-120. This is one reason why they are different.

They are different only if you assume that the mortgage rate is different
from the IRR.

Otherwise, if the IRR is the same as the mortgage rate, you are effectively
saying: "You use an IRR rate for periods 121-240, and the same IRR rate for
1-120". That is, "you just used an IRR for all periods 1-240". There is no
difference.

So I think it behooves you to explain when the mortgage rate is different
(and not different) from the IRR.

I have already provided the answer, both empirically and mathematically,
namely: the mortgage rate is different from the IRR when the interest rates
of the two loans are different; and conversely, the mortgage rate is the
same as the IRR -- and there is no difference between the two IRRs -- when
the interest rates of the two loans are the same.

But is that "obvious", or does that need to be explained?

Finally, it would be nice to explain when the IRR in one case is less than
or greater than the IRR in the other case. I know the answer empirically.
I am still struggling to explain it.


----- original message -----

"Dana DeLouis" wrote in message
...
I have a problem with a discrepancy between two different IRRs
(calculated on the same cash flow but in two different ways)
that, in my opinion should be exactly the same

I understand the question is "why are they different?"

They are different because in #1, you just used an IRR for all periods
1-240.

In #2, look at the equation in M122.

You used: ...NPV(B6/12,M123:$M$242)...

You used the Mortgage rate (B6) for perionds 121-240, and an IRR rate for
1-120. This is one reason why they are different.

The NPV from above is just another way of returning the remaining mortgage
balance at time 120.

= = = = = = = = = =
HTH :)
Dana DeLouis



On 12/25/2009 6:10 PM, Dana DeLouis wrote:
I have a problem with a discrepancy between two different IRRs
(calculated on the same cash flow but in two different ways) that, in my
opinion should be exactly the same.


Hi. I'll try my best here.

For #1, all payments after time 120 were brought back to time 0 at the
unknown irr rate. The higher rate for the Home Equity Loan was spread
out from 10 years, to 20 years. Hence, the IRR for IRR #1 should be
lower.

Mathematically, all payments from 121 to 240 were brought back to time
120 at the IRR rate, and then brought back to time 0 at the IRR rate
also.

However, for #2, you used the remaining loan balance at time 120. You
brought all payments after 120 to time 120 at the known mortgage rate,
and then brought it to time 0 at the IRR rate. The high Equity Loan is
not part of any calculations after time 120 as in the first example.
These two differences are why they are different.

Hope I said this right. :)

Dana DeLouis



On 12/23/09 7:10 PM, Ben wrote:
Hello all,
I have a problem with a discrepancy between two different IRRs
(calculated
on the same cash flow but in two different ways) that, in my opinion
should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will
send over
the excel sheet with the problem. Please note that a basic
understanding of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.

I wrote:
"Dana DeLouis" wrote:
You used the Mortgage rate (B6) for perionds 121-240, and
an IRR rate for 1-120. This is one reason why they are different.

They are different only if you assume that the mortgage rate is different
from the IRR.

But I concede that __when__ "the" IRR is different from the mortgage rate,
your explanation should be sufficiently convincing. And since that is the
situation that Ben presented, it should be good enough.

Very good. I'm probably beating a dead horse.


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"Dana DeLouis" wrote:
They are different because in #1, you just used an IRR for all periods
1-240.
[....]
You used the Mortgage rate (B6) for perionds 121-240, and
an IRR rate for 1-120. This is one reason why they are different.

They are different only if you assume that the mortgage rate is different
from the IRR.

Otherwise, if the IRR is the same as the mortgage rate, you are
effectively saying: "You use an IRR rate for periods 121-240, and the
same IRR rate for 1-120". That is, "you just used an IRR for all periods
1-240". There is no difference.

So I think it behooves you to explain when the mortgage rate is different
(and not different) from the IRR.

I have already provided the answer, both empirically and mathematically,
namely: the mortgage rate is different from the IRR when the interest
rates of the two loans are different; and conversely, the mortgage rate is
the same as the IRR -- and there is no difference between the two IRRs --
when the interest rates of the two loans are the same.

But is that "obvious", or does that need to be explained?

Finally, it would be nice to explain when the IRR in one case is less than
or greater than the IRR in the other case. I know the answer empirically.
I am still struggling to explain it.


----- original message -----

"Dana DeLouis" wrote in message
...
I have a problem with a discrepancy between two different IRRs
(calculated on the same cash flow but in two different ways)
that, in my opinion should be exactly the same

I understand the question is "why are they different?"

They are different because in #1, you just used an IRR for all periods
1-240.

In #2, look at the equation in M122.

You used: ...NPV(B6/12,M123:$M$242)...

You used the Mortgage rate (B6) for perionds 121-240, and an IRR rate for
1-120. This is one reason why they are different.

The NPV from above is just another way of returning the remaining
mortgage balance at time 120.

= = = = = = = = = =
HTH :)
Dana DeLouis



On 12/25/2009 6:10 PM, Dana DeLouis wrote:
I have a problem with a discrepancy between two different IRRs
(calculated on the same cash flow but in two different ways) that, in my
opinion should be exactly the same.


Hi. I'll try my best here.

For #1, all payments after time 120 were brought back to time 0 at the
unknown irr rate. The higher rate for the Home Equity Loan was spread
out from 10 years, to 20 years. Hence, the IRR for IRR #1 should be
lower.

Mathematically, all payments from 121 to 240 were brought back to time
120 at the IRR rate, and then brought back to time 0 at the IRR rate
also.

However, for #2, you used the remaining loan balance at time 120. You
brought all payments after 120 to time 120 at the known mortgage rate,
and then brought it to time 0 at the IRR rate. The high Equity Loan is
not part of any calculations after time 120 as in the first example.
These two differences are why they are different.

Hope I said this right. :)

Dana DeLouis



On 12/23/09 7:10 PM, Ben wrote:
Hello all,
I have a problem with a discrepancy between two different IRRs
(calculated
on the same cash flow but in two different ways) that, in my opinion
should
be exactly the same.
Any one willing to take a crack at it and maybe provide some kind of
explanation is welcome to contact me at , and I will
send over
the excel sheet with the problem. Please note that a basic
understanding of
the time value of money and financing is required.
Thanks in advance for any assistance,
Ben.