I have built a cash flow model and I am having difficulty in calculating my interest payable. The issue is that the interest needs to be calculated on the closing balance of the previous month. I have created a Feed BIF in my Closing balance that creates an Output in my Opening balance. Unfortunately I am unable to use the Output in a calculation for Interest. I think this is due to becoming a circular calculation. Has anybody got a way of getting round this?? I have thought about creating a calculation cube for the interest, but I have to update both cubes for every month and will take ages.
Hi there.
It'd help a lot if you'd attach a small table illustrating desired calculations. Are you doing it in Analyst or Contributor?
Why not use Rate Bif, for ex -- that's just a guess, I don't see desired calc.
Hi, attached is a screen print of the cube I am working with. In order to calculate Closing balance using the opeing balance, reciepts and payments I used the Feed Bif for the output I chose opening balance. The problem with this is that opening balance can't be used in the interest calculation. Hope the attachment helps, if not let me know.
Hm, I just can't get it. What you want to calculate?
Can you add it in a form of "Interest Payable = A+B/C"?
Because if you're doing smth like in wikipedia (http://en.wikipedia.org/wiki/Interest_%28finance%29), then you'd need all those periods&percentages&stuff in the cube.
The way to solve this is create dummy dlist items and move it over by using internal links. You will avoid the circular refence and you can use the bif to calc what you need it to.
Have you solved this issue yet?
I think you can use the Feed Parm BiF to affect the output by using the factor to affect the opening balance in the current month. You can set the param as a arithmetic calculator and then do what you want to the cash flow using the factor field.
I have used this in a few situations and it has proved useful to get around the circular reference.
Have you tried to dummy d-list idea? Sounds like that could also get around the circular reference.
hi guys, havin similar problem. Am using the link to break the circular reference but this causes problem in contributor. Cube only seems to update a certain number of times (around 100) and then stops so the link nolonger works and i'm getting nonsense figures. anyone every come across this? Any suggestions greatly appreciated
"Cube only seems to update a certain number of times (around 100)"
IIRC, the Contributor engine caps iterative calcs (circulars) at 100 executions to avoid runaway processing on the web client (if this is what you are referring to). Been a while since I've looked at it though, so not sure if that is what is happening to you here.
yes i would imagine that this is the problem. do you know if there is any way of changing this setting? thanks for the reply!
I don't know of a way. I believe it may be embeddded in the code itself.
nasty! not what i wanted to hear lol! really need to get this thing working!! thanks for the reply though!
Dont suppose you have any idea of another way to break the circular reference when using the feed BiF. My input line needs to be based on if my opening balance is greater then a certain amount. i cannot do this normally because analyst says it is creating a circular reference. So to reference the opening balance line i created a dummy line that linked the opening balance exactly to it. this is what causing the cube to try and update more then a 100times in contributor. dont suppose anyone has any other idea of how to break the circular reference??
Definitely. You can affect an opening balance by using the feed param BiF.
Set the param to "*", create a factor field that calculates how you need to affect the opening balance to get to a closing balance, multiply that factor by -1, and the "Out" will automatically calculates an affect to the opening balance. In this way, you get around the circular reference by using the BiFs capabilities. Feed param is supported in Contributor (at least 7.3 and 8.1).
Hope that helps. If you might need an example, let me know and I could probably attach something.
Charles,
thanks for the suggestion, and as they say great minds think alike, I have tried this param feed bif b4, unfortunetly doesnt get me around my problem. I probably did explain well. I am trying to calculate opening and closing loan balances. The loan balance is subject to a max of say 1m. what i need to have is a calculation in the "input" line saying :
IF Opening Balance = Max
Then 0
ELSE
Desired Advance.
(very simplified version)
Unfortunatly this cannot be done in either feed or param Bif!
Any ideas?
This should work given your simple example.
________________________________________________
No. IID Item name Format Calc
1 1 Maximum =
2 2 Open =
3 3 Advance =
4 4 Close Subtotal
5 5 Cum Advance BiF
6 6 Logical Open BiF
7 7 Logical Advance Conditional
8 8 Logical Close Conditional
Maximum = 1000000
Open = 100000
Advance = 75000
Close = +Open
+{Cum Advance}
Cum Advance = @Cumul(Advance)
Logical Open = @Lag(1;;{Logical Close})
Logical Advance = IF Close <= Maximum THEN Advance ELSE 0
Logical Close = IF Close <= Maximum THEN Close
ELSE Maximum
____________________________________________________
Let me know if that works for your situation.
Cheers Charles.
Last question, never seen this before?
IF Close <= . Can u explain the <= bit?
The "<=" is the convention the Analyst print to csv documentation uses for "<=".
If you go to the object maintenance screen this is a great place to print documentation directly from an Analyst model.
Let me know if that helps.....keep in touch.
Charlie