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.