Question:
I made a small investment plan, with a target ROI. It looks roughly like this below.
- Initial investment 5000 euro
- Monthly add-on investment 500 euro
- Target ROI 8% (annualized)
For this I can simply use the Future Value calculation in Excel. And you would get something like what shows up in the ‘Target Value column.
| Terms | Add-on Investment | Invested | Real value | Target value | |
| apr-17 | 0 | 5000 | 5000 | 5000 | 5000 |
| mei-17 | 1 | 500 | 5500 | 5525 | 5537 |
| jun-17 | 2 | 500 | 6000 | 6300 | 6077 |
| jul-17 | 3 | 500 | 6500 | 6700 | 6621 |
| aug-17 | 4 | 500 | 7000 | 7316 | 7168 |
Now, I actually want to calculate the ROI of the real-value column. So, basically a compound growth calculation, but with the added complexity of the monthly investments. Any idea how I could do that?
Hi Jeroen,
You can use the XIRR function to calculate CAGR: https://support.office.com/en-us/article/Calculate-a-compound-annual-growth-rate-CAGR-3ccb7cd3-39b3-49ee-8b38-c19972607dfa
Let us know if you get stuck with it.
Mynda
