In: Economics
Presuming you will make a $500,000 dollar investment into your firm. The machine that you purchased has a life span of 6 years. At the end of 6 years there will be no residual value left to the machine. If the machine will generate $145,000 annually what is your internal rate of return.
Recalculate your Internal Rate of return if the initial investment is $800,000 and investment will generate the following annual revenue.
Year 1 $150,000
Year 2 $125,000
Year 3 $175,000
Year 4 $150,000
Year 5 $120,000
Year 6 $100,000
Please do the calculations through excel.
If the financial institution charges you 4.2% interest should you make the investment? If you are making 1 payment annually during the 6 years what is your annual payment at an interest rate of 4.2%?
We will use IRR function in excel to calculate ROR in excel
Years | Cash flow |
0 | -500000 |
1 | 145000 |
2 | 145000 |
3 | 145000 |
4 | 145000 |
5 | 145000 |
6 | 145000 |
IRR | 18.6% |
Years | Cash flow |
0 | -800000 |
1 | 150000 |
2 | 125000 |
3 | 175000 |
4 | 150000 |
5 | 120000 |
6 | 100000 |
IRR | 0.7% |
If financial institutions charge 4.2% interest rate then we should make the investment in the first case as ROR is greater than the interest rate and we should not make the investment in the second case as ROR is less than the interest rate.
We will use PMT function in excel to calculate annual payment.
investment | 500000 |
tenure | 6 |
interest rate | 4.20% |
Annual payment | 96,002.89 |
investment | 800000 |
tenure | 6 |
interest rate | 4.20% |
Annual payment |
1,53,604.62 |
Showing formula in excel
Years | Cash flow |
0 | -500000 |
1 | 145000 |
2 | 145000 |
3 | 145000 |
4 | 145000 |
5 | 145000 |
6 | 145000 |
IRR | =IRR(B2:B8) |
Years | Cash flow |
0 | -800000 |
1 | 150000 |
2 | 125000 |
3 | 175000 |
4 | 150000 |
5 | 120000 |
6 | 100000 |
IRR | =IRR(B12:B18) |
investment | 500000 |
tenure | 6 |
interest rate | 0.042 |
Annual payment | =PMT(B23,B22,-B21) |
investment | 800000 |
tenure | 6 |
interest rate | 0.042 |
Annual payment | =PMT(B28,B27,-B26) |