Power Pivot DAX formula for interest payable when loan is partially repaid
$10-30 USD
Concluído
Publicado há mais de 9 anos
$10-30 USD
Pago na entrega
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?
Grateful for any help
We are group of five people who work as a team.
We have skills in Accounting, Microsoft office, and Tally software.
we ensure efficient work done.
Our Slogan: We Mean Business, not Excuses.
Thank you.
More than 2 years’ experience in data entry , microsft excel, Microsoft world , internet browsing . I am Detail oriented, hard worker and fast learner, I can manage also big projects. I am Professional accounting student.