Debt repayment calculation.

[John 27182]

Hi Techies,

I'm trying to write a calculation for a debt repayment item. Can anyone help?

I have a 2 dimensional D-Cube showing the bank balance of a company over time.  The horizontal D-List is a time dimension. The vertical D-List  has the following five items (and calculations).

Opening Balance: @From(Closing Balance)
Purchase:
Repayments:
Net flows: + Purchase + Repayment
Closing Balance: @Feed(;{Opening Balance};{Net flows};)

In the first period the company has a purchase of -5000, but no more purchases.

I want repayment to be a calculated function which repays the debt at 1500 a year, but  doesn't overpay the debt. So for the first 3 periods it will equal 1500. Then it will equal 500. Then it will equal 0 for all future periods.

Is this possible? Thanks for any help people can offer.

	Jan	Feb	Mar	Apr	May	June
Opening balance	0	-5000	-3500	-2000	-500	0
purchase	-5000	0	0	0	0	0
repayment	0	1500	1500	1500	500	0
net flow	-5000	1500	1500	1500	500	0
closing balance	-5000	-3500	-2000	-500	0	0

[Rutulian]

Hi John,

There might be a cleverer way of doing this with one of the prebuilt BiFs, but with the items you've already got try defining your repayments item as below.

Repayments:
IF {Opening Balance} < -1500
THEN 1500
ELSE IF {Opening Balance} > -1500 AND {Opening Balance} < 0
THEN -{Opening Balance}
ELSE 0

Do scan through the BiFs in the Analyst BiF examples library first though, some of them have multiple inputs and outputs and one might do this for you.

Let us know how you get on, it's always good to close the loop!

Regards,
Alexis

[John 27182]

Hi Alexis,

Thanks for the reply, but the calculation you suggested doesn't seem to work. In fact this is what I initially tried, it's logical but Cognos doesn't like it. When I write the calculation an error pops up complaining that "D-List cannot be  saved: Circular references detected in Built in Functions".

I think the problem is that {Opening Balance} is an output in our BIF, but {Repayments} is an argument in the BIF. Thus Cognos thinks that there is a
circular reference.

I've tried looking at other BIF and I cannot see anything which works.

Any other suggestions?

Regards,
John.

[Rutulian]

Hi John,

It's inelegant, but you can get it working by creating a new item Repayments' to calculate the value then feed it to the BiF input Repayments with an internal d-link.  The downside is you'll need to run this once for every period as it will only feed one step at a time.

A search on the forums for 'circular' suggests that @FeedParam is specifically designed for overcoming issues like this, but I can't quickly identify if/how this can be used for thresholding - it seems to be more for things that are proportional to the inputs.  Could be worth tangling with, though.

[John 27182]

HI Alexis,

I've tried your solution and it works. Thanks.