Excel Power Pivot DAX formula for interest payable on partial loan pre-payment

Status: Closed
Prize: $50
Entries Received: 3
Winner: treshakhanom

Winner

Contest Brief

I need a DAX calculated column of the data model behind the attached Excel file that will reproduce the calculation of the yellow column of the spreadsheet data. If you open the data model with Power Pivot there will be a calculated column with 1's, and that is where the data should appear. You can use intermediate measures and columns to get to the result. Please post pictures of the data model measures and calculated columns, the contest is sealed so no-one else will see your entry. No macros please.

This result is needed 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 arises when there are a series of repayments, and you need to keep track of the amount already payable. I am using Power Pivot DAX and I get a circular reference error when trying to subtract the sum of the amounts already payable.

You will need Excel 2013 and PowerPivot add-in as well as Power View probably

Happy to provide more information if needed

Recommended Skills

Excel

Winner

Post a Contest Like This

Public Clarification Board

treshakhanom
- 11 years ago
Hello sir you there
- 11 years ago
SrinivasaBagade
- 11 years ago
Hi Please clarify that using power pivot is mandatory or I can use intermediatory sheet and derive the desire result with simple excel formula?
- 11 years ago
1. Contest Holder
  - 11 years ago
  Yes, Power Pivot only please.
  - 11 years ago
Contest Holder
- 11 years ago
Thank you for the question, yes it should be in Power Pivot. I need to use the data for other calculations and I want to avoid manual links etc.
- 11 years ago
SrinivasaBagade
- 11 years ago
Hi Please clarify that using power pivot is mandatory or I can use intermediatory sheet and derive the desire result with simple excel formula
- 11 years ago
treshakhanom
- 11 years ago
Dear sir i am waiting your response,already i send you sample file
- 11 years ago
View 4 more messages
1. sajidmahmud
  - 11 years ago
  Sir you send me one email because that is public place other guyes copy my file,so i can give you sample file personal way
  - 11 years ago
2. Contest Holder
  - 11 years ago
  The contest now has sealed bids, no-one else will see your proposed solution
  - 11 years ago
Contest Holder
- 11 years ago
ok
- 11 years ago
View 2 more messages
1. sajidmahmud
  - 11 years ago
  https://www.freelancer.com/u/sajidmahmud.html
  - 11 years ago
2. sajidmahmud
  - 11 years ago
  Sir i am wait for your project and ready so start job
  - 11 years ago
sajidmahmud
- 11 years ago
Sir i send you image file please if you give me chance i can make final file
- 11 years ago