didijaba
Well-known Member
- Joined
- Nov 26, 2006
- Messages
- 511
Hello,
in the Excel data model I need to calculate the interest that becomes payable when the principal of a loan is partially repaid, where: Payable interest = (Total interest accrued to date - Any amount already payable) x Repaid amount / Outstanding amount before repayment The problem is when there are a series of repayments. I am using Power Pivot DAX and I get a circular reference error when trying to subtract the sum of the amounts already payable. I have the repayment number, the total interest accrued to date and the ratio of the repaid amount to the outstanding amount before repayment as three columns of my Excel data model. I think the problem in mathematical terms looks like the following, where Ax is the amount of interest accrued at the date of the xth repayment, and rx is the ratio of repaid amount to outstanding amount before repayment: Repayment number Interest accrued Ratio of amounts Payable interest 1 r1 A1 r1 x A1 2 r2 A2 r2 x (A2-r1A1) 3 r3 A3 r3 x (A3-r2(A2-r1A1)-r1A1) 4 r4 A4 r4 x (A4-r3(A3-r2(A2-r1A1)-r1A1)-r2(A2-r1A1)-r1A1) etc. etc. What would be the formula for a calculated field in Power Pivot that gives the Payable interest given the first three columns? Please look at the attached file, basically I need a calculated column in the DAX data model (Table1) which gives the same result as the yellow F column in the Excel table (interest payable), using only data in columns A to EGrateful for any help
https://www.dropbox.com/s/vidl37sweyxexvm/Sample loan data.xlsx?dl=0
in the Excel data model I need to calculate the interest that becomes payable when the principal of a loan is partially repaid, where: Payable interest = (Total interest accrued to date - Any amount already payable) x Repaid amount / Outstanding amount before repayment The problem is when there are a series of repayments. I am using Power Pivot DAX and I get a circular reference error when trying to subtract the sum of the amounts already payable. I have the repayment number, the total interest accrued to date and the ratio of the repaid amount to the outstanding amount before repayment as three columns of my Excel data model. I think the problem in mathematical terms looks like the following, where Ax is the amount of interest accrued at the date of the xth repayment, and rx is the ratio of repaid amount to outstanding amount before repayment: Repayment number Interest accrued Ratio of amounts Payable interest 1 r1 A1 r1 x A1 2 r2 A2 r2 x (A2-r1A1) 3 r3 A3 r3 x (A3-r2(A2-r1A1)-r1A1) 4 r4 A4 r4 x (A4-r3(A3-r2(A2-r1A1)-r1A1)-r2(A2-r1A1)-r1A1) etc. etc. What would be the formula for a calculated field in Power Pivot that gives the Payable interest given the first three columns? Please look at the attached file, basically I need a calculated column in the DAX data model (Table1) which gives the same result as the yellow F column in the Excel table (interest payable), using only data in columns A to EGrateful for any help
https://www.dropbox.com/s/vidl37sweyxexvm/Sample loan data.xlsx?dl=0