An individual invests today 6,000€ into a fund. In exchange for that amount, the investor will receive 1,200€ at the end of each year for the next 5 years, plus three additional payments of 500€ at the beginning of the fourth quarter of the first, third and fifth years.
Create a model in Excel to find the IRR of the investment. Express the IRR as an effective annual interest rate.
Guide On Rating System
Vote
To calculate the IRR (Internal Rate of Return) in Excel, you can follow these steps:
Step 1: Set up the initial structure of the cash flow. For each year, you will have an outflow of the initial investment (€6,000) and an inflow of the yearly payment (€1,200). Additionally, in the fourth quarter of the first, third, and fifth years, there will be an additional inflow of €500.
Here is a basic example of how the cash flow could be structured in Excel:
Years Cash Flow
0 -€6,000
1 €1,200
2 €1,200
3 €1,200
4 €1,200 + €500
5 €1,200
5 €1,200 + €500
Step 2: Calculate the IRR using Excel's "IRR" function. The formula would be: "=IRR(B1:B7)". In this case, the "B1:B7" refers to the range of cells that contain the cash flow values.
Step 3: Convert the IRR to an effective annual interest rate. The IRR is typically expressed as a percentage per period. Since the periods in this case are yearly, the IRR can be considered as an effective annual interest rate.
By following these steps, you can calculate the IRR of the investment and express it as an effective annual interest rate.
Got a question with my answer?
Cheapmath.com is completely free!
Asking question is free.
Getting answers is free.
+99