IRR Modeling...

Post Reply
Christian
Posts: 46
Joined: Wed Nov 18, 2009 8:43 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003

IRR Modeling...

Post 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
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: IRR Modeling...

Post 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
Technical Director
www.infocat.co.uk
Christian
Posts: 46
Joined: Wed Nov 18, 2009 8:43 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003

Re: IRR Modeling...

Post 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!
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: IRR Modeling...

Post 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
Technical Director
www.infocat.co.uk
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: IRR Modeling...

Post by Wim Gielis »

Hi Christian

Maybe this topic can give you example TI code:

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

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Christian
Posts: 46
Joined: Wed Nov 18, 2009 8:43 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003

Re: IRR Modeling...

Post 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.
Post Reply