Hi,
I'm working on a pet project to keep track of my investments. Periodically I download the data into Excel to calculate tenure, IRR and XIRR which takes time as I have to clean up the data and calculate each of the funds separately. Now I wish to automate some of the steps involving cleaning and calculations using power query. To accomplish the objectives I'm looking for guidance with the development.
To begin with, I receive data in a specific format and after the cleaning up it looks like this;
After the cleaning, I rearranged data for calculations to get the following view;
I repeat the step for all by funds which is time-consuming as entries vary from fund to fund. I need guidance with the replication of the formulas in the power query.
Would truly appreciate all the guidance and input I can get here.
I'm working on a pet project to keep track of my investments. Periodically I download the data into Excel to calculate tenure, IRR and XIRR which takes time as I have to clean up the data and calculate each of the funds separately. Now I wish to automate some of the steps involving cleaning and calculations using power query. To accomplish the objectives I'm looking for guidance with the development.
To begin with, I receive data in a specific format and after the cleaning up it looks like this;
| Scheme or Stock name | Investment date | Investment amount | Current market value |
| Long Term Equity Fund - Growth | 11/03/2015 | ₹ 60,000.00 | ₹ 119,741.00 |
| Long Term Equity Fund - Growth | 01/05/2016 | ₹ 60,000.00 | ₹ 130,626.54 |
| Long Term Equity Fund - Growth | 03/03/2016 | ₹ 60,000.00 | ₹ 126,998.03 |
After the cleaning, I rearranged data for calculations to get the following view;
| Scheme or Stock name | Investment date | Invested | Current market value | XIRR | IRR | Tenure |
| Long Term Equity Fund - Growth | 11/03/2015 | ₹ 60,000.00 | ₹ 119,741.00 | 10% | 8 Years, 0 Months, 1 Days | |
| 01/05/2016 | ₹ 60,000.00 | ₹ 130,626.54 | ||||
| 03/03/2016 | ₹ 60,000.00 | ₹ 126,998.03 | ||||
| 12/02/2023 | ₹ 377,365.56 | |||||
I repeat the step for all by funds which is time-consuming as entries vary from fund to fund. I need guidance with the replication of the formulas in the power query.
Would truly appreciate all the guidance and input I can get here.
