asadriaz
New Member
- Joined
- Oct 26, 2023
- Messages
- 4
- Office Version
- 2021
- 2019
- 2016
- 2013
- Platform
- Windows
I wan to check profit of a scheme for each investment.
Scheme Feature.
I have designed a schedule for 120 profit for a given investment. Which is working quiet well. I just have to enter Investment date and excel provides me with all 120 profits after accurately after incorporating any necessary changes against interest rate.
To achieve this, I am using a lookup technique along with IF statement.
Due to this applicability of rate has become dependent (dynamic) on Investment date in rate sheet.
Now I have tons of investment with their date and profit number (out of 120) in vertical columns, I want to pick rate for each investment for each profit against an investment in each row.
I did some working and apply lookup and worked the 1 row correctly, but as I drag down to second row, the investment date changes and applicable rate (rate table which is dependent on investment date to accurately apply correct rate according to feature) changes too, the 2nd row gives accurate figure but disrupt the 1st row figure due to formula.
I need a way through which I can calculate result in first row, and convert the result into value and drag the formula in 2nd row to start freshly, and then convert the 2nd row to value to preserve the result and move to 3rd row. And so on.
(can upload file iteself in google drive to share my working if necessary)
Scheme Feature.
- Rate are notified every now and then. (May increase or decrease)
- Profit is paid monthly for 10 years (maturity period) hence total number of profits are 120.
- An investor can take one profit on monthly basis or may collect 6 or 5 profit after corresponding period.
- Tricky Part: If the rate increases, existing investors start to receive increased return, but if the new rate is less than the previously notified rate. Than the existing investor will receive their existing increased rate but new investor will take newly notified rate (decreased one).
I have designed a schedule for 120 profit for a given investment. Which is working quiet well. I just have to enter Investment date and excel provides me with all 120 profits after accurately after incorporating any necessary changes against interest rate.
To achieve this, I am using a lookup technique along with IF statement.
Due to this applicability of rate has become dependent (dynamic) on Investment date in rate sheet.
Now I have tons of investment with their date and profit number (out of 120) in vertical columns, I want to pick rate for each investment for each profit against an investment in each row.
I did some working and apply lookup and worked the 1 row correctly, but as I drag down to second row, the investment date changes and applicable rate (rate table which is dependent on investment date to accurately apply correct rate according to feature) changes too, the 2nd row gives accurate figure but disrupt the 1st row figure due to formula.
I need a way through which I can calculate result in first row, and convert the result into value and drag the formula in 2nd row to start freshly, and then convert the 2nd row to value to preserve the result and move to 3rd row. And so on.
(can upload file iteself in google drive to share my working if necessary)
