I am trying to develop a pivot table that will show the impact on profits that a change in loan interest rates would have over a fiscal year.
Each loan facility has an anniversary date upon which the rate can be changed, I am sourcing the balance, current interest rate, month that the change can occur and other various data from a SQL database via Cognos Impromptu. I have the month that the change can occur linked to the fiscal year layout via VLOOKUP formula and basically have the pivot data sheet showing Interest for a year if no change has occurred and interest before and after the change summed with a column showing the difference between the Interest earned if no change and interest earned with a change.
The Pivot is on a new sheet and I put two cells that the user can input data (Change in interest rate and fiscal month of change) after which they would refresh the table and get the data.
One of the data columns shows based on the month that a change could occur the number of months that would be at the new rate.
Rates can be changed quarterly. I did this by a column showing the month of the allowable change from the maturity date then a column which numbered this month according to the fiscal year via the VLOOKUP. Another column which shows the number of months till the fiscal year end via formula. The interest with change uses this column (months till Fiscal Year End) to work out the interest.
I am being asked to go further to show/track if there were multiple changes during the fiscal. How should I approach this?
Thanks,
Triniirish.
Bookmarks