IRR Modeling...

IRR Modeling...

Postby Christian » Wed Feb 10, 2010 1:22 am

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
Christian
 
Posts: 46
Joined: Wed Nov 18, 2009 8:43 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003

Re: IRR Modeling...

Postby Steve Rowe » Wed Feb 10, 2010 3:50 pm

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
User avatar
Steve Rowe
Site Admin
 
Posts: 1274
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 9.0 9.1 9.5
Excel Version: Nearly all of them

Re: IRR Modeling...

Postby Christian » Wed Feb 10, 2010 7:49 pm

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!
Christian
 
Posts: 46
Joined: Wed Nov 18, 2009 8:43 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003

Re: IRR Modeling...

Postby Steve Rowe » Wed Feb 10, 2010 8:56 pm

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
User avatar
Steve Rowe
Site Admin
 
Posts: 1274
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 9.0 9.1 9.5
Excel Version: Nearly all of them

Re: IRR Modeling...

Postby Wim Gielis » Thu Feb 11, 2010 8:31 pm

Hi Christian

Maybe this topic can give you example TI code:

viewtopic.php?f=3&t=1656&p=8641

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.be ==> 83 TM1 articles and a lot of custom code
Newest blog article: Reworking Excel layout for dimensions
Wim Gielis
MVP
 
Posts: 875
Joined: Mon Dec 29, 2008 6:26 pm
Location: Brussels, Belgium
OLAP Product: TM1
Version: TM1 10.1.1
Excel Version: 2010

Re: IRR Modeling...

Postby Christian » Tue Feb 16, 2010 3:13 pm

Thanks Guys, I got sidetracked on something else for a few days, but I'm still working on this. I appreciate the help.
Christian
 
Posts: 46
Joined: Wed Nov 18, 2009 8:43 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003


Return to Cognos TM1

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 4 guests

Loading