Recently, I was helping a bank define Basel 2 requirements.
For every dollar a bank lends, at least 8 cents should come from its own pocket, and the rest from its depositors. But a risky $1 loan may be like a $1.5 loan, whereas a $1 Government loan may be like a $0.5 loan. This is the “risk-weighted asset” (RWA) value. Basel 2 says 8% of risk-weighted assets should come from the bank’s pocket.
I was trying to convince the people who were maintaining the leasing software that the RWA of a lease is the NPV of its future cash flows, and they had a whole lot of questions.
“What is this NPV?”
You can put 90 cents in the bank today at 11% and get $1 next year. So $1 next year is worth 90 cents today. When a customer pays $100 over the next 10 months, it’s worth less than $1000 today. That’s the NPV. The NPV is what you put in the bank today to get that cash flow: $100 over the next 10 months
“So why should we use NPV for leases?”
That’s because when a lease is cancelled, the closure payment is the NPV. If you take a lease for 10 months at $100 a month, this includes the interest. If you terminate the lease after 5 months, you won’t pay $500 for the remaining 5 months. You’ll pay less — the NPV of those $100 for 5 months. So there is some logic to using NPV as the RWA.
“OK, so how do we calculate the NPV?”
You divide each cash flow by (1 + r)^n, where r is the internal rate of return, and n is the number of years. Then you add them up. You’ll get a number less than the sum of cash flows.
“And how do we calculate this IRR?”
(sheepishly) The IRR is that interest rate for which the NPV is zero.
And we got stuck here, because their software didn’t have an IRR function, and the definitions for IRR and NPV are circular.
To do this in Excel is simple. Just enter the cash flow values. So, if on a cash loan of $500, you paid $100 for 6 months, and use the IRR function, as shown below. Your monthly IRR is 5.47%.
But we needed their AS/400 system to do it as well, and it didn’t have the IRR function.
After a few weeks of digging around, I found a paper that said you can calculate the IRR iteratively. Let
- npv be the NPV given an IRR and cash flows
- sum be the sum of cash flows
- p be the principal amount
Then irr = irr * log(p/sum) / log(npv/sum) is the iteration you need to successively apply.
We decided to start with 1.85 times the stated interest rate (which was a pretty good guess for most leases), and kept applying this formula until it stays more or less the same. Worked like a charm.
Here’s the spreadsheet with the calculation.
Thanks, but i need to know how to calcualte it manualy.
Farah IRR can only be calculated manually by using hit and trial method. It the interest rate at which the NPV should equal zero. So try with different values. However if you have a financial calculator then you can find IRR just by inputing the data into it. thanks
Suppose this is the scenario: Principal 100,000 IRR 10% Number of months 12 Monthly payment 8,792 Total interest 4,627 But you only have this information Principal 100,000 = p Monthly payment 8,792 Number of years 12 APR 4.63% Here’s how you can calculate IRR Month Payment 1 8,792 2 8,792 3 8,792 4 8,792 5 8,792 6 8,792 7 8,792 8 8,792 9 8,792 10 8,792 11 8,792 12 8,792 Total payment 105,499 = sum First guess of IRR 8.56% 1.85 times APR NPV using first guess 100,766 = npv Second guess of IRR 9.98% irr x log(p/sum) / log(npv/sum) NPV using second guess 100,009 = npv Third guess of IRR 10.00% irr x log(p/sum) / log(npv/sum) NPV using third guess 100,000 Stop iteration
pls attached finance dictionary like irr,roi,and all related collection sales
very informative article
Thank you! This helped with my assignment.
Is it possible to calculate IRR manualy and if it is, how?
how to calculate remaining EMIs (new tenure ) keeping the amount of EMI constant.
I want to ask that at what basis i can guess first IRR ,second IRR & what is about about APR.
Hi,
Had a question regarding building a cashflow table when only IRR and the initial outlay are stated along with the period.
The project has an $80,000 investment outlay and is expected to yield an IRR of 30% over a 5 year economic life and the investment outlay is depreciated on a straight line basis towards a zero estimated salvage value
Could you please help me with this?
Thanks
the data is good but there should be more examples of different cases, so that it surve all purpose.
Thank You
Seems to be some mix ups between Months and Years, If you do monthly calculations, isn’t the IRR then a monthly percentage rate not an anual percentage rate?
IRR by itself does typically refer to an annual rate, Wayne. I happen to
have used a monthly IRR in the example, and you’re right in pointing out
that it makes things a little confusing. Please replace “month” with “year”
right through the article, and I believe it would read right then.
This is so fantastic. Thank you!
However I want to know how annual interest is 4627 when the sum of 8792 x12 months = 105504 and the difference between the principal amount = 5,504.
Can you explain?
Su
The interest is not on 100000, Su. It’s on a DIMINISHING balance that
just happens to be 100000 at the beginning. After making the first
payment of 8792, the principal reduces, and it’s not easy to find out
how much of that 8792 was for the principal, and how much of it was
for the interest. That’s what the article tries to explain.
Hi,
could you state a reference to the paper you dug out? Since this will provide a good starting point for further research on my site.
Thx in advance
Thomas
I’m afraid I couldn’t find it when I searched again, Thomas. Maybe a fresh Google search will work, except I can’t remember what I looked for, except that it was in a PDF file.
Hello,
I have a question regarding IRR. If I have a portfolio of stocks how it is possible to calculate the IRR? For example, suppose that I invest $100.000 and I’m buying few stocks for $10.000 and $30.000. In total I have paid $40.000.The next day I have a 10% profit, that makes me $4.000 better off. How it is possible to calculate IRR?
Regards
George
Just type in the date, and the amount you received or paid on that date (as positive or negative values respectively). Then use Excel’s IRR function.
Anand, can you tell me how to learn excel quickly? i dont even know how to use pivot table. Need to ramp up quickly, any suggestions?
Excel for Dummies is a good starting point.
Hi Anand,
Nice Examples and answers above, unfortunately i never worked on this before, but this
method is using in my new job, i want to learn IRR & ROI in detail with practical. Dear anand pls help me to learn it in a very easy and quick way, new job is in OIL & GAS Sector. Request for quick reply
Thnx in advance
Moin Siddiqui
Sir,
Realy your excell tips and macros are very usefull to every one those who are not familiar in excell
thankfull to you
thanking you
If one is calculating the IRR on a project that requires debt capital, in determining the annual net income of the project does one have to include principal repayment on the loan
what is meaning of IRR and how it is calculate. where it is use /
Internal rate of return (IRR) is the rate of return at which the project will generate returns of a PV of 0 or simply at which the NPV of the project will be zero. it is calculated using a test & trail method.Formula is first you will calculate 2 NPVs of projected cashflows of the project using any 2 cost of capital rates. suppose if company’s Own cost of capital is 8% you will calculate the NPV first @ 8% and a second NPV at any rate higher than 8 % …..say 15%.The answer with the higher NPV will result in negative NPV then apply the rates to this formula to get the IRR
Lower rate + (NPV @ Lower rate /(Npv @ Lower rate – Npv @ Higher Rate)) x (Higher rate -Lower rate)
suppose LR = 8%
HR =15 %
NPV @ LR = 1000
NPV @ HR=500
IRR will be 14.5 %
that was really helpful and understandable. can I access daily updates of it on my id.
Suppose a cost of the project is 84,418,780, the annual return is 20,000,000 and the annual growth rate is 20%. What is the IRR and PAY BACK PERIOD?
In the context of a finance company, how to calculate cost of capital of an entity and then compare it with IRR of a loan proposal, to arrive at the break-even rate. Pls. elucidate with an example.