Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Math Help - How to approximate IRR with simple formula for two loans of different terms

  1. #1
    Newbie
    Joined
    Apr 2014
    From
    Canada
    Posts
    4

    How to approximate IRR with simple formula for two loans of different terms

    I am trying to make a simple formula to approximate the IRR using the IRR of each loan, their respective term and loan size rather than having to use all of their individual cash flows, some them up, then calc the IRR.

    i.e.

    loan one is $1,000, has a term of 10 years and an IRR of 5%.

    loan two is $100 has a term of 1 year and an IRR of 15%.

    Using a simple weighted does not capture the difference of term - this would show an IRR of 5.91%, while the real IRR is 5.12%.

    Any help is appreciated!
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Banned
    Joined
    Apr 2014
    From
    Yonkers, NY
    Posts
    66
    Thanks
    3

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by Aidie View Post
    I am trying to make a simple formula to approximate the IRR using the IRR of each loan, their respective term and loan size rather than having to use all of their individual cash flows, some them up, then calc the IRR.

    i.e.

    loan one is $1,000, has a term of 10 years and an IRR of 5%.

    loan two is $100 has a term of 1 year and an IRR of 15%.

    Using a simple weighted does not capture the difference of term - this would show an IRR of 5.91%, while the real IRR is 5.12%.

    Any help is appreciated!
    The only thing I can think of is to write a custom Incremental IRR function that takes the loan periods, loan repayment amounts, and loan amounts to find the desired IRR

    Please see the following where you enter the periods of two loans as 12 and 120. The loan repayment amounts of 9.03 and 10.61 and the loan amounts of -100 and -1000

    The IncRATE function finds the IRR of 5.12%

    You may enter other values in arrays for nper, pmt and pv to find the desired IRR for all your loans

    Code:
    Sample Output
    ---------------------------------------------------------------------------------
    
    nper = [12,120]
    pmt = [9.03,10.61]
    pv = [-100,-1000]
    fv = 0
    type = 1
    compounding = 1
    guess = 0.05
    
    tadRATE(nper[], pmt[], pv[], fv, type, compounding, guess) = 0.004270800653174929
    Annual Rate = 0.004270800653174929 * 12
    Annual Rate = 0.05124960783809915
    Annual Rate = 5.12%
    
    ---------------------------------------------------------------------------------

    Code:
    <script type="text/javascript">
    
    var nper = [12,120];
    var pmt = [9.03,10.61];
    var pv = [-100,-1000];
    var fv = 0;
    var atype = 1;
    var compounding = 1;
    var guess = 0.05;
    
    document.write("<p>nper = [12,120]"+"<br/>");
    document.write("pmt = [9.03,10.61]"+"<br/>");
    document.write("pv = [-100,-1000]"+"<br/>");
    document.write("fv = "+fv+"<br/>");
    document.write("type = "+atype+"<br/>");
    document.write("compounding = "+compounding+"<br/>");
    document.write("guess = "+guess+"</p>");
    
    r = tadIncRATE(nper,pmt,pv,fv,atype,compounding,guess);
    ar = r * 12;
    arp = Math.round(ar * 10000)/100;
    document.write( "<p>tadRATE(nper[], pmt[], pv[], fv, type, compounding, guess) = " + r +"<br/>");
    document.write( "Annual Rate = " + r + " * 12" +"<br/>");
    document.write( "Annual Rate = " + ar +"<br/>");
    document.write( "Annual Rate = " + arp +"%</p>");
    
    function tadAEY(r, c)
    {
    	if (r==0.0)
    		return 0.0;
    	if (c==0.0)
    		return Math.exp(r) - 1;
    	else
    		return Math.pow(1.0+r*c, 1/c) - 1;
    }
    
    function tadPVIF(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return 1.0;
    	if (n==0.0)
    		return 1.0;
    	return Math.pow(1.0+tadAEY(r,c),-n);
    }
    
    function tadPVIFbar(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return -n;
    	if (n==0.0)
    		return 0.0;
    	return -n * tadPVIF(r,n+c,c);
    }
    
    function tadPVIFA(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return 1.0;
    	if (n==0.0)
    		return 0.0;
    	return (1.0-tadPVIF(r,n,c))/r;
    }
    
    function tadPVIFAbar(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return 1.0;
    	if (n==0.0)
    		return 0.0;
    	return (r * (1.0-tadPVIFbar(r,n,c))  - (1.0-tadPVIF(r,n,c)) )/(r*r);
    }
    
    function tadIncRATE( nper, pmt, pv, fv, atype, c, guess) {
        guess = (typeof guess === "undefined") ? 0.10 : guess;
        fv = (typeof fv !== "undefined") ? fv : 0;
        c = (typeof c !== "undefined") ? c : 1;
        atype = (typeof atype !== "undefined") ? atype : 0;
    
        var i;
        var x = 0.0;
        var x0 = guess;
        var f;
        var fbar;
    
        for (i=0; i<100; i++)
        {
          f = 0;
          fbar = 0;
          for (j=0; j<nper.length; j++)
          {
             f += pv[j] + pmt[j] * tadPVIFA(x0, nper[j],c) + fv * tadPVIF(x0, nper[j],c);
             fbar +=  pmt[j] * tadPVIFAbar(x0, nper[j],c) + fv * tadPVIFbar(x0, nper[j],c);
          }
          if (fbar == 0.0)
             return null;
          else
             x = x0 - f/fbar;
          if ( Math.abs(x-x0) < 0.000001 )
              return x;
    
           x0 = x;
        }
        return null;
    }
    
    </script>
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Banned
    Joined
    Apr 2014
    From
    Yonkers, NY
    Posts
    66
    Thanks
    3

    Re: How to approximate IRR with simple formula for two loans of different terms

    As I can't find a way to edit my last reply thus posting here

    One of the helper functions in the code that I posted had an error

    The financial function that finds the derivative of PVIFA was incorrect, please replace that function in the original code with the following function

    Code:
    function tadPVIFAbar(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return 1.0;
    	if (n==0.0)
    		return 0.0;
    	return ( r * -tadPVIFbar(r,n,c)  - (1.0-tadPVIF(r,n,c)) ) / (r*r);
    }
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    825
    Thanks
    418

    Re: How to approximate IRR with simple formula for two loans of different terms

    The problem seems weird to me. There could presumably be an infinite number of repayment schemes that result in a given IRR. Nor are loans usually priced in terms of IRRs. I think we need more information.
    Follow Math Help Forum on Facebook and Google+

  5. #5
    MHF Contributor
    Joined
    Nov 2013
    From
    California
    Posts
    2,789
    Thanks
    1149

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by JeffM View Post
    The problem seems weird to me. There could presumably be an infinite number of repayment schemes that result in a given IRR. Nor are loans usually priced in terms of IRRs. I think we need more information.
    the whole concept of trying to compare an investment that matures in 1 yr vs. 1 that matures in 10 yrs doesn't seem valid. What does the money from the first investment do for that 9 yrs? Get stuffed in a matress?
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    825
    Thanks
    418

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by romsek View Post
    the whole concept of trying to compare an investment that matures in 1 yr vs. 1 that matures in 10 yrs doesn't seem valid. What does the money from the first investment do for that 9 yrs? Get stuffed in a matress?
    Interesting conceptual point, but IRRs are usually quoted as annual rates and so comparing them is not completely absurd. Another problem with IRRs is that there is a term structure to market rates of interest, and IRRs do not address that. Nor do IRR's address what risk premium is appropriate. But what strikes me as most bizarre is that in 36 years of banking I never heard a loan discussed in terms of IRR.
    Follow Math Help Forum on Facebook and Google+

  7. #7
    Banned
    Joined
    Apr 2014
    From
    Yonkers, NY
    Posts
    66
    Thanks
    3

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by romsek View Post
    the whole concept of trying to compare an investment that matures in 1 yr vs. 1 that matures in 10 yrs doesn't seem valid. What does the money from the first investment do for that 9 yrs? Get stuffed in a matress?
    This example is sort of like finding IRR of a portfolio of investments
    Follow Math Help Forum on Facebook and Google+

  8. #8
    Banned
    Joined
    Apr 2014
    From
    Yonkers, NY
    Posts
    66
    Thanks
    3

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by JeffM View Post
    Another problem with IRRs is that there is a term structure to market rates of interest, and IRRs do not address that. Nor do IRR's address what risk premium is appropriate.
    Even when there is a term structure to market rates of interest, one can easily find the IRR (the yield on investment). Such as IRR would be the interest rate at which NPV of the investment using the term structure of market interest rates would be the same as the NPV at the of the investment using a single interest rate.

    Here is one such IRR calculation

    Code:
    Rates          2.0%     3.0%     4.0%     3.5%     5.0%     5.5%     7.0%     5.5%
    Cash_Flows     (100)    150      250      (150)    350      450      550      650
    IRR of an investment that goes through interest rate shocks

    Code:
    =tadIRR( , 1, {0.02, 0.03, 0.04, 0.035, 0.05, 0.055, 0.07, 0.055}, , , {-100, 150, 250, -150, 350, 450, 550, 650} )
    5.80%
    results in a single IRR of 5.80%

    To check the IRR, we at first find the NPV using the term structure of interest rates

    Code:
    =tadNPV( {0.02, 0.03, 0.04, 0.035, 0.05, 0.055, 0.07, 0.055}, , , {-100, 150, 250, -150, 350, 450, 550, 650} )
    1,587.06
    The NPV of the investment using the term structure is 1,587.06

    Now to confirm the IRR, we find NPV using the IRR

    Code:
    =tadNPV( 0.05804771, , , {-100, 150, 250, -150, 350, 450, 550, 650} )
    1,587.06
    The NPV of the investment using the IRR is 1,587.06 same as the one found using the term structure

    But then how about finding an IRR when you have a 12 x N matrix of data for an investment

    Code:
    Rates                  2.0%     3.0%        4.0%    3.5%         5.0%     5.5%          7.0%     5.5%
    Growth                 0%       0%          0%      0%           0%       0%            0%       0%
    Tax_Rates              35%      36%         37%     38%          39%      40%           45%      50%
    Cash_Flows             (100)    150         250     (150)        350      450           550      650 
    Adjust_for_inflation   0        0           0       0            0        0             0        0
    Frequencies            4        365         24      365          INF      260           INF      5,200
    Types                  1        0           1       0            1        1             0        0
    Compounding            1        0.019231    0.5     0.00274      1        0.083333      0.25     1
    Periods                1        0.019231    0.5     0.00274      1        0.083333      0.25     1
    Concentrations         1        0.5         2       10           1        0.5           1        0.75
    Hair_Cuts              0%       20%         20%     0%           20%      20%           20%      20%
    Rate_Rigged_By         0%       15%         15%     0%           15%      15%           15%      15%
    Follow Math Help Forum on Facebook and Google+

  9. #9
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    825
    Thanks
    418

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by AbrahamA View Post
    Even when there is a term structure to market rates of interest, one can easily find the IRR (the yield on investment). Such as IRR would be the interest rate at which NPV of the investment using the term structure of market interest rates would be the same as the NPV at the of the investment using a single interest rate.

    Here is one such IRR calculation

    Code:
    Rates          2.0%     3.0%     4.0%     3.5%     5.0%     5.5%     7.0%     5.5%
    Cash_Flows     (100)    150      250      (150)    350      450      550      650
    IRR of an investment that goes through interest rate shocks

    Code:
    =tadIRR( , 1, {0.02, 0.03, 0.04, 0.035, 0.05, 0.055, 0.07, 0.055}, , , {-100, 150, 250, -150, 350, 450, 550, 650} )
    5.80%
    results in a single IRR of 5.80%

    To check the IRR, we at first find the NPV using the term structure of interest rates

    Code:
    =tadNPV( {0.02, 0.03, 0.04, 0.035, 0.05, 0.055, 0.07, 0.055}, , , {-100, 150, 250, -150, 350, 450, 550, 650} )
    1,587.06
    The NPV of the investment using the term structure is 1,587.06

    Now to confirm the IRR, we find NPV using the IRR

    Code:
    =tadNPV( 0.05804771, , , {-100, 150, 250, -150, 350, 450, 550, 650} )
    1,587.06
    The NPV of the investment using the IRR is 1,587.06 same as the one found using the term structure

    But then how about finding an IRR when you have a 12 x N matrix of data for an investment

    Code:
    Rates                  2.0%     3.0%        4.0%    3.5%         5.0%     5.5%          7.0%     5.5%
    Growth                 0%       0%          0%      0%           0%       0%            0%       0%
    Tax_Rates              35%      36%         37%     38%          39%      40%           45%      50%
    Cash_Flows             (100)    150         250     (150)        350      450           550      650 
    Adjust_for_inflation   0        0           0       0            0        0             0        0
    Frequencies            4        365         24      365          INF      260           INF      5,200
    Types                  1        0           1       0            1        1             0        0
    Compounding            1        0.019231    0.5     0.00274      1        0.083333      0.25     1
    Periods                1        0.019231    0.5     0.00274      1        0.083333      0.25     1
    Concentrations         1        0.5         2       10           1        0.5           1        0.75
    Hair_Cuts              0%       20%         20%     0%           20%      20%           20%      20%
    Rate_Rigged_By         0%       15%         15%     0%           15%      15%           15%      15%
    I think you misunderstood what I was trying, obviously not very cogently, to say. The IRR is not a number that tracks to or depends on any observable market rate. Therefore, I do not find IRR to be as meaningful a measure as NPV, which can be based on observable market rates.

    I clearly do not understand your first example. When I calculate the IRR for that series of cash flows, I get a much higher IRR than 5.8% p.a.
    Follow Math Help Forum on Facebook and Google+

  10. #10
    Banned
    Joined
    Apr 2014
    From
    Yonkers, NY
    Posts
    66
    Thanks
    3

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by JeffM View Post
    I think you misunderstood what I was trying, obviously not very cogently, to say. The IRR is not a number that tracks to or depends on any observable market rate. Therefore, I do not find IRR to be as meaningful a measure as NPV, which can be based on observable market rates.

    I clearly do not understand your first example. When I calculate the IRR for that series of cash flows, I get a much higher IRR than 5.8% p.a.
    If there were to be a single rate of interest, the IRR would be quite high for the investment as you have stated

    It comes out to be 160.27%

    Code:
    Excel IRR function
    =IRR( {-100, 150, 250, -150, 350, 450, 550, 650} )
    160.27%
    
    Excel tadIRR function
    =tadIRR( , , , , , {-100, 150, 250, -150, 350, 450, 550, 650} )
    160.27%

    However, if the interest rates, for each term of the investment, go through shocks then the IRR of the investment would be quite different and as I stated the IRR in such a case would the interest rate at which NPV for the investment is the same when using a term structure of interest rates

    For a regular IRR calculation, the equation looks like this

    Code:
    IRR equation (single rate)
    
    Let x = 1 +i
    
    0 = -100 + 150x^-1 + 250x^-2 - 150x^-3 + 350x^-4 + 450x^-5 + 550x^-6 + 650x^-7
    
    we are solving for i
    However when we have a term structure of interest rates, then the IRR equation looks like this

    Code:
    IRR equation (term structure rate)
    
    NPV_term = -100(1.02)^0 + 150(1.03)^-1 + 250(1.04)^-2 - 150(1.035)^-3 + 350(1.05)^-4 + 450(1.055)^-5 + 550(1.07)^-6 + 650(1.055)^-7
    
    Let x = 1 +i
    
    0 = -NPV_term - 100 + 150x^-1 + 250x^-2 - 150x^-3 + 350x^-4 + 450x^-5 + 550x^-6 + 650x^-7
    
    we are solving for i
    The IRR calculations get really messy, if you were to include other attributes of the investment such as growth rate, tax rate, frequency of annuity payments, annuuity type, compounding frequencies of interest, periods with varying lengths, various discounting conventions such as mid-year, possible haircuts on expected future income, rigging of the rate

    See this for more on IRR calculations tadIRR|Excel IRR function
    Follow Math Help Forum on Facebook and Google+

  11. #11
    Banned
    Joined
    Apr 2014
    From
    Yonkers, NY
    Posts
    66
    Thanks
    3

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by JeffM View Post
    Therefore, I do not find IRR to be as meaningful a measure as NPV, which can be based on observable market rates.
    IRR itself is quite problematic, as the assumption is that the proceeds from investment are reinvested at the IRR

    That may not always be the case as the reinvestment rate would often differ from the finance rate

    The MIRR - modified internal rate of return solves this issue to an extent. Yet MIRR assumes that the investor holds on the investment till its maturity

    This assumption too is invalid as the investor may decide to dispose of the investment anywhere from start of investment before its maturity. Thus the horizon of the investment Th lies between T0 < Th < Tm

    where t0 = 0
    Tm = n

    This requires an analyst to find the HRR - horizon rate of return for the investment which is more appropriate of a measure as opposed to IRR and MIRR when the investor is seeking disposal before maturity

    I could later come back and show the MIRR and HRR results for the sample investment using arbitrary finance rate, reinvestment rate and a horizon
    Follow Math Help Forum on Facebook and Google+

  12. #12
    Newbie
    Joined
    Apr 2014
    From
    Canada
    Posts
    4

    Re: How to approximate IRR with simple formula for two loans of different terms

    I overly simplified my example. I am looking at a portfolio of loans that has a yield to maturity / irr that is based on the cash flows and current market value of the portfolio. I am trying to determine what happens if I add a loan to this. The typical method to solve this is to add another column and enter in the cash flows and then recalc the irr for the new total portfolio. As this is part of another calc, I was hoping for a simplified formula where I could approximate this. You can do a simple weighting of the two irr's, but this only works if they have the same term. I can get a rough idea of the effect using the weighted term of the portfolio and comparing it to the term of the loan. i.e. if the term of the loan and IRR are higher than the weighted portfolio, then I know the true IRR will be higher than the weighted IRRs of the 2 portfolios.
    Follow Math Help Forum on Facebook and Google+

  13. #13
    Banned
    Joined
    Apr 2014
    From
    Yonkers, NY
    Posts
    66
    Thanks
    3

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by Aidie View Post
    I overly simplified my example. I am looking at a portfolio of loans that has a yield to maturity / irr that is based on the cash flows and current market value of the portfolio. I am trying to determine what happens if I add a loan to this. The typical method to solve this is to add another column and enter in the cash flows and then recalc the irr for the new total portfolio. As this is part of another calc, I was hoping for a simplified formula where I could approximate this. You can do a simple weighting of the two irr's, but this only works if they have the same term. I can get a rough idea of the effect using the weighted term of the portfolio and comparing it to the term of the loan. i.e. if the term of the loan and IRR are higher than the weighted portfolio, then I know the true IRR will be higher than the weighted IRRs of the 2 portfolios.
    Simple weighting is not going to work here as you are looking at IRR/YTM/yield values at different time intervals for the same investment (series of cash flows from various loans in a sequence)

    You can enter the different payment amounts and the terms of the loans and loan amounts in the arrays for pmt, nper, and pv in the code at the bottom of this message

    I had typed in a long reply but only to find your new post so here is what I was going to add to the discussion. Please read the following

    Let me expand on the discussion and show a number of different measures of ROI - return on investment. The investor would find that the IRR happens to be her return on investment however she knows her cost of financing and reinvestment. Thus she is more inclined to find the modified internal rate of return, the MIRR. This measure will ensure that the costs are discounted at the finance rate and the proceeds are accumulated at the reinvestment rate. This would suffice if she plans to hold on to the investment till it's maturity at the end of 10th year. But after initiating the investment at time period t=0, she knows her rate of return to be the IRR at which the proceeds were going to be reinvested. Luckily she has found that she can get higher returns on the proceeds by reinvesting into another venture. Now she has here initial return the IRR and the reinvestment rate, thus she would be interested in knowing her overall return from this investment using the newly found information about two different yields. She may hold on to the investment till its maturity in the 10 year or she may decide to dispose of the investment at the end of 5th year. To do this she needs a way to find the HRR - horizon rate of return/

    Let us help her in finding the different rates of return for her investment.


    If her proceeds were to be reinvested at the rate of the return then she finds her IRR is 5.12%

    Code:
    nper = [12,120]
    pmt = [9.03,10.61]
    pv = [-100,-1000]
    fv = 0
    horizon 1= 120
    horizon 2= 60
    type = 1
    compounding = 1
    finance rate = 4%
    reinvestment rate = 5.5%
    guess = 5%
    
    tadIncRATE(nper[], pmt[], pv[], fv, type, compounding, guess) = 0.004270776315034454
    Annual IRR = 0.004270776315034454 * 12
    Annual IRR = 0.051249315780413446
    Annual IRR = 5.12%
    If she knows her finance rate to be 4% and her reinvestment rate to be 5.5% then here MIRR is 5.98%

    Code:
    tadIncMIRR( finance rate = 4%, reinvestment rate= 5.5%, nper, pmt, pv, 1) = 0.0049846091845417995
    Annual MIRR = 0.0049846091845417995 * 12
    Annual MIRR = 0.059815310214501594
    Annual MIRR = 5.98%
    If her initial return from the investment was the IRR of 5.12% yet she found that she could reinvest the proceeds at 5.5% till the maturity of investment in the 10th year, then her HRR - horizon rate of return is 5.34%

    Code:
    tadIncHRR( finance rate = irr= 5.12%, reinvestment rate= 5.5%, horizon= 120, nper , pmt, 1) = 0.004451740393462522
    Annual HRR = 0.004451740393462522 * 12
    Annual HRR = 0.053420884721550266
    Annual HRR = 5.34%
    Yet knowing her IRR and the reinvestment rate but she decides to dispose of the investment at the end of 5th year, then her HRR - horizon rate of return is 5.18%

    Code:
    tadIncHRR( finance rate = irr= 5.12%, reinvestment rate= 5.5%, horizon= 60, nper , pmt, 1) = 0.004320164691287154
    Annual HRR = 0.004320164691287154 * 12
    Annual HRR = 0.051841976295445846
    Annual HRR = 5.18%
    If she still requires more financial analysis for her investment, I will be glad to help her find the duration and convexity of this investment. The duration will help her immunize the investment from change in the yield, the IRR. The duration will give her the percentage rate by which the value of the investment may rise/fall if the rate of return were to fall/rise by 1%. The duration is a straight line decline or rise whereas convexity brings in the curvature that is better reflective of the change in net value of the investment. Duration is measure in years and it is the percentage rate whereas convexity is measured in years square and it is the variance.

    Here is the amended code with the additions of the new financial functions that find the incremental MIRR - modified internal rate of return and HRR - horizon rate of return

    Code:
    <script type="text/javascript">
    
    var nper = [12,120];
    var pmt = [9.03,10.61];
    var pv = [-100,-1000];
    var fv = 0;
    var atype = 1;
    var compounding = 1;
    var frate = 0.04;
    var rrate = 0.055;
    var horizon1 = 120;
    var horizon2 = 60;
    var guess = 0.05;
    
    document.write("<p>nper = [12,120]"+"<br/>");
    document.write("pmt = [9.03,10.61]"+"<br/>");
    document.write("pv = [-100,-1000]"+"<br/>");
    document.write("fv = "+fv+"<br/>");
    document.write("horizon 1= "+horizon1+"<br/>");
    document.write("horizon 2= "+horizon2+"<br/>");
    document.write("type = "+atype+"<br/>");
    document.write("compounding = "+compounding+"<br/>");
    document.write("finance rate = "+frate*100+"%<br/>");
    document.write("reinvestment rate = "+rrate*100+"%<br/>");
    document.write("guess = "+guess*100+"%</p>");
    
    irr = tadIncRATE(nper,pmt,pv,fv,atype,compounding,guess);
    ar = irr * 12;
    arp = Math.round(ar * 10000)/100;
    document.write( "<p>tadIncRATE(nper[], pmt[], pv[], fv, type, compounding, guess) = " + irr +"<br/>");
    document.write( "Annual IRR = " + irr + " * 12" +"<br/>");
    document.write( "Annual IRR = " + ar +"<br/>");
    document.write( "Annual IRR = " + arp +"%</p>");
    
    mirr = tadIncMIRR( frate, rrate, nper, pmt, pv, 1);
    amirr = mirr * 12;
    amirrp = Math.round(amirr * 10000)/100;
    document.write( "<p>tadIncMIRR( finance rate = "+ frate*100 +"%, reinvestment rate= " + rrate*100 +"%, nper, pmt, pv, 1) = " + mirr +"<br/>");
    document.write( "Annual MIRR = " + mirr + " * 12" +"<br/>");
    document.write( "Annual MIRR = " + amirr +"<br/>");
    document.write( "Annual MIRR = " + amirrp +"%</p>");
    
    hrr = tadIncHRR( irr, rrate, horizon1, nper, pmt, 1);
    ahrr = hrr * 12;
    ahrrp = Math.round(ahrr * 10000)/100;
    document.write( "<p>tadIncHRR( finance rate = irr= "+ arp +"%, reinvestment rate= " + rrate*100 + "%, horizon= " + horizon1 +", nper , pmt, 1) = " + hrr +"<br/>");
    document.write( "Annual HRR = " + hrr + " * 12" +"<br/>");
    document.write( "Annual HRR = " + ahrr +"<br/>");
    document.write( "Annual HRR = " + ahrrp +"%</p>");
    
    
    hrr = tadIncHRR( irr, rrate, horizon2, nper, pmt, 1);
    ahrr = hrr * 12;
    ahrrp = Math.round(ahrr * 10000)/100;
    document.write( "<p>tadIncHRR( finance rate = irr= "+ arp +"%, reinvestment rate= " + rrate*100 + "%, horizon= " + horizon2 +", nper , pmt, 1) = " + hrr +"<br/>");
    document.write( "Annual HRR = " + hrr + " * 12" +"<br/>");
    document.write( "Annual HRR = " + ahrr +"<br/>");
    document.write( "Annual HRR = " + ahrrp +"%</p>");
    
    function tadAEY(r, c)
    {
    	if (r==0.0)
    		return 0.0;
    	if (c==0.0)
    		return Math.exp(r) - 1;
    	else
    		return Math.pow(1.0+r*c, 1/c) - 1;
    }
    
    function tadFVIF(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return 1.0;
    	if (n==0.0)
    		return 1.0;
    	return Math.pow(1.0+tadAEY(r,c),n);
    }
    
    function tadPVIF(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return 1.0;
    	if (n==0.0)
    		return 1.0;
    	return Math.pow(1.0+tadAEY(r,c),-n);
    }
    
    function tadPVIFbar(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return -n;
    	if (n==0.0)
    		return 0.0;
    	return -n * tadPVIF(r,n+c,c);
    }
    
    function tadPVIFA(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return 1.0;
    	if (n==0.0)
    		return 0.0;
    	return (1.0-tadPVIF(r,n,c))/r;
    }
    
    function tadPVIFAbar(r, n, c)
    {
    	c = (typeof c !== "undefined") ? c : 1;
    	if (r==0.0)
    		return 1.0;
    	if (n==0.0)
    		return 0.0;
    	return (r * -tadPVIFbar(r,n,c)  - (1.0-tadPVIF(r,n,c)) )/(r*r);
    }
    
    function tadIncHRR( irr, rrate, horizon, nper, pmt, c)
    {
        c = (typeof c !== "undefined") ? c : 1;
    
        npv = 0.0;
        nhv = 0.0;
        rrate /= 12.0;
    
        for (j=0; j<nper.length; j++)
        {
           npv += pmt[j] * tadPVIFA(irr, nper[j], c);
           nhv += pmt[j] * tadPVIFA(rrate, nper[j], c);
        }
        
        hrr = Math.pow(nhv/npv, 1.0/horizon)*(1+rrate) - 1
        return hrr;
    
    }
    
    function tadIncMIRR( frate, rrate, nper, pmt, pv, c)
    {
        c = (typeof c !== "undefined") ? c : 1;
    
        npv = 0.0;
        nfv = 0.0;
    
        T = 0;
        frate /=12.0;
        rrate /=12.0;
    
        for (j=0; j<nper.length; j++)
        {
    	if(nper[j] > T)
    		T = nper[j];
        }
    
        for (j=0; j<nper.length; j++)
        {
           npv += -pv[j];
           nfv += pmt[j] * tadPVIFA(frate, nper[j], c) * tadFVIF(rrate, T, c);
        }
        mirr = Math.pow(nfv/npv, 1.0/T) - 1
        return mirr;
    
    }
    
    function tadIncRATE( nper, pmt, pv, fv, atype, c, guess)
    {
        guess = (typeof guess === "undefined") ? 0.10 : guess;
        fv = (typeof fv !== "undefined") ? fv : 0;
        c = (typeof c !== "undefined") ? c : 1;
        atype = (typeof atype !== "undefined") ? atype : 0;
    
        var i;
        var x = 0.0;
        var x0 = guess;
        var f;
        var fbar;
    
        for (i=0; i<100; i++)
        {
          f = 0;
          fbar = 0;
          for (j=0; j<nper.length; j++)
          {
             f += pv[j] + pmt[j] * tadPVIFA(x0, nper[j],c) + fv * tadPVIF(x0, nper[j],c);
             fbar +=  pmt[j] * tadPVIFAbar(x0, nper[j],c) + fv * tadPVIFbar(x0, nper[j],c);
          }
          if (fbar == 0.0)
             return null;
          else
             x = x0 - f/fbar;
          if ( Math.abs(x-x0) < 0.000001 )
              return x;
    
           x0 = x;
        }
        return null;
    }
    </script>

    BTW: the author of this post never attended a business or computing schools as he considers such ventures to be waste of time looking at what they teach out there at 80% of business schools. The author is a self taught and self proclaimed financial engineer who has researched into numerical methods to solve finance related math problems. Most of his ideas were stolen and big name companies are on there way to create financial functions that are based on author's research. The author does not have a job, and is looking for one where his skills not education can be put into practice.
    Last edited by AbrahamA; April 22nd 2014 at 10:56 PM. Reason: added missing text
    Follow Math Help Forum on Facebook and Google+

  14. #14
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    825
    Thanks
    418

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by Aidie View Post
    I overly simplified my example. I am looking at a portfolio of loans that has a yield to maturity / irr that is based on the cash flows and current market value of the portfolio. I am trying to determine what happens if I add a loan to this. The typical method to solve this is to add another column and enter in the cash flows and then recalc the irr for the new total portfolio. As this is part of another calc, I was hoping for a simplified formula where I could approximate this. You can do a simple weighting of the two irr's, but this only works if they have the same term. I can get a rough idea of the effect using the weighted term of the portfolio and comparing it to the term of the loan. i.e. if the term of the loan and IRR are higher than the weighted portfolio, then I know the true IRR will be higher than the weighted IRRs of the 2 portfolios.
    Aidie

    I greatly doubt that there is a formula to do what you want to do. The reason is that there is no simple formula for calculating an internal rate of return over a large number of periods in the first place. So a formula to figure out its change is probably even less easy to find. This is a guess on my part because proving that something is impossible is itself very difficult. You may want to read my next post to Abraham (once I write it) even though it is not directly relevant to your question.
    Follow Math Help Forum on Facebook and Google+

  15. #15
    Super Member
    Joined
    Feb 2014
    From
    United States
    Posts
    825
    Thanks
    418

    Re: How to approximate IRR with simple formula for two loans of different terms

    Quote Originally Posted by AbrahamA View Post
    Even when there is a term structure to market rates of interest, one can easily find the IRR (the yield on investment). Such as IRR would be the interest rate at which NPV of the investment using the term structure of market interest rates would be the same as the NPV at the of the investment using a single interest rate.

    Here is one such IRR calculation

    Code:
    Rates          2.0%     3.0%     4.0%     3.5%     5.0%     5.5%     7.0%     5.5%
    Cash_Flows     (100)    150      250      (150)    350      450      550      650
    results in a single IRR of 5.80%

    To check the IRR, we at first find the NPV using the term structure of interest rates

    The NPV of the investment using the term structure is 1,587.06

    Now to confirm the IRR, we find NPV using the IRR

    Code:
    =tadNPV( 0.05804771, , , {-100, 150, 250, -150, 350, 450, 550, 650} )
    1,587.06
    The NPV of the investment using the IRR is 1,587.06 same as the one found using the term structure
    My Dear Abraham

    I am not intending to argue with you. First, I dislike arguing. Second, you seem to be fully cognizant of many types of financial measures and how to compute them: in that area, you are almost certainly more facile than I am.

    I am talking about meaning, not computation. I find the meaning of the IRR to be dubious because, as its name says, it is an "internal" measure, meaning that it takes no account of external information such as actual market rates. (Under certain circumstances of course, the IRR is used to compute a proxy for an "unobservable" market rate: it is a rate implied by a price and a series of expected cash flows.) When we add in the mathematical fact that a given set of cash flows may have multiple IRRs, the meaning of "THE" IRR becomes non-existent: I can't rationally speak about the IRR in the singular if the math indicates plural values.

    Now let's consider your example. (A little additional explanation on your part might have been helpful.)

    Cash Flows By Year
    -100, 150, 250, -150, 350, 450, 550, 650

    Market Interest Rates by Year
    2%, 3%, 4%, 3.5%, 5%, 5.5%, 7%, 5.5%.

    Now as you yourself saw, the IRR (assuming all cash flows come at the start of the year) is 160.27%.

    In terms of your present value calculation, you do not really explain what these rates are or whether the cash flows occur at the start of the year, mid-year, or year end. To be consistent with your IRR computation of 160.27%, let's assume that all cash flows occur at the beginning of the year. In that case, the rate for the eighth year is irrelevant. Let's also assume that the rates you give by year are the expected rates for one-year money in that year. (I recognize that your assumptions were probably different, but you did not specify them so I have to guess.)

    Given those assumptions, I compute the expected annual percentage rate for investments paying off at the start of year n to be approximately

    year 2 2%
    year 3 2.5%
    year 4 3%
    year 5 3.12%
    year 6 3.83%
    year 7 4.27%

    That gives me an expected present value of outflows of approximately 237.28 and expected present value of inflows of approximately 1997.47
    for an expected net present value of approximately 1760.18 (discrepancy in units column due to rounding error.) This does not differ much from your unexplained computation of 1567.06, and I expect the difference is due to differences in assumptions (I am not challenging your math abilities in any way).

    But let's ignore for the moment those differences.

    Total outflows = 250.
    Total inflows = 2400.
    Highest annual interest rate expected = 7%

    So if I got 7% for 8 years (and that is the maximum I could expect at market rates) on an immediate investment of 250, my return would be

    $(1.07)^8 * 250 \approx 1.7182 * 250.00 = 429.55 << 2400.$

    Consequently, it simply is not meaningful (at least not to me) to say that the IRR adjusted for the time structure of interest rates is only 5.8%. However we measure the effective rate, it has to be a lot more than 7%, let alone 5.8%.

    This is the problem with formulas: they spit out numbers, but they do not tell you what the numbers signify.
    Follow Math Help Forum on Facebook and Google+

Page 1 of 2 12 LastLast

Similar Math Help Forum Discussions

  1. Error in approximate formula
    Posted in the Differential Geometry Forum
    Replies: 4
    Last Post: August 13th 2010, 07:33 AM
  2. Expression of Formula In Some Terms
    Posted in the Algebra Forum
    Replies: 3
    Last Post: February 23rd 2010, 11:42 PM
  3. Find terms of a sequence and a formula
    Posted in the Differential Geometry Forum
    Replies: 2
    Last Post: February 16th 2010, 05:52 PM
  4. Reduction Formula (In terms of x)
    Posted in the Calculus Forum
    Replies: 2
    Last Post: May 11th 2008, 05:03 AM
  5. Formula in terms of n for the sum
    Posted in the Algebra Forum
    Replies: 2
    Last Post: February 5th 2007, 05:54 PM

Search Tags


/mathhelpforum @mathhelpforum