LinkBack URL
About LinkBacks
When you get a loan , the bank will give an interest rate from which you can make a series of payments by choice.
but IRR works the other way. It will look at all the series of payments and then determine the interest rate.
When you get a loan , the bank will give an interest rate from which you can make a series of payments by choice.
but IRR works the other way. It will look at all the series of payments and then determine the interest rate.
As the name suggests IRR is "your" Internal Rate and it can be from the cost of borrowings (bank) + your shareholders expected rate of returns etc
IRR can be different for each department within a company, each company within the same group and between company to company within the same industry
Thread moved to the Tips & Tutorials subforum.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
IRR is the interest rate at which all of the following hold trueOriginally Posted by masterinex
The net present worth of investment is ZERO
The net future value of investment is ZERO
The benefit to cost ratio of investment is ONE
Now when you are borrowing money, the present value of your payments must equal the principal amount and IRR is the rate at which discounted payments is the same as the principal
You spoke of using Excel IRR function for your loan however Excel RATE would have given you the same IRR
For your data from spreadsheet IRR function as follows
=IRR( { -400, 100, 100, 100, 100, 100 } )
gives an IRR of 7.931%
Now since the payments are in uniform amount, we can use RATE function to find the same IRR as follows
=RATE( 5, -100, 400 )
gives an IRR of 7.931%
Use of IRR is not confined to capital expenditure decisions, almost any rate of investment would qualify for the term IRR
If you are thinking of opening a savings account at the bank then the quoted annual interest rate say 4% on your savings is also your IRR for the investment since at this rate the future worth of your periodic deposits is the amount bank pays you at maturity
If you are borrowing money from bank as you say in amount of $400 then the present value of 5 payments in amount of $100 is the same as the principal amount when such payments are discounted at the IRR
For further readings about use of finance formulas follow along to finance-formulas.com
Last edited by MoneyMaker; 06-04-2013 at 01:45 AM. Reason: fixed a typo
Let me explain how the Mary goes round in circles
When you are borrowing from the bank, it the bank sets the lending rate in your case it was 7.931%.
You pay the bank interest on such payments at this rate and when such payments are discounted at IRR the present value of these 5 payments of $100 is the same as the amount $400 that the bank lent you
Here the bank paid you $400 up front and you paid back $500 which when discounted at IRR is equal to $400
T CF PVIF @ 7.931 PV 0 -400 1 -400 1 100 0.9265 92.65 2 100 0.8584 85.84 3 100 0.7954 79.54 4 100 0.7369 73.69 5 100 0.6828 68.28 -- -- NPV 0
Now the same bank offers you to open a savings account and offers you an interest rate of 5% on your deposits. This 5% is the rate at which the same bank is borrowing money from you. Now you are the lender and the bank is the borrower but irony of the story is that the borrower is dictating the terms at which he is willing to pay you interest. And this borrowing rate must be lower than the bank's lending rate for the bank to stay afloat
T CF FVIF @ 5% FV 0 -100 1.2763 -127.63 1 -100 1.2155 -121.55 2 -100 1.1576 -115.76 3 -100 1.1025 -110.25 4 -100 1.05 -105 5 0 0 580.19 -- -- NPV 0
Now you lent $100 each for 5 terms to the bank on which bank paid you interest and at the end paid you back an amount of $580.19 thus it paid you an interest of $80.19 on your lending.
But as we will now see that you actually did not lent the bank an amount of $500 since $100 payments occurred at different time periods thus the $100 paid in term 4 does not have the same value as the $100 you lent in time period 0. To see how much you actually lent to the bank and how much money bank paid you bank in today's dollars, we will now discount the series of cash flows as follows
T CF PVIF @ 5% PV 0 -100 1 -100 1 -100 0.9524 -95.24 2 -100 0.907 -90.7 3 -100 0.8638 -86.38 4 -100 0.8227 -82.27 5 580.19 0.7835 454.6 -- -- NPV 0
As you can see that you lent an amount of $454.60 to the bank and the present value of money $580.19 that bank paid you at the end is also worth $454.6 in terms of present
Last edited by MoneyMaker; 06-04-2013 at 03:03 PM. Reason: fixed typos
how come there is an entry with 580.19 for the last table . Does that mean a positive cash flow of 580.19 $ , means im getting 580.19 $ ? does NPV always add to zero for cashflows ?
Let me put the cash flows for savings deposits in an NPV equation form.
You have five periodic deposits of $100 and then a cash inflow in amount of $580.19 (loan + interest) paid by the bank.
Cf0 PVIF(i%, t) + Cf1 PVIF(i%, t) + Cf2 PVIF(i%, t) + Cf3 PVIF(i%, t) + Cf4 PVIF(i%, t) + Cf5 PVIF(i%, t) = NPV
-100 PVIF(5%, 0) - 100 PVIF(5%, 1) - 100 PVIF(5%, 2) - 100 PVIF(5%, 3) - 100 PVIF(5%, 4) + 580.19 PVIF(5%, 5) = 0
-100 (1) - 100 (0.9524) - 100 (0.907) - 100 (0.8638) - 100 (0.8227) + 580.19 (0.7835) = 0
-100 -95.24 -90.7 -86.38 -82.27 + 454.60 = 0
-454.60 + 454.60 = 0
0 = 0
Alternatively since all payments are in equivalent amount we may make use of time value of money equation to find the present value of cash flow as follows.
PV + PMT (1+i% * type) PVIFA(i%, n) + FV PVIF(i%, n) = 0
PV - 100 (1+5% * 1) PVIFA(5%, 5) + 580.19 PVIF(5%, 5) = 0
PV - 100 (1.05) (4.32947) + 580.19 (0.7835) = 0
PV - 100 (4.5460) + 454.60 = 0
PV - 454.60 + 454.60 = 0
PV = 454.60 - 454.60
PV = 0
Yes the net present value of periodic payments and it's terminal value must equal zero.
Last edited by MoneyMaker; 06-24-2013 at 01:35 AM. Reason: removed extra text
1) Loan from your perspective
400 -100 -100 -100 -100 -100
2) The same loan from bank's perspective
-400 100 100 100 100 100
In both of the above situations, the TVM equation looks as follows
PV + PMT (1+i% * type) PVIFA(i%, n) + FV PVIF(i%,n) = 0
Here we are finding present value of payments and terminal value
So (1) looks as follows
400 - 100 (1+i% * type) PVIFA(i%, n) + 0 = 0
And (2) looks as follows
-400 + 100 (1+i% * type) PVIFA(i%, n) + 0 = 0
The net present value for both (1) and (2) is ZERO
3) Savings account from your perspective
-100 -100 -100 -100 -100 580.19
4) The same saving account from bank's perspective
100 100 100 100 100 -580.19
In both of the above situations, the TVM equation looks as follows
PV FVIF(i%,n) + PMT (1+i% * type) FVIFA(i%, n) + FV = 0
Here we are finding future value of payments and initial cash flow
So (3) looks as follows
0 - 100 (1+i% * type) FVIFA(i%, n) + 580.19 = 0
And (4) looks as follows
0 + 100 (1+i% * type) FVIFA(i%, n) - 580.19 = 0
The net future value for both (3) and (4) is ZERO
The present value and future value are the two sides of the same coin. When cash is going out of one hand it is coming into another hand.
This is kind of like good and evil been suggested by some as two sides of the same coin.
Think of Google who calls itself "Epitome of GOOD" and their motto says "DO NO EVIL"
But then when you dig deeper into what this beast Google actually practices, you would come to see the reflections of GOOD and EVIL in the same MIRROR.
Last edited by MoneyMaker; 06-24-2013 at 03:22 AM. Reason: highlighted text
In theory the net present value should always add to zero for cash flows, but in reality this may not materialize
You see the expected future gains are exposed to risks such as banks on the road to bankruptcy or in certain cases such as the Cyprus bank crisis in Spring of 2013 may lead to loss in expected incomes. Thus forcing the banks to take a percentage of the gains to pay it's or the country's bills to secure a bail out loan.
I know Southern Europeans are mostly whites but not all white males are created equal so was the impression I came away with the way Euro Group president Jeroen Dijsselbloem told off the Cypriot President who went looking for a hand out in Brussels
Coming back to the topic and staying with example where you made 5 deposits in amount of $100 this time a Laiki bank in Cyprus. The bank promised to pay you a yield of 5%.
Had the bank kept it's promise you should have been paid an amount of $580.19 at the time of maturity
But when you went to the bank at the end of 5th year, you were told by the bank that a hair cut of 6.75% will be imposed on your savings as the terms of the Cyprus Bail out deal demanded by the Troika
Thus you had to accept a lower than expected amount of $541.03 (or 93.25% of the actual amount)
With this amount your net present value calculations now look this way
PV + PMT (1+i% * type) PVIFA(i%, n) + FV PVIF(i%, n) = 0
PV - 100 (1+5% * 1) PVIFA(5%, 5) + 541.03 PVIF(5%, 5) = 0
PV - 100 (1.05) (4.32947) + 541.03 (0.7835) = 0
PV - 100 (4.5460) + 423.91 = 0
PV - 454.60 + 423.91 = 0
PV = 454.60 - 423.91
PV = $30.69
So what does it mean to have a net present value that is greater than zero, well we can now find out the rate of return on this investment (IRR) using the new maturity amount of 541.03
=RATE( 5, -100, 0, 541.03, 1)
IRR = 2.64%
So now your return has fallen down from 5% to 2.64%
In the last post, it was shown how a net present value may not result in zero for a loan due to a hair cut imposed by a bank.
But let us now see the original loan example from a different viewpoint. The examples discussed earlier assumed that all payments will be made as expected thus the net present value of the loan amount and periodic payments will result in zero as shown below.
tadNPV(7.9308%, {400,-100,-100,-100,-100,-100}, 1, 1, 1, 1 )
$0
Here the 7.9308% is the IRR, a discount rate at which the NPV = 0
But in real life, borrowers may not be able to meet the obligations of making full payments or they simply default on loans. There are various reasons for the inability of the borrower to meet the terms of the loan. Some of these include loss of a job, unexpected expenses arising from personal injury of self or another family member or people like me who are careless and get in a habit of excessive gambling thus risking all their savings in hopes of a quick buck.
This leads us to introduce a new element in the net present value calculation called the realization factor RF. It's value may be 0% and above. A value of 0% means inability to make the full payment, a value of 50% would mean ability to make only half of the actual payment. A value of 100% means making the actual payment in full. A value above 100% would mean paying in excess of original payment.
With this in mind, let us now see what happens when a borrower who receive a loan of $400 from a bank and agreed to make 5 periodic payments in amount of $100.
But as events unfolded, the borrower was only able to make part of the actual payments beginning in period 2 by only paying 90% of the $100. And in the subsequent periods the payments dropped further to 80%, 50%, and 25% of the actual payment.
In this case, we can find the net present value using Excel NPV function by passing it values for not only the loan and expected payments but also values for realization factor RF.
tadNPV(7.9308%, {400, -100, -100, -100, -100, -100}, {100%, 100%, 90%, 80%, 50%, 25%}, 1, 1, 1, 1 )
NPV = $112.54
As you can see the bank is left unpaid a present value of $112.54 when the borrower was unable to make the full payments in amount of $100.
This results in an lower IRR than the one that the bank expected on it's investment of $400.
=IRR( {400, -100, -90, -80, -50, -25} )
IRR = -5.7546%
As you can see the IRR for the loan was 7.9038% from the perspective of the bank, but when the borrower was unable to make full payments the IRR for bank dropped to -5.7546%
A negative IRR indicating that the bank took a loss on this investment.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks