Page 1 of 1

IRR Modeling...

Posted: Wed Feb 10, 2010 1:22 am
by Christian
Hi, Everyone,

I am trying to build a rolling IRR model and wonder if anyone has done this before? There is not an IRR bif like in CP, or a fx like in Excel, within TM1. To circumvent that, I've created my TM1 cube with the necessary fields, am calculating IRR in a slice in Perspectives, and using DBSW to return the value to the IRR% row within the TM1 model. That works, but it doesn't set off the chain reaction of calculations I had hoped.

In short, there are collections, IRR%, open basis, revenue, amortization, and end basis. When collections change, it should change end basis, which in turn changes IRR% for the next period and this sets off a chain reaction until the collections life cycle is over.

I know this is a shot in the dark, but I thought I'd give it a shot.

Cheers,
Christian

Re: IRR Modeling...

Posted: Wed Feb 10, 2010 3:50 pm
by Steve Rowe
http://en.wikipedia.org/wiki/Internal_rate_of_return

Sounds like an interesting project.

Off the top of my head you might want to look at using a TI process rather than a DBS....

If you give a bit more detail about what's broken or not behaving we can try and help.
Cheers

Re: IRR Modeling...

Posted: Wed Feb 10, 2010 7:49 pm
by Christian
Thanks Steve.

Right now, I have excel/Perspectives calculating IRR given a certain schedule and it is working fine. The design forwards the derived IRR% from that current month to the another row (IRR Staging Next Period) in the next month. That's what DBSW reads. This way it should cascade forward and change end basis, which fuels IRR% for the next month...and so on. However, it's like DBSW has to be refreshed.

When I initially set this up and calculated it, several months were correct. I got to month x (not sure exactly how many out) and all of a sudden DBSW wasn't reading the correct value, yet the formula was correct. I copied the formula from the prior cell that worked, and pasted it into the current month (formula is portable), and the same formula I had before now reads the correct value.

Obviously, I need this to work in one keystroke when a change is made.

I'm not sure how to incorporate a process for this, but would love to hear any ideas you might have.

Thanks!

Re: IRR Modeling...

Posted: Wed Feb 10, 2010 8:56 pm
by Steve Rowe
You might want to switch to DBS and DBRs rather than DBRW and DBSW. If you are sending numbers to and from TM1 on the same sheet then that might help.

The other thing that I'm guessing is you have a long sequence of ruled values all depending on some initial inputs. It may be that you are blowing the feeder stack. This can show syptoms like you give, where everything works well for many periods into the future and then just stops working. The system should be complaining about this in the message log of the server.
If you search for feeder stack you'll see some other threads on this.

HTH
Cheers

Re: IRR Modeling...

Posted: Thu Feb 11, 2010 8:31 pm
by Wim Gielis
Hi Christian

Maybe this topic can give you example TI code:

http://forums.olapforums.com/viewtopic. ... 656&p=8641

Wim

Re: IRR Modeling...

Posted: Tue Feb 16, 2010 3:13 pm
by Christian
Thanks Guys, I got sidetracked on something else for a few days, but I'm still working on this. I appreciate the help.