If I bring in [ACH Date] to your fixed calc, then the calculation lines up with your Q4-2018 number for Principal Outstanding, but the other numbers for the remaining quarters do not. However, the ratios are much closer to Principle Outstanding than before...not sure if that's what you're looking for in terms of numbers? Thx, Don
1 of 1 people found this helpful
I believe the issue is in the logic - the outstanding principal is by its nature a running number - i.e. it is a point in time number - - the value you want is the outstanding principal at 3/31, 6/30. 9/30 and 12/31 --- not the sum of the end of month values over the quarter -
you can do that with a conditional statement that looks at the max date by loan by the last month for each quarter (you don't have an end of month entry in your file) -
does that make sense?
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Yes, that mostly makes sense.
What would this conditional statement look like? I've tried a few using IF and DATETRUNC, but am still having trouble getting the principal outstanding amount.
I added an end of month calc (the "EOM") field.
Example WB.twbx 769.7 KB
Did this simple conditional statement and it worked.
IF DATEPART('month',[Month]) = 12 THEN [Principal Outstanding]
ELSEIF DATEPART('month',[Month]) = 9 THEN [Principal Outstanding]
ELSEIF DATEPART('month',[Month]) = 6 THEN [Principal Outstanding]
ELSEIF DATEPART('month',[Month]) = 3 THEN [Principal Outstanding]
I'm glad to help out -
you could also just filter for the 4 EOQ months
Either way it works