Sometimes in the process of investing in securities you wonder and do not know when you can payback or how to calculate whether the next investment is profitable or not. The following article shows how to use the MIRR function to help you answer the above question.
Instructions for using the MIRR function
Description: Perform the calculation of the internal rate of return adjusted for periodic cash flow. Not only that, but MIRR allows calculating the cost and interest if reinvested.
Syntax: MIRR (values, finance_rate, reinvest_rate) .
Inside:
- values: Include payments, including negative values. This includes initial expenses, loans, etc.
- finance_rate: Interest paid on loans in the cash flow.
- reinvest_rate: Interest received when reinvesting.
Example: Give the following data table:
Calculate:
- Calculate the interest rate of the investment after a period of 3 years.
- Calculate the interest rate of the investment after a period of 5 years.
- Calculate the interest rate of the investment after a period of 5 years and use the reinvestment with the interest rate of 12%.
Perform the problem:
1. Interest rate of the investment after 3 years
In the cell to calculate enter the formula: = MIRR (D5: D8, D11, D12) .
Result:
So after 3 years, the interest rate = -6.6% => The investment after 3 years has no interest.
2. Interest rate after 5 years
In the cell to calculate enter the formula: = MIRR (D5: D10, D11, D12) .
Results after calculation:
So the investment after 5 years is profitable and the interest rate is 11% of the capital.
3. Interest rate received after 5 years with reinvestment receives interest rate of 12%
In the cell to calculate enter the formula: = MIRR (D5: D10, D11.12%) .
Result:
The interest rate after 5 years of reinvestment with the interest rate of 12% = 9% <11% => The effective reinvestment is not high, you should consider.
The above is the calculation and estimation of interest rates after investment and reinvestment. Hope you consider carefully before deciding to invest.
Good luck!